oracle import & export 操作相關腳本 （轉）

1.export 前的準備工作： 注：在産品庫執行下列語句 1）獲得目前執行的SQL語句：

select osuser, username, sid, serial#, sql_text

 from v$session a, v$sqltext b

 where a.sql_address = b.address

  order by address, piece

  2）獲得某些使用者的資料庫對象存放在哪些表空間裡： select tablespace_name, round(sum(bytes)/1024/1024) sum_mbytes

from dba_data_files

where tablespace_name in

(select distinct tablespace_name

from dba_segments

where owner in ('SSO', 'SSOWEB')

)

group by tablespace_name

order by tablespace_name

3）獲得表空間未使用的空間： select tablespace_name, round(sum(bytes)/(1024*1024)) as free_space

from dba_free_space

group by tablespace_name

order by tablespace_name   4）獲得表空間空間細節： select

a.tablespace_name,

round(a.bytes/1024/1024) total,

round(b.bytes/1024/1024) used,

round(c.bytes/1024/1024) free,

round((b.bytes*100)/a.bytes) "% used",

round((c.bytes*100)/a.bytes) "% free"

from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c

where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name

order by tablespace_name

5）生成建立某些使用者的對象所在表空間的語句 set feedback off

set heading off

select 'create tablespace ' || tablespace_name || ' datafile ' || '''' || file_name ||'''' || ' size ' || round(bytes/1024/1024) || 'm'

from dba_data_files

where tablespace_name in

(select distinct tablespace_name

from dba_segments

where owner in ('SSO', 'SSOWEB')

)

set feedback on

set heading on

6）在産品庫執行 3.驗證 IMPORT 操作的結果是否正确的步驟，并記錄下每條指令的結果，以便跟導入後在測試庫執行的相同指令的結果進行對比。

2.導出導入操作：

注：在産品庫執行1）和2），在測試庫執行3）

1）建立pump目錄并授權 select * from dba_directories;

create directory pump_dir as '/opt/oracle/backup/pump';

grant read, write on directory pump_dir to <username>;

select * from user_role_privs;

2）導出 

expdp system/password

      dumpfile=pp_$(date +%y%m%d).dmp

      DIRECTORY=pump_dir

      parallel=2

      SCHEMAS=bbs,sso

      job_name=job_sso_exp$(date +%y%m%d) 

3）導入 

impdp system/password 

     dumpfile=pp_$(date +%y%m%d).dmp

     DIRECTORY=pump_dir

     TABLE_EXISTS_ACTION=REPLACE

     parallel=2

     SCHEMAS=bbs,sso

     job_name=job_sso_imp$(date +%y%m%d)     

3.驗證 IMPORT 操作的結果是否正确： 注：a.在測試庫執行下列語句     b.導入後，在測試庫執行下列語句，并跟在 1.export 前的準備工作        中在産品庫執行的相同語句的結果進行對比。 1）驗證兩個庫的資料庫對象數是否相同 select owner, object_type, count(*)

from dba_objects

where owner in ('MAIN', 'SSO', 'SSOWEB', 'USD')

group by owner, object_type

order by owner, object_type

2）驗證資料表的行數是否相同 

select owner, sum(num_rows) sum_rows

from dba_tables

where owner in ('SSO', 'SSOWEB', 'MAIN', 'USD')

group by owner

order by owner

3）驗證大表的行數是否相同

select owner, num_rows

from dba_tables

where owner in ('SSO', 'SSOWEB', 'MAIN', 'USD')

and num_rows > 1000000

order by owner, num_rows desc

4）

驗證索引的行數是否相同 

select owner, sum(num_rows) sum_rows

from dba_indexes

where owner in ('SSO', 'SSOWEB', 'MAIN', 'USD')

group by owner

order by owner

5）驗證大索引的行數是否相同 

select owner, num_rows

from dba_inexes

where owner in ('SSO', 'SSOWEB', 'MAIN', 'USD') and num_rows > 1000000

order by owner, num_rows     4.處理無效的資料庫對象： 注：a.在測試庫執行下列語句  1）查找某些使用者的無效對象 select object_type, object_name, status

from dba_objects

where owner in ('SSO', 'MAIN') and status = 'INVALID'

order by owner

  2）查找某些使用者無效的索引：

select owner, index_name, table_name, status

from dba_indexes

where status <> 'VALID' and owner in ('MAIN', 'SSO', 'USD')

3）查找某些使用者無效的索引分區：

select index_owner, index_name, partition_name, tablespace_name, status

from dba_ind_partitions

where index_name in (

select index_name

from dba_indexes

where status = 'N/A'

and owner in ('MAIN', 'SSO', 'SSOWEB'))

4）生成重建某些使用者無效索引的語句：

select 'alter index ' || owner || '.' || index_name || ' rebuild;'

from dba_indexes

where status = 'INVALID' and owner in ('SCOTT')

注：本操作因不關心存儲過程，是以沒有相應的重編譯存儲過程的語句。