How to Delete Table, Index, Schema Statistics?

_____________________________________________________________________________________________________________________

Here are the commands to delete the statistics.
Table:-
       EXEC dbms_stats.delete_table_stats('<TALE OWNER>', '<TABLE NAME>');

EXEC dbms_stats.delete_table_stats(OWNNAME=>'<TALE OWNER>', TABNAME=>'<TABLE NAME>');

Index:-
       EXEC dbms_stats.delete_index_stats('TEST', 'EMP_PK');

EXEC dbms_stats.delete_index_stats(OWNNAME=>'TEST', INDNAME=>'EMP_PK');

Schema:-

EXEC dbms_stats.delete_schema_stats('<SCHEMA NAME>');

Examples:-
Here are my schema tables which is analyzed on 30th September.
SQL> select table_name, last_analyzed from all_tables where owner='TEST'

TABLE_NAME            LAST_ANAL
------------------------- ---------
BONUS                 30-SEP-19
DEPARTMENT            30-SEP-19
EMPLOYEE              30-SEP-19
PROJECT               30-SEP-19
PROJECT_PARTICIPATION 30-SEP-19
ROLE                  30-SEP-19
SALARYGRADE           30-SEP-19

7 rows selected.

Drop the table statistics
   1.  SQL>  EXEC dbms_stats.delete_table_stats('TEST', 'BONUS');

PL/SQL procedure successfully completed.

   2.  SQL>  EXEC dbms_stats.delete_table_stats(OWNNAME=>'TEST', TABNAME=>'BONUS');

PL/SQL procedure successfully completed.


SQL> select table_name, last_analyzed from all_tables where owner='TEST' and table_name='BONUS';

TABLE_NAME            LAST_ANAL
------------------------- ---------
BONUS


Drop the Index statistics

SQL> select index_name, last_analyzed from all_indexes where table_name ='EMPLOYEE'

INDEX_NAME            LAST_ANAL
------------------------- ---------
EMP_PK                30-SEP-19

   1.  SQL>  EXEC dbms_stats.delete_index_stats('TEST', 'EMP_PK');

PL/SQL procedure successfully completed.

   2.  SQL>  EXEC dbms_stats.delete_index_stats(OWNNAME=>'TEST', INDNAME=>'EMP_PK');

PL/SQL procedure successfully completed.

Drop the SCHEMA statistics


SQL>  EXEC dbms_stats.delete_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from all_tables where owner='TEST';

TABLE_NAME            LAST_ANAL
------------------------- ---------
BONUS
DEPARTMENT
EMPLOYEE
PROJECT
PROJECT_PARTICIPATION
ROLE
SALARYGRADE

7 rows selected.

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google