16.3.2 Virtual Table Dictionary

SELECT binary_signature FROM v$version;

SELECT database_id, name, kind, access_mode, can_use, state, is_default
  FROM v$databases
 ORDER BY database_id;

SELECT operation_id, database_name, state, last_error
  FROM v$database_operations
 ORDER BY operation_id DESC;

-- List currently active sessions
SELECT id, user_name, user_ip, client_type, login_time
  FROM v$session
 WHERE closed = 0
 ORDER BY login_time;

-- Check running queries
SELECT id, sess_id, state, query
  FROM v$stmt
 WHERE state LIKE 'Execute in progress%'
    OR state LIKE 'Fetch in progress%'
    OR state LIKE 'Append in progress%';

-- Check specific settings
SELECT name, value, deflt
  FROM v$property
 WHERE name IN ('PORT_NO', 'TRACE_LOG_LEVEL', 'MAX_SESSION_COUNT');

-- Query settings that differ from defaults
SELECT name, value, deflt
  FROM v$property
 WHERE value != deflt
 ORDER BY name;

SELECT total_space, used_space, used_ratio, ratio_cap
  FROM v$storage_usage;

SELECT name, usage, max_usage
  FROM v$sysmem
 ORDER BY usage DESC;

SELECT id, type, customer, issue_date,
       install_date, violate_status, violate_msg
  FROM v$license_info;

-- List all V$ virtual tables available on the current server
SELECT name
  FROM v$tables
 WHERE name LIKE 'V$%'
 ORDER BY name;