分类: 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有关!郁闷~)