This content has been marked as final. Show 7 replies
Hello Ados ,
To start with , please post your export script and the logfile which is getting generated. I believe you would have specified the logfile paramter in the export expdp script you have executed.
Edited by: Kk on Jun 2, 2011 5:33 AM
The command I'm using is:
expdp system/xxxx schemas=my_schema directory=datapump_dir dumpfile=my_schema.dmp logfile=my_schema.log
As you can see, the logfile is not keeping up with what is actually happening, I guess it needs to generate more lines before the logfile updates. I believe it's really working on the TABLES already, not stuck with MATERIALIZED VIEWS as the log suggests:
;;; Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 02 June, 2011 6:00:00 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=my_schema directory=datapump_dir dumpfile=my_schema.dmp logfile=my_schema.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 165.8 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/LIBRARY/LIBRARY Processing object type SCHEMA_EXPORT/LIBRARY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
HI Ados ,
Is it still running or done ?
Any updates on this ?
It is still running , once it is done you can see SYSTEM"."SYS_EXPORT_SCHEMA_01 is successfully exported in the end of your log file.
Thanks for replying. Yes, it was still running, very slowly. I had to kill it though as I couldn't leave it running over the weekend.
I'm still none the wiser as to why it is going so slowly with this table.
Once I had a 40gb table. The table was pretty dynamic. Bulk data was inserted and deleted from the table (different data). So the table was actualy 8gb large, but since there was no shrink, the size used by the table was 40gb.1 person found this helpful
Maybe it's the same situation. Even if you only have 30 millions rows, check dba_segment and see how big this table is. Export will not export empty blocks, so the export file should not be large, but inside the database the table can be huge.
Also, in which tablespace does this table reside? On windows there can be issues with datafile fragmentation. Check also this aspect.
Thanks for the reply.
My problem occurs on several tables, and I think it's just down to fragmentation. I've been able to rebuild some of the tables (CTAS, rename) and they datapump out in minutes. Unfortunately I'm still left with the one 25Gb one which I cannot rebuild at present. Luckily, using good old fashioned exp I can get a dump of the table in a reasonable time.
Also, in which tablespace does this table reside? On windows there can be issues with datafile fragmentation. Check also this aspect.That looks interesting, any references/documents that discuss this issue?