DM8 数据库对象管理

一、用户

1.1 用户定义

– 创建 test 用户

CREATE USER IF NOT EXISTS test
IDENTIFIED BY Test_123 HASH WITH SHA512 SALT
PASSWORD_POLICY 15
ACCOUNT UNLOCK
NOT READ ONLY
PROFILE DEFAULT
NOT_ALLOW_IP "199.198.12.13" NOT_ALLOW_DATETIME SUN "1:00:00" to SUN "2:00:00" DEFAULT TABLESPACE tbs_test
DEFAULT INDEX TABLESPACE tbs_idx_test
QUOTA UNLIMITED ON tbs_test
QUOTA UNLIMITED ON tbs_idx_test ;

1.2 修改用户

– 设置用户密码过期

SQL> ALTER USER TEST PASSWORD EXPIRE;
executed successfully
used time: 19.286(ms). Execute id is 620. 
SQL>

– 修改新密码并保留当前老密码,丢弃老密码

SQL> ALTER USER TEST IDENTIFIED BY Test_123 RETAIN CURRENT PASSWORD;
executed successfully
used time: 13.841(ms). Execute id is 1801. 
SQL> connect test/Test_123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 17.501(ms)
SQL> connect test/Zgw_1234
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 11.244(ms)
SQL> alter user test DISCARD OLD PASSWORD;
executed successfully
used time: 34.237(ms). Execute id is 2201. 
SQL> connect test/Zgw_1234
[-2501]:Invalid username or password. 
SQL> connect test/Test_123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 19.164(ms)
SQL>

– 锁定用户

ALTER USER TEST ACCOUNT LOCK;
ALTER USER TEST ACCOUNT UNLOCK;

– 赋予用户 TEST2 代理权限，使用户 TEST2 可以认证登录用户 TEST1

ALTER USER TEST1 GRANT CONNECT THROUGH TEST2;
connect TEST2/PWD AS TEST1;

– 修改密码输入错误次数为无限制

ALTER USER TEST LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

– 修改用户默认模式（新 session 生效，当前 session 通过

set schema test1
alter user test on schema test1;

1.3 删除用户

– 删除用户如果用户拥有对象则失败

SQL> DROP USER IF EXISTS TEST ;
executed successfully
used time: 169.284(ms). Execute id is 2601. 
SQL>

– 删除用户及其拥有的数据库对象

SQL> DROP USER IF EXISTS TEST CASCADE;
executed successfully
used time: 169.284(ms). Execute id is 2601.
SQL>

二、模式

– 创建用户

SQL> CREATE USER IF NOT EXISTS test IDENTIFIED BY Test_123;
executed successfully
used time: 12.575(ms). Execute id is 2602.

– 创建模式指定所属用户

SQL> CREATE SCHEMA test1 AUTHORIZATION test;
executed successfully
used time: 8.078(ms). Execute id is 2603.

– 不指定模式创建表 T1

[dmdba@dm-standalone ~]$ disql test/Test_123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 18.179(ms)
disql V8
SQL> create table t1(id int);
executed successfully
used time: 29.944(ms). Execute id is 3301.

– 指定模式 TEST1 创建表 T1

SQL> create table test1.t1(id int);
executed successfully
used time: 8.597(ms). Execute id is 3303.

– 不指定模式 T1 表插入数据

SQL> insert into t1 values(1);
affect rows 1
used time: 3.495(ms). Execute id is 3304. 
SQL> commit;
executed successfully
used time: 4.148(ms). Execute id is 3305.

– 指定模式 TEST1 的 T1 表插入数据

SQL> insert into test1.t1 values(2);
affect rows 1
used time: 4.584(ms). Execute id is 3306. 
SQL> commit;
executed successfully
used time: 14.696(ms). Execute id is 3307.

– 不指定模式默认查询为用户名同名模式 TEST 下的 T1 表

SQL> select * from t1;
LINEID ID
---------- ----------- 1 1
used time: 0.376(ms). Execute id is 3308.

– 指定模式 TEST1 查询 T1 表数据

SQL> SELECT * FROM test1.t1;
LINEID ID
---------- ----------- 1 2
used time: 5.054(ms). Execute id is 3314.

– 查看两个 T1 表分别属于哪个模式

SQL> SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME='T1';
LINEID OWNER TABLE_NAME
---------- ----- ---------- 1 TEST T1
2 TEST1 T1
used time: 40.180(ms). Execute id is 3313.

– 普通删除包含对象的模式报错

SQL> drop schema TEST1;
drop schema TEST1;
[-5001]:Error in line: 1
Not empty schema [TEST1]. used time: 2.633(ms). Execute id is 0.

– 级联删除包含对象的模式成功

SQL> drop schema TEST1 cascade;
executed successfully
used time: 109.738(ms). Execute id is 3315.

三、表空间

3.1 创建表空间

– 创建表空间

create tablespace if not exists tbs2
datafile 'tbs2.dbf' size 100 autoextend on next 10 maxsize 1000
ENCRYPT WITH OPENSSL_SM4_OFB_V1 by "Dameng_123" WITH HUGE PATH '/data/DM/HTBS2';

3.2 修改表空间

– 表空间脱机

SQL> alter tablespace tbs2 offline;
executed successfully
used time: 22.350(ms). Execute id is 4104.

– 修改表空间数据文件名称（必须在脱机状态下操作）

SQL> alter tablespace tbs2 rename datafile 'tbs2.dbf' to 'tbs2_1.dbf';
executed successfully
used time: 00:00:02.033. Execute id is 4105.

– 表空间联机

SQL> alter tablespace tbs2 online;
executed successfully
used time: 15.873(ms). Execute id is 4106.

– 增加数据文件

SQL> alter tablespace tbs2 add datafile 'tbs3.dbf' size 64;
executed successfully
used time: 38.435(ms). Execute id is 4107.

– 增加 HUGE 路径

SQL> alter tablespace tbs2 add huge path '/data/DM/HTBS3';
executed successfully
used time: 9.673(ms). Execute id is 4108.

– 删除数据文件

SQL> alter tablespace tbs2 drop datafile 'tbs3.dbf';
executed successfully
used time: 15.618(ms). Execute id is 4109.

– 修改表空间名称

SQL> alter tablespace tbs2 rename to tbs2_1;
executed successfully
used time: 19.135(ms). Execute id is 4110. 
SQL>

3.3 删除表空间

SQL> drop tablespace if exists tbs2;
executed successfully
used time: 2.107(ms). Execute id is 4111.
SQL>

3.4 失效文件处理

– 创建表空间

SQL> create tablespace if not exists tbs4
2 datafile 'tbs4.dbf' size 100 autoextend on next 10 maxsize 1000
3 ENCRYPT WITH OPENSSL_SM4_OFB_V1 by "Dameng_123" 4 WITH HUGE PATH '/data/DM/HTBS4' ;
executed successfully
used time: 51.828(ms). Execute id is 4112. 
SQL>

– 添加数据文件

SQL> alter tablespace tbs4 add datafile 'tbs3.dbf' size 64;
executed successfully
used time: 28.110(ms). Execute id is 4113. 
SQL>

– OS 级删除数据文件

cp /data/DM/tbs3.dbf /data/DM/bak/
rm -f /data/DM/tbs3.dbf

– 查看文件状态

used time: 22.251(ms). Execute id is 4501. 
SQL> select PATH,STATUS$ from v$datafile;
LINEID PATH STATUS$
---------- ------------------------- ----------- 
1 /data/DM/SYSTEM.DBF 1
2 /data/DM/tbs_test.dbf 1
3 /data/DM/tbs_idx_test.dbf 1
4 /data/DM/tbs4.dbf 1
5 /data/DM/tbs3.dbf 0
6 /data/DM/MAIN.DBF 1
7 /data/DM/ROLL.DBF 1
8 /data/DM/TEMP.DBF 1
9 /data/DM/tbs2_1.dbf 1
9 rows got
used time: 5.433(ms). Execute id is 4502.

– OS 级备份文件拷贝

cp /data/DM/bak/tbs3.dbf /data/DM/tbs3.dbf

– 失效文件检查

SQL> SP_FILE_SYS_CHECK ();
DMSQL executed successfully
used time: 0.288(ms). Execute id is 4503.

– 恢复前准备

SQL> SP_TABLESPACE_PREPARE_RECOVER('TBS4');
DMSQL executed successfully
used time: 7.931(ms). Execute id is 4504.

– 恢复文件

SQL> SP_TABLESPACE_RECOVER('TBS4');
DMSQL executed successfully
used time: 7.931(ms). Execute id is 4504.

– 恢复后查看

SQL> select PATH,STATUS$ from v$datafile;
LINEID PATH STATUS$
---------- ------------------------- ----------- 
1 /data/DM/SYSTEM.DBF 1
2 /data/DM/tbs_test.dbf 1
3 /data/DM/tbs_idx_test.dbf 1
4 /data/DM/tbs4.dbf 1
5 /data/DM/tbs3.dbf 1
6 /data/DM/MAIN.DBF 1
7 /data/DM/ROLL.DBF 1
8 /data/DM/TEMP.DBF 1
9 /data/DM/tbs2_1.dbf 1
9 rows got
used time: 5.433(ms). Execute id is 4502.

3.5 查看表空间和数据文件关系

SQL> SELECT ts.NAME, df.PATH FROM V$TABLESPACE AS ts, V$DATAFILE AS df WHERE
ts.ID = 2 df.GROUP_ID;
LINEID NAME PATH
---------- ------------ ------------------------- 
1 SYSTEM /data/DM/SYSTEM.DBF
2 TBS2_1 /data/DM/tbs2_1.dbf
3 TEMP /data/DM/TEMP.DBF
4 ROLL /data/DM/ROLL.DBF
5 MAIN /data/DM/MAIN.DBF
6 TBS4 /data/DM/tbs3.dbf
7 TBS4 /data/DM/tbs4.dbf
8 TBS_IDX_TEST /data/DM/tbs_idx_test.dbf
9 TBS_TEST /data/DM/tbs_test.dbf
9 rows got
used time: 12.567(ms). Execute id is 1228. 
SQL>

四、表

4.1 创建表

– 创建表

SQL> create table if not exists table1
2 (
3 id int IDENTITY(1,1) cluster primary key, 
4 name varchar(20) not null default 'a' , 
5 phone varchar(11) not null default '99999999999', 
6 ctime timestamp(6) default now ON UPDATE now
7 )STORAGE(
8 INITIAL 5, 
9 MINEXTENTS 5, 
10 NEXT 2, 
11 FILLFACTOR 85);
executed successfully
used time: 31.459(ms). Execute id is 4514. 
SQL>

– 创建外部表
– 编写数据文件

[dmdba@dm-standalone DM]$ vim /data/DM/data.txt
a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234
|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15

– 编写控制文件

[dmdba@dm-standalone DM]$ vim /data/DM/ctrl.txt
LOAD DATA
INFILE 'data.txt' INTO TABLE EXT
FIELDS '|'

– 创建目录对象如下：

[dmdba@dm-standalone DM]$ disql sysdba/Dameng_123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 16.128(ms)
disql V8
SQL> CREATE OR REPLACE DIRECTORY "EXTDIR" AS '/data/DM';
executed successfully
used time: 7.594(ms). Execute id is 4701. 
SQL>

– 建表语句：

SQL> CREATE EXTERNAL TABLE EXT (
2 L_CHAR CHAR(1), 
3 L_CHARACTER CHARACTER(3), 
4 L_VARCHAR VARCHAR(20), 
5 L_NUMERIC NUMERIC(6,2), 
6 L_DECIMAL DECIMAL(6,2), 
7 L_DEC DEC(6,2), 
8 L_MONEY DECIMAL(19,4), 
9 L_BIT BIT, 
10 L_BOOL BIT, 
11 L_BOOLEAN BIT, 
12 L_INTEGER INTEGER, 
13 L_INT INT, 
14 L_BIGINT BIGINT, 
15 L_TINYINT TINYINT, 
16 L_BYTE BYTE, 
17 L_SMALLINT SMALLINT, 
18 L_BINARY BINARY, 
19 L_VARBINARY VARBINARY, 
20 L_FLOAT FLOAT, 
21 L_DOUBLE DOUBLE, 
22 L_REAL REAL, 
23 L_DATE DATE, 
24 L_TIME TIME, 
25 L_TIMESTAMP TIMESTAMP,
26 L_INTERVAL INTERVAL YEAR
27 )FROM DEFAULT DIRECTORY EXTDIR LOCATION ('ctrl.txt');
executed successfully
used time: 11.080(ms). Execute id is 4702.

– 查看结果

SQL> SELECT * FROM EXT;
LINEID L_CHAR L_CHARACTER L_VARCHAR L_NUMERIC L_DECIMAL L_DEC
L_MONEY L_BIT L_BOOL L_BOOLEAN L_INTEGER L_INT L_BIGINT
L_TINYINT
---------- ------ ----------- ------------ --------- --------- ----- ------- ----- ------ --------- -----------
----------- -------------------- ----------- L_BYTE L_SMALLINT L_BINARY L_VARBINARY L_FLOAT
L_DOUBLE L_REAL L_DATE L_TIME
----------- ----------- -------- ----------- ------------------------- -------------------------
------------------ ---------- -------- L_TIMESTAMP L_INTERVAL
-------------------------- --------------------- 
1 a abc varchar_data 12.34 12.34 12.34 12.34
0 1 1 1234 1234 1234 100
11 1234 0x01 0x01
1.420000000000000E+01 1.210000000000000E+01 1.2100000E+01
1999-10-01 09:10:21
2002-12-12 00:00:00.000000 INTERVAL '15' YEAR(2)
used time: 53.704(ms). Execute id is 4703. SQL>

– 控制文件样例（复杂）

OPTIONS(
DATA = 'quan.txt' ERRORS = 5
BADFILE = 't1.bad' LOG = 't1.log' NULL_STR = ' ffff ' SKIP = 0
CHARACTER_CODE = 'utf-8' )
LOAD DATA
INFILE 'quan.txt' STR x '0A' BADFILE 'test1.bad' INTO TABLE fldr1
FIELDS TERMINATED BY '||

– 创建列存表

SQL> CREATE HUGE TABLE t_huge
2 (
3 o_orderkey INT, 
4 o_custkey INT, 
5 o_orderstatus CHAR(1), 
6 o_totalprice FLOAT, 
7 o_orderdate DATE, 
8 o_order111 CHAR(15), 
9 o_clerk CHAR(15), 
10 o_shippriority INT, 
11 o_comment VARCHAR(79) STORAGE(stat none)
12 )STORAGE(SECTION(65536) , FILESIZE(64), WITH DELTA, ON main) COMPRESS LEVEL 9 FOR 'QUERY HIGH' (o_comment);
executed successfully
used time: 20.006(ms). Execute id is 4705. 
SQL>

– 分区表
–范围分区

SQL> CREATE TABLE T_RANGE(
2 caller CHAR(15), 
3 callee CHAR(15), 
4 time DATETIME, 
5 duration INT
6 )
7 PARTITION BY RANGE(time)(
8 PARTITION p1 VALUES LESS THAN ('2018-04-01'), 
9 PARTITION p2 VALUES LESS THAN ('2018-07-01'), 
10 PARTITION p3 VALUES EQU OR LESS THAN ('2018-10-01'), 
11 PARTITION p4 VALUES LESS THAN (MAXVALUE));
executed successfully
used time: 25.016(ms). Execute id is 4707. 
SQL>

–HASH 分区

SQL> CREATE TABLE T_HASH(
2 caller CHAR(15), 
3 callee CHAR(15), 
4 time DATETIME, 
5 duration INT
6 )
7 PARTITION BY HASH(duration) PARTITIONS 4;
executed successfully
used time: 68.306(ms). Execute id is 4706. 
SQL>

–LIST 分区

SQL> CREATE TABLE T_LIST(
2 caller CHAR(15), 
3 callee CHAR(15),
4 time DATETIME, 
5 duration INT
6 )
7 PARTITION BY LIST(caller) (
8 PARTITION p1 VALUES('aa'), 
9 PARTITION p2 VALUES('bb'), 
10 PARTITION p3 VALUES('cc'), 
11 PARTITION p4 VALUES('dd'), 
12 PARTITION p5 VALUES(DEFAULT));
executed successfully
used time: 12.645(ms). Execute id is 4710. 
SQL>

4.2 修改表

– 修改表
– 增加列

SQL> alter table table1 add column addr varchar(30) not null default 'aaa';
executed successfully
used time: 93.047(ms). Execute id is 108. 
SQL>

– 修改列类型

SQL> alter table table1 modify addr varchar(50);
executed successfully
used time: 18.543(ms). Execute id is 1003.

– 删除列

SQL> alter table table1 drop column addr cascade;
executed successfully
used time: 39.572(ms). Execute id is 1004.

– 修改列名称

SQL> alter table table1 rename column name to name1;
executed successfully
used time: 25.530(ms). Execute id is 1005. 
SQL> desc table1
LINEID NAME TYPE$ NULLABLE
---------- ----- ----------- -------- 
1 ID INTEGER N
2 NAME1 VARCHAR(50) N
3 PHONE VARCHAR(11) N
4 CTIME DATETIME(6) Y
used time: 1.846(ms). Execute id is 1006. 
SQL>

– 修改列允许为空

SQL> alter table table1 alter column name1 set null ;
executed successfully
used time: 14.160(ms). Execute id is 1007. 
SQL> desc table1
LINEID NAME TYPE$ NULLABLE
---------- ----- ----------- -------- 
1 ID INTEGER N
2 NAME1 VARCHAR(50) Y
3 PHONE VARCHAR(11) N
4 CTIME DATETIME(6) Y
used time: 2.350(ms). Execute id is 1008. 
SQL>

– 禁用表触发器

SQL> alter table table1 disable all triggers;
executed successfully
used time: 16.652(ms). Execute id is 1009. 
SQL>

– 修改表名

SQL> alter table table1 rename to table2;
executed successfully
used time: 24.520(ms). Execute id is 1010. 
SQL>

4.3 删除表

– 删除表

SQL> drop table table2 cascade;
executed successfully
used time: 99.737(ms). Execute id is 1020. 
SQL>

4.4 表数据处理

– delete 删除表数据

SQL> select * from table2;
LINEID ID NAME1 PHONE CTIME
---------- ----------- ----- ----- -------------------------- 1 1 a b 2025-04-16 19:01:14.098269
2 2 c d 2025-04-16 19:02:01.869087
3 3 e f 2025-04-16 19:02:01.869106
used time: 0.351(ms). Execute id is 1015. 
SQL> delete table2 where id=2;
affect rows 1
used time: 3.882(ms). Execute id is 1016. 
SQL> select * from table2;
LINEID ID NAME1 PHONE CTIME
---------- ----------- ----- ----- -------------------------- 1 1 a b 2025-04-16 19:01:14.098269
2 3 e f 2025-04-16 19:02:01.869106
used time: 0.911(ms). Execute id is 1017.

truncate 清空表数据

SQL> truncate table table2;
executed successfully
used time: 73.660(ms). Execute id is 1018. 
SQL> select * from table2;
no rows
used time: 1.170(ms). Execute id is 1019. 
SQL>

4.4 查看表信息

– 查看表定义语句

SQL> CALL SP_TABLEDEF('SYSDBA', 'T_HASH');
LINEID COLUMN_VALUE
----------
----------------------------------------------------------------------------------------------------------------------
------------------------------------- 
1 CREATE TABLE "SYSDBA"."T_HASH" ( "CALLER" CHAR(15), "CALLEE" CHAR(15), "TIME" DATETIME(6), "DURATION" INT) PARTITION BY
HASH("DURATION") ( PARTITION "DMHASHPART0" STORAGE(ON "MAIN", CLUSTERBTR) , PARTITION "DMHASHPART1" STORAGE(ON "MAIN", CLUSTERBTR) , PARTITION "DMHASHPART2" STORAGE(ON "MAIN", CLUSTERBTR) , PARTITION
"DMHASHPART3" STORAGE(ON "MAIN", CLUSTERBTR) )
STORAGE(HASHPARTMAP(1), ON "MAIN", CLUSTERBTR) ;
used time: 22.753(ms). Execute id is 1021. 
SQL>

– 查看表自增列信息

SQL> create table if not exists table1
2 (
3 id int IDENTITY(2,3) cluster primary key, 4 name varchar(20) not null default 'a' 5 )STORAGE(
6 INITIAL 5, 7 MINEXTENTS 5,
8 NEXT 2, 9 FILLFACTOR 85);
executed successfully
used time: 14.338(ms). Execute id is 1038. 
SQL> insert into table1 values('a'),('b');
affect rows 2
used time: 4.863(ms). Execute id is 1039. 
SQL> commit;
executed successfully
used time: 4.178(ms). Execute id is 1040. 
SQL> select ident_current('SYSDBA.TABLE1');
LINEID IDENT_CURRENT('SYSDBA.TABLE1') 
---------- ------------------------------ 
1 5
used time: 0.640(ms). Execute id is 1041. 
SQL> select ident_seed('SYSDBA.TABLE1');
LINEID IDENT_SEED('SYSDBA.TABLE1') 
---------- --------------------------- 
1 2
used time: 0.202(ms). Execute id is 1042. 
SQL> select ident_incr('SYSDBA.TABLE1');
LINEID IDENT_INCR('SYSDBA.TABLE1') 
---------- --------------------------- 
1 3
used time: 7.012(ms). Execute id is 1043. 
SQL>

–查看表的空间使用情况

SQL> SELECT table_used_space('SYSDBA','TABLE1');
LINEID TABLE_USED_SPACE('SYSDBA','TABLE1') 
---------- ----------------------------------- 
1 16
used time: 11.138(ms). Execute id is 1044. 
SQL> SELECT table_used_pages('SYSDBA','TABLE1');
LINEID TABLE_USED_PAGES('SYSDBA','TABLE1') 
---------- ----------------------------------- 
1 15
used time: 9.912(ms). Execute id is 1045. 
SQL>

五、索引

5.1 聚集索引

– 创建聚集索引

SQL> CREATE TABLE T1 (ID int,NAME varchar(20),DEPARTMENT varchar(20));
executed successfully
used time: 23.940(ms). Execute id is 1207. 
SQL> INSERT INTO T1 VALUES(1, 'Zhang San', 'A 部');
affect rows 1
used time: 14.473(ms). Execute id is 1208. 
SQL> INSERT INTO T1 VALUES(2, 'Li Si', 'B 部');
affect rows 1
used time: 0.645(ms). Execute id is 1209. 
SQL> INSERT INTO T1 VALUES(3, 'Wang Wu', 'C 部');
affect rows 1
used time: 0.513(ms). Execute id is 1210. 
SQL> INSERT INTO T1 VALUES(4, 'Chen Liu', 'D 部');
affect rows 1
used time: 0.652(ms). Execute id is 1211. 
SQL> commit;
executed successfully
used time: 3.725(ms). Execute id is 1212. 
SQL> CREATE CLUSTER INDEX C1 on T1(ID);
executed successfully
used time: 53.203(ms). Execute id is 1213.

– 使用聚集索引查询

SQL> EXPLAIN SELECT * FROM T1 WHERE ID=2;
1 #NSET2: [1, 1, 112]
2 #PRJT2: [1, 1, 112]; exp_num(4), is_atom(FALSE)
3 #CSEK2: [1, 1, 112]; scan_type(ASC), C1(T1), scan_range[2,2]
used time: 2.749(ms). Execute id is 0. 
SQL>

5.2 非聚集索引

– 使用非聚集索引查询

SQL> CREATE INDEX S1 on T1(NAME);
executed successfully
used time: 47.254(ms). Execute id is 1214. 
SQL> EXPLAIN SELECT ID FROM T1 WHERE NAME='Wang Wu';
1 #NSET2: [1, 1, 64]
2 #PRJT2: [1, 1, 64]; exp_num(2), is_atom(FALSE)
3 #SSEK2: [1, 1, 64]; scan_type(ASC), S1(T1), scan_range['Wang Wu','Wang Wu'],
is_global(0)
used time: 17.598(ms). Execute id is 0. 
SQL>

5.3 函数索引

 SQL> create index f1 on t1(id+1);
executed successfully
used time: 11.147(ms). Execute id is 1215. 
SQL> explain select * from t1 where id+1=3;
1 #NSET2: [1, 1, 112]
2 #PRJT2: [1, 1, 112]; exp_num(4), is_atom(FALSE)
3 #BLKUP2: [1, 1, 112]; F1(T1)
4 #SSEK2: [1, 1, 112]; scan_type(ASC), F1(T1), scan_range[3,3], is_global(0)
used time: 3.183(ms). Execute id is 0. 
SQL>

5.4 全局索引及局部索引

– 全局索引

SQL> drop table t1;
executed successfully
used time: 121.190(ms). Execute id is 1216. 
SQL> create table t1(c1 int, c2 int, c3 int) partition by range(c1) (
2 partition p1 values less than(100), 3 partition p2 values less than(200), 4 partition p3 values less than(maxvalue)
5 );
executed successfully
used time: 15.222(ms). Execute id is 1217. 
SQL> insert into t1 values(11,12,13),(111,112,113),(211,212,213);
commit;affect rows 3
used time: 16.035(ms). Execute id is 1218. 
SQL>
executed successfully
used time: 2.099(ms). Execute id is 1219.
SQL> create index idx1 on t1(c2) global;
executed successfully
used time: 36.595(ms). Execute id is 1220. 
SQL> explain select * from t1 where c2>200;
1 #NSET2: [1, 1, 24]
2 #PRJT2: [1, 1, 24]; exp_num(4), is_atom(FALSE)
3 #PARALLEL: [1, 1, 24]; scan_type(FULL), key_num(0, 0, 0), simple(0)
4 #BLKUP2: [1, 1, 24]; IDX1(T1)
5 #SSEK2: [1, 1, 24]; scan_type(ASC), IDX1(T1), scan_range(200,max],
is_global(1)
used time: 17.194(ms). Execute id is 0.

– 局部索引

SQL> create index idx2 on t1(c3);
executed successfully
used time: 28.698(ms). Execute id is 1221. 
SQL> explain select * from t1 where c3>200;
1 #NSET2: [1, 1, 24]
2 #PRJT2: [1, 1, 24]; exp_num(4), is_atom(FALSE)
3 #PARALLEL: [1, 1, 24]; scan_type(FULL), key_num(0, 0, 0), simple(0)
4 #BLKUP2: [1, 1, 24]; IDX2(T1)
5 #SSEK2: [1, 1, 24]; scan_type(ASC), IDX2(T1), scan_range(200,max],
is_global(0)
used time: 14.981(ms). Execute id is 0. SQL>

5.5 全文索引

– 创建全文索引

SQL> create table t2(content varchar(2000));
executed successfully
used time: 14.281(ms). Execute id is 1222. 
SQL> insert into t2 values('删除原有的全文索引，对基表进行全表扫描，逐一重构索
引信息。在创建全文索引成功
后，需完完全更新全文索引才可以执行有效的全文检索。完全更新全文索引没有次
数限制，
用户可以根据需要在增量更新或者是完全更新失败以及发生系统故障后都可以执
行完全更
新全文索引。另外，完全更新由于完全丢弃辅助表已有数据，重新开始对基表数据
进行分词
并填充到辅助表，因此服务器允许这种情况下更改分词算法。');
affect rows 1
used time: 4.598(ms). Execute id is 1223.
SQL> commit;
executed successfully
used time: 14.938(ms). Execute id is 1224. 
SQL> CREATE CONTEXT INDEX cti_content ON t2(content) LEXER DEFAULT_LEXER;
executed successfully
used time: 55.257(ms). Execute id is 1225.

– 使用全文索引

SQL> explain select * from t2 where content like '%索引%';
1 #NSET2: [1, 1, 60]
2 #PRJT2: [1, 1, 60]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [1, 1, 60]; exp11 > 0
4 #CSCN2: [1, 1, 60]; INDEX33555530(T2); btr_scan(1)
used time: 4.577(ms). Execute id is 0. 
SQL> explain select * from t2 where contains(content,'索引');
1 #NSET2: [1, 1, 60]
2 #PRJT2: [1, 1, 60]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [1, 1, 60]; contains > 0
4 #CSCN2: [1, 1, 60]; INDEX33555530(T2); btr_scan(1)
used time: 2.268(ms). Execute id is 0.
SQL>

– 重组全文索引

SQL> ALTER CONTEXT INDEX cti_content ON t2 INCREMENT;
executed successfully
used time: 913.268(ms). Execute id is 1240. 
SQL> ALTER CONTEXT INDEX cti_content ON t2 REBUILD;
executed successfully
used time: 63.319(ms). Execute id is 1241. 
SQL>

5.6 重建及删除索引

– 重建索引

SQL> select name,id from sysobjects where name='IDX1';
LINEID NAME ID
---------- ---- ----------- 
1 IDX1 33555522
used time: 15.695(ms). Execute id is 1243. 
SQL> SP_REBUILD_INDEX('SYSDBA', 33555522);
DMSQL executed successfully
used time: 26.080(ms). Execute id is 1244. 
SQL>

– 删除索引

SQL> drop index idx1;
executed successfully
used time: 33.818(ms). Execute id is 1245. 
SQL>

六、触发器

– before and delete 触发器

SQL> create table t_triger(id int,name varchar(30));
executed successfully
used time: 10.897(ms). Execute id is 1246. 
SQL> insert into t_triger values(1,'a'),(2,'b'),(3,'c');
affect rows 3
used time: 6.279(ms). Execute id is 1247. 
SQL> commit;
executed successfully
used time: 15.405(ms). Execute id is 1248. 
SQL> CREATE OR REPLACE TRIGGER bef_del
2 BEFORE DELETE ON t_triger
3 FOR EACH ROW
4 BEGIN
5 PRINT '您正在对表 t_triger 进行删除操作，被删除记录 id：'||:old.id;
6 END;
7 /
executed successfully
used time: 17.924(ms). Execute id is 1253. 
SQL> set serveroutput on
SQL> delete t_triger where id=2;
您正在对表 t_triger 进行删除操作，被删除记录 id：2
affect rows 1
used time: 16.644(ms). Execute id is 1256. 
SQL>

– after and insert 触发器

SQL> CREATE OR REPLACE TRIGGER aft_insert
2 after insert ON t_triger
3 FOR EACH ROW
4 BEGIN
5 PRINT '您正在对表 t_triger 进行新增操作，新记录 id 为：'||:new.id;
6 END;
7 /
executed successfully
used time: 22.731(ms). Execute id is 1258. 
SQL> insert into t_triger values(4,'d');
您正在对表 t_triger 进行新增操作，新记录 id 为：4
affect rows 1
used time: 5.676(ms). Execute id is 1259. 
SQL>

– 创建时间触发器每月 28 号 9 点~18 点触发

SQL> CREATE OR REPLACE TRIGGER timer2
3 AFTER TIMER ON DATABASE
4 FOR EACH 1 MONTH DAY 28
5 FROM TIME '09:00' TO TIME '18:00' FOR EACH 1 MINUTE
6 DECLARE
7 str VARCHAR;
8 BEGIN
9 PRINT 'HELLO WORLD';
10 END;
11 /
executed successfully
used time: 19.497(ms). Execute id is 1260. 
SQL>

– 删除触发器

SQL> drop trigger timer2;
executed successfully
used time: 49.364(ms). Execute id is 1261. 
SQL> drop trigger if exists aft_insert;
executed successfully
used time: 44.087(ms). Execute id is 1262. 
SQL>

– 重编译触发器

SQL> ALTER TRIGGER bef_del COMPILE ;
executed successfully
used time: 18.717(ms). Execute id is 1268. 
SQL>

七、视图

7.1 普通视图

– 创建视图

SQL> CREATE OR REPLACE VIEW normal_view AS SELECT * FROM t1;
executed successfully
used time: 28.995(ms). Execute id is 1264. 
SQL> select * from normal_view;
LINEID C1 C2 C3
---------- ----------- ----------- ----------- 
1 11 12 13
2 111 112 113
3 211 212 213
used time: 8.320(ms). Execute id is 1265. 
SQL> CREATE OR REPLACE VIEW normal_view2(v_c1,v_c2) AS SELECT c1,c2 FROM t1;
executed successfully
used time: 18.175(ms). Execute id is 1266. 
SQL> select * from normal_view2;
LINEID V_C1 V_C2
---------- ----------- ----------- 
1 11 12
2 111 112
3 211 212
used time: 10.843(ms). Execute id is 1267. 
SQL>

– 创建只读视图

SQL> CREATE or replace VIEW read_only_view AS SELECT * FROM t1 with read only;
executed successfully
used time: 25.696(ms). Execute id is 1272. 
SQL> insert into read_only_view values(7,8,9);
insert into read_only_view values(7,8,9);
[-2649]:Error in line: 1
Try to insert readonly view [READ_ONLY_VIEW]. used time: 10.866(ms). Execute id is 0. SQL> insert into normal_view values(7,8,9);
affect rows 1
used time: 10.177(ms). Execute id is 1273. 
SQL>

– 删除视图

SQL> drop view if exists normal_view;
executed successfully
used time: 26.065(ms). Execute id is 1274. 
SQL> drop view if exists read_only_view cascade;
executed successfully
used time: 23.862(ms). Execute id is 1275. 
SQL>

– 编译视图

SQL> alter view normal_view2 COMPILE;
executed successfully
used time: 12.795(ms). Execute id is 1276. 
SQL>

7.2 物化视图

– 创建物化视图日志

SQL> CREATE MATERIALIZED VIEW LOG ON sysdba.t1 WITH ROWID(c1,c2,c3) PURGE
START WITH SYSDATE + 5 REPEAT INTERVAL '1' DAY;
executed successfully
used time: 56.844(ms). Execute id is 1305. 
SQL>

– 创建物化视图

SQL> create materialized view m_view(v_c1,v_c2)
2 build immediate tablespace main storage(INITIAL 5)
3 refresh force on demand start with sysdate next sysdate+1 with rowid using
default
4 disable query rewrite
5 as select c1,c2 from t1;
executed successfully
used time: 33.368(ms). Execute id is 1302. 
SQL>

– 删除物化视图日志

SQL> DROP MATERIALIZED VIEW LOG ON sysdba.t1;
executed successfully
used time: 62.899(ms). Execute id is 1306.

– 删除物化视图

SQL> drop materialized view m_view;
executed successfully
used time: 51.006(ms). Execute id is 1303.
SQL>

八、序列和同义词

8.1 序列

– 创建序列

SQL> create sequence if not exists seq_1
2 start with 1
3 increment by 1
4 minvalue 1
5 maxvalue 10000
6 cycle
7 cache 5
8 order;
executed successfully
used time: 20.355(ms). Execute id is 1307. 
SQL> select seq_1.nextval;
LINEID NEXTVAL
---------- -------------------- 
1 1
used time: 12.403(ms). Execute id is 1309. 
SQL> select seq_1.currval;
LINEID CURRVAL
---------- -------------------- 
1 1
used time: 1.492(ms). Execute id is 1310.

– 修改序列最大值

SQL> alter sequence seq_1 maxvalue 999999;
executed successfully
used time: 9.707(ms). Execute id is 1311.

– 修改序列名称

SQL> alter sequence seq_1 rename to seq_2;
executed successfully
used time: 10.605(ms). Execute id is 1312.

– 删除序列

SQL> drop sequence seq_2;
executed successfully
used time: 19.758(ms). Execute id is 1313. 
SQL>

8.2 同义词

–创建全局同义词

SQL> create or replace public synonym if not exists syn_t1 for sysdba.t1;
executed successfully
used time: 21.982(ms). Execute id is 1314. 
SQL>

–创建非全局同义词

SQL> create or replace synonym if not exists sysdba.syn_t2 for sysdba.t1;
executed successfully
used time: 21.982(ms). Execute id is 1314. 
SQL>

– 查询同义词

SQL> select * from syn_t1;
LINEID C1 C2 C3
---------- ----------- ----------- ----------- 
1 11 12 13
2 7 8 9
3 111 112 113
4 211 212 213
used time: 4.084(ms). Execute id is 1316. 
SQL>

– 删除全局同义词

SQL> drop public synonym syn_t1;
executed successfully
used time: 40.296(ms). Execute id is 1317.

– 删除非全局同义词

SQL> drop synonym syn_t2;
executed successfully
used time: 38.541(ms). Execute id is 1318. 
SQL>

九、控制文件、重做日志、回滚段

9.1 控制文件

可以在 dm.ini 中通过设置 CTL_PATH 配置参数的值来指定控制文件的路径，缺省控制文件在数据目录下。例如可以把控制文件复制到 D 盘的 control_path目录下，同时修改 dm.ini 中 CTL_PATH = D:\control_path。
– 利用控制文件生成文本文件

[dmdba@dm-standalone DM]$ dmctlcvt type=1 src=/data/DM/dm.ctl
dest=/data/DM/ctl_bak.txt
DMCTLCVT V8
convert ctl to txt success!
[dmdba@dm-standalone DM]$ ll|grep ctl_bak.txt
-rw-r--r-- 1 dmdba dinstall 12274 Apr 17 01:39 ctl_bak.txt

– 利用文本文件生成控制文件

[dmdba@dm-standalone DM]$ dmctlcvt TYPE=2 SRC= /data/DM/ctl_bak.txt
DEST=/data/DM/dm.ctl

9.2 重做日志文件

– 查询重做日志文件信息

SQL> select FILE_ID,PATH,RLOG_SIZE/1024/1024 as MB from V$RLOGFILE;
LINEID FILE_ID PATH MB
---------- ----------- ----------------- -------------------- 1 0 /data/DM/DM01.log 4096
2 1 /data/DM/DM02.log 4096
used time: 15.781(ms). Execute id is 1701. 
SQL>

– 添加重做日志文件

SQL> ALTER DATABASE ADD LOGFILE '/data/DM/DM03.log' size 128;
executed successfully
used time: 31.217(ms). Execute id is 1702. 
SQL> select FILE_ID,PATH,RLOG_SIZE/1024/1024 as MB from V$RLOGFILE;
LINEID FILE_ID PATH MB
---------- ----------- ----------------- -------------------- 1 0 /data/DM/DM01.log 4096
2 1 /data/DM/DM02.log 4096
3 2 /data/DM/DM03.log 128
used time: 0.219(ms). Execute id is 1703. 
SQL>

– 修改重做日志文件大小

SQL> ALTER DATABASE RESIZE LOGFILE '/data/DM/DM03.log' to 256;
executed successfully
used time: 17.576(ms). Execute id is 1711. 
SQL>

9.3 回滚段

回滚文件的路径记录在控制文件里面，可以使用 dmctlcvt 工具在 DM 服务器关闭的状态下对控制文件进行修改。使用 dmctlcvt 工具将控制文件转换为文本文件，编辑文本文件中要修改的文件的路径后再使用 dmctlcvt 工具将文本文件转换为控制文件即可。