Oracle 数据迁移及 Oracle 全局索引变更为分区索引

1. 在新环境数据库用户下执行建需要迁移表的建表语句

 CREATE TABLE "T_TSPS_CONDUCT_AUDITS" (
  "C_ID" VARCHAR2(64 BYTE), 
  "N_PERSON_ID" NUMBER, 
  "C_CODE" VARCHAR2(64 BYTE), 
  "C_NAME" VARCHAR2(64 BYTE), 
  "C_DEPT" VARCHAR2(64 BYTE), 
  "N_LOG_TIME" NUMBER, 
  "C_LOG_TYPE" NUMBER, 
  "C_CONTENT" VARCHAR2(1024 BYTE), 
  "C_RESULT" VARCHAR2(2 BYTE), 
  "C_SN" VARCHAR2(128 BYTE), 
  "N_DEPT_ID" NUMBER, 
  "C_BLACK_FLAG" VARCHAR2(2 BYTE), 
  "C_CREATE_TIME" DATE, 
  "C_IDENTIFIER" VARCHAR2(36 BYTE)
) PARTITION BY RANGE (C_CREATE_TIME)  interval (numtodsinterval (1,'DAY'))
(
 PARTITION audit_p180613 VALUES LESS THAN (TO_DATE('2018-6-13', 'yyyy-mm-dd'))
 );
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_ID" IS '主键';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."N_PERSON_ID" IS '人员id';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_CODE" IS '人员编码';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_NAME" IS '人员姓名';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_DEPT" IS '所属部门';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."N_LOG_TIME" IS '日志上报时间';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_LOG_TYPE" IS '日志类型 1:网络2:进程3:外设4:安装文件5:登陆操作系统10:url日志类型 11:单流量日志类型12:批量流量日志类型';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_CONTENT" IS '操作内容';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_RESULT" IS '0:被禁止，1：成功,2:其它';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_SN" IS '证书sn';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."N_DEPT_ID" IS '部门id';
​
   COMMENT ON COLUMN "T_TSPS_CONDUCT_AUDITS"."C_IDENTIFIER" IS '身份证号';
​
 CREATE UNIQUE INDEX "T_TSPS_CONDUCT_AUDITS_PK" ON "T_TSPS_CONDUCT_AUDITS" ("C_ID");

2. 在新服务上建立与老板数据库的服务通信

create public database link lnDblink 
connect to topsec 
identified by manager 
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.106.12.71)(PORT=4521)))
(CONNECT_DATA=(SERVICE_NAME=ora11g)))';

解释：此 SQL 在新数据库服务上执行 topsec ：老服务数据库用户名 manager ：老服务数据库用户密码 HOST: 老数据库地址 PORT：老数据库端口 SERVICE_NAME：老数据库服务名

3. 迁移数据

insert into T_TSPS_CONDUCT_AUDITS select * from topsec.T_TSPS_CONDUCT_AUDITS@lnDblink;

4. 全局索引改为分区索引
   a.-- 查询出指定用户（TOPSEC_MNHLJ）下，指定表（T_TSPS_CONDUCT_AUDITS） 上的索引

select * from dba_indexes where owner='TOPSEC_MNHLJ' and table_name='T_TSPS_CONDUCT_AUDITS';

b.-- 删除以上查询到的索引

DROP INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PCODE";
DROP INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PID";
DROP INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_INDEX1";

c.-- 删除主键约束后，删除主键对应的索引

ALTER TABLE "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS" DROP CONSTRAINT T_TSPS_CONDUCT_AUDITS_PK;
DROP INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PK";

d.-- 依次创建所需的分区索引

CREATE INDEX "TOPSEC_MNHLJ"."IDX_TTCA_CTIME_G" ON "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS" ("C_CREATE_TIME", 0) LOCAL NOLOGGING
PARALLEL 8;
​
CREATE INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PID" ON "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS" ("N_PERSON_ID") LOCAL NOLOGGING
PARALLEL 8;
​
CREATE INDEX "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PK" ON "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS" ("C_ID") LOCAL NOLOGGING
PARALLEL 8;

e.-- 取消新建索引上的并行度

alter index "TOPSEC_MNHLJ"."IDX_TTCA_CTIME_G" noparallel;
alter index "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PID" noparallel;
alter index "TOPSEC_MNHLJ"."T_TSPS_CONDUCT_AUDITS_PK" noparallel;

f.-- 查看分区索引是否创建成功

select * from dba_part_indexes where owner='TOPSEC_MNHLJ' and table_name='T_TSPS_CONDUCT_AUDITS';