【原创】Oracle数据库逻辑迁移步骤

Oracle数据库逻辑迁移步骤

一、首先，查询并得到导出所有非Oracle自带用户数据的exp导出脚本：

 1 SELECT 'EXP SYSTEM/PASSWORD@ORCL FILE=' || USERNAME || '.DMP LOG=' ||
 2 
 3         USERNAME || '.LOG OWNER=' || USERNAME || '
 4 
 5 '
 6 
 7   FROM ALL_USERS T
 8 
 9  WHERE T.CREATED >
10 
11        (SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1;

二、接着，得到新库中创建所需非Oracle自带用户的sql脚本：

 1 SELECT 'CREATE USER ' || USERNAME || 'IDENTIFIED BY PASSWORD;' || '
 2 
 3        /' || '
 4 
 5        GRANT CONNECT, RESOURCE TO ' || USERNAME || ';' || '
 6 
 7        /'
 8 
 9   FROM ALL_USERS T
10 
11  WHERE T.CREATED >
12 
13        (SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1;

三、接着，得到在新库中创建导入数据所需表空间的sql脚本：

1 SELECT 'CREATE TABLESPACE ' || NAME || 'LOGGING DATAFILE ' || NAME ||
2 
3        '.ORA' ||
4 
5        'SIZE 1000M AUTOEXTEND ON NEXT 300M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;'
6 
7   FROM V$TABLESPACE
8 
9  WHERE NAME NOT IN ('SYSTEM', 'UNDOTBS1', 'SYSAUX', 'USERS', 'TEMP');

四、最后，查询并得到导入所有非Oracle自带用户数据的imp导入脚本：

 1 SELECT 'IMP SYSTEM/PASSWORD@ORCL FILE=' || USERNAME || '.DMP LOG=' ||
 2 
 3         USERNAME || '.LOG FROMUSER=' || USERNAME || ' TOUSER=' || USERNAME || '
 4 
 5 '
 6 
 7   FROM ALL_USERS T
 8 
 9  WHERE T.CREATED >
10 
11        (SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1;

末了，进行查询，确认迁移数据成功。

转载于:https://www.cnblogs.com/xiaoTT/archive/2013/03/28/2986367.html