English Sentence Loading...
英语句子加载中...
Oracle

oracle import & export 操作相关脚本

作者: admin  日期: 2008-02-22 10:26

字体大小: 小 中 大
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')

引用通告地址: 点击获取引用地址
标签: Oracle
评论: 0 | 引用: 0 | 阅读: 81
发表评论
昵　称: 密　码:
网　址: 邮　箱:
选　项:    
头　像:
内　容: