（1）查看原表DDL
SQL> set linesize 200 pages 200 long 9999 lineshow off
SQL> select dbms_metadata.get_ddl('TABLE','EMP','HAHA') from dual;
CREATE TABLE "HAHA"."EMP"
(
"ID" NUMBER NOT NULL,
"NAME" VARCHAR(20),
"AGE" NUMBER,
"DEPTNO" NUMBER,
"SALARY" NUMBER,
"PHONE" NUMBER,
"HIRE_DATE" DATETIME(6) DEFAULT SYSDATE,
"SEX" BIT,
CONSTRAINT "PK_EMP_ID" CLUSTER PRIMARY KEY("ID"),
CONSTRAINT "FK_EMP_DEPTNO" FOREIGN KEY("DEPTNO") REFERENCES "HAHA"."DEPT"("DEPTNO"),
CHECK("AGE" > 18)) STORAGE(ON "HAHA", CLUSTERBTR) ;
已用时间: 2.874(毫秒). 执行号:2481539.
（2）检查原表相关信息
检查原表的索引
SQL> select index_name,table_name,degree,status,partitioned from user_indexes t where table_name='EMP';
行号 INDEX_NAME TABLE_NAME DEGREE STATUS PARTITIONED
---------- ---------------- ---------- ------ ------ -----------
1 INDEX33556206 EMP NULL VALID NO
2 INDEX33556197 EMP NULL VALID NO
已用时间: 253.690(毫秒). 执行号:2481855.
检查原表约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME,R_CONSTRAINT_NAME from dba_constraints where OWNER='HAHA' and TABLE_NAME='EMP';
行号 TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME R_CONSTRAINT_NAME
---------- ---------- --------------- --------------- ------------- -----------------
1 EMP CONS134219061 C NULL NULL
2 EMP PK_EMP_ID P INDEX33556197 NULL
3 EMP FK_EMP_DEPTNO R INDEX33556204 PK_DEPT_NO
已用时间: 575.028(毫秒). 执行号:2481955.
检查原表涉及的其他对象
SQL> select * from user_dependencies t where t.referenced_name = 'EMP';
行号 OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME SCHEMAID DEPENDENCY_TYPE
---------- ----- ---------- ------- ---------------- --------------- --------------- -------------------- ----------- ---------------
1 HAHA V_EMP VIEW HAHA EMP TABLE NULL 150995949 HARD
2 HAHA TR_ROW_EMP TRIGGER HAHA EMP TABLE NULL 150995949 HARD
已用时间: 109.897(毫秒). 执行号:2481956.
发现存在 基于EMP表创建的视图和触发器
查看视图DDL
SQL> select dbms_metadata.get_ddl('VIEW','V_EMP','HAHA') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_EMP','HAHA')
----------------------------------------------------------------------------------------------------
create or replace view v_emp as
select e.id,e.name,d.dept_name,e.salary,e.hire_date from emp e,dept d
where e.deptno=d.deptno;
已用时间: 2.061(毫秒). 执行号:2481958.
查看触发器DDL
SQL> select dbms_metadata.get_ddl('TRIGGER','TR_ROW_EMP','HAHA') from dual;
DBMS_METADATA.GET_DDL('TRIGGER','TR_ROW_EMP','HAHA')
---------------------------------------------------------------------------------------------------
CREATE TRIGGER "TR_ROW_EMP"
BEFORE UPDATE
ON "EMP"
referencing OLD ROW AS "OLD" NEW ROW AS "NEW"
for each row
begin
dbms_output.put_line('run row tr');
end;
已用时间: 2.316(毫秒). 执行号:2481959.
检查原表的外键依赖
SQL> select t1.table_name,
t2.table_name as "TABLE_NAME(R)",
t1.constraint_name,
t1.r_constraint_name as "CONSTRAINT_NAME(R)",
a1.column_name,
a2.column_name as "COLUMN_NAME(R)"
from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
where t1.table_name='EMP' and
t1.r_constraint_name = t2.constraint_name and
t1.constraint_name = a1.constraint_name and
t1.r_constraint_name = a2.constraint_name;
TABLE_NAME TABLE_NAME(R) CONSTRAINT_NAME CONSTRAINT_NAME(R) COLUMN_NAME COLUMN_NAME(R)
---------- ------------- --------------- ------------------ ----------- --------------
EMP DEPT FK_EMP_DEPTNO PK_DEPT_NO DEPTNO DEPTNO
已用时间: 509.415(毫秒). 执行号:2481962.
可见EMP表存在DEPTNO字段的外键依赖于DEPT表
查看表数据量
SQL> select segment_name,sum(bytes)/1024/1024 from dba_segments where segment_name='EMP' and owner='HAHA' group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024
------------ --------------------
EMP 95
已用时间: 00:00:02.597. 执行号:2481963.
SQL> select count(*) from emp;
COUNT(*)
--------------------
1000000
emp表实际数据量为95MB，100w条数据
检查表空间使用情况，查看资源是否充足
SQL> select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 MAXBYTES/1024/1024
------------------------------- --------------- -------------------- ------------------
/dm/dmdbms/dmdb/DMDB/SYSTEM.DBF SYSTEM 27 16777215
/dm/dmdbms/dmdb/DMDB/DMHS.DBF DMHS 1024 16777215
/dm/dmdbms/dmdb/DMDB/SYSAWR.DBF SYSAUX 1015 10240
/dm/dmdbms/dmdb/DMDB/HAHA.DBF HAHA 252 30720
/dm/dmdbms/dmdb/DMDB/MAIN.DBF MAIN 748 16777215
/dm/dmdbms/dmdb/DMDB/TEMP.DBF TEMP 136 16777215
/dm/dmdbms/dmdb/DMDB/ROLL.DBF ROLL 128 16777215
7 rows got
已用时间: 195.349(毫秒). 执行号:2481964.
（3）导出原表数据
cd /dm/dmdbms/bin
./dexp haha/haha12345 directory=/dm/dmdbms/dmdb file=emp.dmp log=exp_emp.log tables=emp
（4）重命名原表
SQL> alter table emp rename to emp_old;
操作已执行
（5）创建分区表：根据emp表的字段，可以通过hire_date字段按月进行水平分区改造
create table emp
(id number,
name varchar(20),
age number,
deptno number,
salary number,
phone number,
hire_date datetime,
sex bit
)
partition by range (hire_date)
(
partition hd_1 values less than (to_date('2020-01-01','yyyy-mm-dd')),
partition hd_2 values less than (to_date('2020-02-01','yyyy-mm-dd')),
partition hd_3 values less than (to_date('2020-03-01','yyyy-mm-dd')),
partition hd_4 values less than (to_date('2020-04-01','yyyy-mm-dd')),
partition hd_5 values less than (to_date('2020-05-01','yyyy-mm-dd')),
partition hd_6 values less than (to_date('2020-06-01','yyyy-mm-dd')),
partition hd_7 values less than (to_date('2020-07-01','yyyy-mm-dd')),
partition hd_8 values less than (to_date('2020-08-01','yyyy-mm-dd')),
partition hd_9 values less than (to_date('2020-09-01','yyyy-mm-dd')),
partition hd_10 values less than (to_date('2020-10-01','yyyy-mm-dd')),
partition hd_11 values less than (to_date('2020-11-01','yyyy-mm-dd')),
partition hd_12 values less than (to_date('2020-12-01','yyyy-mm-dd')),
partition hd_13 values equ or less than (to_date('2021-01-01','yyyy-mm-dd')),
partition hd_14 values equ or less than (maxvalue)
);
创建约束及唯一性本地索引，局部唯一索引必须包含全部分区列
SQL> create unique index IDX_EMP_P_ID on EMP (id,hire_date);
操作已执行
已用时间: 324.634(毫秒). 执行号:2481970.
SQL> alter table EMP add primary key (id,hire_date);
操作已执行
已用时间: 275.109(毫秒). 执行号:2481971.
SQL> alter table emp add constraint FK_EMP_NO foreign key (deptno) references dept (deptno);
操作已执行
已用时间: 282.824(毫秒). 执行号:2481972.
SQL> alter table emp add constraint CK_EMP_AGE check (age>18);
操作已执行
已用时间: 914.340(毫秒). 执行号:2482013.
查看分区表信息
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t where table_name='EMP';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- ----------------- --------------------
EMP RANGE 14
已用时间: 3.829(毫秒). 执行号:2481973.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='EMP' order by 3;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- -----------------------------
EMP HD_1 DATETIME'2020-01-01 00:00:00'
EMP HD_2 DATETIME'2020-02-01 00:00:00'
EMP HD_3 DATETIME'2020-03-01 00:00:00'
EMP HD_4 DATETIME'2020-04-01 00:00:00'
EMP HD_5 DATETIME'2020-05-01 00:00:00'
EMP HD_6 DATETIME'2020-06-01 00:00:00'
EMP HD_7 DATETIME'2020-07-01 00:00:00'
EMP HD_8 DATETIME'2020-08-01 00:00:00'
EMP HD_9 DATETIME'2020-09-01 00:00:00'
EMP HD_10 DATETIME'2020-10-01 00:00:00'
EMP HD_11 DATETIME'2020-11-01 00:00:00'
EMP HD_12 DATETIME'2020-12-01 00:00:00'
EMP HD_13 DATETIME'2021-01-01 00:00:00'
EMP HD_14 MAXVALUE
14 rows got
已用时间: 22.369(毫秒). 执行号:2481975.
（6）插入原表数据
SQL> insert into emp select * from emp_old;
影响行数 1000000
已用时间: 00:01:15.679. 执行号:2481976.
SQL> commit;
操作已执行
（7）重建依赖对象
删除并重建触发器
SQL> drop trigger TR_ROW_EMP;
操作已执行
SQL> create or replace trigger TR_ROW_EMP
before update
on EMP
for each row
begin
dbms_output.put_line('run row tr');
end;
/
操作已执行
重建视图
SQL> create or replace view v_emp as
select e.id,e.name,d.dept_name,e.salary,e.hire_date from emp e,dept d
where e.deptno=d.deptno;2 3
操作已执行
已用时间: 91.774(毫秒). 执行号:2482009.
再次检查依赖关系
SQL> select * from user_dependencies t where t.referenced_name = 'EMP';
OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME SCHEMAID DEPENDENCY_TYPE
----- ---------- ------- ---------------- --------------- --------------- -------------------- ----------- ---------------
HAHA TR_ROW_EMP TRIGGER HAHA EMP TABLE NULL 150995949 HARD
HAHA V_EMP VIEW HAHA EMP TABLE NULL 150995949 HARD
已用时间: 2.033(毫秒). 执行号:2482010.
SQL> select t1.table_name,
t2.table_name as "TABLE_NAME(R)",
t1.constraint_name,
t1.r_constraint_name as "CONSTRAINT_NAME(R)",
a1.column_name,
a2.column_name as "COLUMN_NAME(R)"
from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
where t1.table_name='EMP' and
t1.r_constraint_name = t2.constraint_name and
t1.constraint_name = a1.constraint_name and
t1.r_constraint_name = a2.constraint_name;
TABLE_NAME TABLE_NAME(R) CONSTRAINT_NAME CONSTRAINT_NAME(R) COLUMN_NAME COLUMN_NAME(R)
---------- ------------- --------------- ------------------ ----------- --------------
EMP DEPT FK_EMP_NO PK_DEPT_NO DEPTNO DEPTNO
已用时间: 28.474(毫秒). 执行号:2482006.
（8）根据实际情况决定是否彻底drop掉emp_old表，释放空间。
SQL> drop table emp_old;
操作已执行
已用时间: 00:00:01.228. 执行号:2481998.
937
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
