Oracle Basic SQL*Plus Commands Every DBA Must Know

Introduction

Oracle SQL*Plus is the most basic and powerful command-line tool used by DBAs to manage and monitor Oracle databases.
Whether you are a beginner, application support engineer, or preparing for Oracle DBA interviews, these commands are part of daily operations.

In this blog, we will cover important Oracle SQL*Plus commands used to check:

• Database status
• Instance status
• Datafiles and tablespaces
• Redo log files
• TEMP and UNDO files
• Database size and free space

Connecting to SQL*Plus

To perform DBA-level checks, connect using SYSDBA:

sqlplus / as sysdba

Verify the connected user:

SHOW USER;

Check Database Status

This command shows whether the database is open or mounted:

SELECT name, open_mode FROM v$database;


Output Meaning
READ WRITE – Database is open and running
MOUNTED – Database mounted but not open
NOMOUNT – Only instance started

Check Instance Status

Instance status tells us whether memory and background processes are running

SELECT instance_name, status FROM v$instance;

Instance Status Values

• STARTED
• MOUNTED
• OPEN

List Datafiles

Datafiles store actual table and index data.

SELECT file_id, file_name FROM dba_data_files;

Check Size of Datafiles

SELECT file_name,
       ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_data_files;

List Tablespaces

SELECT tablespace_name, status FROM dba_tablespaces;

Common tablespaces:

• SYSTEM
• SYSAUX
• USERS
• TEMP

Tablespace Size

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) AS size_mb
FROM dba_data_files
GROUP BY tablespace_name;

Redo Log Group Status

Redo logs are critical for crash recovery.

SELECT group#, status FROM v$log;

Redo Log Status

• CURRENT
• ACTIVE
• INACTIVE

Redo Log File Details

Check the location of Redo files

SELECT group#, member FROM v$logfile;

Redo Log File Size

SELECT group#,
       ROUND(bytes/1024/1024,2) AS size_mb
FROM v$log;

Check TEMP Files

TEMP tablespace is used for sorting and temporary operations.

SELECT file_name,
       ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_temp_files;

Identify UNDO Tablespace

SELECT tablespace_name
FROM dba_tablespaces
WHERE contents='UNDO';

Check UNDO Datafiles

SELECT file_name,
       ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_data_files
WHERE tablespace_name IN
(SELECT tablespace_name
 FROM dba_tablespaces
 WHERE contents='UNDO');

Check Total Database Size

SELECT ROUND(SUM(bytes)/1024/1024/1024,2) AS size_gb
FROM dba_data_files;

Check Free Space in Tablespaces

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name;

Used Space per Tablespace

SELECT df.tablespace_name,
       ROUND(SUM(df.bytes)/1024/1024,2) -
       ROUND(NVL(SUM(fs.bytes),0)/1024/1024,2) AS used_mb
FROM dba_data_files df
LEFT JOIN dba_free_space fs
ON df.tablespace_name = fs.tablespace_name
GROUP BY df.tablespace_name;

Conclusion

These Oracle SQL*Plus commands are mandatory knowledge for:

• Oracle DBAs
• Application support engineers
• Interview preparation
• Daily database monitoring

Mastering these basics will help you confidently manage Oracle databases and troubleshoot issues faster.