oracle迁移某个用户的表到另外一个用户里
目录
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 'comment on table ' || TABLE_NAME || ' is ''' || COMMENTS || ''';' |
| FROM USER_TAB_COMMENTS |
| WHERE TABLE_NAME LIKE 'ODS_%' |
| AND TABLE_NAME NOT LIKE 'ODS_V_%' |
| ORDER BY TABLE_NAME; |
| |
字段注释:
| --字段注释 |
| SELECT 'comment on column ' || TABLE_NAME || '.' || COLUMN_NAME || ' |
| is ''' || COMMENTS || ''';' |
| FROM USER_COL_COMMENTS |
| WHERE TABLE_NAME LIKE 'ODS_%' |
| AND TABLE_NAME NOT LIKE 'ODS_V_%' |
| ORDER BY TABLE_NAME; |
2.5、主键约束、唯一键约束、外键约束迁移
| 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.6、pck与视图迁移
如工具类ods_pack_commonutils
2.7、索引迁移(包含约束)
| 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.8、触发器迁移
| 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.9、创建同义词(在原用户执行,可省略)
是根据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; |