Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

Statistics Information LOCK Function

Document Type | Technical Information

Category | Administration

Applicable Product Version | T6, T7

Document Number | TADTI135

 

Overview

This function is used to prevent changes to statistics information and to disable automatic collection.
This article explains how to lock statistics information for specific tables, specific users (schemas), and specific partitions.
 
 

Method

1. Locking Statistics Information by SCHEMA / TABLE 
<By SCHEMA>
SQL> exec DBMS_STATS.LOCK_SCHEMA_STATS('SCHEMA_NAME');

<By TABLE>
SQL> exec DBMS_STATS.LOCK_TABLE_STATS('SCHEMA_NAME','TABLE_NAME');
 
2. Checking TABLEs with Locked Statistics Information 
SQL> select owner, object_name from dba_objects 
     where object_id in (select obj_id from _dd_tbl where bitand(status,1)=1);
 
3. Unlocking Statistics Information by SCHEMA / TABLE
<By SCHEMA>
exec DBMS_STATS.UNLOCK_SCHEMA_STATS('SCHEMA_NAME');

<By TABLE>
exec DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA_NAME','TABLE_NAME');
 
4. When performing LOCK_SCHEMA_STATS, the statistics information for each TABLE is locked,
and if necessary, you can also use UNLOCK_TABLE_STATS for each TABLE individually. 
SQL> exec DBMS_STATS.LOCK_SCHEMA_STATS('TIBERO');
PSM completed.
SQL> select owner, object_name from dba_objects 
     where object_id in (select obj_id from _dd_tbl where bitand(status,1)=1);
OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
TIBERO                         TEST
TIBERO                         SALES

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('TIBERO','TEST');
PSM completed.   
SQL> select owner, object_name from dba_objects 
     where object_id in (select obj_id from _dd_tbl where bitand(status,1)=1);
OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
TIBERO                         SALES

 

 

 

Related to