At times when I do an export (expdp) from a database, the export just seems to last for ages…and then some..
While I was waiting one time for the export to finish, I found out the expdp in Oracle 11g make use of the streams_pool_size. I had AMM running,which should be responsible to up the pool, but still the process was slow. After checking, it turned out the streams_pool_size was 0 (as it is supposed to be with AMM enabled). The command to check the current set streams_pool_size is:
SQL> show parameters streams_pool_size;
So I killed the job, fired up an sql session to the database and gave the next command:
SQL> alter system set streams_pool_size=40M;
of when running a RAC (RacOne) instance:
SQL> alter system set streams_pool_size=40M scope=both sid='testdb';
After this change I started the expdp again, and it was done in a jiffy…Of course it depends on the size of the export, but if the sga_max_size allows it, you can always increase this value.
Until next time!