This document provides all Oracle SQL commands for the Discovery phase.
It focuses on one database, using [SID] to identify it.
It assumes only database-level access (no server or OS access).
It assumes you can connect via SQL*Plus or similar tools with DBA privileges.
It clarifies how to identify the database’s SID and key details.
It retrieves the most recent diagnostic log entries (e.g., alert log) as [LOG_FILE].
Commands cover database status, data files, tablespaces, parameters, roles, and network settings.
Placeholders are marked in square brackets (e.g., [SID]).
Each placeholder has a preceding SQL command to retrieve its value.
Commands are separated by blank lines for readability.
Run commands in *SQLPlus, SQL Developer**, or a similar tool as a DBA user.
SELECT instance_name AS sid FROM v$instance;
What it does:
Gets the SID of the current database.
Returns the instance name (e.g., ORCL).
Use as [SID] in later commands.
SELECT instance_name, status, version, host_name FROM v$instance;
What it does:
Shows the status of the current database.
Returns instance name, status (e.g., OPEN), version, and host name.
Helps verify if the database is running and accessible.
SELECT name, cdb FROM v$database;
What it does:
Gets the database name and CDB status.
Returns the name and whether it’s a CDB (YES) or non-CDB (NO).
Helps identify if Pluggable Databases (PDBs) are used.
SELECT name, open_mode FROM v$pdbs;
What it does:
Lists all Pluggable Databases if the database is a CDB.
Shows PDB names and open modes (e.g., READ WRITE).
Returns no rows if the database is non-CDB.
SELECT value AS diag_dest FROM v$diag_info WHERE name = 'Diag Trace';
What it does:
Gets the diagnostic trace directory for the current database.
Returns the path (e.g., C:\app\oracle\diag\rdbms\orcl\ORCL\trace).
Use as [DIAG_DEST] to locate diagnostic files.
Note: Actual path is server-side; only visible if used in later views.
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE component_id = 'rdbms'
ORDER BY originating_timestamp DESC
FETCH FIRST 100 ROWS ONLY;
What it does:
Retrieves the most recent 100 alert log entries for the current database.
Shows timestamp and message text (e.g., errors, startups).
Use as [LOG_FILE] for diagnostic information.
Replaces file-based alert log access since no server access is available.
SELECT name FROM v$datafile;
What it does:
Lists all data file paths for the current database.
Shows files like C:\app\oracle\oradata\ORCL\system01.dbf.
Note: Paths are server-side; used for configuration awareness.
SELECT name FROM v$controlfile;
What it does:
Lists all control file paths for the current database.
Shows files like C:\app\oracle\oradata\ORCL\control01.ctl.
Note: Paths are server-side; used for configuration awareness.
SELECT member FROM v$logfile;
What it does:
Lists all redo log file paths for the current database.
Shows files like C:\app\oracle\oradata\ORCL\redo01.log.
Note: Paths are server-side; used for configuration awareness.
SELECT tablespace_name, status, contents FROM dba_tablespaces;
What it does:
Lists all tablespaces in the current database.
Shows tablespace name, status, and contents (e.g., PERMANENT).
Helps understand storage configuration.
SHOW PARAMETER;
What it does:
Lists all database parameters for the current database.
Shows parameter names, types, and values.
Helps identify configuration settings like memory or log settings.
SELECT role FROM dba_roles;
What it does:
Lists all roles defined in the current database.
Shows role names (e.g., DBA, CONNECT).
Helps understand security and access control.
SELECT privilege FROM dba_sys_privs WHERE grantee = USER;
What it does:
Lists system privileges for the current user.
Shows privileges like CREATE SESSION or DBA.
Helps verify your access level for running commands.
SELECT SUM(bytes)/1024/1024/1024 AS size_gb FROM dba_data_files;
What it does:
Calculates the total size of data files in gigabytes.
Shows the storage used by the database.
Helps assess database footprint.
SELECT sid, serial#, username, status, program FROM v$session WHERE status = 'ACTIVE' AND username IS NOT NULL;
What it does:
Lists all active user sessions in the current database.
Shows session ID, serial number, username, status, and program.
Helps identify current database activity.
SELECT value AS listener_address FROM v$parameter WHERE name = 'local_listener';
What it does:
Gets the listener address for the current database.
Shows the configured listener (e.g., host:port).
Helps verify network configuration.
SELECT name, network_name FROM v$services;
What it does:
Lists all services defined for the current database.
Shows service names and network names.
Helps understand how clients connect (e.g., via TNS).
SELECT log_mode FROM v$database;
What it does:
Checks if the database is in ARCHIVELOG or NOARCHIVELOG mode.
Shows the log mode (e.g., ARCHIVELOG).
Helps understand backup and recovery settings.
SELECT operation, status, start_time, end_time FROM v$rman_backup_job_details ORDER BY start_time DESC FETCH FIRST 1 ROWS ONLY;
What it does:
Shows details of the most recent RMAN backup job.
Includes operation, status, start time, and end time.
Returns no rows if no backups exist.
Requires RMAN usage in the database.
SELECT value AS characterset FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
What it does:
Gets the database’s character set.
Shows the value (e.g., AL32UTF8).
Helps understand encoding for data storage.
SELECT dbtimezone FROM v$database;
What it does:
Gets the database’s time zone.
Shows the time zone (e.g., +00:00).
Helps with timestamp and scheduling considerations.