How to Delete Table, Index, Schema Statistics?

advertisements

_____________________________________________________________________________________________________________________

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

advertisements

 

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.

_____________________________________________________________________________________________________________________

Website Stats