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-20 All Rights Reserved | Site Map | Contact | Disclaimer