dump 迁移oracle db

2/13/2017来源:SQL技巧人气:1846

-- 1:创建表空间 -- data -> /data1, index -> /data3, 请按需要修改----可以用df -h 查看有幾個data select 'CREATE TABLESPACE '||tablespace_name||' DATAFILE '||case instr(upper(tablespace_name),'IND') when 0 then '''/data1' else '''/data3' end        ||'/oradata/shpnf1qa/'||lower(tablespace_name)||'01.dbf'' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8G;' as tbs   from dba_tablespaces  where tablespace_name not in('SYSTEM','SYSAUX','UNDOTBS1','TEMP','OGG') -- 2 创建角色 select 'CREATE ROLE '||role||' NOT IDENTIFIED;'   from dba_roles where role not in( /* select ''''||role||''',' from dba_roles; -- 先用此SQL跑新DB,把结果填到下面,再去旧DB跑整个SQL */ )and role not in 'GGS_GGSUSER_ROLE' -- 3 导出旧DB的Schemas(metadata only),请务必注意Oracle_SID,同Server多版本ORACLE的还要注意ORACLE_HOME select count(*)       --,get_charcount(wm_concat(username),','),wm_concat(username)   from dba_users where username not in( /* select ''''||username||''',' from dba_users; -- 先用此SQL跑新DB,把结果填到下面,再去旧DB跑整个SQL */ ) and username not in('OGG','PERFSTAT'); select get_charcount('xxx',',') CHAR_CNT from dual -- dump前请检查新旧DB上是否有DUMP_DIR目录,没有就创建并授权 CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/u02/dmp'; GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO system GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO public expdp system/pegadb*system schemas=xxx directory=DUMP_DIR content=metadata_only dumpfile=20130708_shpndb0_schemas.dmp logfile=20130708_shpndb0_schemas.log impdp system/pegadb*system schemas=xxx directory=DUMP_DIR content=metadata_only dumpfile=20130708_shpndb0_schemas.dmp logfile=20130708_shpndb0_schemas.imp.log -- 4 创建同义词 select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'  from dba_synonyms where table_owner in ('TP','ET_EAI','TSP_PADB') -- 5 授权系统表to Public grant select on dba_tab_PRivs to public; grant select on dba_source to public; grant select on dba_jobs to public; grant select on v_$lock to public; grant select on v_$session to public; -- grant sys table to MONDB select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'   from dba_tab_privs where grantee='MONDB'; 搜出結果然後在新DB上執行 -- 6 编译失效Objects  GRANT SELECT ON SYS.DBA_OBJECTS TO PUBLIC;  GRANT SELECT ON SYS.DBA_LOG_GROUPS TO PUBLIC; EXEC dbms_utility.compile_schema('OGG',false); EXEC dbms_utility.compile_schema('TP',false); EXEC dbms_utility.compile_schema('ET_EAI',false); EXEC dbms_utility.compile_schema('TSP_PADB',false); EXEC dbms_utility.compile_schema('MONDB',false); EXEC dbms_utility.compile_schema('PUBLIC',false);