How to lock & Unlock table stats:
Whenever we do schema refresh from one db to another db or one server to another server there is a need to rename the old table and lock the old table statistics so that the users can query to the new table and can get the data from it....
Today we will see how to lock a table stats and unlock a table stats
Query to check if the table stats is locked:
col OWNER for a12
col table_name for a30
col STATTYPE_LOCKED for a15
select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='EMP_DATA' and owner='HR';
OWNER TABLE_NAME STATTYPE_LOCKED
------------ ------------------------------ ---------------
HR EMP_DATA NULL
or
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = '&TABLE_NAME' and owner = '&TABLE_OWNER';
Here we can see that the table stats not locked
STATTYPE_LOCKED – ALL -> stats are locked for the table
STATTYPE_LOCKED – NULL – > stats are not locked
To lock table stats:
exec dbms_stats.lock_table_stats('HR','EMP_DATA');
select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='EMP_DATA' and owner='HR';
OWNER TABLE_NAME STATTYPE_LOCKED
------------ ------------------------------ ---------------
HR EMP_DATA ALL
To unlock table stats:
SQL> EXEC DBMS_STATS.unlock_table_stats('HR',’EMP_DATA’);
PL/SQL procedure successfully completed.
To lock schema stats:
SQL> EXEC DBMS_STATS.unlock_schema_stats(‘HR’);
PL/SQL procedure successfully completed.
Enjoy the work and do like me if you like this article!!!
