Oracle DBA study plan

Diagram the SGA, PGA, and background processes from memory, then label each component's role in a SQL statement's lifecycle

Trace a single SELECT from parse through fetch and write down which structure (buffer cache, shared pool, redo log buffer, DBWn, LGWR, CKPT) is touched at each step

Query V$SGA, V$PGASTAT, V$PROCESS, and V$BGPROCESS to confirm your diagram against a live instance

Map the logical hierarchy (database, tablespace, segment, extent, block) to physical files (datafiles, redo logs, control files, temp files)

Explain checkpoint, SCN, and instance recovery behavior well enough to describe what happens when the instance crashes mid-transaction

Create a database from scratch with the CREATE DATABASE statement, then repeat the exercise using DBCA in silent mode

Configure initialization parameters through an SPFILE and demonstrate which changes take effect immediately versus after restart

Start and stop the instance in NOMOUNT, MOUNT, and OPEN modes and state which files each stage opens

Query V$INSTANCE, V$DATABASE, V$PARAMETER, and V$SPFILE to verify instance and database state after each operation

Diagnose a deliberately broken parameter or missing file using the alert log and ORA- error text

Create dictionary-managed and locally managed tablespaces with automatic and uniform extent allocation and compare the resulting behavior

Add a datafile with AUTOEXTEND, cap its maximum size, and demonstrate what happens when the limit is reached

Place a segment in a specific tablespace, then relocate it and verify the move with DBA_SEGMENTS and DBA_DATA_FILES

Reclaim space by shrinking a datafile or dropping an unused tablespace, and confirm the result in DBA_FREE_SPACE

Generate a space-usage report joining DBA_TABLESPACE_USAGE_METRICS with DBA_DATA_FILES and interpret which tablespace is the growth risk

Write listener.ora and tnsnames.ora by hand to expose one database under two service aliases

Start, stop, reload, and check status of the listener with lsnrctl, and confirm registration with the dynamic service views

Connect through the aliases using Easy Connect, local naming, and a directory naming approach, and record the differences

Reproduce and then fix ORA-12541 and ORA-12514 by inspecting listener status, service registration, and the connect descriptor

Restart the listener after a database bounce and verify services re-register without manual intervention

Create users with default and temporary tablespaces and enforce quota limits that produce a controlled ORA-01536

Build a profile that limits sessions per user, idle time, and failed logins, then prove each limit fires

Grant privileges through roles rather than direct object grants and rewrite an over-privileged account into a least-privilege design

Audit existing privileges with DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to find accounts holding unsafe grants

Configure password verification and demonstrate account lockout followed by an administrative unlock

Reproduce a row-level lock conflict across two sessions, then resolve it both by commit and by kill session

Force a deadlock, locate the ORA-00060 trace in the alert log, and interpret the deadlock graph to identify the victim

Explain and demonstrate the difference between READ COMMITTED and SERIALIZABLE within your own sessions using V$TRANSACTION

Enable a unified auditing policy on a specific table or privilege and query the resulting audit trail

Compare unified audit records against the older audit trail views and clean up audit data with the appropriate DBMS package

Enable ARCHIVELOG mode and verify that redo archiving succeeds after a log switch

Take a full RMAN backup, then restore and recover after deliberately deleting a datafile, and record recovery time

Perform point-in-time recovery to a timestamp before an accidental DELETE and confirm the lost rows return

Configure undo retention and size an undo tablespace so a long-running query does not raise ORA-01555 under a simulated load

Inspect undo usage with V$UNDOSTAT and V$TRANSACTION to distinguish active, unexpired, and expired extents during a running transaction

Move a schema with Data Pump export and import, using both a directory object and a network link, and compare the resulting row counts

Load a flat file with SQL*Loader and query it through an external table, then note where each approach is the better choice

Re-run the week 7 undo sizing calculation against your final database and adjust retention or tablespace size

Retake the two weakest domain quizzes from weeks 1 through 7 and close every gap you can name

Sit a full-length timed practice exam and categorize each miss by domain