How to export schema or table statistics in oracle

<<Back to Oracle DATAPUMP Main Page

How to export schema or table statistics in oracle

Step1> create an staging table to hold the stats
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (
    ownname => 'TEST'
,   stattab => 'SCHEMA_STAT_TEST'
,   tblspace => 'USERS'
);
END;
/
PL/SQL procedure successfully completed.
Step2> Verify the Staging Table Details

SQL> select owner,table_name from dba_tables where table_name='SCHEMA_STAT_TEST'
OWNER                          TABLE_NAME
------------------------------ ----------------------------------------
TEST                       SCHEMA_STAT_TEST

1 row selected.
 SQL> select count(*) from TEST.SCHEMA_STAT_TEST;

  COUNT(*)
----------
         0

1 row selected.

To see the structure of the table you can describe it
SQL> desc TEST.SCHEMA_STAT_TEST

Now that we are done with the table prepration that is going to hold the stats, lets export the stats of schema TEST in table SCHEMA_STAT_TEST

Step3> Export Schema Stats.
BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS (
    ownname => 'TEST'
,   stattab => 'SCHEMA_STAT_TEST'
);
END;
/
PL/SQL procedure successfully completed.
OR
Export Table Stats.
To export Table Stats run below procedure
BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS (
    ownname => 'TEST'
, tabname => 'EMPLOYEES'
,   stattab => 'SCHEMA_STAT_TEST'
);
END;
/

Step3> export the table SCHEMA_STAT_TEST using expdp utility

Now since the table SCHEMA_STAT_TEST is populated with Status you  can simply export this table using expdp command and you are done.

$cat parfile=export.par

directory=EXT
dumpfile=EXP_TEST_SCHEMA_STATS_%U.dmp
FILESIZE=20G
logfile=EXP_TEST_SCHEMA_STATS_.log
tables=TEST.SCHEMA_STAT_TEST

$  expdp system@TST1P parfile=export.par

Export: Release 12.1.0.2.0 - Production on Fri Feb 8 11:16:29 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@TST1P parfile=export.par

........................................................
........................................................
 . exported "TEST"."SCHEMA_STAT_TEST"           612.4 MB 4005725 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/backup/EXT/EXP_TEST_SCHEMA_STATS_01.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Feb 8 11:17:41 2019 elapsed 0 00:01:05
Step4> Optionally drop the staging stats table
BEGIN
  DBMS_STATS.DROP_STAT_TABLE (
    ownname => 'TEST'
,   stattab => 'SCHEMA_STAT_TEST'
);
END;
/