Export import statistics in Oracle

  1. Create Statistics table
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');

2. Export the Stats of Schema or table

For Schema:
exec dbms_stats.export_schema_stats(ownname => 'SCOTT',stattab => 'STAT_BACKUP');

For Table:
exec dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'EMPLOYEES',stattab => 'STAT_BACKUP');

3. Import the Stats of Schema or Table

For Schema Stats:
exec dbms_stats.import_schema_stats(ownname => 'SCOTT',stattab => 'STAT_BACKUP');

For Table Stats:
exec dbms_stats.import_table_stats(ownname => 'SCOTT',tabname => 'EMPLOYEES',stattab => 'STAT_BACKUP');

4. Drop Stats table if not required.

exec dbms_stats.drop_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.