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

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

目录

• oracle迁移某个用户的表到另外一个用户里
  • 1、创建新用户
    • 1.1、表空间创建
    • 1.2、创建新用户
    • 1.3、授权
  • 2、表数据迁移
    • 2.1、迁移前后统计某个用户表结构对象信息
    • 2.2、序列迁移，先执行获取创建语句
    • 2.3、表迁移
    • 2.4、表注释、字段注释迁移
    • 2.5、主键约束、唯一键约束、外键约束迁移
    • 2.6、pck与视图迁移
    • 2.7、索引迁移(包含约束)
    • 2.8、触发器迁移
    • 2.9、创建同义词（在原用户执行，可省略）
  • 3、删除原用户的数据
    • 3.1、删除旧序列
    • 3.2、删除原表
    • 3.3、删除旧触发器

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;