Unlock Schema
--------------------------------------------------------------------------------
$ run Application Env
$ sqlplus apps/apps
1. run this query for unlock schema checking
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 XDP XDP_FA_QTAB ALL
XNP XNP_OUT_MSG_QTAB ALL XNP XNP_IN_TMR_QTAB ALL XNP XNP_IN_MSG_QTAB ALL XNP XNP_IN_EVT_QTAB ALL SYS WRH$_FILESTATXS ALL
79 rows selected.
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');
exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQCALL');
exec dbms_stats.unlock_table_stats('AMV','AMV_MATCHING_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_SMTP_O_1_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_RET_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_SAMPLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUTBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_INBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED_TABLE_M');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_CONTROL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_OUT');
exec dbms_stats.unlock_table_stats('CCT','CCT_QDE_RESP_Q_TBL');
exec dbms_stats.unlock_table_stats('CCT','CCT_IBME_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IBU','IBU_SUBS_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_STAGING_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_EXCEP_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_DEF_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('OKC','OKC_AQ_EV_TAB');
exec dbms_stats.unlock_table_stats('ODM','DMS_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('APPS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_IH_BULK_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_PF_LOGGING_TABLE');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q2_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q1_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_M_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_MP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_ER_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_B_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_BP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','ECX_OUTQUEUE');
exec dbms_stats.unlock_table_stats('APPS','ECX_IN_OAG_Q_TABLE');
exec dbms_stats.unlock_table_stats('APPS','ECX_INQUEUE');
exec dbms_stats.unlock_table_stats('APPS','CZ_MESSAGE_QENTRIES');
exec dbms_stats.unlock_table_stats('AR','AR_REV_REC_QT');
exec dbms_stats.unlock_table_stats('ASO','ASO_ORDER_FEEDBACK_T');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_OQT');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_IQT');
exec dbms_stats.unlock_table_stats('IEM','IEMP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEM','IEMPP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_2');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_1');
exec dbms_stats.unlock_table_stats('XDP','XDP_WORKITEM_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_WF_CHANNEL_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_PENDING_ORDER_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_ORDER_PROCESSOR_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_FA_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_OUT_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_TMR_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_EVT_QTAB');
exec dbms_stats.unlock_table_stats('SYS','WRH$_FILESTATXS');
79 rows selected.
Gather Schema
--------------------------------------------------------------------------------
*/
FND_STATS.GATHER_SCHEMA_STATS (
schemaname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
internal_flag NUMBER DEFAULT NULL,
Errors OUT Error_Out,
request_id NUMBER default null,
hmode VARCHAR2 default 'LASTRUN',
options in VARCHAR2 default 'GATHER',
modpercent NUMBER default 10,
invalidate VARCHAR2 default 'Y'
);
/*
$sqlplus apps/apps
Sql> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10,32,'NOBACKUP', NULL,'LASTRUN','GATHER AUTO', 10, 'N');
--------------------------------------------------------------------------------
*/
FND_STATS.GATHER_SCHEMA_STATS (
schemaname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
internal_flag NUMBER DEFAULT NULL,
Errors OUT Error_Out,
request_id NUMBER default null,
hmode VARCHAR2 default 'LASTRUN',
options in VARCHAR2 default 'GATHER',
modpercent NUMBER default 10,
invalidate VARCHAR2 default 'Y'
);
/*
$sqlplus apps/apps
Sql> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10,32,'NOBACKUP', NULL,'LASTRUN','GATHER AUTO', 10, 'N');
No comments:
Post a Comment