Oracle: SQL*Plus, views & RMAN
Learn the difference between querying data and operating the database that stores it.
GUIDE3 published lessons · Updated 2026-09-14
Before you begin
Complete SQL foundations. Use a disposable Oracle database and a dedicated lab account. SQL*Plus is a client; RMAN is a separate recovery tool. Use the documentation matching your version. Do not paste a password into command arguments or lesson files.
Working toward: Inspect a database, manage access deliberately, and demonstrate consistent backup and recovery.
Read each explanation, work through the example, and attempt the exercise before opening its answer. Published lessons are ready to study; unfinished roadmap topics remain planned.
Validation: Based on upstream documentation. Platform-specific labs require the learner’s matching environment and are not execution-tested here.
1. Know your session
An Oracle schema is associated with a database user. A client connects to a service, and multitenant databases add a container context. Before changing data, identify both user and container. Start SQL*Plus with sqlplus /nolog, then CONNECT your_lab_user@your_lab_service and enter the prompted password.
SELECT USER FROM dual;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name FROM dual;What to expect
The session’s user and container name. These are lab-specific, not fixed expected strings.
Your turn
Why check the container before a perfectly valid SQL statement?
Show answer and reasoning
The same object name can refer to a different context or be unavailable there. Correct syntax does not prove the target is correct.Watch for: Do not use SYS for routine practice or assume a successful login reached the intended pluggable database.
Link to this lesson2. Choose the right dictionary view
USER views describe your own objects; ALL views describe objects accessible to you; DBA views require broader privileges and describe database-wide information. An empty USER_TABLES result can be completely normal for a new account.
SELECT table_name FROM user_tables ORDER BY table_name;
SELECT granted_role FROM user_role_privs ORDER BY granted_role;What to expect
Tables owned by the lab user and roles granted to that user. No rows does not mean the database has no tables.
Your turn
Should a report account receive broad DBA rights just to inventory its own tables?
Show answer and reasoning
No. USER_TABLES provides the narrower view. Grant only the necessary read access for the task.Watch for: Dictionary privileges and multitenant scope differ; check version-specific documentation.
Link to this lesson3. Read recovery configuration
RMAN has its own prompt and grammar. SHOW ALL reports configured recovery settings; LIST BACKUP reports known backup metadata. Neither demonstrates that an application can be recovered. Use a lab recovery account with the required privileges and a configured target connection.
-- At the RMAN prompt, connected to the lab target:
SHOW ALL;
LIST BACKUP SUMMARY;What to expect
Configuration values and any catalogued backups. Default settings are marked; a new lab may have no backups.
Your turn
A backup is listed. What must happen before calling the recovery plan proven?
Show answer and reasoning
Check backup accessibility and validation, then perform an isolated restore/recovery and application checks. Measure elapsed time and recovered data position.Watch for: RMAN commands are not SQL*Plus commands. Do not experiment with DELETE or RESTORE against an operational target.
Link to this lessonPath to advanced
In-progress stages identify the lessons already published. All other listed topics remain planned. Each addition needs teaching, a reproducible lab, failure cases, and a checkpoint before the capstone.
Data and privileges
Schemas, roles, USER/ALL/DBA views, sessions, transactions and least privilege.
PLANNEDOperate
Tablespaces, capacity, performance evidence, Data Pump and patch compatibility.
PLANNEDRecovery capstone
ARCHIVELOG planning, RMAN backups, validation and a timed isolated restore.
PLANNED
References
Original AUWEN lessons, with upstream documentation for further study and version checks.
All learning paths and update notes →