-bash-3.2$ Source Application Enviromenet
-bash-3.2$ sqlplus apps/apps
SQL> select owner, table_name, stattype_locked
2 from dba_tab_statistics
3 where stattype_locked is not null;
OWNER TABLE_NAME STATT ------------------------------ ----------------------------------- SYS SYS$SERVICE_METRICS_TAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ_PROP_TABLE ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SCHEDULER_FILEWATCHER_QT ALL SYS SCHEDULER$_REMDB_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL
SYS AQ_EVENT_TABLE ALL SYSTEM TBLMIG_MSG_QTAB ALL SYSTEM DEF$_AQERROR ALL SYSTEM DEF$_AQCALL ALL AMV AMV_MATCHING_QUEUE_TBL ALL APPLSYS AQ$_WF_CONTROL_P ALL APPLSYS FND_CP_TM_AQTBL ALL APPLSYS WF_NOTIFICATION_OUT ALL
79 rows selected.
SQL> spool /u01/unlock_schema.sql
SQL> set lines 500 pages 5000
SQL> run
1 select 'exec dbms_stats.unlock_table_stats('||''''||owner||''''||','||''''||table_name||''''||')'||';'
2* from dba_tab_statistics where stattype_locked is not null
'EXECDBMS_STATS.UNLOCK_TABLE_STATS('||''''||OWNER||''''||','||''''||TABLE_NAME||''''||')'||';' ------------------------------------------------------------------------------------------------------ exec dbms_stats.unlock_table_stats('SYS','SYS$SERVICE_METRICS_TAB'); exec dbms_stats.unlock_table_stats('SYS','KUPC$DATAPUMP_QUETAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_PROP_TABLE'); exec dbms_stats.unlock_table_stats('SYS','AQ$_MEM_MC'); exec dbms_stats.unlock_table_stats('SYS','ALERT_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER_FILEWATCHER_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_REMDB_JOBQTAB'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_EVENT_QTAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_EVENT_TABLE'); exec dbms_stats.unlock_table_stats('SYSTEM','TBLMIG_MSG_QTAB'); exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQERROR');
SQL> spool off
79 rows selected.
-bash-3.2$ cd /u01
-bash-3.2$ pwd
/u01
-bash-3.2$ ls *.sql
unlock_schema.sql
-bash-3.2$
-bash-3.2$ sqlplus apps/apps @unlock_schema.sql
SQL> commit;
Commit complete.
-bash-3.2$ sqlplus apps/apps
SQL> select owner, table_name, stattype_locked
2 from dba_tab_statistics
3 where stattype_locked is not null;
OWNER TABLE_NAME STATT ------------------------------ ----------------------------------- SYS SYS$SERVICE_METRICS_TAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ_PROP_TABLE ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SCHEDULER_FILEWATCHER_QT ALL SYS SCHEDULER$_REMDB_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL
SYS AQ_EVENT_TABLE ALL SYSTEM TBLMIG_MSG_QTAB ALL SYSTEM DEF$_AQERROR ALL SYSTEM DEF$_AQCALL ALL AMV AMV_MATCHING_QUEUE_TBL ALL APPLSYS AQ$_WF_CONTROL_P ALL APPLSYS FND_CP_TM_AQTBL ALL APPLSYS WF_NOTIFICATION_OUT ALL
79 rows selected.
SQL> spool /u01/unlock_schema.sql
SQL> set lines 500 pages 5000
SQL> run
1 select 'exec dbms_stats.unlock_table_stats('||''''||owner||''''||','||''''||table_name||''''||')'||';'
2* from dba_tab_statistics where stattype_locked is not null
'EXECDBMS_STATS.UNLOCK_TABLE_STATS('||''''||OWNER||''''||','||''''||TABLE_NAME||''''||')'||';' ------------------------------------------------------------------------------------------------------ exec dbms_stats.unlock_table_stats('SYS','SYS$SERVICE_METRICS_TAB'); exec dbms_stats.unlock_table_stats('SYS','KUPC$DATAPUMP_QUETAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_PROP_TABLE'); exec dbms_stats.unlock_table_stats('SYS','AQ$_MEM_MC'); exec dbms_stats.unlock_table_stats('SYS','ALERT_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER_FILEWATCHER_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_REMDB_JOBQTAB'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_EVENT_QTAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_EVENT_TABLE'); exec dbms_stats.unlock_table_stats('SYSTEM','TBLMIG_MSG_QTAB'); exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQERROR');
SQL> spool off
79 rows selected.
-bash-3.2$ cd /u01
-bash-3.2$ pwd
/u01
-bash-3.2$ ls *.sql
unlock_schema.sql
-bash-3.2$
-bash-3.2$ sqlplus apps/apps @unlock_schema.sql
SQL> commit;
Commit complete.
No comments:
Post a Comment