100 Essential Oracle DBA Commands: Instance, SQL Tuning, RMAN & Data Guard

Preface

Oracle DBA daily work involves more than executing a few SQL statements. Issues like database startup failures, session blocking, tablespace alerts, UNDO anomalies, SQL performance degradation, archive log accumulation, backup failures, and Data Guard lag all require diagnosis via commands and dynamic performance views. This article compiles 100 high-frequency commands across instance management, parameter configuration, session troubleshooting, lock analysis, SQL performance, tablespace, user privileges, UNDO, temporary tablespace, RMAN, Data Guard, RAC, Data Pump, and OS diagnostics. Usernames, paths, SIDs, SQL_IDs, tablespace names, and file numbers are examples; replace with actual environment values. Confirm impact scope before executing destructive operations in production.

1. Instance and Database Management (Commands 1-10)

Covers basic instance control: sqlplus / as sysdba for local SYSDBA login; sqlplus sys@orcl as sysdba for remote. Startup commands: STARTUP (full open), STARTUP MOUNT (for recovery, archivelog mode, datafile relocation), STARTUP NOMOUNT (for database creation, control file rebuild). ALTER DATABASE OPEN and ALTER DATABASE OPEN READ ONLY to open from MOUNT. Shutdown: SHUTDOWN IMMEDIATE preferred in production (rolls back uncommitted transactions, disconnects users). Query instance status via v$instance (instance_name, host_name, version, status, database_status, startup_time). Database status/role via v$database (name, open_mode, database_role, log_mode, protection_mode, switchover_status). Archive mode check: ARCHIVE LOG LIST or SELECT log_mode FROM v$database. Datafile total size: aggregate dba_data_files bytes to GB (excludes temp, control, redo, archive logs).

2. Parameters, Control Files, Redo Logs (Commands 11-20)

View parameters: SHOW PARAMETER processes or query v$parameter for name, value, isdefault, issys_modifiable. Modify online: ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH SID='*'; SCOPE options: MEMORY (current instance only), SPFILE (persist after restart), BOTH. Reset parameter: ALTER SYSTEM RESET open_cursors SCOPE=SPFILE SID='*' (restart usually needed). Create PFILE from SPFILE: CREATE PFILE='/tmp/initorcl.ora' FROM SPFILE (backup or fix corrupt SPFILE). Create SPFILE from PFILE: CREATE SPFILE FROM PFILE='/tmp/initorcl.ora' (RAC: ensure SPFILE on shared storage). Control file locations: SHOW PARAMETER control_files or SELECT name FROM v$controlfile. Redo log groups/members: join v$log and v$logfile for group#, thread#, sequence#, size_mb, status, archived, member. Manual log switch: ALTER SYSTEM SWITCH LOGFILE (ends current group). Manual checkpoint: ALTER SYSTEM CHECKPOINT (advances checkpoint info, not immediate dirty block flush). Archive current log: ALTER SYSTEM ARCHIVE LOG CURRENT (waits for archival completion, used in backup/Data Guard).

3. Session, Transaction, Lock Troubleshooting (Commands 21-30)

Active sessions: query v$session for sid, serial#, username, status, machine, program, event, sql_id, last_call_et where type='USER' and status='ACTIVE'. Session details: same view with osuser, module, action, wait_class, prev_sql_id, logon_time for specific SID. Connection counts by user/machine/program/status: group by those columns to detect connection pool leaks. Long-running operations: v$session_longops shows sid, serial#, opname, target, sofar, totalwork, progress_pct, elapsed_seconds, time_remaining (captures full scans, backup, stats gathering). Blocked sessions: filter v$session where blocking_session IS NOT NULL, order by seconds_in_wait. Lock wait relationships: self-join v$lock on id1,id2 where a.block=1 and b.request>0 to find blocker/waiter SIDs. Locked objects: join v$locked_object, dba_objects, v$session for object owner, name, type, locked_mode. Kill session: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; RAC add @instance_id. Disconnect session: ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE or POST_TRANSACTION to wait for commit. Active transactions using UNDO: join v$transaction and v$session for used_ublk, used_urec, undo_mb (calculated via db_block_size).

4. SQL Performance Diagnosis (Commands 31-40)

Current SQL for session: join v$session and v$sql on sql_id and child_number. Full SQL text by SQL_ID: v$sqltext_with_newlines ordered by piece. Top SQL by elapsed time: subquery on v$sql with elapsed_time/1e6, avg_elapsed_seconds, executions>0, top 20. Top by CPU time: similar using cpu_time. Top by logical reads (buffer_gets): calculate gets_per_exec. Top by physical reads (disk_reads): calculate reads_per_exec. Explain plan:

EXPLAIN PLAN FOR SELECT ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

(optimizer estimate, not actual). Actual plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', cursor_child_no=>NULL, format=>'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'))

; requires row source statistics (hint GATHER_PLAN_STATISTICS). Bind variable capture: v$sql_bind_capture (not captured every execution). Session cumulative wait events: v$session_event for specific SID, top 20 by time_waited (lifecycle cumulative, not just current SQL).

5. Tablespace and Datafile Management (Commands 41-50)

Permanent tablespace usage with autoextend max: complex query joining dba_data_files (current bytes, maxbytes) and dba_free_space to compute current_gb, used_gb, free_gb, current_used_pct, max_gb, remaining_to_max_gb. Datafile info: dba_data_files for file_id, tablespace_name, file_name, current_gb, autoextensible, max_gb, status. Tempfile info: same from dba_temp_files. Add datafile:

ALTER TABLESPACE USERS ADD DATAFILE '/path/users02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 40G

(verify filesystem space, permissions, block size, max blocks). Resize datafile: ALTER DATABASE DATAFILE '/path/users02.dbf' RESIZE 20G (ORA-03297 if used blocks beyond target). Enable autoextend:

ALTER DATABASE DATAFILE '/path/users02.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 40G

(avoid UNLIMITED on limited filesystems). Read-only tablespace: ALTER TABLESPACE ARCHIVE_DATA READ ONLY; revert with READ WRITE. Offline/online: ALTER TABLESPACE APP_DATA OFFLINE IMMEDIATE / ONLINE (never offline SYSTEM, SYSAUX, current UNDO, default TEMP). Create tablespace:

CREATE TABLESPACE APP_DATA DATAFILE '/path/app_data01.dbf' SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 100G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

. Drop tablespace: DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES (irreversible, high risk).

6. Segments, Objects, Users, Privileges (Commands 51-60)

Largest segments: dba_segments top 30 by bytes (owner, segment_name, partition_name, segment_type, tablespace_name, size_gb). Schema space usage: sum bytes per owner from dba_segments. Specific table segment size: filter dba_segments by owner and segment_name (note: LOB, partitions, indexes separate). Index status: dba_indexes for owner, index_name, table_name, index_type, status, visibility (Oracle 11g may lack visibility column), tablespace_name, last_analyzed. Invalid objects: dba_objects where status='INVALID'. Compile schema invalid objects:

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema=>'APP_USER', compile_all=>FALSE)

or run @?/rdbms/admin/utlrp.sql after upgrades. Create user:

CREATE USER app_user IDENTIFIED BY "StrongPassword_2026" DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp PROFILE default

(CDB: verify container). Grant CREATE SESSION: GRANT CREATE SESSION TO app_user; avoid DBA role. Grant object privileges: CREATE TABLE, VIEW, PROCEDURE, SEQUENCE. Tablespace quota: ALTER USER app_user QUOTA 20G ON app_data or UNLIMITED. Lock/unlock/expire password: ALTER USER app_user ACCOUNT LOCK/UNLOCK, PASSWORD EXPIRE, change password and unlock in one statement.

7. Temporary Tablespace, UNDO, Statistics (Commands 61-70)

Temp tablespace usage: v$temp_space_header for used_gb, free_gb, used_pct per tablespace. Sessions consuming most temp: join v$tempseg_usage, v$session, dba_tablespaces to get temp_mb per session (blocks * block_size). Temp segment overall: v$sort_segment for current_users, used_blocks, free_blocks, used_mb, free_mb. Add tempfile:

ALTER TABLESPACE TEMP ADD TEMPFILE '/path/temp02.dbf' SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 50G

. Resize tempfile: ALTER DATABASE TEMPFILE '/path/temp02.dbf' RESIZE 30G (check high water mark and active sessions first). UNDO extent status: dba_undo_extents group by tablespace_name, status (ACTIVE, UNEXPIRED, EXPIRED) with size_gb. Top UNDO-consuming transactions: join v$transaction and v$session for undo_mb (used_ublk * db_block_size). UNDO_RETENTION: SHOW PARAMETER undo_retention; set via ALTER SYSTEM SET undo_retention=3600 SCOPE=BOTH (retention not guaranteed if space pressure and no RETENTION GUARANTEE). Gather schema stats: DBMS_STATS.GATHER_SCHEMA_STATS with AUTO_SAMPLE_SIZE, FOR ALL COLUMNS SIZE AUTO, AUTO_DEGREE, cascade=TRUE. Gather table stats: similar with no_invalidate=FALSE. Production large tables: evaluate parallelism, sampling, window, plan change risk.

8. RMAN Backup and Recovery (Commands 71-80)

RMAN login: rman target / or rman target sys@orcl. Show configuration: SHOW ALL (check retention policy, controlfile autobackup, device type, parallelism, archivelog deletion policy). Report schema: REPORT SCHEMA (datafile numbers, sizes, tablespaces). Backup summary: LIST BACKUP SUMMARY; detailed: LIST BACKUP OF DATABASE. Crosscheck and delete expired: CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP (EXPIRED means repository record exists but file missing). Backup database + archivelogs: BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG (compression trade-off: CPU vs storage/window). Backup archivelogs and delete input: BACKUP ARCHIVELOG ALL DELETE INPUT (Data Guard: coordinate with deletion policy). Delete obsolete: REPORT OBSOLETE; DELETE NOPROMPT OBSOLETE (run report first). Validate database and archivelogs: BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; validate existing backups: RESTORE DATABASE VALIDATE (checks readability, physical/logical corruption). Restore single datafile (e.g., file 7): RUN block with SQL offline, RESTORE DATAFILE 7, RECOVER DATAFILE 7, SQL online (SYSTEM, UNDO, controlfile, non-archivelog mode differ).

9. Data Guard, RAC, PDB (Commands 81-90)

Data Guard role/protection: v$database for name, database_role, open_mode, protection_mode, protection_level, switchover_status. Archive destination status: v$archive_dest_status where status!='INACTIVE' (check ERROR column for network, service name, path, password file, standby state). Log gap: v$archive_gap shows thread#, low_sequence#, high_sequence# (one gap at a time). Standby apply processes: v$managed_standby for process (RFS receive, MRP0 apply coordinator, ARCH archive), status, thread#, sequence#, block#, blocks. Start real-time apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

(newer versions may default to real-time). Stop apply: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL (before maintenance/switchover). Last received/applied sequence: query v$archived_log max sequence# per thread# for received and applied='YES' (compare with v$dataguard_stats and business RPO). RAC instance status: gv$instance for inst_id, instance_name, host_name, version, status, database_status, startup_time. RAC session distribution: gv$session group by inst_id, username, status to verify load balance. PDB management (12c+): SHOW PDBS; ALTER PLUGGABLE DATABASE ALL OPEN; ALTER PLUGGABLE DATABASE ALL SAVE STATE; SHOW CON_NAME.

10. Scheduler, Data Pump, Listener, OS Diagnostics (Commands 91-100)

Scheduler job status: dba_scheduler_jobs for owner, job_name, enabled, state, last_start_date, last_run_duration, next_run_date, failure_count. Run job manually:

DBMS_SCHEDULER.RUN_JOB(job_name=>'APP_USER.JOB_SYNC_DATA', use_current_session=>FALSE)

(FALSE=background, TRUE=wait). Stop job forcefully:

DBMS_SCHEDULER.STOP_JOB(job_name=>'APP_USER.JOB_SYNC_DATA', force=>TRUE)

(may interrupt transactions). Data Pump directory: OS mkdir/chown/chmod, then

CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/backup/dump'; GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO app_user

(DB doesn't create OS dir). Export schema:

expdp system@orcl schemas=APP_USER directory=DUMP_DIR dumpfile=app_user_%U.dmp logfile=app_user_exp.log parallel=4 filesize=20G compression=all

(%U for parallel files). Import with remap:

impdp system@orcl directory=DUMP_DIR dumpfile=app_user_%U.dmp logfile=app_user_imp.log remap_schema=APP_USER:APP_USER_TEST remap_tablespace=APP_DATA:APP_DATA_TEST parallel=4

(pre-create user/tablespace/quota if needed). Listener status: lsnrctl status; services: lsnrctl services; start/stop: lsnrctl start/stop. Test TNS: tnsping orcl (only resolves name/reaches listener, not full login); real test: sqlplus app_user@orcl. ADRCI alert log: adrci exec="show homes"; tail 100 lines:

adrci exec="set homepath diag/rdbms/orcl/orcl; show alert -tail 100 -term"

; follow: adrci exec="set homepath ...; show alert -tail -f" (homepath from show homes). OS process: ps -ef | grep '[o]ra_pmon' for instances; top CPU Oracle processes:

ps -eo pid,ppid,%cpu,%mem,etime,args --sort=-%cpu | grep '[o]ra_' | head -20

. Map OS PID to session: join v$process and v$session on p.addr=s.paddr where p.spid=&os_pid.

Conclusion

Mastering Oracle DBA is not about memorizing commands but knowing when to use each, interpreting results, and determining next verification steps. High CPU requires distinguishing between heavy SQL, frequent parsing, runaway parallelism, or thundering herd. Tablespace growth demands differentiating business growth, segment bloat, recycle bin, LOB expansion, or misconfigured autoextend. Commands are entry points; diagnostic reasoning is the core competency.