We can create a stat table in Oracle that can be used to store statistics of the schema, tables, and indexes. In this post we will see how to create and drop the stat table in Oracle, and what are the uses of it. what is the structure of the table?
How to create stat table in Oracle
We can create the stat table using the DBMS_STATS.CREATE_STAT_TABLE procedure
Syntax
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME', stat_tab => 'STATS_TABLE', tblspace => 'STATS_TABLESPACE');
Examples
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT,'STATTAB', 'TOOLS');
We can also multiple stats table also and use them for different set of tables
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT,'STATTAB1', 'TOOLS');
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT,'STATTAB2', 'TOOLS');
Uses of the stats table
- This can be used to export the stats of the schema or table and then export that table and importing to another database and then import to the schema in another database
- We can compare the stats of the table with time using this table
Structure of the table
This table is simple with lots of columns. As per Oracle The columns and types that compose this table are irrelevant as they should be accessed solely through the procedures in this package i.,e DBMS_STATS
How to drop the stat table
Here ownname is the owner of the stats table and stattab is the table name
Syntax
EXEC DBMS_STATS.DROP_STAT_TABLE('&ownner','&table_name');
Example
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT','STATTAB');
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT','STATTAB1');
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT','STATTAB2');
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT','STATTAB3');
I hope you like this article on How to create stat table in Oracle and How to drop the stat table. Please do provide the feedback
Related Articles
How to compare statistics using history for a table in Oracle
How to compare statistics using stattab for a table in Oracle
How to transfer statistics between databases in Oracle
How to import statistics in Oracle
How to export statistics in Oracle