• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to create stat table in Oracle

How to create stat table in Oracle

May 2, 2023 by techgoeasy Leave a Comment

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');

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to drop the sql baseline in Oracle
  • how to create sql baseline from AWR repository without STS
  • how to create sql baseline from Sql tuning set in Oracle
  • How to create sql baseline from cursor cache in Oracle
  • how to create sql baseline in oracle 19c

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us
x
x