Lock and unlock the optimizer statistics Schema, table and Partition

3 Replies

Lock/unlock stats for Schema, table and Partition

Check the status is locked/unlocked 

--Check Stats is locked for table (if ALL is return then its locked otherwise NULL)
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'HR';
STATT

-----
ALL


--Check list of table Stats locked in Schema HR

SELECT table_name,stattype_locked,partition_name FROM dba_tab_statistics WHERE owner = 'HR' and stattype_locked is not null;

-- Check list of locked partition stats in Schema and table name

SELECT table_name,stattype_locked,partition_name FROM dba_tab_statistics WHERE owner = 'HR' and table_name='EMPLOYEES' and stattype_locked is not null;

Lock and unlock the stats for Schema

-- lock the schema stats
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('schema_name');

--unlock the schema stats
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('schema_name');

Lock and unlock the stats of table

--Lock the stats for the table
exec dbms_stats.lock_table_stats('schema_name', 'table_name');

--Unlock the stats for the table
exec dbms_stats.unlock_table_stats('schema_name', 'table_name');

Lock and unlock the stats of Table Partition

-- Lock the Partition Stats
exec dbms_stats.lock_partition_stats('Schema_name', 'table_name', 'partition_name');

--unlock the partition stats
exec dbms_stats.unlock_partition_stats('Schema_name', 'table_name', 'partition_name');

3 thoughts on “Lock and unlock the optimizer statistics Schema, table and Partition”

  1. Unknown's avatarAnonymous

    Hi Sandeep

    There is a typo in your unlock command for Partition Statistics. You have put lock command for Unlock also. Please correct it. Thanks.

    Reply ↓
  3. Unknown's avatarAnonymous

    not exactly, but you can stop the automatic stats job as

    BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE (client_name => ‘auto optimizer stats collection’, operation => NULL, window_name => NULL); END;

    Reply ↓

Leave a Reply