Retrieves the current System Change Number of the database
💡 The SCN is the database's internal clock — used in PITR and standby synchronisation checks.
SELECT current_scn FROM v$database;
Current Redo Log Info
Active redo log thread, sequence, first and next change numbers
💡 Shows which redo log group is currently active — important when monitoring log switches or archiving issues.
SELECT thread#, sequence#, first_change#, next_change#
FROM v$log;
Archive Log Info
Lists recent archive logs with thread, sequence, applied status, and timestamps
💡 Helps verify archive logs are being generated and applied — key check for Data Guard environments.
SELECT thread#, sequence#, applied,
first_time, next_time
FROM v$archived_log
ORDER BY sequence# DESC
FETCH FIRST 20 ROWS ONLY;
Total Database Size
Sum of all datafiles, temp files, redo logs, and control files in GB
💡 Gives a complete picture of how much disk space the database is consuming across all file types.
SELECT (SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files)
+ (SELECT NVL(SUM(bytes),0)/1024/1024/1024 FROM dba_temp_files)
+ (SELECT SUM(bytes)/1024/1024/1024 FROM v$log)
+ (SELECT SUM(block_size*file_size_blks)/1024/1024/1024 FROM v$controlfile)
AS "Total Size GB"
FROM dual;
All Database Parameters
Lists all initialization parameters and their current values
💡 Use this to review or audit the current database configuration — helpful before and after any parameter change.
SELECT name || '=' || value
FROM v$parameter
ORDER BY name;
Query Specific Parameter
Query the current value of a specific initialization parameter
💡 Replace the parameter name as needed — useful for quickly checking sga_target, pga_aggregate_target, undo_retention, etc.
SELECT name, value, description
FROM v$parameter
WHERE name = 'undo_retention'; -- change parameter name
Control File Locations
Lists all control file paths and their status
💡 Always verify control file locations after a restore or migration — missing or inaccessible control files will prevent database startup.
SELECT name, status, is_recovery_dest_file
FROM v$controlfile;
🏢
Multitenant — CDB & PDB
List All PDBs
Shows all pluggable databases with con_id, name, open mode, and restricted status
💡 Run from CDB$ROOT — shows the full list of PDBs and their current state. Any PDB in MOUNTED state needs to be opened.
-- Run from CDB$ROOT as SYSDBA
SELECT con_id, name, open_mode, restricted
FROM v$pdbs
ORDER BY con_id;
Switch to a PDB Container
Changes the current session context to the specified pluggable database
💡 Run this after connecting to CDB$ROOT — required before running DBA tasks inside a specific PDB. Replace PDBNAME with your actual PDB name.
-- Switch session into a specific PDB
ALTER SESSION SET CONTAINER = PDBNAME;
-- Verify current container
SHOW CON_NAME;
-- Switch back to root
ALTER SESSION SET CONTAINER = CDB$ROOT;
Open / Close a PDB
Commands to open or close a pluggable database — run from CDB$ROOT
💡 Always verify which PDB you are opening/closing before executing. The SAVE STATE option makes the open persist across CDB restarts.
-- Open a specific PDB (run from CDB$ROOT)
ALTER PLUGGABLE DATABASE PDBNAME OPEN;
-- Open all PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Close a specific PDB
ALTER PLUGGABLE DATABASE PDBNAME CLOSE IMMEDIATE;
-- Save state (auto-open after CDB restart)
ALTER PLUGGABLE DATABASE PDBNAME SAVE STATE;
Show Current Container
Displays the name of the container (CDB$ROOT or PDB name) the session is currently in
💡 Always run this first in a multitenant environment to confirm you are in the right container before executing DDL or DML.
SHOW CON_NAME;
-- Or using SQL
SELECT SYS_CONTEXT('USERENV','CON_NAME') AS current_container FROM dual;
Create PDB Local User
Creates a local user inside a specific PDB (not a common CDB user)
💡 Local users exist only in the PDB — switch into the PDB container first. Common users start with C## prefix and exist across all PDBs.
-- First switch to the target PDB
ALTER SESSION SET CONTAINER = PDBNAME;
-- Create a local user in the PDB
CREATE USER pdb_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO pdb_user;
PDB Datafile Locations
Shows datafile paths for each PDB in the CDB — useful for storage planning and migration
💡 Run from CDB$ROOT — shows which datafiles belong to each PDB by con_id. Essential reference before cloning or unplugging a PDB.
-- Run from CDB$ROOT
SELECT p.name AS pdb_name, d.name AS datafile_path,
ROUND(d.bytes/1024/1024/1024,2) AS size_gb
FROM v$datafile d
JOIN v$pdbs p ON d.con_id = p.con_id
ORDER BY p.name, d.name;
💾
Tablespace & Storage
Tablespace Usage %
Used percentage, size GB, used MB, and free GB per tablespace — ordered by most used
💡 Run this daily to catch any tablespace approaching full capacity before it causes errors in production.
SELECT tablespace_name,
ROUND((used_space*100)/tablespace_size,2) AS used_pct,
ROUND(tablespace_size*8192/1024/1024/1024,2) AS size_gb,
ROUND(used_space*8192/1024/1024,2) AS used_mb,
ROUND((tablespace_size-used_space)*8192/1024/1024/1024,2) AS free_gb
FROM dba_tablespace_usage_metrics
ORDER BY used_pct DESC;
Tablespace Total vs Free
Total allocated GB and free GB per tablespace side by side
💡 A quick side-by-side comparison of how much space each tablespace has versus how much is actually free.
-- Total GB per tablespace
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024,2) AS total_gb
FROM dba_data_files
GROUP BY tablespace_name;
-- Free GB per tablespace
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024,2) AS free_gb
FROM dba_free_space
GROUP BY tablespace_name;
Tablespace Datafiles
Lists all datafiles with tablespace name, file path, size in GB, and autoextend setting
💡 Check autoextend settings to avoid surprise disk space exhaustion when a tablespace grows.
SELECT tablespace_name, file_name,
bytes/1024/1024/1024 AS size_gb,
autoextensible
FROM dba_data_files
ORDER BY tablespace_name;
Validate Datafiles
Check file number, name, and current status of all datafiles
💡 A file in OFFLINE or RECOVER status needs immediate attention — run this daily to stay aware of any datafile issues.
SELECT file#, name, status
FROM v$datafile;
Temp Tablespace Files
Lists temporary tablespace files with name and size in MB
💡 Temp tablespace holds sort operations and hash joins — knowing its size helps diagnose ORA-1652 errors.
SELECT tablespace_name, file_name,
bytes/1024/1024 AS size_mb
FROM dba_temp_files;
Temp Tablespace Current Usage
Real-time used and free GB in temp tablespace based on active sort segments
💡 Shows how much temp space is actually in use right now — useful during heavy query loads.
SELECT A.tablespace_name AS tablespace, D.gb_total,
ROUND(SUM(A.used_blocks * D.block_size)/1024/1024/1024,2) AS gb_used,
ROUND(D.gb_total - SUM(A.used_blocks * D.block_size)/1024/1024/1024,2) AS gb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size,
ROUND(SUM(C.bytes)/1024/1024/1024,2) AS gb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name, D.gb_total;
Tablespace Usage (Specific)
Detailed usage metrics for a specific tablespace — change the name as needed
💡 Use this to deep-dive into a single tablespace — handy when a specific tablespace is reported as full.
SELECT *
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = 'USERS'; -- change name
Datafile Block Corruption
Returns details of any datafile block corruptions detected
💡 An empty result here is good news. Any rows mean data blocks are corrupt and must be investigated immediately.
SELECT * FROM v$database_block_corruption;
👤
Sessions & Locks
Active Sessions
Lists all active sessions with SID, serial#, username, program, and machine
💡 Your starting point when users report slowness — shows who is connected and actively working.
SELECT sid, serial#, username, status,
program, machine
FROM v$session
WHERE status = 'ACTIVE';
Session Count by Status
Total session count grouped by status (ACTIVE / INACTIVE)
💡 A sudden spike in ACTIVE sessions often signals a problem — this gives you the count at a glance.
SELECT status, COUNT(*)
FROM v$session
GROUP BY status;
Blocking Sessions
Shows which sessions are blocking others — includes blocker and waiter details
💡 Blocking sessions freeze application transactions. This immediately identifies the blocker so you can investigate or kill it.
SELECT w.sid AS waiting_sid, w.username AS waiting_user,
b.sid AS blocking_sid, b.username AS blocking_user,
w.seconds_in_wait AS wait_seconds, w.event
FROM v$session w
JOIN v$session b ON b.sid = w.blocking_session
WHERE w.blocking_session IS NOT NULL
ORDER BY w.seconds_in_wait DESC;
Kill a Session
Template to kill a specific session — replace SID and SERIAL# values
💡 Use this after identifying a problematic session from Active Sessions or Blocking Sessions queries above.
-- Find SID and SERIAL# first, then run:
ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;
Long Running Queries
Active sessions running SQL for more than 60 seconds with elapsed time
💡 Identifies runaway queries before they impact production — check this when users report intermittent slowness.
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
ROUND(elapsed_time/1000000,1) AS elapsed_secs,
q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
AND elapsed_time > 60000000
ORDER BY elapsed_time DESC;
Long Operation Progress
Monitors real-time progress of Data Pump, RMAN, or any long-running database operation
💡 Essential during Data Pump imports/exports and RMAN jobs — shows % complete, elapsed time, and estimated finish time.
SELECT a.sid,
TO_CHAR(start_time,'DD-MON HH24:MI') AS start_time,
opname, SUBSTR(b.module,1,15) AS module,
target, totalwork, sofar,
ROUND(elapsed_seconds/60,1) AS elapsed_min, b.status,
time_remaining AS remaining_secs,
SYSDATE + time_remaining/86400 AS est_end_time,
ROUND(sofar/NULLIF(totalwork,0)*100,2) AS pct_complete
FROM gv$session_longops a
JOIN v$session b ON a.sid = b.sid
WHERE b.status = 'ACTIVE' AND totalwork <> sofar
ORDER BY start_time;
Current SQL per Session
Shows the SQL currently being executed by each active session
💡 Useful when you need to see exactly what SQL a specific session is running — critical for performance diagnostics.
SELECT s.sid, s.serial#, s.username, s.status,
s.machine, q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
AND s.username IS NOT NULL
ORDER BY s.sid;
📊
Performance Monitoring
Top SQL by CPU
Top 10 SQL statements ranked by CPU time from shared pool
💡 Identify the most CPU-hungry queries currently in the shared pool — key starting point for performance tuning.
SELECT sql_id, executions, cpu_time,
elapsed_time, disk_reads,
SUBSTR(sql_text,1,80) AS sql_text
FROM v$sql
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;
Top Wait Events
System-wide wait events ranked by total wait time
💡 Wait events reveal where the database is spending its time. High "db file sequential read" often means index/IO issues.
SELECT event, total_waits, total_timeouts,
ROUND(time_waited/100,2) AS time_waited_secs,
ROUND(average_wait/100,4) AS avg_wait_secs
FROM v$system_event
WHERE event NOT LIKE 'SQL*Net%'
ORDER BY time_waited DESC
FETCH FIRST 15 ROWS ONLY;
Buffer Cache Hit Ratio
Calculates the buffer cache hit ratio — should be above 95%
💡 A ratio below 90% means the database is doing too many physical disk reads. Consider increasing the SGA buffer cache size.
SELECT ROUND(
(1 - (phyrds.value / (congets.value + dbgets.value))) * 100, 2
) AS buffer_hit_ratio_pct
FROM v$sysstat phyrds, v$sysstat congets, v$sysstat dbgets
WHERE phyrds.name = 'physical reads'
AND congets.name = 'consistent gets'
AND dbgets.name = 'db block gets';
SGA Component Sizes
Current sizes of all SGA components in MB
💡 Shows how memory is distributed across the buffer cache, shared pool, and large pool — useful when tuning memory.
SELECT component,
ROUND(current_size/1024/1024,2) AS current_mb,
ROUND(min_size/1024/1024,2) AS min_mb,
ROUND(max_size/1024/1024,2) AS max_mb
FROM v$sga_dynamic_components
ORDER BY current_size DESC;
Undo Segment Usage
Active, unexpired, and expired undo extents per segment
💡 High undo usage can cause ORA-01555 errors. Use this to monitor undo activity during long transactions.
SELECT usn, status, wraps, extends, shrinks, xacts
FROM v$rollstat
JOIN v$rollname USING (usn)
ORDER BY usn;
Top SQL by Disk Reads
Top 10 SQL statements ranked by physical disk reads
💡 High disk reads indicate missing indexes, full table scans, or insufficient buffer cache. These are prime candidates for SQL tuning.
SELECT sql_id, executions, disk_reads,
ROUND(disk_reads/NULLIF(executions,0),1) AS reads_per_exec,
SUBSTR(sql_text,1,80) AS sql_text
FROM v$sql
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;
🛡️
Backup & RMAN
RMAN Backup Job Status
Last 10 RMAN backup jobs with status, start/end times and elapsed duration
💡 Run this every morning as part of your daily DBA check — immediately shows if last night's backup succeeded or failed.
SELECT session_key, input_type, status,
TO_CHAR(start_time,'DD-MON-YYYY HH24:MI') AS start_time,
TO_CHAR(end_time,'DD-MON-YYYY HH24:MI') AS end_time,
ROUND(elapsed_seconds/60,1) AS elapsed_min
FROM v$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 10 ROWS ONLY;
RMAN Backup Sets
Lists recent backup pieces with handle, completion time, and size in GB
💡 Shows the actual backup files on disk — useful to verify backup pieces exist before a recovery operation.
SELECT handle,
TO_CHAR(completion_time,'DD-MON-YYYY HH24:MI') AS completed,
ROUND(bytes/1024/1024/1024,2) AS size_gb, status
FROM v$backup_piece
WHERE status = 'A'
ORDER BY completion_time DESC;
Fast Recovery Area Usage
FRA size, used space, reclaimable space, and percentage used
💡 If FRA usage exceeds 85%, the database may stop archiving. Monitor this daily to prevent ORA-00257 errors.
SELECT name AS fra_location,
ROUND(space_limit/1024/1024/1024,2) AS limit_gb,
ROUND(space_used/1024/1024/1024,2) AS used_gb,
ROUND(space_reclaimable/1024/1024,2) AS reclaimable_mb,
ROUND((space_used/space_limit)*100,2) AS used_pct
FROM v$recovery_file_dest;
Archive Log Generation Rate
Number of archive logs generated per day with total size in GB
💡 Helps understand how quickly the database generates archive logs — critical for sizing the FRA.
SELECT TRUNC(first_time) AS log_date,
COUNT(*) AS logs_generated,
ROUND(SUM(blocks*block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log
WHERE standby_dest = 'NO'
GROUP BY TRUNC(first_time)
ORDER BY log_date DESC;
Datafiles Not Backed Up
Identifies datafiles with no recent backup — potential recovery risk
💡 Any datafile missing a recent backup means you cannot fully recover the database if that file is lost.
SELECT d.file#, d.name, d.status,
TO_CHAR(b.completion_time,'DD-MON-YYYY') AS last_backup
FROM v$datafile d
LEFT JOIN v$backup_datafile b ON d.file# = b.file#
WHERE b.completion_time IS NULL
OR b.completion_time < SYSDATE - 7
ORDER BY d.file#;
Daily Archive Log Volume
Archive logs generated per day with count and total size using completion time
💡 Sudden spikes in archive log volume indicate heavy DML activity — important for sizing FRA and archive log destinations.
SELECT TRUNC(completion_time) AS log_date,
COUNT(*) AS log_count,
ROUND(SUM(blocks*block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log
GROUP BY TRUNC(completion_time)
ORDER BY log_date DESC;
🔐
Users & Privileges
All Database Users
Username, account status, lock date, expiry date, default tablespace, profile, and last login
💡 Full user inventory — shows locked/expired accounts, last login time, and which profile each user is under.
SELECT username, account_status, lock_date, expiry_date,
default_tablespace, created, profile, last_login
FROM dba_users
ORDER BY username;
Locked or Expired Accounts
All user accounts that are currently locked, expired, or both
SELECT username, account_status, expiry_date, profile
FROM dba_users
WHERE account_status LIKE '%LOCKED%'
OR account_status LIKE '%EXPIRED%'
ORDER BY username;
DBA Role Members
Lists all users and roles that have been granted the DBA role
💡 The DBA role has full database privileges — this should have very few entries. Any unexpected users here is a security concern.
SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE granted_role = 'DBA'
ORDER BY grantee;
System Privileges by User
Lists all system privileges granted to each user or role
💡 Useful for security audits — shows who has been granted powerful privileges like CREATE ANY TABLE or DROP ANY INDEX.
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
ORDER BY grantee, privilege;
Role Grants
Lists all role assignments to users and other roles
💡 Shows the full role hierarchy — useful when troubleshooting access issues or auditing who has what permissions.
SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
ORDER BY grantee;
Object Privileges by User
All object-level privileges (SELECT, INSERT, etc.) granted to users
💡 Identifies which users have direct object access — important for data security reviews and least-privilege enforcement.
SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
ORDER BY grantee, owner, table_name;
Unlock User Account
Template to unlock a locked user account and optionally reset the password
💡 Use this when a user is locked out — always verify with the application team first.
-- Unlock account
ALTER USER &username ACCOUNT UNLOCK;
-- Reset password (optional)
ALTER USER &username IDENTIFIED BY &new_password;
🔄
Data Guard
Data Guard Role & Protection Mode
Database role, protection mode, protection level, open mode, and switchover status
💡 First query to run on any Data Guard database — confirms whether you are on the Primary or Standby.
SELECT name, db_unique_name, database_role,
protection_mode, protection_level,
open_mode, switchover_status
FROM v$database;
Apply Lag & Transport Lag
Shows apply lag and transport lag — run on standby database
💡 A non-zero apply lag means the standby is behind the primary. Any lag increases potential data loss during a failover.
SELECT name, value, unit, time_computed
FROM v$dataguard_stats
WHERE name IN ('apply lag','transport lag');
Primary Last Generated Sequence
Shows the last archive log sequence number generated on the primary database per thread
💡 Run on the PRIMARY to get the latest sequence number — compare with standby to check for any gap.
SELECT thread#, MAX(sequence#) AS "Last Seq Generated"
FROM v$archived_log val, v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY thread#
ORDER BY thread#;
Sequence Applied vs Received (Standby)
Compares last received sequence vs last applied sequence on the standby — shows any redo gap
💡 Run on the STANDBY to verify redo is applied without gaps. A difference of 0 means the standby is fully in sync.
SELECT ARCH.THREAD# AS "Thread",
ARCH.SEQUENCE# AS "Last Seq Received",
APPL.SEQUENCE# AS "Last Seq Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "Difference"
FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_TIME) IN
(SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#)) ARCH,
(SELECT THREAD#, SEQUENCE# FROM V$LOG_HISTORY
WHERE (THREAD#, FIRST_TIME) IN
(SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY
GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Managed Standby Processes
Shows all active MRP, RFS, and Archiver processes on the standby
💡 Check that MRP0 is in APPLYING_LOG status — if it shows WAIT_FOR_LOG the standby may have stopped receiving redo.
SELECT process, status, thread#,
sequence#, block#, blocks
FROM v$managed_standby
ORDER BY process;
Start / Stop MRP (Apply Process)
Commands to start and stop the Managed Recovery Process on the standby
💡 Use START to begin real-time redo apply, and CANCEL to stop it cleanly before a switchover or maintenance.
-- Start real-time redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;
-- Stop MRP (cancel apply)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Redo Log Gap Check
Shows standby redo log processes with non-idle statuses — helps identify gaps or stalled apply
💡 If WAIT_FOR_LOG persists, the primary has stopped sending redo — investigate network or primary-side archiver.
SELECT THREAD#, SEQUENCE#, STATUS
FROM GV$MANAGED_STANDBY
WHERE STATUS NOT IN ('ALLOCATED','CONNECTED')
ORDER BY SEQUENCE# DESC;
Standby Redo Log Verification
Shows all online and standby redo log files with group, type, thread, size, and member path
💡 Standby redo logs (SRLs) must be present and properly sized for real-time apply. Each SRL should match the primary redo log size.
SELECT a.group#, a.status, a.type, a.member,
b.thread#, ROUND(b.bytes/1024/1024/1024,2) AS size_gb
FROM v$logfile a
LEFT JOIN v$standby_log b ON a.group# = b.group#
ORDER BY a.group#;
Logical Standby Progress
Applied SCN vs newest SCN — shows how far behind a logical standby is
💡 For logical standby only — a large SCN difference means the apply engine is falling behind and needs investigation.
-- Run on Logical Standby
SELECT APPLIED_SCN, NEWEST_SCN,
(NEWEST_SCN - APPLIED_SCN) AS difference
FROM DBA_LOGSTDBY_PROGRESS;
-- State of Logical Standby
SELECT STATE FROM V$LOGSTDBY_STATE;
📦
Database Objects
Invalid Objects
Count of invalid objects grouped by owner and object type
💡 Invalid procedures, functions, or packages cause application errors. Run this after any database upgrade or patching activity.
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status <> 'VALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;
Object Counts by Type
Total count of database objects grouped by type (tables, indexes, views, etc.)
💡 Gives a bird's-eye view of the database schema — useful for capacity planning and comparing environments.
SELECT object_type, COUNT(*) AS total_count
FROM dba_objects
WHERE object_type IN ('TABLE','INDEX','VIEW','PROCEDURE',
'FUNCTION','PACKAGE','TRIGGER','SEQUENCE','SYNONYM')
GROUP BY object_type
ORDER BY total_count DESC;
Object Counts by Owner
Total objects per schema owner grouped by type
💡 Use this to understand which application schemas contain the most objects — helpful during migrations and schema reviews.
SELECT owner, object_type, COUNT(*) AS cnt
FROM dba_objects
GROUP BY owner, object_type
ORDER BY owner, object_type;
Largest Tables by Segment Size
Top 20 largest tables by allocated segment size in GB
💡 Helps identify space-hogging tables — useful for storage planning and deciding which tables might benefit from partitioning.
SELECT owner, segment_name,
ROUND(bytes/1024/1024/1024,3) AS size_gb
FROM dba_segments
WHERE segment_type = 'TABLE'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;
Database Directories
Lists all Oracle directory objects with owner and filesystem path
💡 Directory objects are used by Data Pump, UTL_FILE, and external tables — knowing where they point is essential during migrations.
SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY owner, directory_name;
Recompile All Invalid Objects
Uses UTL_RECOMP to recompile all invalid schema objects in parallel
💡 Run this after database upgrades, patching, or schema changes to restore all invalid objects to VALID status.
-- Recompile all invalid objects (parallel)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);
-- Or recompile all serially
EXEC UTL_RECOMP.RECOMP_SERIAL;
🏥
Health Check
Tablespaces Nearing Capacity
Lists tablespaces using more than 75% of their allocated space
💡 Your most important daily check — catches tablespaces heading toward full before applications throw ORA-01653 errors.
SELECT tablespace_name,
ROUND((used_space*100)/tablespace_size,2) AS used_pct,
ROUND(tablespace_size*8192/1024/1024/1024,2) AS size_gb,
ROUND((tablespace_size-used_space)*8192/1024/1024/1024,2) AS free_gb
FROM dba_tablespace_usage_metrics
WHERE (used_space*100)/tablespace_size > 75
ORDER BY used_pct DESC;
ASM Disk Group Usage
Shows allocated GB, free GB, and percentage used for each ASM disk group
💡 If you are using ASM storage, run this daily — a disk group nearing full will cause database writes to fail.
SELECT name,
ROUND(total_mb/1024,2) AS allocated_gb,
ROUND(free_mb/1024,2) AS free_gb,
ROUND(((total_mb-free_mb)/total_mb)*100,2) AS pct_used
FROM v$asm_diskgroup
ORDER BY pct_used DESC;
Segment Size by Owner
Total segment size in GB for every schema owner, largest first
💡 Quickly shows which application schemas are consuming the most storage — standard check during capacity reviews.
SELECT owner,
ROUND(SUM(bytes)/1024/1024/1024,2) AS size_gb
FROM dba_segments
WHERE bytes > 0
GROUP BY owner
ORDER BY size_gb DESC;
Segment Size by Owner & Type
Breaks down storage usage by schema owner and segment type (tables, indexes, LOBs)
💡 Useful when a specific schema uses unexpected disk space — shows whether tables, indexes, or LOBs are driving the growth.
SELECT owner, segment_type,
ROUND(SUM(bytes)/1024/1024/1024,2) AS size_gb
FROM dba_segments
WHERE bytes > 0
GROUP BY owner, segment_type
ORDER BY owner, size_gb DESC;
FRA Health Check
Fast Recovery Area size, used space, and percentage used in one quick query
💡 FRA filling up is one of the most common causes of database downtime. Keep usage below 80% to maintain safe archiving headroom.
SELECT ROUND(space_limit/1048576,0) AS limit_mb,
ROUND(space_used/1048576,0) AS used_mb,
ROUND(space_reclaimable/1048576,0) AS reclaimable_mb,
ROUND((space_used/space_limit)*100,2) AS pct_used
FROM v$recovery_file_dest;
Invalid Object Summary
Quick count of all invalid objects in the database with owner breakdown
💡 A daily check — any increase in invalid object count after a deployment or maintenance window needs immediate attention.
SELECT COUNT(*) AS total_invalid FROM dba_objects WHERE status <> 'VALID';
-- Detail breakdown by owner
SELECT owner, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status <> 'VALID'
GROUP BY owner
ORDER BY invalid_count DESC;
Active Session Overview
Current count of active vs inactive sessions and total connections
💡 A quick pulse check of database load — abnormally high ACTIVE sessions compared to normal baseline signals a potential problem.
SELECT status, type, COUNT(*) AS session_count
FROM v$session
GROUP BY status, type
ORDER BY status, type;
🔗
Oracle RAC — Real Application Clusters
Check Cluster Service Status
Verifies whether the CRS stack is online on one or all RAC nodes
💡 Run this as the first check when investigating a RAC node issue — confirms whether the Clusterware stack is up.
-- Check CRS stack on the local node
crsctl check crs
-- Check cluster on all nodes
crsctl check cluster -all
Cluster Resources Status (Detailed)
Displays all cluster resources in a formatted table with online/offline state per node
💡 The most complete RAC health snapshot — every resource is listed with its state on each node.
crsctl stat res -t
Stop / Start Cluster Services
Gracefully stops or starts the Clusterware stack on a specific node or across the entire cluster
💡 Always stop a specific node first during planned maintenance to avoid disrupting the whole cluster.
-- Stop cluster on node rac1
crsctl stop cluster -n rac1
-- Stop cluster on ALL nodes
crsctl stop cluster -all
-- Start cluster on node rac1
crsctl start cluster -n rac1
-- Start cluster on ALL nodes
crsctl start cluster -all
Voting Disk Location
Shows the location and status of all voting disks used by CSS to determine cluster membership
💡 Voting disks are critical for RAC quorum — if a node cannot reach the majority, it will be evicted from the cluster.
crsctl query css votedisk
RAC Database Status via SRVCTL
Shows the current running status of a RAC database and its instances across all cluster nodes
💡 Replace 'oradb' with your actual database unique name. Shows which instances are running on which nodes.
-- Replace 'oradb' with your actual database name
srvctl status database -d oradb
OCR File Integrity Check
Checks the Oracle Cluster Registry (OCR) disk location and verifies its integrity
💡 The OCR stores cluster configuration. Run ocrcheck after cluster issues to confirm the registry is not corrupt.
ocrcheck
Cluster Name & Version
Displays the cluster name and version of the Oracle Clusterware installation
💡 cemutlo -n returns the cluster name and -w returns the full version string — useful for raising an SR with Oracle Support.
-- Show cluster name
cemutlo -n
-- Show cluster version
cemutlo -w
Enable / Disable CRS Auto Restart
Controls whether Oracle Clusterware automatically starts when the server boots up
💡 Disable auto restart before OS-level patching so the cluster does not come up automatically after a reboot. Always re-enable once maintenance is complete.
-- Disable CRS auto-restart on server reboot
crsctl disable crs
-- Re-enable CRS auto-restart
crsctl enable crs
RAC Listener Status
Shows the status of all RAC listeners managed by SRVCTL across all cluster nodes
💡 In a RAC environment always use SRVCTL to manage listeners — not lsnrctl directly, which bypasses cluster awareness.
srvctl status listener
Stop / Start HAS (Oracle Restart)
Stops or starts the High Availability Service stack — used on standalone Grid Infrastructure environments
💡 HAS is the equivalent of CRS on a single-node Oracle Restart configuration. Stopping HAS on a RAC node brings down all Oracle resources on that node.
-- Stop the High Availability Service
crsctl stop has
-- Start the High Availability Service
crsctl start has
-- Check HAS status
crsctl check has
Cluster Interconnect Details
Displays all network interfaces registered with Oracle Interface Configuration tool
💡 The private interconnect carries Cache Fusion traffic. Confirm the correct interface is configured for the interconnect and not the public network.
oifcfg getif
ASMCMD — List All Diskgroups
Lists all ASM disk groups available to the ASM instance with storage attributes
💡 Run inside ASMCMD shell. The -k flag shows detailed statistics including disk headers. Verify DATA, FRA, REDO diskgroups are all mounted.
-- Connect to ASMCMD shell first (as grid user):
-- asmcmd
-- List diskgroups with details
lsdsk -k
🐧
Linux Commands for Oracle DBAs
Navigation Commands
Essential directory and file system navigation commands for daily DBA use
💡 Always run pwd first when SSH-ing to a new server — confirm your location before running any command in production.
pwd # Print current directory
ls -l # List with permissions, owner, size
ls -la # Include hidden files
ls -lh # Human-readable sizes
cd /u01/app # Go to absolute path
cd .. # Go one level up
cd ~ # Go to home directory
cd - # Go to previous directory
clear # Clear terminal (Ctrl+L)
Disk Space Usage
Check disk space usage on filesystems and directories
💡 As a DBA, run df -h daily to check filesystem space — especially /u01, /u02, and archive log destinations to prevent ORA-00257.
df -h # All mounted filesystems (human-readable)
df -h /u01 # Specific filesystem
du -sh /u01/app/oracle # Size of a directory
du -sh /u02/archivelogs/* # Archive log sizes
du -h --max-depth=1 /u01 # Directory sizes one level deep
Process Monitoring
Monitor running processes, CPU and memory usage — essential for DBA performance checks
💡 Use ps -ef | grep ora to quickly confirm Oracle background processes are running. top shows real-time CPU and memory consumption.
ps -ef | grep ora # All Oracle processes
ps -ef | grep pmon # Check PMON is running
ps aux | sort -rk 3 | head # Top CPU consumers
top # Real-time process monitor
top -u oracle # Oracle user processes only
free -h # Memory and swap usage
File Permissions & Ownership
Change file permissions and ownership — critical for Oracle software installations
💡 Oracle datafiles and binaries must have correct ownership (oracle:oinstall) and permissions. Incorrect permissions prevent startup.
Commands to monitor the Oracle alert log and search for errors in real-time
💡 Always monitor the alert log after any database operation — it is the first place Oracle reports errors, crashes, and important events.
# Tail the alert log in real-time
tail -100f $ORACLE_BASE/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log
# Search for ORA- errors in alert log
grep -i "ORA-" alert_ORCL.log | tail -50
# Search for errors in last 100 lines
tail -100 alert_ORCL.log | grep -i "error\|ORA-\|fatal"
# Find alert log location from SQL
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
Oracle Environment Variables
Check and set Oracle environment variables — required before running Oracle commands
💡 Always verify your environment variables before running sqlplus or RMAN — wrong ORACLE_SID will connect to the wrong database.
# Check current Oracle environment
echo $ORACLE_HOME
echo $ORACLE_SID
echo $ORACLE_BASE
echo $PATH
# Set Oracle environment variables
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
# List all environment variables
env | grep -i oracle
SSH & File Transfer (SCP)
Connect to remote servers and transfer files between hosts
💡 Use SCP to transfer Oracle software binaries, backup files, and scripts between servers. Always verify the destination path before transferring large files.
# SSH to a remote server
ssh oracle@192.168.1.100
ssh -p 22 oracle@hostname
# Copy file TO remote server
scp /local/file.tar oracle@remote:/destination/
# Copy file FROM remote server
scp oracle@remote:/path/file.tar /local/path/
# Copy directory recursively
scp -r /local/dir oracle@remote:/destination/
# Check SSH connectivity
ssh -v oracle@hostname # verbose output
grep, awk, sed — Text Processing
Powerful text filtering tools used daily by Oracle DBAs for log analysis and scripting
💡 grep with -i is case-insensitive — essential for searching Oracle logs. awk is ideal for extracting columns from structured output.
# grep — search for patterns
grep -i "ora-" alert_ORCL.log # Case-insensitive
grep -n "error" file.txt # Show line numbers
grep -v "SQL*Net" file.txt # Exclude matches
grep -E "ORA-00257|ORA-01555" # Multiple patterns
# awk — extract columns
df -h | awk '{print $1, $5}' # Show col 1 and 5
ps -ef | awk '{print $1, $8}' # User and command
# sed — stream editor
sed 's/OLDVALUE/NEWVALUE/g' file # Replace text
sed -n '10,20p' file.txt # Print lines 10-20
Network Connectivity Checks
Test network connectivity and check Oracle listener port availability
💡 Always check port 1521 connectivity from the application server before troubleshooting TNS errors — the issue may be a firewall rule, not Oracle.
ping -c 4 hostname # Test basic connectivity
traceroute hostname # Trace network path
# Check if port 1521 is listening
netstat -tlnp | grep 1521
ss -tlnp | grep 1521
# Test port reachability from client
telnet db-server 1521
# Check tnsping (Oracle)
tnsping ORCL # Test TNS connection
Cron Jobs (Scheduled Tasks)
Schedule Oracle backup scripts and maintenance tasks using crontab
💡 Always test your script manually before adding to cron. Redirect stdout and stderr to a log file so you can see failures in cron-executed scripts.
# Edit the crontab for the oracle user
crontab -e
# View current crontab
crontab -l
# Crontab format: MIN HOUR DOM MON DOW COMMAND
# Run RMAN backup every night at 11:00 PM
0 23 * * * /home/oracle/scripts/rman_backup.sh >> /tmp/rman_backup.log 2>&1
# Run archive log cleanup every 6 hours
0 */6 * * * /home/oracle/scripts/clean_archivelogs.sh
# Check cron service is running
systemctl status crond
vi / vim Editor Essentials
Essential vi/vim commands for editing Oracle configuration files on Linux
💡 vi is available on every Unix/Linux server — even minimal installations. Master these basic commands to edit listener.ora, tnsnames.ora, and bash_profile.
vi filename # Open file in vi editor
# MODES:
# Press 'i' → Insert mode (start typing)
# Press 'Esc' → Return to command mode
# SAVING & QUITTING (command mode):
:w # Save file
:q # Quit (no changes)
:wq # Save and quit
:q! # Quit without saving
:wq! # Force save and quit
# NAVIGATION (command mode):
gg # Go to first line
G # Go to last line
:n # Go to line number n
/searchterm # Search forward
n # Next search result
YUM Package Management
Install Oracle pre-requisite packages and manage software using YUM
💡 Always install oracle-database-preinstall-19c before installing Oracle Database — it automatically sets kernel parameters and creates the oracle user.
# Install Oracle 19c pre-install package
yum install -y oracle-database-preinstall-19c
# Install common packages
yum install -y vim wget curl net-tools
# Check if a package is installed
rpm -qa | grep oracle
# Update all packages
yum update -y
# Download file using wget
wget https://example.com/oracle_software.zip
# Check installed package version
rpm -q oracle-database-preinstall-19c
Archive & Compress Files
Create, extract, and manage compressed archives — used for Oracle software and backup transfers
💡 Use tar -czf for Oracle software zip files and for transferring large directories between servers during migrations.
# Create a compressed tar archive
tar -czf archive.tar.gz /path/to/dir
# Extract a tar archive
tar -xzf archive.tar.gz
# List contents without extracting
tar -tzf archive.tar.gz
# Unzip Oracle software
unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0.0/dbhome_1/
# Check file size
ls -lh archive.tar.gz
du -sh /path/to/directory
User & Group Management
Create Oracle OS users and groups required for Oracle Database and Grid Infrastructure installation
💡 The oracle user must be in the oinstall, dba, and asmdba groups. Wrong group membership is one of the most common Oracle installation failures.
# Create groups
groupadd oinstall
groupadd dba
groupadd asmdba
groupadd asmadmin
# Create oracle user
useradd -g oinstall -G dba,asmdba oracle
passwd oracle
# Create grid user (for Grid Infrastructure)
useradd -g oinstall -G asmdba,asmadmin grid
passwd grid
# Check user groups
id oracle
id grid
# Add existing user to a group
usermod -aG dba oracle