Exploring Oracle Database as a DBA

Overview

In this workshop you will:
- Connect to Oracle Database as a DBA.
- Explore key data dictionary views.
- Practice SQL*Plus formatting commands to improve output readability.

Commands are intended to be executed in SQL*Plus (or a compatible client) connected to an Oracle Database instance, with terminal commands shown when needed (for example to start sqlplus).

Notation
- Terminal: commands in bash blocks (prompt example: [oracle@dbserver ~]$).
- SQL*Plus: commands in sql blocks (run after connecting with sqlplus).

1. Connecting as DBA

Start SQL*Plus and connect with DBA privileges. On a lab machine, you often use OS authentication:

Terminal ([oracle@dbserver ~]$):

sqlplus / AS SYSDBA

Check who you are and which instance you are connected to:

SQL*Plus:

SHOW USER

SELECT instance_name, status
FROM   v$instance;

This confirms that you are connected as a privileged user and that the instance is open.

2. Exploring the Data Dictionary

The Oracle Data Dictionary is a set of views that describe database objects, users, privileges and configuration.

Useful starter queries:

Tables owned by the current user

SELECT table_name
FROM   user_tables;

All users in the database (requires DBA privilege)

SELECT username,
       account_status
FROM   dba_users;

Database name, creation date and log mode

SELECT name,
       created,
       log_mode
FROM   v$database;

Oracle version information

SELECT banner
FROM   v$version;

These queries help you see how Oracle exposes metadata through views like USER_TABLES, DBA_USERS, V$DATABASE and V$VERSION.

3. Formatting Output in SQL*Plus

SQL*Plus offers commands that do not change the data, but change how query results are displayed.

3.1 COLUMN FORMAT

Use COLUMN to control the width and alignment of columns in the output:

COLUMN username       FORMAT A20
COLUMN account_status FORMAT A15

This is especially useful before queries like:

SELECT username, account_status
FROM   dba_users;

You adjust column widths so long values are not truncated or wrapped awkwardly.

3.2 SET Commands

Use SET to control global display settings in SQL*Plus:

SET LINESIZE 120
SET PAGESIZE 40
SET NUMFORMAT 999,999.99

Effects:
- LINESIZE controls the maximum line width before wrapping.
- PAGESIZE controls how many rows are printed before a header repeats.
- NUMFORMAT sets a default numeric format (here with thousands separator and two decimals).

You normally set these once at the beginning of a session to get clean, readable output for all queries.

4. Displaying Other Useful Information

As a DBA you frequently query dynamic performance views (the V$ views) to monitor the instance.

Instance and host information

SELECT instance_name,
       host_name,
       version
FROM   v$instance;

This tells you which host the instance is running on and which version of Oracle is installed.

Current sessions

SELECT username,
       status,
       osuser,
       machine
FROM   v$session
WHERE  username IS NOT NULL;

This shows which database users are currently connected, their session status, and which OS user/machine they are coming from.

Initialization parameters

SELECT name,
       value
FROM   v$parameter
WHERE  name LIKE 'db_%';

This filters key database parameters whose names start with db_ (for example, db_name, db_block_size, db_cache_size).

5. Key Takeaways

In this workshop you:
- Connected as a DBA and verified the current instance.
- Queried core data dictionary and dynamic performance views.
- Used COLUMN and SET commands in SQL*Plus to control the layout of query results.

These skills are the foundation for later workshops where you will create schemas, manage users and roles, and perform more advanced administration tasks.