Hello Experts,
DB -11.2.0.4
OS - RHEL 6.6
We recently changed below tables in sysaux tablespace from non-partitioned to interval partitioned tables as advised by Oracle support so that old statistics would get purged automatically.
SYS.'WRI$_OPTSTAT_HISTGRM_HISTORY'
sys.'WRI$_OPTSTAT_HISTHEAD_HISTORY'
Now I am struggling in taking export of these tables.
with expdp -
-bash-4.1$ nohup expdp \' / as sysdba\' file=expdb.dmp logfile=expdp.log tables=SYS.'WRI$_OPTSTAT_HISTGRM_HISTORY', sys.'WRI$_OPTSTAT_HISTHEAD_HISTORY' directory=DATA_PUMP_DIR &
ORA-39166: Object SYS.WRI$_OPTSTAT_HISTGRM_HISTORY was not found.
ORA-39166: Object SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at Thu Mar 24 16:20:33 2016 elapsed 0 00:02:00
expdp is showing expected behavior here as it cannot take backup of any object (not just $ tables) in sys schema , it will always fail with ORA-39166.
this is described in below doc -
DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Doc ID 553402.1)
then I tried legacy exp -
1. when the table was a non-partitioned table -
exp system file=export_WRI_1st_april.dmp log=exp_1st_april_log_WRI.log tables=sys.'WRI$_OPTSTAT_HISTGRM_HISTORY',sys.'WRI$_OPTSTAT_HISTHEAD_HISTORY' indexes=Y CONSTRAINTS=Y TRIGGERS=Y
1. When the table was non-partioned.
Current user changed to SYS
. . exporting table WRI$_OPTSTAT_HISTGRM_HISTORY 684900 rows exported
. . exporting table WRI$_OPTSTAT_HISTHEAD_HISTORY 81565 rows exported
Export terminated successfully without warnings.
2. when the table was changed to interval portioned table -
exp system file=export_WRI_1st_april.dmp log=exp_1st_april_log_WRI.log tables=sys.'WRI$_OPTSTAT_HISTGRM_HISTORY',sys.'WRI$_OPTSTAT_HISTHEAD_HISTORY' indexes=Y CONSTRAINTS=Y TRIGGERS=Y
About to export specified tables via Conventional Path ...
Current user changed to SYS
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully
It did not work for a single partition also -
exp system file=export_WRI_2nd_sept_april.dmp log=export_WRI_2nd_sept_april.log tables=sys.'WRI$_OPTSTAT_HISTGRM_HISTORY:SYS_P3068'
About to export specified tables via Conventional Path ...
Current user changed to SYS
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully
I think exp would not work for these kind of tables as interval partitioned tables are introduced in 11g R2 and there was not much changes happened in "exp" after 10g as expdp was a recommended way to do the tasks ( strongly my opinion, I may be wrong though), to check this claim i created a table in another schema with CTAS and then created same indices on it and tried both exp and expdp.
SQL> sho user;
USER is "HEMANT"
SQL>
create table local_table
TABLESPACE "SYSAUX"
PARTITION BY RANGE ("SAVTIME_DATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "P_PERMANENT" VALUES LESS THAN (TO_DATE(' 2016-09-12 04:56:27', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
as select * from sys.'WRI$_OPTSTAT_HISTGRM_HISTORY';
Now again with exp -
exp file=export_hemant_test.dmp log=export_hemant_test.log tables=hemant.local_table
About to export specified tables via Conventional Path ...
Current user changed to HEMANT
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully
expdp system dumpfile=expdb.dmp logfile=expdp.log tables=hemant.local_table directory=temp_dir
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=expdb.dmp logfile=expdp.log tables=hemant.local_table directory=temp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HEMANT"."LOCAL_TABLE":"P_PERMANENT" 5.929 KB 4 rows
. . exported "HEMANT"."LOCAL_TABLE":"SYS_P61" 5.875 KB 2 rows
. . exported "HEMANT"."LOCAL_TABLE":"SYS_P62" 5.875 KB 2 rows
. . exported "HEMANT"."LOCAL_TABLE":"SYS_P63" 5.875 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
D:\EXPDB.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 2 13:32:01 2016 elapsed 0 00:00:15
IMPDP also worked fine.
impdp system dumpfile=expdb.dmp logfile=impdp.log remap_table=local_table:local_new_table directory=temp_dir
. . imported "HEMANT"."LOCAL_NEW_TABLE":"P_PERMANENT" 5.929 KB 4 rows
. . imported "HEMANT"."LOCAL_NEW_TABLE":"SYS_P61" 5.875 KB 2 rows
. . imported "HEMANT"."LOCAL_NEW_TABLE":"SYS_P62" 5.875 KB 2 rows
. . imported "HEMANT"."LOCAL_NEW_TABLE":"SYS_P63" 5.875 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 2 13:53:12 2016 elapsed 0 00:00:04
--> so now we can take exp of sys objects but can not use expdp.
--> exp cannot take backup of interval partitioned tables.
--> expdp can take backup of interval partitioned tables but not in sys schema.
so how to take backup of interval portioned tables of sys schema ?
P.S. - PLEASE EXCUSE any typo in syntax as I could not copy from the server so typed everything manually here.
Regards,
Hemant