Oracle 命令例记

--データ表領域作成
CREATE TABLESPACE test_data LOGGING DATAFILE 'C:\app\TEST_DATA' SIZE 300M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--インデックス表領域作成
CREATE TABLESPACE test_index LOGGING DATAFILE 'C:\app\TEST_INDEX' SIZE 100M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--一時表領域作成
CREATE TEMPORARY TABLESPACE test_tmp TEMPFILE 'C:\app\TEAP_TMP' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

---ユーザ作成
CREATE USER TESTDBA PROFILE "DEFAULT" IDENTIFIED BY "testpass"
    DEFAULT   TABLESPACE "test_data"
    TEMPORARY TABLESPACE "test_tmp"
    QUOTA UNLIMITED ON "test_data"
    QUOTA UNLIMITED ON "test_index"
    ACCOUNT UNLOCK
;
(修正時：ALTER USER TEAPDBA DEFAULT TABLESPACE test_data TEMPORARY TABLESPACE test_tmp;)
---権限付与

--権限付与
GRANT connect, resource to TESTDBA;
GRANT DBA to TEAPDBA;
GRANT UNLIMITED TABLESPACE TO TEAPDBA ;
(
GRANT ALTER SESSION TO TEAP1;
GRANT ALTER SESSION TO TEAP;
GRANT CREATE SESSION TO TEAP;
GRANT CREATE TABLE TO TEAP;
GRANT CREATE SYNONYM TO TEAP;
GRANT CREATE VIEW TO TEAP;
GRANT CREATE SEQUENCE TO TEAP;
GRANT CREATE PROCEDURE TO TEAP;
GRANT CREATE TYPE TO TEAP;
GRANT CREATE TRIGGER TO TEAP;
GRANT CREATE DATABASE LINK TO TEAP;
)

---表作成
CREATE TABLE MasterTBL.MST_test(
    Divi number(4,0) DEFAULT 0 NOT NULL, -- 区分
    No varchar2(10) DEFAULT '', -- 番号
    Name varchar2(20 CHAR) DEFAULT '', -- 名称
    Code char(4) DEFAULT '', -- コード
    MakeDateTime DATE DEFAULT sysdate, -- 作成日
  CONSTRAINT PK_MST_OperatorContent PRIMARY KEY ( Divi,No )
);

  COMMENT ON COLUMN MasterTBL.MST_test.Divi IS '区分';
  COMMENT ON COLUMN MasterTBL.MST_test.No IS '番号';
  COMMENT ON COLUMN MasterTBL.MST_test.Name IS '名称';
  COMMENT ON COLUMN MasterTBL.MST_test.Code IS 'コード';
  COMMENT ON COLUMN MasterTBL.MST_test.MakeDateTime IS '作成日';

CREATE UNIQUE INDEX "MasterTBL"."PK_MST_test" ON "MasterTBL"."MST_test" ("Divi", "No") ;

CREATE OR REPLACE SYNONYM "USER1"."TABLENAME1" FOR "USER2"."TABLE2";

CREATE SEQUENCE MasterTBL.MST_test.No MINVALUE 1 MAXVALUE 9999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE ;

CREATE OR REPLACE
TRIGGER MasterTBL.MST_test_NO_TRIGGER
BEFORE INSERT
ON MasterTBL.MST_test
FOR EACH ROW
BEGIN
  IF :NEW.No IS NULL THEN
    SELECT No.NEXTVAL INTO :NEW.No FROM DUAL;
  END IF;
END;

ALTER 表字段内容：

ALTER TABLE MasterTBL.MST_test ADD Datetest varchar2(8) DEFAULT '';
COMMENT ON COLUMN MasterTBL.MST_test.Datetest IS '测试日期';
(ALTER TABLE MasterTBL.MST_test DROP COLUMN Datetest;)

变更数据库文字类型：

================================================================================================================
SQL> select * from nls_session_parameters;
SQL> select * from nls_database_parameters where parameter like 'NLS%CHARAC%';
PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8

shutdown immediate;     
startup mount;
alter session set sql_trace=true;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use AL32UTF8;
//alter database character set internal_use AL16UTF16;
alter session set sql_trace=false;
shutdown immediate;
startup;
===============================================================================================================

select OWNER,TABLE_NAME,TABLESPACE_NAME from  dba_tables where owner ='MASTERTBL';

update TestTable.TestValue set AttrVal='1' where CategoryCode = '00' and AttrKey = '0'；

INSERT INTO Test_Table (column1, column2) values ('4S',4);

truncate table TestDBA.TestTable；