The UNLOCK_SCHEMA_STATS procedure unlocks statistics for all tables in a schema.
Syntax
DBMS_STATS.UNLOCK_SCHEMA_STATS (
ownname VARCHAR2,
stattype VARCHAR2 DEFAULT 'ALL');
Parameters
| Parameter | Description |
|---|---|
| ownname | The name of the schema. |
| stattype | The type of lock. Valid values:
|
Considerations
To call this procedure, you must be the owner of the table. For objects owned by
SYS, you must be the owner or have theSYSDBAprivilege.When statistics for a table are locked, statistics for all dependent objects, including table-level statistics, column-level statistics, histogram statistics, and statistics for all dependent indexes, are also locked.
If the statistics for an object are locked, an error is returned when you execute the
SET_*,DELETE_*,IMPORT_*, orGATHER_*procedure to modify statistics for a single table, index, or column.The
UNLOCK_SCHEMA_STATSprocedure andUNLOCK_TABLE_STATSprocedure cannot unlock statistics for partitions. When you call theLOCK_TABLE_STATSprocedure, it sets the lock at the table level. At this point, you cannot collect statistics for dependent objects such as partitions and indexes. Additionally, if table statistics are locked, dependent objects are also locked, so you do not need to explicitly call theLOCK_PARTITION_STATSprocedure to lock partition statistics.
Examples
Unlock statistics for all tables in the hr schema.
obclient [test]> USE hr;
Database changed
obclient [hr]> CALL DBMS_STATS.UNLOCK_SCHEMA_STATS ('hr', stattype=>'ALL');
Query OK, 0 rows affected