ORA-01690: sort area size too small

Error
Expdp is failing due to the error ORA-01690: sort area size too small. In my case PGA parameter is not set.
Description
After that started the Full expdp backup, Is failing. Here is the error given below:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [INDEX_STATISTICS]
ORA-01690: sort area size too small
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164

Solution
Check the memory componenet in Oracle.

SQL> show parameter sga

NAME         TYPE        VALUE 
-----------  ---------   --------- 
lock_sga     boolean     FALSE 
pre_page_sga boolean     FALSE 
sga_max_size big integer 6528M 
sga_target   big integer 6528M 

SQL> show parameter pga

 
NAME                  TYPE         VALUE 
--------------------  -----------  --------- 
pga_aggregate_target  big integer  0 

-- IF pga aggregate is zero then check memory parameter
SQL> show parameter memory

NAME                      TYPE         VALUE 
------------------------- -----------  ------- 
hi_shared_memory_address  integer      0 
memory_max_target         big integer  0 
memory_target             big integer  0 
shared_memory_address     integer      0 

2. Set the value of PGA target because memory target or PGA target is not set.

alter system set pga_aggregate_target = 2G scope=spfile;

3. It’s fixed the issue in EXPDP.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.