Datapump Export Schema statistics only

Exporting Schema statistics only from one server to another server can be done in 2 ways

METHOD 1:  using DATAPUMP

Hello Every one today I did Data pump export of only Schema META DATA(entire schema objects structure ) without moving actual data, to do so use CONTENT=METADATA_ONLY parameter to get only schema metadata.

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

·         ALL unloads both data and metadata. This is the default.

·         DATA_ONLY unloads only table row data; no database object definitions are unloaded.

·         METADATA_ONLY unloads only database object definitions; no table row data is unloaded.

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

vi expdp_metadata.par

DIRECTORY=DPUMP_OMEGA_DIR1

DUMPFILE=SYSFM_META_%U.dmp

LOGFILE=LOGFILE_OMEGA_DIR1:metadata.log

PARALLEL=16

SCHEMAS=SYSFM

CONTENT=METADATA_ONLY

JOB_NAME=metadata_expdp

$ expdp  SYSFM/********  parfile=expdp_metadata.par

Export: Release 11.2.0.1.0 - Production on Fri Feb 17 21:31:00 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

Starting "SYSFM"."METADATA_EXPDP":  SYSFM/******** parfile=expdp_metadata.par

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/DB_LINK

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/COMMENT

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "SYSFM"."METADATA_EXPDP" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSFM.METADATA_EXPDP is:

  /var/backup/omega/SYSFM_META_01.dmp

Job "SYSFM"."METADATA_EXPDP" successfully completed at 22:52:11

So SYSFM_META_01.dmp  dump file has been created in the DUMP Directory ‘/var/backup/omega’.

To check the status of the data pump job while running

SQL> select  OWNER_NAME,JOB_NAME,STATE  from  dba_datapump_jobs  where  JOB_NAME = 'METADATA_EXPDP';

Copy the dumpfile to the destination server and Perform similar steps with the import option.
If we want to export all the metadata Except few things then add 'EXCLUDE' parameter in the parfile and execute it Ex: Exclude = GRANTS,STATISTICS

METHOD 2:  using dbms_stats package

Step 1:

On the Source side create a stat table to hold the schema statistics using dbms_stats.create_stat_table(‘<SCHEMANAME>’,’<TABLENAME>’)

SQL> Conn sysfm/******

Connected.

SQL> EXEC DBMS_STATS.create_stat_table('SYSFM','STATS');

PL/SQL procedure successfully completed.

Step2:  

Export the SYSFM schema statistics into the table STATS.

begin

dbms_stats.export_schema_stats(ownname => 'SYSFM',stattab => 'STATS');

end;

/

Now all statistics from the SYSFM schema are stored in the table named “STATS”. The table must be transferred to the destination schema

Step3:  

Now move the STATS table which has created to the destination server by using expdp or exp utility or database link.

After copying the dumpfile into the destination server Import the STATS table into the same schema ‘SYSFM’

begin

exec dbms_stats.import_schema_stats(ownname => 'SYSFM',stattab => 'STATS');

 end;

 /

PL/SQL procedure successfully completed.