oracle import & export 操作相关脚本

---

 

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 ;

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

 

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