A curated collection of diagnostic and maintenance SQL queries for Oracle Database. Each block can be copied with a single click. Queries are designed to run in SQL*Plus or DBeaver without modification. Most queries require DBA privileges and access to v$ and dba_ views.
Oracle Database offers many optional features and options (e.g. RAC, Partitioning, Advanced Compression, In-Memory, Spatial). Those are grouped under Optional Features.
SELECT banner AS full_version,
substr(banner, instr(banner, '.', 1, 1) - 2,
instr(banner, '.', 1, 2) - instr(banner, '.', 1, 1) + 2) AS short_version,
CASE WHEN banner LIKE '%Enterprise%' OR banner LIKE '%EE%' THEN 'Enterprise'
WHEN banner LIKE '%Express%' THEN 'XE'
WHEN banner LIKE '%Developer-Release%' THEN 'Free'
ELSE 'Standard'
END AS edition,
CASE WHEN substr(banner, instr(banner, '.', 1, 1) - 2,
instr(banner, '.', 1, 2) - instr(banner, '.', 1, 1) + 2)
IN ('19.0', '23.0', '26.0') THEN 'YES'
ELSE 'NO'
END AS recent_major
FROM v$version
WHERE banner LIKE 'Oracle%';
SELECT d.name AS db_name,
d.created,
i.instance_name, i.host_name,
i.startup_time,
i.archiver,
d.log_mode,
d.platform_name,
d.dbid
FROM v$database d, v$instance i;
SELECT comp_id, comp_name, version, status, modified
FROM dba_registry
ORDER BY comp_id;
SELECT name,
value,
isdefault,
ismodified,
description
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'db_cache_size',
'shared_pool_size', 'memory_target', 'memory_max_target',
'large_pool_size', 'java_pool_size', 'streams_pool_size',
'inmemory_size', 'log_buffer', 'db_keep_cache_size',
'db_recycle_cache_size', 'db_block_size',
'processes', 'sessions', 'open_cursors',
'db_files', 'undo_retention', 'control_management_pack_access')
ORDER BY isdefault, name;
SELECT name, value$
FROM sys.props$
WHERE name LIKE 'NLS%'
ORDER BY name;
SELECT lg.group#, lg.bytes, lg.status AS log_status,
lf.member AS log_file,
lf.status AS file_status,
lg.thread#, lg.sequence#
FROM v$logfile lf, v$log lg
WHERE lf.group# = lg.group#
ORDER BY lg.thread#, lg.group#;
SELECT trunc(first_time) AS switch_date,
count(*) AS switches_per_day
FROM v$log_history
WHERE first_time > sysdate - 31
GROUP BY trunc(first_time)
ORDER BY trunc(first_time) DESC;
SELECT owner,
count(*) AS total,
sum(decode(object_type, 'TABLE', 1, 0)) AS tables,
sum(decode(object_type, 'TABLE PARTITION', 1, 0)) AS table_partitions,
sum(decode(object_type, 'INDEX', 1, 0)) AS indexes,
sum(decode(object_type, 'TRIGGER', 1, 0)) AS triggers,
sum(decode(object_type, 'PACKAGE', 1, 0)) AS packages,
sum(decode(object_type, 'PACKAGE BODY', 1, 0)) AS package_bodies,
sum(decode(object_type, 'PROCEDURE', 1, 0)) AS procedures,
sum(decode(object_type, 'FUNCTION', 1, 0)) AS functions,
sum(decode(object_type, 'SEQUENCE', 1, 0)) AS sequences,
sum(decode(object_type, 'SYNONYM', 1, 0)) AS synonyms,
sum(decode(object_type, 'VIEW', 1, 0)) AS views,
sum(decode(object_type, 'MATERIALIZED VIEW', 1, 0)) AS materialized_views,
sum(decode(object_type, 'TYPE', 1, 0)) AS types,
sum(decode(object_type, 'LOB', 1, 0)) AS lobs,
sum(decode(object_type, 'XML SCHEMA', 1, 0)) AS xml_schemas
FROM dba_objects
GROUP BY owner
ORDER BY owner;
SELECT owner, object_type,
count(*) AS invalid_count
FROM dba_objects
WHERE status <> 'VALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;
SELECT owner, type,
count(DISTINCT name) AS objects,
count(*) AS total_lines
FROM dba_source
GROUP BY owner, type
ORDER BY owner, type;
SELECT owner, data_type,
count(*) AS column_count,
max(data_length) AS max_length,
max(data_precision) AS max_precision
FROM all_tab_columns
WHERE owner NOT IN ('SYS', 'XDB', 'MDSYS', 'ORDSYS')
GROUP BY owner, data_type
ORDER BY owner, data_type;
SELECT owner, library_name, file_spec, status, dynamic
FROM all_libraries
WHERE owner NOT IN ('SYS', 'XDB', 'MDSYS', 'ORDSYS')
ORDER BY owner, library_name;
WITH ts_occ AS (
SELECT tablespace_name, sum(bytes) AS used_bytes,
max(extent_id) + 1 AS max_extents
FROM dba_extents
GROUP BY tablespace_name
),
ts_free AS (
SELECT tablespace_name, max(bytes) AS max_free_bytes
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT a.tablespace_name,
round(sum(a.bytes) / 1048576) AS total_mb,
round(nvl(b.used_bytes, 0) / 1048576) AS used_mb,
round(100 * nvl(b.used_bytes, 0) / nullif(sum(a.bytes), 0)) AS used_pct,
round(nvl(c.max_free_bytes, 0) / 1048576) AS max_free_mb,
b.max_extents
FROM dba_data_files a, ts_occ b, ts_free c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name(+)
GROUP BY a.tablespace_name, b.used_bytes, c.max_free_bytes, b.max_extents
ORDER BY a.tablespace_name;
SELECT segment_type,
round(sum(bytes) / 1048576) AS size_mb,
count(*) AS segments
FROM dba_segments
GROUP BY segment_type
ORDER BY sum(bytes) DESC;
SELECT owner,
round(sum(decode(segment_type, 'TABLE', bytes, 0)) / 1048576) AS table_mb,
round(sum(decode(segment_type, 'INDEX', bytes, 0)) / 1048576) AS index_mb,
round(sum(decode(segment_type, 'TABLE PARTITION', bytes, 0)) / 1048576) AS table_part_mb,
round(sum(decode(segment_type, 'INDEX PARTITION', bytes, 0)) / 1048576) AS index_part_mb,
round(sum(decode(substr(segment_type, 1, 3), 'LOB', bytes, 0)) / 1048576) AS lob_mb,
round(sum(bytes) / 1048576) AS total_mb
FROM dba_segments
GROUP BY owner
ORDER BY owner;
SELECT segment_name, segment_type, owner, tablespace_name,
round(sum(bytes) / 1048576) AS size_mb
FROM dba_extents
GROUP BY segment_name, segment_type, owner, tablespace_name
ORDER BY sum(bytes) DESC
FETCH FIRST 32 ROWS ONLY;
SELECT segment_name, segment_type, owner, tablespace_name,
count(*) AS extents,
round(sum(bytes) / 1048576) AS size_mb
FROM dba_extents
GROUP BY segment_name, segment_type, owner, tablespace_name
ORDER BY count(*) DESC
FETCH FIRST 32 ROWS ONLY;
SELECT round(sum(space * 8) / 1024) AS recycle_used_kb,
count(*) AS objects
FROM dba_recyclebin;
SELECT tablespace_name, file_name, bytes, maxbytes,
increment_by, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'
ORDER BY tablespace_name, file_name;
SELECT a.tablespace_name, a.file_name,
round(a.bytes / 1048576) AS size_mb,
f.phyrds AS physical_reads,
f.phywrts AS physical_writes,
round(f.phyrds / nullif((sysdate - i.startup_time) * 24 * 60 * 60, 0), 2) AS reads_per_sec
FROM dba_data_files a, v$filestat f, v$instance i
WHERE a.file_id = f.file#
ORDER BY f.phyrds DESC;
SELECT s.schemaname AS username, s.inst_id,
count(*) AS sessions,
sum(decode(s.status, 'ACTIVE', 1, 0)) AS active
FROM gv$process p, gv$session s
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.type = 'USER'
GROUP BY s.schemaname, s.inst_id
ORDER BY count(*) DESC;
SELECT s.sid || ',' || s.serial# AS sid_serial,
s.schemaname AS username,
s.osuser, p.spid AS process,
s.type, s.status,
s.program, s.module, s.inst_id,
to_char(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
s.client_identifier
FROM gv$process p, gv$session s
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.type DESC, s.status, s.inst_id, s.sid;
SELECT s.sid, s.serial#, s.username, s.inst_id,
q.sql_id,
replace(replace(q.sql_text, '<', '<'), '>', '>') AS sql_text
FROM gv$session s, gv$sql q
WHERE s.sql_address = q.address
AND s.type <> 'BACKGROUND'
AND s.status = 'ACTIVE'
AND s.username <> 'SYS'
AND s.inst_id = q.inst_id
ORDER BY s.sid;
SELECT machine, program, inst_id,
count(*) AS sessions,
sum(decode(status, 'ACTIVE', 1, 0)) AS active
FROM gv$session
WHERE type = 'USER'
GROUP BY machine, program, inst_id
ORDER BY count(*) DESC
FETCH FIRST 20 ROWS ONLY;
SELECT l.sid, l.type,
decode(l.lmode,
0, 'WAITING', 1, 'Null', 2, 'Row Share',
3, 'Row Exclusive', 4, 'Share',
5, 'Share Row Exclusive', 6, 'Exclusive',
l.lmode) AS lock_mode,
decode(l.request,
0, 'HOLD', 1, 'Null', 2, 'Row Share',
3, 'Row Exclusive', 4, 'Share',
5, 'Share Row Exclusive', 6, 'Exclusive',
l.request) AS request,
count(*) AS lock_count
FROM gv$lock l
GROUP BY l.sid, l.type, l.lmode, l.request
ORDER BY l.sid, l.type, l.lmode, l.request;
SELECT w.inst_id, w.sid AS waiter_sid, w.serial# AS waiter_serial,
w.username AS waiter_user,
b.sid AS blocker_sid, b.serial# AS blocker_serial,
b.username AS blocker_user
FROM gv$session w, gv$session b
WHERE w.blocking_session = b.sid
AND w.blocking_instance = b.inst_id
ORDER BY w.inst_id, w.sid;
SELECT dob.owner, dob.object_name, dob.object_type,
l.sid, l.type, l.lmode, l.request
FROM v$locked_object dlo
JOIN dba_objects dob ON dlo.object_id = dob.object_id
JOIN v$lock l ON l.sid = dlo.session_id
WHERE dob.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY dob.owner, dob.object_name;
SELECT username,
default_tablespace, temporary_tablespace,
account_status, profile, expiry_date,
created, lock_date
FROM dba_users
ORDER BY username;
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
ORDER BY resource_name;
SELECT username, inst_id, sysdba, sysoper
FROM gv$pwfile_users
ORDER BY inst_id, username;
SELECT username, account_status
FROM dba_users
WHERE password IN ('E066D214D5421CCC', '24ABAB8B06281B4C',
'72979A94BAD2AF80', 'C252E8FA117AF049',
'88A2B2C183431F00', 'F894844C34402B67',
'79DF7A1BD138CF11', '7C9BA362F8314299',
'9300C0977D7DC75E', 'A97282CE3D94E29E',
'AC9700FD3F1410EB', 'E7B5D92911C831E1',
'5638228DAF52805F', 'D4DF7931AB130E37',
'545E13456B7DDEA0', '71E687F036AD56E5')
ORDER BY account_status DESC, username;
SELECT os_username, username, owner,
obj_name, action_name, returncode,
count(*) AS audit_count
FROM dba_audit_trail
GROUP BY os_username, username, owner, obj_name,
action_name, returncode
ORDER BY count(*) DESC
FETCH FIRST 20 ROWS ONLY;
SELECT 'Buffer Cache Hit Ratio' AS metric,
round((1 - sum(decode(name, 'physical reads', value, 0)) /
nullif(sum(decode(name, 'db block gets', value, 0))
+ sum(decode(name, 'consistent gets', value, 0)), 0)) * 100, 2) || '%' AS value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads')
UNION ALL
SELECT 'Library Cache Miss Ratio',
round(sum(reloads) / nullif(sum(pins), 0) * 100, 3) || '%'
FROM v$librarycache
UNION ALL
SELECT 'Dictionary Cache Miss Ratio',
round(sum(getmisses) / nullif(sum(gets), 0) * 100, 3) || '%'
FROM v$rowcache
UNION ALL
SELECT 'Redo Log Space Requests',
to_char(value)
FROM v$sysstat
WHERE name = 'redo log space requests';
SELECT s.inst_id,
sum(decode(s.name, 'logons cumulative', s.value, 0)) AS logons,
sum(decode(s.name, 'user commits', s.value, 0)) AS commits,
sum(decode(s.name, 'user rollbacks', s.value, 0)) AS rollbacks,
sum(decode(s.name, 'execute count', s.value, 0)) AS executions,
round(sum(decode(s.name, 'DB time', s.value, 0)) / 100) AS dbcpu_cs,
round(sum(decode(s.name, 'user commits', s.value, 0))
/ nullif((sysdate - i.startup_time) * 24 * 60 * 60, 0), 3) AS tps
FROM gv$sysstat s, gv$instance i
WHERE s.inst_id = i.inst_id
AND s.name IN ('logons cumulative', 'user commits', 'user rollbacks',
'execute count', 'DB time')
GROUP BY s.inst_id, i.startup_time
ORDER BY s.inst_id;
SELECT
sum(decode(name, 'physical read total IO requests', value, 0)
- decode(name, 'physical read total multi block requests', value, 0)) AS small_reads,
sum(decode(name, 'physical write total IO requests', value, 0)
- decode(name, 'physical write total multi block requests', value, 0)) AS small_writes,
sum(decode(name, 'physical read total multi block requests', value, 0)) AS large_reads,
sum(decode(name, 'physical write total multi block requests', value, 0)) AS large_writes,
round(sum(decode(name, 'physical read total bytes', value, 0)) / 1048576) AS total_read_mb,
round(sum(decode(name, 'physical write total bytes', value, 0)) / 1048576) AS total_write_mb
FROM v$sysstat
WHERE name IN ('physical read total IO requests',
'physical read total multi block requests',
'physical write total IO requests',
'physical write total multi block requests',
'physical read total bytes',
'physical write total bytes');
SELECT 'Redo Allocation' AS latch_name,
gets, misses,
round(misses / nullif(gets, 0) * 100, 3) AS miss_pct,
immediate_gets, immediate_misses,
round(immediate_misses / nullif(immediate_gets, 0) * 100, 3) AS immediate_miss_pct
FROM v$latch
WHERE latch# = 15
UNION ALL
SELECT 'Redo Copy',
gets, misses,
round(misses / nullif(gets, 0) * 100, 3),
immediate_gets, immediate_misses,
round(immediate_misses / nullif(immediate_gets, 0) * 100, 3)
FROM v$latch
WHERE latch# = 16;
SELECT w.class, w.count,
round(w.count / nullif((SELECT sum(value) FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets')), 0) * 100, 4) AS contention_pct
FROM v$waitstat w
WHERE w.class IN ('free list', 'system undo header', 'system undo block',
'undo header', 'undo block')
ORDER BY w.class;
-- Stale table statistics
SELECT owner, count(*) AS stale_tables,
max(to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS')) AS max_last_analyzed
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
GROUP BY owner
ORDER BY owner;
-- Stale index statistics
SELECT owner, count(*) AS stale_indexes,
max(to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS')) AS max_last_analyzed
FROM dba_ind_statistics
WHERE stale_stats = 'YES'
GROUP BY owner
ORDER BY owner;
SELECT owner, object_name, object_type,
created, last_ddl_time
FROM (
SELECT owner, object_name, object_type, created, last_ddl_time
FROM dba_objects
WHERE owner NOT IN ('SYS', 'SYSTEM', 'RDSADMIN')
ORDER BY greatest(nvl(last_ddl_time, to_date('1970-01-01', 'YYYY-MM-DD')), created) DESC
)
FETCH FIRST 20 ROWS ONLY;
SELECT a.owner, a.table_name
FROM dba_tables a
WHERE a.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS', 'RDSADMIN')
AND NOT EXISTS (
SELECT 1 FROM dba_constraints c
WHERE c.owner = a.owner
AND c.table_name = a.table_name
AND c.constraint_type = 'P'
)
ORDER BY a.owner, a.table_name;
SELECT owner, table_name, tablespace_name,
num_rows, last_analyzed
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS', 'RDSADMIN')
AND (num_rows = 0 OR num_rows IS NULL)
ORDER BY owner, table_name;
SELECT owner, table_name, tablespace_name,
num_rows, blocks, empty_blocks,
avg_row_len, degree AS parallel_degree,
compression, compress_for
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS', 'RDSADMIN')
ORDER BY blocks DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY;
SELECT owner, index_name, table_name, status, uniqueness,
tablespace_name, round(bytes / 1048576) AS size_mb
FROM dba_indexes, dba_segments
WHERE dba_indexes.index_name = dba_segments.segment_name
AND dba_indexes.owner = dba_segments.owner
AND dba_indexes.status <> 'VALID'
AND dba_indexes.partitioned <> 'YES'
ORDER BY owner, index_name;
SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE status <> 'USABLE'
ORDER BY index_owner, index_name, partition_name;
SELECT owner, index_name, table_name,
uniqueness, status,
round(num_rows) AS rows,
round(avg_leaf_blocks_per_key) AS avg_leaf_blks_per_key,
round(avg_data_blocks_per_key) AS avg_data_blks_per_key
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS', 'RDSADMIN')
ORDER BY owner, table_name;
SELECT owner,
count(*) AS index_count,
round(sum(bytes) / 1048576) AS total_mb
FROM dba_segments
WHERE segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
GROUP BY owner
ORDER BY sum(bytes) DESC;
SELECT table_owner, table_name,
count(*) AS partition_count,
to_char(sum(num_rows), '999,999,999,999,999') AS total_rows,
round(sum(blocks / 128)) AS estimated_size_mb
FROM dba_tab_partitions
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')
GROUP BY table_owner, table_name
ORDER BY table_owner, table_name;
SELECT table_owner, table_name, tablespace_name,
partition_name, partition_position,
num_rows, subpartition_count,
high_value
FROM dba_tab_partitions
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')
AND rownum <= 100
ORDER BY table_owner, table_name, partition_position;
-- Compressed tables
SELECT owner, compression, compress_for,
count(*) AS tables
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')
AND compression = 'ENABLED'
GROUP BY owner, compression, compress_for
ORDER BY owner;
-- Compressed indexes
SELECT owner, compression, compress_for,
count(*) AS indexes
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')
AND compression = 'ENABLED'
GROUP BY owner, compression, compress_for
ORDER BY owner;
SELECT degree, instances,
count(*) AS tables
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')
GROUP BY degree, instances
ORDER BY degree DESC, instances DESC;
SELECT conf#, name, value
FROM v$rman_configuration
ORDER BY conf#;
SELECT creator, registrar, status, archived,
count(*) AS archive_count,
round(sum(blocks * block_size) / 1048576) AS total_mb
FROM v$archived_log
WHERE deleted = 'NO'
GROUP BY creator, registrar, archived, status
ORDER BY status, creator, registrar;
SELECT value AS control_files
FROM v$parameter
WHERE name = 'control_files';
SELECT type, record_size, records_total, records_used,
first_index, last_index, last_record
FROM v$controlfile_record_section;
SELECT * FROM v$flash_recovery_area_usage;
SELECT name, round(value / 1048576) AS size_mb
FROM v$parameter
WHERE name = 'db_recovery_file_dest_size';
SELECT owner_name, job_name, state, operation,
job_mode, degree, attached_sessions
FROM dba_datapump_jobs
ORDER BY owner_name, job_name;
SELECT owner, db_link, username, host, created
FROM dba_db_links
ORDER BY host, username, owner, db_link;
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE isdefault = 'FALSE'
ORDER BY name;
SELECT pi.ksppinm AS parameter, cv.ksppstvl AS value
FROM x$ksppcv cv, x$ksppi pi
WHERE cv.indx = pi.indx
AND translate(pi.ksppinm, '_', '#') LIKE '#%'
AND bitand(pi.ksppiflg / 256, 1) <> 1
AND pi.ksppinm LIKE '%optimizer%'
ORDER BY pi.ksppinm;
SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY owner, directory_name;
SELECT platform_name FROM v$database;
SELECT stat_name,
to_char(value, '999,999,999,999,990.0') AS value
FROM v$osstat
WHERE stat_name IN ('LOAD', 'PHYSICAL_MEMORY_BYTES', 'NUM_CPUS',
'NUM_CPU_CORES', 'NUM_CPU_SOCKETS')
ORDER BY stat_name;
SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI') AS sysdate,
to_char(current_date, 'YYYY-MM-DD HH24:MI') AS current_date,
dbtimezone AS db_tz,
sessiontimezone AS session_tz,
to_char(systimestamp, 'TZR') AS os_tz
FROM dual;
-- Reference: latest Release Updates (12.2+)
SELECT '23.26.0, 21.20, 19.29' AS latest_ru,
'20.2, 18.14, 12.2.0.1.220118' AS additional_ru,
'12.1.0.2.221018, 11.2.0.4.201020, 10.2.0.5.19' AS latest_psu,
'9.2.0.8, 8.1.7.4, 7.3.4.5' AS older_psu
FROM dual;
Oracle Database offers many licensed options and packs. The queries below help detect which options are installed and in use.
-- Installed options
SELECT 'Installed:' AS status, parameter FROM v$option WHERE value = 'TRUE'
UNION ALL
SELECT 'Not installed:', parameter FROM v$option WHERE value = 'FALSE'
ORDER BY status, parameter;
SELECT name, sum(detected_usages) AS usage_count,
min(first_usage_date) AS first_used,
max(last_usage_date) AS last_used,
description
FROM dba_feature_usage_statistics
WHERE currently_used = 'TRUE'
GROUP BY name, description
ORDER BY name;
SELECT name, sum(detected_usages) AS usage_count, description
FROM dba_feature_usage_statistics
WHERE currently_used = 'FALSE'
GROUP BY name, description
ORDER BY name;
SELECT name, highwater, description, version
FROM dba_high_water_mark_statistics
ORDER BY version DESC, name;
SELECT users_max, sessions_max,
sessions_current, sessions_highwater,
cpu_count_current, cpu_count_highwater,
cpu_core_count_current, cpu_core_count_highwater,
cpu_socket_count_current, cpu_socket_count_highwater
FROM v$license;
SELECT pool, name,
round(bytes / 1048576) AS size_mb
FROM (SELECT pool, name, bytes
FROM v$sgastat
ORDER BY bytes DESC)
WHERE rownum <= 20;
SELECT name, value, isdefault
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'db_cache_size',
'shared_pool_size', 'memory_target', 'memory_max_target',
'large_pool_size', 'java_pool_size', 'inmemory_size',
'inmemory_query', 'max_pdbs')
ORDER BY isdefault, name;
-- Undo parameters
SELECT name, value
FROM v$parameter
WHERE name LIKE 'undo%'
ORDER BY name;
-- Undo extents by status
SELECT tablespace_name, status,
count(*) AS extents,
round(sum(bytes) / 1048576) AS size_mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;
SELECT round(avg(tuned_undoretention)) AS avg_retention,
max(tuned_undoretention) AS max_retention,
min(tuned_undoretention) AS min_retention,
round(stddev(tuned_undoretention)) AS stddev_retention
FROM v$undostat;
SELECT file#, block#, corruption_type, block_count
FROM v$database_block_corruption
ORDER BY file#, block#;
SELECT file#, name, status, enabled, bytes,
checkpoint_change#, tablespace_name
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY file#;
SELECT l.log_id, l.job_name,
to_char(l.log_date, 'YYYY/MM/DD HH24:MI:SS') AS log_date,
to_char(r.actual_start_date, 'YYYY/MM/DD HH24:MI:SS') AS actual_start,
r.run_duration, r.status, r.errors
FROM dba_scheduler_job_log l, dba_scheduler_job_run_details r
WHERE l.log_id = r.log_id(+)
ORDER BY l.log_date DESC
FETCH FIRST 40 ROWS ONLY;
SELECT job_name, owner, repeat_interval,
start_date, job_action, program_name,
run_count, last_run_duration, enabled
FROM dba_scheduler_jobs
ORDER BY owner, enabled DESC, last_run_duration DESC NULLS LAST;
-- Legacy DBMS_JOBS
SELECT job, schema_user, interval, what,
round(total_time) AS total_time_sec,
next_date, last_date, failures, broken
FROM dba_jobs;
-- Currently running jobs
SELECT /*+ rule */ job, sid, last_date, failures
FROM dba_jobs_running;
SELECT a.segment_name, a.tablespace_name,
round(sum(a.bytes) / 1048576) AS size_mb,
max(a.extent_id) + 1 AS extents,
b.status
FROM dba_extents a, dba_rollback_segs b
WHERE a.segment_name = b.segment_name
AND a.segment_type = 'ROLLBACK'
GROUP BY a.tablespace_name, a.segment_name, b.status
ORDER BY a.tablespace_name, a.segment_name;
Generated from ora2html.sql v1.0.37b — github.com/meob/db2html