Security & Commands Reference

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

-- Work area execution modes
SELECT ROUND(optimal.cnt/NULLIF(total.cnt,0)*100,1),
       ROUND(onepass.cnt/NULLIF(total.cnt,0)*100,1),
       ROUND(multipass.cnt/NULLIF(total.cnt,0)*100,1)
FROM (SELECT SUM(OPTIMAL_EXECUTIONS+ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS) AS cnt
      FROM V$SQL_WORKAREA_HISTOGRAM WHERE LOW_OPTIMAL_SIZE>0) total,
     (SELECT SUM(OPTIMAL_EXECUTIONS) AS cnt FROM V$SQL_WORKAREA_HISTOGRAM WHERE LOW_OPTIMAL_SIZE>0) optimal,
     (SELECT SUM(ONEPASS_EXECUTIONS) AS cnt FROM V$SQL_WORKAREA_HISTOGRAM WHERE LOW_OPTIMAL_SIZE>0) onepass,
     (SELECT SUM(MULTIPASSES_EXECUTIONS) AS cnt FROM V$SQL_WORKAREA_HISTOGRAM WHERE LOW_OPTIMAL_SIZE>0) multipass

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'
)

-- SGA component resizes
SELECT TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI'), COMPONENT,
       OPER_TYPE, ROUND(INITIAL_SIZE/1073741824,2),
       ROUND(FINAL_SIZE/1073741824,2), STATUS
FROM V$SGA_RESIZE_OPS ORDER BY START_TIME DESC FETCH FIRST 20 ROWS ONLY

-- AWR: peak PGA (licensed only)
SELECT ROUND(MAX(VALUE)/1073741824,2),
       TO_CHAR(MAX(s.END_INTERVAL_TIME) KEEP
               (DENSE_RANK LAST ORDER BY p.VALUE),'YYYY-MM-DD HH24:MI')
FROM DBA_HIST_PGASTAT p
JOIN DBA_HIST_SNAPSHOT s ON s.SNAP_ID=p.SNAP_ID
WHERE p.NAME='maximum PGA allocated' AND s.END_INTERVAL_TIME>SYSDATE-30

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

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'
)
ORDER BY CASE RESOURCE_NAME
  WHEN 'sessions' THEN 1 WHEN 'processes' THEN 2
  WHEN 'transactions' THEN 3 WHEN 'enqueue_locks' THEN 4 ELSE 9 END

-- 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')

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
  ) AS days_remaining
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 explicit GRANT SELECT ON V_$DIAG_ALERT_EXT TO tunevault_user
-- This grant is NOT included in SELECT_CATALOG_ROLE

-- Last backup by type
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
ORDER BY CASE INPUT_TYPE
  WHEN 'DB FULL' THEN 1 WHEN 'DB INCR' THEN 2
  WHEN 'ARCHIVELOG' THEN 3 ELSE 4 END

-- Recent 10 jobs
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), ROUND(OUTPUT_BYTES/1073741824,2), ELAPSED_SECONDS
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC FETCH FIRST 10 ROWS ONLY

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

-- Archivelog generation rate (last 24h) for "hours until full" forecast
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

-- Hourly archivelog volume (last 24h)
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

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

-- Recent RMAN operations
SELECT OPERATION, STATUS,
  TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI:SS'),
  TO_CHAR(END_TIME,'YYYY-MM-DD HH24:MI:SS'),
  MBYTES_PROCESSED, OUTPUT
FROM V$RMAN_STATUS
WHERE OPERATION IN ('BACKUP','RESTORE','RECOVER','DELETE','VALIDATE')
  AND START_TIME > SYSDATE-7
ORDER BY START_TIME DESC FETCH FIRST 20 ROWS ONLY

-- Backup set corruption
SELECT COUNT(*), SUM(BLOCKS) FROM V$BACKUP_CORRUPTION

-- Copy corruption
SELECT COUNT(*), SUM(BLOCKS) FROM V$COPY_CORRUPTION

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

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'
  )

SELECT LIMIT FROM DBA_PROFILES
WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION'

SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail'

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

SELECT COUNT(*) FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
  AND AUTHENTICATION_TYPE='NONE'
  AND USERNAME NOT IN ('SYS','SYSTEM')

-- Find snapshot range for lookback period
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

-- ADDM task for snapshot range
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

-- ADDM findings
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