Export/Import Data Task
"建议: 导入数据之前注意检查各个TABLE之间是否有严格的关联,比如 FK, PK 等Constraint, 如果有,需要先disable FK.
先导入数据,然后进行Index的建立,最后进行权限赋予及Trigger建立等,总时间大概为30分钟。
select * from all_constraints where constraint_type='R' and WNER='DFMS' ; 如何查找FK外键"
自动归档模式下,注意导入数据时定期清理归档
1. Schema : DFMS
2. Objects : Table, View, Synonyms, Function, Procedure, Package, Trigger, Sequence, Jobs, Profiles
3. Method : Oracle10g expdp / impdp
4. How to :
"A. Before using expdp, at first we need to create directory in the database server , and grant privilege ""read,write"" to the schema .
[on resource server]
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/data/dump_dir/';
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO SYSTEM; (DFMS is the schema name that required to be exported)
[on target server]
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/data/dump_dir/';
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO SYSTEM; (DFMS is the schema name that required to be imported)
注意: 导入之前如果先建立几个分区表的定义 。 导入的时候不会导入这些分区表对应的原表的metadata, 也不导入这这些表的data , 所以要注意。 ORA-39151: Table ""DFMS"".""WIP_D_SN_INPUT"" exists. All dependent metadata and data will be skipped due to table_exists_action of skip , 相关的trigger 等也不会导入。
这里我们需要先导入所有表的metadata,然后修改几个分区表的定义,最后导入data(不包含metadata)
B.
$ expdp system/oracle directory=DUMP_DIR content=data_only dumpfile=full_dfms20110630.dmp schemas=dfms [on the resource database server]
$ impdp system/oracle directory=DUMP_DIR content=data_only dumpfile=full_dfms20110630.dmp schemas=dfms [on the target database server]
然后修改10个分区表的定义(记录针对这些table的objects , 比如trigger等,drop掉,然后使用分区表定义重新建立)。
$ expdp system/oracle directory=DUMP_DIR content=data_only dumpfile=full_dfms20110630.dmp schemas=dfms [on the resource database server]
$ impdp system/oracle directory=DUMP_DIR content=data_only dumpfile=full_dfms20110630.dmp schemas=dfms [on the target database server]
DFMS : system / system#oracle$
RAC : system / system#oracle
" CONTENT Specifies data to unload where the valid keywords are: (ALL), DATA_ONLY, and METADATA_ONLY.
We need 5~10 mins to export DFMS DB system , dmp file size : about 13G
It will take 2~3 mins to copy dmp file to the target database server
It will take 0.5~1 Hours to imp dmp file to the target database server
It will take 10mins to create index .
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-706253/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-706253/