Datapump EXPDP - ORA-39127 SYS.DBMS_AW_EXP.schema_info_exp error

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/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/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDCTL',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 473
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDDTA',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 505
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
Master table "SYSTEM"."EXPDP_METADATA" successfully loaded/unloaded

SOLUTION:

SQL> DELETE FROM sys.exppkgact$ WHERE package='DBMS_AW_EXP';

2 rows deleted.

SQL> commit;

Commit complete.

Cleanup orphaned datapump jobs

Assume you have some old datapump job, not running for ages:

SQL> select owner_name, job_name,state from dba_datapump_jobs;

OWNER_NAME JOB_NAME STATE
--------------------------------------------------------------------------
SYSTEM TAB_TOREDDTA EXECUTING
DATAPUMP SYS_EXPORT_TABLE_01 NOT RUNNING


You can try attach to this job to use kill_job like this:

expdp datapump/pass@schema attach=SYS_EXPORT_TABLE_01

But in this step you may have a lot of different errors that caused you are not able to attach. How to get rid of this job?
You need to delete master table/ First find it:

SQL> select owner,object_name,status,object_id from dba_objects where object_name = 'SYS_EXPORT_TABLE_01'

OWNER OBJECT_NAME STATUS OBJECT_ID
-------------------- ------------------------------ ------- ----------
DATAPUMP SYS_EXPORT_TABLE_01 VALID 88842

Then drop it:

drop table datapump.SYS_EXPORT_TABLE_01;

And check whether it helps:

SQL> select owner_name, job_name,state from dba_datapump_jobs;

OWNER_NAME JOB_NAME STATE
------------------------------ -----------------------------------------
SYSTEM TAB_TOREDDTA EXECUTING

Identify FULL SCANS in Oracle 10.2.0 database

When you want to see what objects are the most often full scanned, use this:

select object_name,count(*) runs from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat
where operation='TABLE ACCESS' and options='FULL' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
group by object_name
order by runs desc
/

Then, you can try to identify the most often issued sql statement:

select distinct sqlstat.sql_id, count(*) FOUND_IN_AWR,sqlarea.sql_text from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat, v$sqlarea sqlarea
where operation='TABLE ACCESS' and options='FULL' and object_name ='<found in part1>' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
and plansql.sql_id = sqlarea.sql_id
group by sqlstat.sql_id,sqlarea.sql_text
order by FOUND_IN_AWR desc
/

Create outline knowing needed hint

Let's assume you have a query with suffer performance problems. You worked out what hint may help, but you cannot apply hint to query as it is genereted by application. In case of simple hints, you can do this updating ol$hint table (in private outline). However sometimes it is not simple to know what updates needs to be done. This is simple way of doing this:

1. Identify query

2. CREATE OR REPLACE OUTLINE ORIGINALSQL ON

/

3. CREATE OR REPLACE OUTLINE HINTSQL ON

/

4. UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL')
/

5. commit
/

6. DROP OUTLINE HINTSQL
/

7. alter outline ORIGINALSQL rename to
/