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')

 

 

 

注：本操作因不关心存储过程，所以没有相应的重编译存储过程的语句。