Lock and unlock the optimizer stats Schema, table and Partition

Lock and unlock the optimizer stats of Schema, table and Partition

Lock and unlock the stats of 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

--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

--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.lock_partition_stats('Schema_name','table_name','partition_name');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.