EBS-DBA 维护

9404803

　　--查询表空间使用率：
　　SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 4 DESC;
　　--查看客户化表的空间占用
　　SELECT TABLESPACE_NAME,SEGMENT_NAME,TO_CHAR(SUM(BYTES)/(1024*1024),'999G999D999') CNT_MB
FROM DBA_EXTENTS
WHERE (OWNER LIKE 'CUX%' OR SEGMENT_NAME LIKE 'CUX%') AND SEGMENT_TYPE LIKE 'TABLE%'
GROUP BY TABLESPACE_NAME,SEGMENT_NAME
ORDER BY 3 DESC;
　　
　　查看EBS在线用户
　　SELECT U.USER_NAME,
       APP.APPLICATION_SHORT_NAME,
       FAT.APPLICATION_NAME,
       FR.RESPONSIBILITY_KEY,
       FRT.RESPONSIBILITY_NAME,
       FFF.FUNCTION_NAME,
       FFT.USER_FUNCTION_NAME,
       ICX.FUNCTION_TYPE,
       ICX.FIRST_CONNECT,
       ICX.LAST_CONNECT
  FROM ICX_SESSIONS          ICX,
       FND_USER              U,
       FND_APPLICATION       APP,
       FND_APPLICATION_TL    FAT,
       FND_RESPONSIBILITY    FR,
       FND_RESPONSIBILITY_TL FRT,
       FND_FORM_FUNCTIONS    FFF,
       FND_FORM_FUNCTIONS_TL FFT
WHERE 1 = 1
   AND U.USER_ID = ICX.USER_ID
   AND ICX.RESPONSIBILITY_APPLICATION_ID = APP.APPLICATION_ID
   AND FAT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
   AND FAT.LANGUAGE = 'ZHS'
   AND FR.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
   AND FR.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
   AND FRT.LANGUAGE = 'ZHS'
   AND FRT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
   AND FRT.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
   AND FFF.FUNCTION_ID = ICX.FUNCTION_ID
   AND FFT.FUNCTION_ID = ICX.FUNCTION_ID
   AND ICX.DISABLED_FLAG != 'Y'
   AND ICX.PSEUDO_FLAG = 'N'
   AND (ICX.LAST_CONNECT +
       DECODE(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
               NULL,
               ICX.LIMIT_TIME,
               0,
               ICX.LIMIT_TIME,
               FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >
       SYSDATE
   AND ICX.COUNTER < ICX.LIMIT_CONNECTS;
　　查看出错/警告的请求
　　SELECT /*+ */
  REQUESTED_START_DATE,
PHASE_CODE,
decode(STATUS_CODE,'G','WARNING','E','Error'),
REQUEST_ID,
PROGRAM,
REQUESTOR,
CONCURRENT_PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_SHORT_NAME,
ARGUMENT_TEXT,
ACTUAL_COMPLETION_DATE,
COMPLETION_TEXT,
PARENT_REQUEST_ID,
REQUEST_TYPE,
FCP_PRINTER,
FCP_PRINT_STYLE,
FCP_REQUIRED_STYLE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REQUESTED_BY,
HAS_SUB_REQUEST,
IS_SUB_REQUEST,
UPDATE_PROTECTED,
QUEUE_METHOD_CODE,
RESPONSIBILITY_APPLICATION_ID,
RESPONSIBILITY_ID,
CONTROLLING_MANAGER,
LAST_UPDATE_LOGIN,
PRIORITY_REQUEST_ID,
ENABLED
  FROM FND_CONC_REQ_SUMMARY_V
WHERE    STATUS_CODE IN ('G','E')
AND
    (REQUEST_DATE >= TRUNC(SYSDATE - 7))
ORDER BY REQUEST_ID DESC
　　
　　监控SOA
　　
SELECT *
FROM (SELECT REQUEST_TIMESTAMP AS REQDATETIME,
REQUEST_COMPLETED AS RESDATETIME,
WRQ.IREP_NAME AS WEB_SERVICE_NAME,
FIC.CLASS_ID AS CLASS_ID,
METHOD AS METHOD_NAME,
USER_NAME,
WRQ.USER_ID,
IP_ADDRESS,
REQUEST_STATUS AS REQUEST_STATUS,
RESPONSE_STATUS AS RESPONSE_STATUS,
MESSAGE_ID,
REQUEST_PATTERN,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'FND_SOA_RESPONSE_STATUS'
AND LOOKUP_CODE = WRQ.RESPONSE_STATUS) AS RES_STATUS,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'FND_SOA_REQUEST_STATUS'
AND LOOKUP_CODE = WRQ.REQUEST_STATUS) AS REQ_STATUS,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'FND_SOA_MESSAGE_PATTERN'
AND LOOKUP_CODE = WRQ.REQUEST_PATTERN) AS INTERACTION_ARCHITECTURE,
(SELECT DECODE(COUNT(1), 0, 'LOG_ABSENT', 'LOG_PRESENT')
FROM FND_LOG_MESSAGES
WHERE TRANSACTION_CONTEXT_ID IN
(SELECT TRANSACTION_CONTEXT_ID
FROM FND_LOG_TRANSACTION_CONTEXT
WHERE TRANSACTION_TYPE = 'SOA_INSTANCE'
AND TRANSACTION_ID = WRQ.MESSAGE_ID
AND REGEXP_LIKE(WRQ.MESSAGE_ID,
'^-?[[:digit:],.]*$'))) AS ENABLE_LOG
FROM FND_SOA_REQUEST WRQ, FND_USER FU, FND_IREP_CLASSES FIC
WHERE WRQ.USER_ID = FU.USER_ID(+)
AND FIC.IREP_NAME = WRQ.IREP_NAME
AND FIC.CLASS_TYPE NOT IN ('SOAPSERVICEDOC', 'WEBSERVICEDOC')) QRSLT
WHERE 1=1
AND WEB_SERVICE_NAME NOT IN ('CUX_TAL_PO001_WS_PKG')
AND REQDATETIME >= to_date('2014/11/03','yyyy/mm/dd')
ORDER BY REQDATETIME DESC