How to exclude the statistics, table_statistics, index_statistics when export of dumpfile in Oracle database 19C
Posted by Mir Sayeed Hassan on December 3rd, 2024
How to exclude the statistics, table_statistics, index_statistics when export of dumpfile in Oracle database 19C
Check the database version SYS> select name, status, open_mode, version from V$database, v$instance; NAME STATUS OPEN_MODE VERSION --------------------------------------------------- ORA19CDB OPEN READ WRITE 19.0.0.0.0
Note: – Use the below parameter to exclude the statistics EXCLUDE=STATISTICS in expdp
– This can be applied on table/schema/fulldatabase level export
exclude=statistics : This will exclude both Table & Index statistics.
exclude=table_statistics —> This will exclude only Table statistics.
exclude=index_statistics —> This will exclude only Index statistics.
Use of exclude=statistics parameter in expdp in schema level backup: schema name “HASSAN”
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_stat_03dec24.dmp logfile=hassan_stat_03dec24.log schemas=hassan exclude=statistics Export: Release 19.0.0.0.0 - Production on Tue Dec 03 07:01:52 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_stat_03dec24.dmp logfile=hassan_stat_03dec24.log schemas=hassan exclude=statistics Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows . . exported "HASSAN"."T1" 5.632 KB 9 rows . . exported "HASSAN"."TEST1" 5.125 KB 4 rows . . exported "HASSAN"."TEST2" 5.109 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/hassan_stat_03dec24.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 03 07:02:18 2024 elapsed 0 00:00:22
Use of exclude=table_statistics parameter in expdp in schema level backup: schema name “HASSAN”
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_03dec24.dmp logfile=hassan_03dec24.log schemas=hassan exclude=table_statistics Export: Release 19.0.0.0.0 - Production on Tue Dec 03 06:59:00 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_03dec24.dmp logfile=hassan_03dec24.log schemas=hassan exclude=table_statistics Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows . . exported "HASSAN"."T1" 5.632 KB 9 rows . . exported "HASSAN"."TEST1" 5.125 KB 4 rows . . exported "HASSAN"."TEST2" 5.109 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/hassan_03dec24.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 03 06:59:29 2024 elapsed 0 00:00:25
Use of exclude=index_statistics parameter in expdp in schema level backup: schema name “HASSAN”
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_inx_03dec24.dmp logfile=hassan_inx_03dec24.log schemas=hassan exclude=index_statistics Export: Release 19.0.0.0.0 - Production on Tue Dec 03 07:03:39 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_inx_03dec24.dmp logfile=hassan_inx_03dec24.log schemas=hassan exclude=index_statistics Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows . . exported "HASSAN"."T1" 5.632 KB 9 rows . . exported "HASSAN"."TEST1" 5.125 KB 4 rows . . exported "HASSAN"."TEST2" 5.109 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/hassan_inx_03dec24.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 03 07:04:08 2024 elapsed 0 00:00:24