分类： Oracle
2010-04-12 22:29:11
ORACLE 2010-03-28 21:09:50 阅读45 评论0 字号：大中小
第一部分：到数据前先删除目标数据库上的用户，创建目录.... 1
1，先找出最大的几张表，要是是备份或者日志之类的无关紧要的表呢，就不要导出他们了（用exlude参数）。... 1
2，导出mctest3模式并除日志表SYS0023_TABLE_LOG_OLD.. 2
3，导出数据库前需要确认的三样东西，否则将来导入会有问题的哦... 2
3.2，统计外部表及其存放的数据库目录，应该在目标库上线创建该目录，否则导入会报错！... 3
3.3，检查使用到的表空间，目标库上也应该有这些表空间... 3
2，创建目标用户，这里我要remap用户名；旧用户名：mctest3；新用户名：mctest. 4
正文：
SQL> select sid,serial#,username,machine from v$session where username='MCTEST3';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
149 806 MCTEST3 kk.com.cn
SQL> alter system kill session '149,806';
System altered.
SQL> drop user mctest3 cascade;
[oracle@meta ~]$ mkdir /tmp/exp_dir/
SQL> create or replace directory exp_dir as '/tmp/exp_dir';
Directory created.
在源数据库上操作
这个方法统计的不太准确，但大致接近！～
SQL> select * from (select table_name,blocks*8192/1024/1024 from user_tables order by blocks desc) where rownum < 20;
TABLE_NAME BLOCKS*8192/1024/1024
----------------------- ------- ---------------------
EXT_FIDELIO_STORE_QTY <==可见desc的时候null值是很大的哦！
EXT_CONVERSION
EXT_STORE
EXT_CREDITOR_MASTER
EXT_PRODUCT_FB
EXT_BANK_DETAIL
EXT_PRODUCT_NEWCASTLE_0423
EXT_PRODUCT_NEWCASTLE
EXT_PRODUCT
SYS0023_TABLE_LOG_OLD 734.289063 <==734M，太大了！
I0222_INV_BILL_ACT 58.0703125
I0210_INV_COUNT_DETAIL 48.890625
I0222_INV_BILL_ACT_04_08 45.8671875
I0222_INV_BILL_ACT_BAK 35.1171875
I0222_INV_BALANCE 23.8203125
I0216_INV_BALANCE 22.2265625
I0222_INV_BALANCE_BAK 20.5546875
ORDER0036_ORDER_DETAIL 20
ORDER0031_ORDER_DETAIL 19.9921875
19 rows selected.
参数说明：http://bkeep.blog.163.com/blog/static/123414290201022882518197/
思路：可以先导出这些表的结构信息（不要数据）。再用下面的命令导出整个模式并排除该日志表！
[oracle@kk bdump]$ expdp system/sys directory=exp_dir dumpfile=test.dump schemas=zbb exclude=table:\"IN \(\'SYS0023_TABLE_LOG_OLD\'\)\"
Export: Release 10.2.0.1.0 - Production on Wednesday, 24 March, 2010 3:43:22
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Da
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=exp_dir dumpfile=test.dump schemas=zbb exclude=table:"IN ('SYS0023_TABLE_LOG_OLD')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DA
奇怪：上面的方法导出来的dump文件跟包含该日志表的dump文件一样大！！！！！
SQL> select object_type,count(*) from dba_objects
SQL> where owner='MCTEST3'
SQL> GROUP by object_type
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 374
PROCEDURE 128
PACKAGE BODY 18
LOB 4
PACKAGE 19
TRIGGER 646
TABLE 553
SYNONYM 39
INDEX 423
FUNCTION 45
VIEW 53
JAVA CLASS 1
JAVA SOURCE 1
13 rows selected.
SQL> select count(*) from dba_objects where owner='MCTEST3';
COUNT(*)
----------
2304
SQL> select TABLE_NAME, DEFAULT_DIRECTORY_NAME from dba_external_tables where owner = 'MCTEST3';
TABLE_NAME DEFAULT_DIRECTORY_NAME
------------------------------ ------------------------------
EXT_BANK_DETAIL ADMIN_DAT_DIR
EXT_STORE ADMIN_DAT_DIR
EXT_CREDITOR_MASTER ADMIN_DAT_DIR
EXT_PRODUCT_FB ADMIN_DAT_DIR
EXT_CONVERSION ADMIN_DAT_DIR
EXT_FIDELIO_STORE_QTY ADMIN_DAT_DIR
EXT_PRODUCT_NEWCASTLE ADMIN_DAT_DIR
EXT_PRODUCT_NEWCASTLE_0423 ADMIN_DAT_DIR
EXT_PRODUCT ADMIN_DAT_DIR
9 rows selected.
SQL> select * from dba_directories where directory_name = 'ADMIN_DAT_DIR'
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------- --------------------------- ---------------------------------------
SYS ADMIN_DAT_DIR /usr/local/pkg/apps/datacenter/file/dbfile
--for tables
SQL> select count(*),TABLESPACE_NAME from dba_tables
where owner='MCTEST3'
group by TABLESPACE_NAME;
COUNT(*) TABLESPACE_NAME
---------- ------------------------------
23 ZBB
9
9 MCTEST3
23 USERS
487 SYSTEM
--for indexes
SQL> select count(*),TABLESPACE_NAME from dba_indexes
where owner='MCTEST3'
group by TABLESPACE_NAME;
COUNT(*) TABLESPACE_NAME
---------- ------------------------------
3 MCTEST3
14 USERS
410 SYSTEM
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='MCTEST3';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------------- -------------- ------------------ ---------------------------------------
MCTEST3 MCTEST3 TEMP2
在目标数据库上操作
SQL> create tablespace zbb
2 datafile '/opt/oracle/oradata/boy/zbb01.dbf' size 100M
3 autoextend on;
Tablespace created.
SQL> run
1 create tablespace mctest3
2 datafile '/opt/oracle/oradata/boy/mctest301.dbf' size 100M
3* autoextend on
Tablespace created.
注意：如果用db_links来倒数据，这里还需要创建对应的临时表空间（意思是用户在源和目标库上使用的临时表空间要一致）
SQL> SELECT * FROM DBA_DB_LINKS;
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------------------------------------- --------------- -------- -----------------
PUBLIC MC37.REGRESS.RDBMS.DEV.US.ORACLE.COM MCTEST3 mc 23-MAR-10
SQL> create temporary tablespace temp02
2 tempfile '/opt/oracle/oradata/boy/temp02.dbf' size 10M
3 extent management local uniform size 1M;
Tablespace created.
Tips：所需表空间已经创建好了。包括temp2
SQL> conn /as sysdba
Connected.
SQL> create user zbb identified by zbb
2 default tablespace zbb
3 account unlock;
User created.
SQL> grant dba to mctest;
Grant succeeded.
#mkdir -p /usr/local/pkg/apps/datacenter/file/dbfile
SQL> create or replace directory ADMIN_DAT_DIR as '/usr/local/pkg/apps/datacenter/file/dbfile';
Directory created.
这里remap mctest3 到zbb；remap system表空间到zbb表空间！
[oracle@kk exp_dir]$ impdp system/sys DIRECTORY=exp_dir DUMPFILE=mctest3.dump REMAP_SCHEMA=mctest3:zbb REMAP_TABLESPACE=system:zbb
。。。。。。。
结果：导入成功！！！
注意：这里会将该用户的jobs也导入进来！
遗憾：导入到最后要等特别长的时间！！（好像跟导入jobs有关！郁闷～）
