Oracle Database Administration/Storage

This lesson introduces Oracle database storage structures.

Objectives and Skills
Objectives and skills for the storage structures portion of Oracle Database Administration I certification include:
 * Managing Database Storage Structures
 * Overview of tablespace and datafiles
 * Create and manage tablespaces
 * Space management in tablespaces

Readings

 * 1) Oracle: Database 2-Day DBA Managing Database Storage Structures

Multimedia

 * 1) YouTube: Datafiles & Tablespaces pt 2

Tutorial

 * 1) Complete the tutorial Oracle: Managing Database Storage Structures.

Control Files

 * 1) Identify control file copies.
 * 2) Use Enterprise Manager Database Control / Server / Storage to identify current control file copies.
 * 3) Use the following query to identify current control file copies:

Tablespaces and Datafiles

 * 1) Identify existing tablespaces and datafiles.
 * 2) Use Enterprise Manager Database Control / Server / Storage to identify current tablespaces and usage.
 * 3) Use the following query to identify current tablespaces and usage:
 * 4) Use the following query to identify current datafiles:
 * 5) Create new tablespaces.
 * 6) Use Enterprise Manager to create a new tablespace named  . Add a corresponding datafile with 1 MB file size and autoextend storage in 1 MB increments. Use Show SQL to display the generated SQL before creating the tablespace and datafile.
 * 7) Use the following query to identify current datafiles:
 * 8) Use the following query to create another tablespace and datafile:
 * 9) Use the following queries to verify tablespace and datafile creation:
 * 10) Manage tablespaces.
 * 11) Use Enterprise Manager to take the TEST tablespace offline using Normal mode.
 * 12) Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
 * 13) Use Enterprise Manager to place the TEST tablespace online.
 * 14) Use Enterprise Manager to make the TEST tablespace readonly.
 * 15) Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
 * 16) Use Enterprise Manager to make the TEST tablespace writable.
 * 17) Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
 * 18) Use the following query to take the TEST2 tablespace offline:
 * 19) Use the following query to display tablespace status:
 * 20) Use the following query to place the TEST2 tablespace online:
 * 21) Use the following query to make the TEST2 tablespace readonly:
 * 22) Use the following query to display tablespace status:
 * 23) Use the following query to make the TEST2 tablespace writable:
 * 24) Use the following query to display tablespace status:
 * 25) Delete tablespaces.
 * 26) Use Enterprise Manager to delete the TEST tablespace and associated data files.
 * 27) Use the following query to delete the TEST2 tablespace and associated data files:
 * 28) Use Oracle-managed files.
 * 29) Review Oracle: Using Oracle-Managed Files.
 * 30) Enable Oracle-managed files using the following query:
 * 31) Create a new tablespace using the following query:
 * 32) View datafile information using the following query:
 * 33) Remove the new tablespace using the following query:

Segments, Extents, and Data Blocks

 * 1) Review Oracle: Data Blocks, Extents, and Segments.
 * 2) Review Oracle: Displaying Information About Space Usage for Schema Objects.
 * 3) Describe the DBA_SEGMENTS view with the following query:
 * 4) Describe the DBA_EXTENTS view with the following query:
 * 5) Select tablespace, segment, extent, block and storage allocation for the HR schema using the following query:
 * 6) Select tablespace, segment, extent ID, block and storage allocation for the HR schema using the following query: