目录
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;