526互联

7、oracle迁移某个用户的表到另外一个用户里

发布时间 2023-12-05 16:42:45作者: 站着说话不腰疼

目录

oracle迁移某个用户的表到另外一个用户里

1、创建新用户

1.1、表空间创建

--创建表空间
CREATE TABLESPACE ODS_SPACE LOGGING DATAFILE '/oracle/oradata/DEV/ODS_SPACE.DBF' SIZE 32M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

--创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP_ODS_SPACE TEMPFILE '/oracle/oradata/DEV/temp_ods_space.DBF' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

1.2、创建新用户

CREATE USER odsuser IDENTIFIED BY BmikeXO4Xr account unlock DEFAULT TABLESPACE ODS_SPACE TEMPORARY TABLESPACE TEMP_ODS_SPACE;

1.3、授权

--登录和资源权限(？无建表等权限，需要另外授权)
grant connect to odsuser;
grant resource to odsuser;

--其它结构对象操作权限
grant create table to odsuser;
grant create job to odsuser;
grant create synonym to odsuser;
grant create view to odsuser;
grant create session to odsuser;
grant UNLIMITED TABLESPACE to odsuser;
grant CREATE DATABASE LINK to odsuser;
grant select any table to odsuser;
--测试环境额外授权
GRANT debug any procedure, debug connect session to odsuser;

--导出权限
grant read,write on directory dmps to odsuser;

2、表数据迁移

2.1、迁移前后统计某个用户表结构对象信息

SELECT object_type,
       COUNT(object_name)
  FROM user_objects
 WHERE upper(object_name) LIKE '%ODS%'
   AND upper(object_name) NOT LIKE upper('%TEMP%')
 GROUP BY object_type
 ORDER BY 2;

2.2、序列迁移，先执行获取创建语句

SELECT 'create sequence ' || 'odsuser' || '.' || SEQUENCE_NAME || ' start with ' ||
       LAST_NUMBER || ' maxvalue ' || MAX_VALUE || ' minvalue ' || MIN_VALUE ||
       ' increment by ' || INCREMENT_BY || ' cache ' || CACHE_SIZE || ' ' ||
       DECODE(CYCLE_FLAG, 'N', 'NOCYCLE', 'CYCLE') || ' ' ||
       DECODE(ORDER_FLAG, 'N', 'NOORDER', 'ORDER') || ' ;' AS "create_scripts",
       'drop sequence ' || 'dmuser' || '.' || SEQUENCE_NAME || ';' AS "drop_scripts"
  FROM USER_SEQUENCES
 WHERE CACHE_SIZE != 0
   AND UPPER(SEQUENCE_NAME) LIKE UPPER('%ODS%');

odsuser优先执行创建序列，根据每个不同环境获取到序列开始值不一致

2.3、表迁移

SELECT 'create table odsuser.' || T.TABLE_NAME || ' as select * from dmuser.' ||
       T.TABLE_NAME || ';' AS CREATE_SQL,
       'drop table dmuser.' || T.TABLE_NAME || ';' AS DROP_SQL
  FROM USER_TABLES T
 WHERE UPPER(T.TABLE_NAME) LIKE UPPER('%ODS%')
   AND UPPER(TABLE_NAME) NOT LIKE UPPER('%TEMP%');

2.4、主键约束、唯一键约束、外键约束迁移

SELECT AU.TABLE_NAME,
       AU.CONSTRAINT_NAME,
       AU.CONSTRAINT_TYPE,
       DECODE(AU.CONSTRAINT_TYPE,
              'P',
              ('ALTER TABLE odsuser.' || AU.TABLE_NAME || ' ADD CONSTRAINT ' ||
              AU.CONSTRAINT_NAME || ' PRIMARY KEY (' || LISTAGG(CU.COLUMN_NAME, ',') WITHIN
               GROUP(ORDER BY CU.COLUMN_NAME) || ');'),
              'U',
              ('ALTER TABLE odsuser.' || AU.TABLE_NAME || ' ADD CONSTRAINT ' ||
              AU.CONSTRAINT_NAME || ' Unique (' || LISTAGG(CU.COLUMN_NAME, ',') WITHIN
               GROUP(ORDER BY CU.COLUMN_NAME) || ');'),
              AU.CONSTRAINT_TYPE || '-其它待处理') AS CREATE_SQL
  FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
 WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
   AND AU.CONSTRAINT_TYPE IN ('P', 'U', 'R') --P-主键，U-唯一键，R-外键
   AND UPPER(AU.TABLE_NAME) LIKE UPPER('%ODS%')
   AND UPPER(AU.TABLE_NAME) NOT LIKE UPPER('%TEMP%')
 GROUP BY AU.TABLE_NAME, AU.CONSTRAINT_NAME, AU.CONSTRAINT_TYPE
 ORDER BY AU.TABLE_NAME ASC, AU.CONSTRAINT_NAME ASC;

2.5、pck与视图迁移

如工具类ods_pack_commonutils

2.6、索引迁移(包含约束)

SELECT T2.TABLE_NAME,
       T2.INDEX_NAME,
       LISTAGG(T3.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY T3.COLUMN_POSITION) INDEX_COLUMN,
       'call ods_pack_commonutils.DROP_TABLE_INDEX(''' || T2.INDEX_NAME || ''');' DROP_INDEX,
       'call ods_pack_commonutils.ADD_TABLE_INDEX (''' || T2.TABLE_NAME || ''',''' ||
       LISTAGG(T3.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY T3.COLUMN_POSITION) || ''',''' || T2.INDEX_NAME || ''');' INDEX_SQL
  FROM USER_INDEXES T2, USER_IND_COLUMNS T3
 WHERE T2.INDEX_NAME = T3.INDEX_NAME
   AND T2.TABLE_NAME = T3.TABLE_NAME
   AND T2.TABLE_NAME LIKE '%ODS%'
   AND UPPER(T2.TABLE_NAME) NOT LIKE UPPER('%TEMP%')
   AND T2.CONSTRAINT_INDEX = 'NO' --YES-约束索引标识, NO-非约束索引
      --AND t2.status = 'VALID'
   AND T3.COLUMN_NAME NOT LIKE '%SYS%'
 GROUP BY T2.INDEX_NAME, T2.TABLE_NAME
 ORDER BY T2.TABLE_NAME;

2.7、触发器迁移

SELECT 'call dm_pack_commonutils.DROP_TRIGGER(''' || TRIGGER_NAME || ''');' AS DMUSER,
       'call ods_pack_commonutils.DROP_TRIGGER(''' || TRIGGER_NAME || ''');' AS ODSUSER
  FROM ALL_TRIGGERS
 WHERE TABLE_NAME IN (SELECT 'ODS_' || BIZ_CODE FROM DMUSER.DM_CONF_TABLE)
   AND OWNER = UPPER('dmuser');

2.8、创建同义词（在原用户执行，可省略）

是根据DM_CONF_TABLE动态查询的表名

SELECT 'create or replace synonym ods_' || LOWER(BIZ_CODE) || ' for odsuser.ods_' ||
       LOWER(BIZ_CODE) || ';'
  FROM DM_CONF_TABLE;

3、删除原用户的数据

3.1、删除旧序列

drop sequence dmuser.ODS_SEQ_table_name;

3.2、删除原表

drop table dmuser.ODS_table_name ;

3.3、删除旧触发器

drop TRIGGER dmuser.ODS_TR_table_name;