Exporting schema taking time

Swadhin Ray
Swadhin Ray used Ask the Experts™
Hello Experts,
I am facing an issue while exporting one schema which is taking huge time without any rows.

Command used:
exp username/password@instance file=user_name.dmp log=username_exp.log GRANTS=y CONSTRAINTS=y  ROWS=n statistics=none

Open in new window

Cannot use data pump as directory access to servers are restricted.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

How many tables are in this schema?  How many rows do the tables have?  And, how many blocks do the tables use?

To answer those questions, please run this query and post the results here:

select table_name, blocks, num_rows, last_analyzed
from all_tables where owner = '[schema_name]'
order by blocks desc, table_name;
Here are the details :

total number of tables := 1015
total number of rows := 1906967872
total number of blocks := 69096012
johnsoneSenior Oracle DBA

Curious why number or rows matter.  Export is begin done with ROWS=n

While it is running, I would be looking at wait events.  See what the process is waiting for.  I'm not sure what it could be, but it is likely waiting for something.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark GeerlingsDatabase Administrator

Good point johnsone, I had missed the "ROWS=n".

Can you give us some more information, like:
1. Have you run other exports like this successfully?
2. Where does this exp job run, on the server or on a client?  (If on a client, the network speed and distance may be factors).
On client and within same network.  And yes other exports using expdp is fast then exp.
Mark GeerlingsDatabase Administrator

Exp and expdp are two, very-different programs.  Their runtimes are usually very different.  Have you done any exp runs that are fast (or at least acceptable)?
not on this one but other systems yes that’s fast when compared with this
johnsoneSenior Oracle DBA

Has you ever run exp successfully from this client?  I'm thinking some sort of firewall or network setting.  I'm guessing that it should time out if that was the case, but worth a shot.

As I said before, I would run exp and then look in the database and see what the issue it.  Likely a wait event there, but you cannot tell without actually checking it.
let me try on another client and see if , may be client issue as I was firing it from a VDI machine

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial