Every Command We Run.
Nothing Hidden.

SELECT
  d.NAME, i.INSTANCE_NAME, i.HOST_NAME, i.VERSION,
  d.PLATFORM_NAME,
  TO_CHAR(i.STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS'),
  ROUND(SYSDATE - i.STARTUP_TIME),
  (SELECT VALUE FROM v$parameter WHERE name = 'cpu_count'),
  ROUND((SELECT TO_NUMBER(VALUE)/1024/1024/1024 FROM v$parameter WHERE name = 'sga_target'), 1),
  ROUND((SELECT TO_NUMBER(VALUE)/1024/1024/1024 FROM v$parameter WHERE name = 'pga_aggregate_target'), 1),
  (SELECT TO_NUMBER(VALUE) FROM v$parameter WHERE name = 'db_block_size')
FROM v$database d, v$instance i

SELECT BANNER FROM V$VERSION WHERE ROWNUM = 1

SELECT
  ts.TABLESPACE_NAME,
  ROUND(um.USED_SPACE * ts.BLOCK_SIZE / 1024/1024/1024, 1) AS used_gb,
  ROUND(um.TABLESPACE_SIZE * ts.BLOCK_SIZE / 1024/1024/1024, 1) AS total_gb,
  ROUND(um.USED_PERCENT, 1) AS pct_used,
  CASE WHEN df.autoext > 0 THEN 1 ELSE 0 END AS autoextend
FROM DBA_TABLESPACE_USAGE_METRICS um
JOIN DBA_TABLESPACES ts ON ts.TABLESPACE_NAME = um.TABLESPACE_NAME
LEFT JOIN (
  SELECT TABLESPACE_NAME,
    SUM(CASE WHEN AUTOEXTENSIBLE = 'YES' THEN 1 ELSE 0 END) AS autoext
  FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME
) df ON df.TABLESPACE_NAME = um.TABLESPACE_NAME
ORDER BY um.USED_PERCENT DESC

SELECT UNDOBLKS, TXNCOUNT, MAXQUERYLEN, MAXCONCURRENCY,
       TUNED_UNDORETENTION, EXPIREDBLKS, UNEXPIREDBLKS, ACTIVEBLKS
FROM V$UNDOSTAT WHERE ROWNUM = 1 ORDER BY END_TIME DESC

-- Undo tablespace size
SELECT d.TABLESPACE_NAME, SUM(d.BYTES)/1073741824,
       SUM(d.BYTES - NVL(f.FREE_BYTES,0))/1073741824,
       ROUND(SUM(d.BYTES - NVL(f.FREE_BYTES,0))/SUM(d.BYTES)*100,1), t.RETENTION
FROM DBA_DATA_FILES d JOIN DBA_TABLESPACES t ON t.TABLESPACE_NAME=d.TABLESPACE_NAME
LEFT JOIN (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) f
  ON f.FILE_ID=d.FILE_ID
WHERE t.CONTENTS='UNDO' GROUP BY d.TABLESPACE_NAME, t.RETENTION

SELECT TABLESPACE_NAME, ROUND(TABLESPACE_SIZE/1073741824,2),
       ROUND(FREE_SPACE/1073741824,2),
       ROUND((TABLESPACE_SIZE-FREE_SPACE)/NULLIF(TABLESPACE_SIZE,0)*100,1)
FROM DBA_TEMP_FREE_SPACE

-- Top temp-consuming sessions
SELECT s.SID, s.SERIAL#, s.USERNAME,
       ROUND(s.BLOCKS*8192/1048576,1), s.TABLESPACE
FROM V$TEMPSEG_USAGE s ORDER BY s.BLOCKS DESC FETCH FIRST 10 ROWS ONLY

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1073741824, 2) AS size_gb
FROM DBA_SEGMENTS
WHERE OWNER NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS',
  'ORDSYS','XDB','CTXSYS','WMSYS','EXFSYS')
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
ORDER BY size_gb DESC FETCH FIRST 10 ROWS ONLY

SELECT COUNT(*) AS problem_count,
       COUNT(CASE WHEN STATUS = 'OFFLINE' THEN 1 END) AS offline_count
FROM DBA_DATA_FILES WHERE STATUS NOT IN ('AVAILABLE','ONLINE')

SELECT COUNT(*) AS object_count,
       ROUND(SUM(SPACE)*8192/1073741824, 2) AS size_gb
FROM DBA_RECYCLEBIN

SELECT ROUND(SUM(VALUE)/1024/1024/1024, 1) FROM V$SGA

-- Buffer cache hit ratio
SELECT ROUND((1 - (phys.VALUE / (db_gets.VALUE + con_gets.VALUE))) * 100, 1)
FROM (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical reads') phys,
     (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'db block gets') db_gets,
     (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'consistent gets') con_gets

-- Library cache hit ratio
SELECT ROUND(SUM(PINS - RELOADS) / NULLIF(SUM(PINS), 0) * 100, 1) FROM V$LIBRARYCACHE

-- Dictionary cache hit ratio
SELECT ROUND(SUM(GETS - GETMISSES) / NULLIF(SUM(GETS), 0) * 100, 1) FROM V$ROWCACHE

-- Shared pool free %
SELECT ROUND(free_bytes.val / total_bytes.val * 100, 1)
FROM (SELECT SUM(BYTES) AS val FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory') free_bytes,
     (SELECT SUM(BYTES) AS val FROM V$SGASTAT WHERE POOL='shared pool') total_bytes

SELECT
  ROUND((SELECT TO_NUMBER(VALUE)/1024/1024/1024 FROM v$parameter WHERE name='pga_aggregate_target'),1),
  ROUND((SELECT VALUE/1024/1024/1024 FROM V$PGASTAT WHERE NAME='total PGA allocated'),1),
  ROUND((SELECT VALUE/1024/1024/1024 FROM V$PGASTAT WHERE NAME='maximum PGA allocated'),1),
  (SELECT VALUE FROM V$PGASTAT WHERE NAME='over allocation count'),
  ROUND((SELECT VALUE FROM V$PGASTAT WHERE NAME='cache hit percentage'),1)
FROM DUAL

SELECT STAT_NAME, VALUE FROM V$OSSTAT
WHERE STAT_NAME IN (
  'NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME',
  'SYS_TIME','IOWAIT_TIME',
  'PHYSICAL_MEMORY_BYTES','FREE_MEMORY_BYTES'
)

SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 500), EXECUTIONS,
  ROUND(ELAPSED_TIME/1000000, 1), ROUND(CPU_TIME/1000000, 1),
  BUFFER_GETS, DISK_READS, ROWS_PROCESSED,
  CASE WHEN EXECUTIONS > 0 THEN ROUND(ELAPSED_TIME/EXECUTIONS/1000, 2) ELSE 0 END,
  CASE WHEN EXECUTIONS > 0 THEN ROUND(BUFFER_GETS/EXECUTIONS) ELSE 0 END,
  PLAN_HASH_VALUE
FROM V$SQL
WHERE EXECUTIONS > 0 AND ELAPSED_TIME > 0
  AND PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN',
    'MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','DBSFWUSER')
  AND SQL_TEXT NOT LIKE '%v$%' AND SQL_TEXT NOT LIKE '%V$%'
  AND COMMAND_TYPE IN (2, 3, 6, 7, 189)
ORDER BY ELAPSED_TIME DESC FETCH FIRST 10 ROWS ONLY

SELECT EVENT, WAIT_CLASS, TOTAL_WAITS,
  ROUND(TIME_WAITED/100, 1) AS time_waited_s,
  CASE WHEN TOTAL_WAITS > 0
    THEN ROUND((TIME_WAITED/100/TOTAL_WAITS)*1000, 2) ELSE 0 END AS avg_wait_ms
FROM V$SYSTEM_EVENT
WHERE WAIT_CLASS NOT IN ('Idle') AND TOTAL_WAITS > 0
ORDER BY TIME_WAITED DESC FETCH FIRST 15 ROWS ONLY

SELECT i.OWNER, i.INDEX_NAME, i.TABLE_NAME,
  ROUND(s.LEAF_BLOCKS *
    (SELECT TO_NUMBER(VALUE) FROM v$parameter WHERE name='db_block_size')
    / 1024/1024) AS size_mb,
  i.BLEVEL, s.LEAF_BLOCKS, i.CLUSTERING_FACTOR,
  NVL(s.PCT_DIRECT_ACCESS,100), i.STATUS,
  CASE
    WHEN i.STATUS != 'VALID' THEN 'unusable'
    WHEN i.BLEVEL > 4 THEN 'critical'
    WHEN NVL(s.PCT_DIRECT_ACCESS,100) < 50 THEN 'critical'
    WHEN i.BLEVEL > 3 THEN 'fragmented'
    WHEN NVL(s.PCT_DIRECT_ACCESS,100) < 70 THEN 'fragmented'
    ELSE 'ok'
  END AS health_status
FROM DBA_INDEXES i
LEFT JOIN DBA_IND_STATISTICS s
  ON s.OWNER=i.OWNER AND s.INDEX_NAME=i.INDEX_NAME AND s.PARTITION_NAME IS NULL
WHERE i.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS',
  'EXFSYS','WMSYS','XDB','CTXSYS','APPQOSSYS','DBSFWUSER',
  'APEX_040000','APEX_040200','APEX_050000','FLOWS_FILES')
  AND i.INDEX_TYPE = 'NORMAL' AND NVL(s.LEAF_BLOCKS, 0) > 100
ORDER BY CASE WHEN i.STATUS != 'VALID' THEN 1 WHEN i.BLEVEL > 4 THEN 2
              WHEN i.BLEVEL > 3 THEN 3 ELSE 4 END,
         s.LEAF_BLOCKS DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY

-- Resource limits
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, INITIAL_ALLOCATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN (
  'sessions','processes','enqueue_locks','enqueue_resources',
  'dml_locks','temporary_table_locks','transactions','max_rollback_segments'
)

-- Session counts
SELECT COUNT(*), COUNT(CASE WHEN STATUS='ACTIVE' AND TYPE='USER' THEN 1 END),
       COUNT(CASE WHEN TYPE='USER' THEN 1 END) FROM V$SESSION

-- Blocked sessions
SELECT COUNT(*) FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL AND STATUS='ACTIVE'

-- Long-running SQL
SELECT COUNT(*), ROUND(MAX((SYSDATE - SQL_EXEC_START)*1440), 1)
FROM V$SESSION WHERE STATUS='ACTIVE' AND TYPE='USER'
  AND SQL_EXEC_START IS NOT NULL AND (SYSDATE - SQL_EXEC_START)*1440 > 5

SELECT d.VALUE AS disk_sorts, m.VALUE AS mem_sorts
FROM (SELECT VALUE FROM V$SYSSTAT WHERE NAME='sorts (disk)') d,
     (SELECT VALUE FROM V$SYSSTAT WHERE NAME='sorts (memory)') m

SELECT s.VALUE AS long_scans, i.VALUE AS index_lookups
FROM (SELECT VALUE FROM V$SYSSTAT WHERE NAME='table scans (long tables)') s,
     (SELECT VALUE FROM V$SYSSTAT WHERE NAME='table fetch by rowid') i

SELECT COUNT(*),
  COUNT(CASE WHEN OBJECT_TYPE IN ('PACKAGE BODY','PACKAGE') THEN 1 END),
  COUNT(CASE WHEN OBJECT_TYPE='PROCEDURE' THEN 1 END),
  COUNT(CASE WHEN OBJECT_TYPE='VIEW' THEN 1 END),
  COUNT(CASE WHEN OBJECT_TYPE='TRIGGER' THEN 1 END)
FROM DBA_OBJECTS WHERE STATUS='INVALID'
  AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS',
    'XDB','CTXSYS','WMSYS','EXFSYS','APPQOSSYS','DBSFWUSER','OJVMSYS','DVSYS','LBACSYS')

SELECT COUNT(*), COUNT(CASE WHEN LAST_ANALYZED IS NULL THEN 1 END)
FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES'
  AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','XDB','CTXSYS','WMSYS','EXFSYS')

-- SCN headroom
SELECT CURRENT_SCN,
  ROUND((TO_NUMBER(SYSDATE-TO_DATE('01-01-1988','DD-MM-YYYY'))*24*3600*16384*1024-CURRENT_SCN)/(24*3600*16384),0)
FROM V$DATABASE

SELECT TO_CHAR(ORIGINATING_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS'), MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > SYSDATE-1
  AND (MESSAGE_TEXT LIKE 'ORA-%' OR MESSAGE_TEXT LIKE '%checkpoint%'
       OR MESSAGE_TEXT LIKE '%corruption%' OR MESSAGE_TEXT LIKE '%recovery%'
       OR MESSAGE_TEXT LIKE '%error%' OR MESSAGE_TEXT LIKE '%warning%'
       OR MESSAGE_TEXT LIKE '%TNS-%' OR MESSAGE_TEXT LIKE '%instance%'
       OR MESSAGE_TEXT LIKE 'Thread%')
ORDER BY ORIGINATING_TIMESTAMP DESC FETCH FIRST 200 ROWS ONLY
-- Note: requires GRANT SELECT ON V_$DIAG_ALERT_EXT (not in SELECT_CATALOG_ROLE)

SELECT INPUT_TYPE, STATUS,
  TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI:SS'),
  TO_CHAR(END_TIME,'YYYY-MM-DD HH24:MI:SS'),
  ROUND((SYSDATE - END_TIME)*24, 1) AS hours_ago,
  ROUND(OUTPUT_BYTES/1073741824, 2) AS size_gb, ELAPSED_SECONDS
FROM (
  SELECT INPUT_TYPE, STATUS, START_TIME, END_TIME, OUTPUT_BYTES, ELAPSED_SECONDS,
         ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY END_TIME DESC) AS RN
  FROM V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='COMPLETED'
) WHERE RN=1

SELECT NAME, ROUND(SPACE_LIMIT/1073741824,2), ROUND(SPACE_USED/1073741824,2),
  ROUND(SPACE_RECLAIMABLE/1073741824,2), NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST

SELECT FILE_TYPE, ROUND(PERCENT_SPACE_USED,1),
       ROUND(PERCENT_SPACE_RECLAIMABLE,1), NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE ORDER BY PERCENT_SPACE_USED DESC

SELECT ROUND(SUM(BLOCKS * BLOCK_SIZE)/1073741824, 2)
FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > SYSDATE-1 AND STANDBY_DEST='NO'

SELECT LOG_MODE, ROUND((SYSDATE - STARTUP_TIME)*24) FROM V$DATABASE, V$INSTANCE

SELECT TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24'), COUNT(*),
       ROUND(SUM(BLOCKS*BLOCK_SIZE)/1048576, 1)
FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > SYSDATE-1 AND STANDBY_DEST='NO'
GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY-MM-DD HH24') ORDER BY 1 DESC

SELECT ROUND(COUNT(*)/24.0, 1), COUNT(*) FROM V$LOG_HISTORY WHERE FIRST_TIME > SYSDATE-1

SELECT COUNT(*), SUM(BLOCKS) FROM V$BACKUP_CORRUPTION
SELECT COUNT(*), SUM(BLOCKS) FROM V$COPY_CORRUPTION

SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE ACCOUNT_STATUS = 'OPEN'

-- Dangerous PUBLIC grants
SELECT COUNT(*) FROM DBA_SYS_PRIVS WHERE GRANTEE = 'PUBLIC'
  AND PRIVILEGE IN ('CREATE PROCEDURE','CREATE ANY PROCEDURE',
    'CREATE ANY TRIGGER','ALTER SYSTEM','ALTER DATABASE',
    'DROP ANY TABLE','EXECUTE ANY PROCEDURE')

-- DBA-privileged user count
SELECT COUNT(DISTINCT GRANTEE) FROM DBA_SYS_PRIVS
WHERE PRIVILEGE='DBA' AND GRANTEE NOT IN ('SYS','SYSTEM','DBA','SYSMAN')

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION'
SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail'

-- Schema-only (no-auth) open accounts
SELECT COUNT(*) FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN' AND AUTHENTICATION_TYPE='NONE'
  AND USERNAME NOT IN ('SYS','SYSTEM')

SELECT 1 FROM APPS.DUAL WHERE ROWNUM = 1
-- If this query fails (ORA-00942), EBS is not present. All APPS.* queries are skipped.

SELECT b.user_concurrent_queue_name, b.node_name,
       a.running_processes, a.max_processes
FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
WHERE a.concurrent_queue_id = b.concurrent_queue_id

SELECT phase_code, COUNT(*) FROM APPS.FND_CONCURRENT_REQUESTS
WHERE phase_code IN ('P','R') GROUP BY phase_code

SELECT component_type, component_name, component_status, startup_mode
FROM apps.fnd_svc_components WHERE component_type LIKE 'WF%' ORDER BY 1, 2

SELECT COUNT(*) FROM APPS.WF_NOTIFICATIONS
WHERE STATUS='OPEN' AND MAIL_STATUS='MAIL' AND BEGIN_DATE < SYSDATE - 1/24

SELECT COUNT(*) FROM APPS.WF_ERROR

SELECT MIN(SNAP_ID), MAX(SNAP_ID), MIN(BEGIN_INTERVAL_TIME), MAX(END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT WHERE END_INTERVAL_TIME > SYSDATE - :lookback_hours/24

SELECT TASK_ID, TASK_NAME FROM DBA_HIST_ADDM_TASKS
WHERE BEGIN_SNAP_ID = :begin_snap AND END_SNAP_ID = :end_snap
  AND STATUS = 'COMPLETED' FETCH FIRST 1 ROWS ONLY

SELECT f.FINDING_ID, f.TYPE, f.MESSAGE, f.BENEFIT,
       d.ATTR1 AS recommendation, d.TYPE AS rec_type
FROM DBA_ADVISOR_FINDINGS f
LEFT JOIN DBA_ADVISOR_RECOMMENDATIONS d ON d.TASK_ID=f.TASK_ID AND d.FINDING_ID=f.FINDING_ID
WHERE f.TASK_ID = :task_id ORDER BY f.BENEFIT DESC NULLS LAST FETCH FIRST 20 ROWS ONLY