-- 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_HOMEselect 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_CNTfrom dual-- dump前請檢查新舊DB上是否有DUMP_DIR目錄，沒有就創建并授權CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/u02/dmp';GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO systemGRANT READ, WRITE ON DIRECTORY DUMP_DIR TO publicexpdp system/pegadb*system schemas=xxx directory=DUMP_DIR content=metadata_only dumpfile=20130708_shpndb0_schemas.dmp logfile=20130708_shpndb0_schemas.logimpdp 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 Publicgrant 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 MONDBselect '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);
