Today we are going to have a look at steps we use for lock/unlock statistics on a table.
In the world of technology, we all love to work with one click. The way we DBAs would like to work in the same tradition as we know sometimes we may not want to analyze a table automatically and want it to analyze later manually where we are thinking data in the table does not change. In that case, we can lock the statistics gathering for a particular table.
SQL>conn oraclehelp/password -- create table SQL> create table EMPLOYEE_1( id number, name varchar2(50) ); Table created. -- create index SQL> create index pk_employee_idx on employee(id); Index created. SQL> conn / as sysdba -- Check the locked status of the table SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'EMPLOYEE_1' and owner = 'ORACLEHELP'; STATTYPE_LOCKED -------------------------------------- Null
means the table is not locked for gathering statistics.
-- lock Gathering Statistics
SQL> exec dbms_stats.lock_table_stats('ORACLEHELP, 'employee_1');
PL/SQL procedure successfully completed.
-- Check the status after locked
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'EMPLOYEE_1' and owner = 'ORACLEHELP';
STATTYPE_LOCKED
---------------
ALL
The table status is locked now.
— Let try now to gather statistics on the locked table
SQL> exec dbms_stats.gather_table_stats('ORACLEHELP', 'employee_1');
BEGIN dbms_stats.gather_table_stats('ORACLEHELP', 'employee_1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1
— Let’s try now to gather statistics on the index using analyze
SQL> analyze index SCOTT.pk_employee_idx compute statistics; analyze index scott.pk_employee_idx compute statistics * ERROR at line 1: ORA-38029: object statistics are locked
— unlock gathering statistics
SQL> exec dbms_stats.unlock_table_stats('ORACLEHELP', 'employee_1');
PL/SQL procedure successfully completed.
— Now try to gather table statistics again
SQL> exec dbms_stats.gather_table_stats('ORACLEHELP', 'employee_1');
PL/SQL procedure successfully completed
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
