How to export and import table statistics in oracle?

@aniya.jaskolski 

Exporting and importing table statistics in Oracle can be achieved through the use of the DBMS_STATS package. Here are the steps to export and import table statistics in Oracle:

Exporting table statistics:

1. Connect to your Oracle database using SQL*Plus or any other SQL client.
2. Use the DBMS_STATS.EXPORT_TABLE_STATS procedure to export statistics for a specific table. The syntax is as follows:

1
2
3
4
5
6
7
8
9

BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname    => 'schema_name',
    tabname    => 'table_name',
    stattab    => 'statistics_table_name',
    statid     => 'statistics_id'  
  );
END;
/

1. Replace 'schema_name', 'table_name', 'statistics_table_name', and 'statistics_id' with the appropriate values for your table.
2. Once executed, this procedure will export the table statistics to the specified statistics table.

Importing table statistics:

1. Connect to your Oracle database using SQL*Plus or any other SQL client.
2. Use the DBMS_STATS.IMPORT_TABLE_STATS procedure to import statistics for a specific table. The syntax is as follows:

1
2
3
4
5
6
7
8
9

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(
    ownname    => 'schema_name',
    tabname    => 'table_name',
    stattab    => 'statistics_table_name',
    statid     => 'statistics_id'  
  );
END;
/

1. Replace 'schema_name', 'table_name', 'statistics_table_name', and 'statistics_id' with the appropriate values for your table.
2. Once executed, this procedure will import the table statistics from the specified statistics table to the table in the database.

By following these steps, you can easily export and import table statistics in Oracle using the DBMS_STATS package.