# 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

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

## Multimedia

* YouTube: Datafiles & Tablespaces pt 2

## Activities
### Tutorial

* Complete the tutorial Oracle: Managing Database Storage Structures.

### Control Files

* Identify control file copies.
** Use Enterprise Manager Database Control / Server / Storage to identify current control file copies.
** Use the following query to identify current control file copies: SELECT * FROM V$CONTROLFILE;

### Tablespaces and Datafiles

* Identify existing tablespaces and datafiles.
** Use Enterprise Manager Database Control / Server / Storage to identify current tablespaces and usage.
** Use the following query to identify current tablespaces and usage: SELECT * FROM V$TABLESPACE;
** Use the following query to identify current datafiles: SELECT NAME FROM V$DATAFILE;
* Create new tablespaces.
** Use Enterprise Manager to create a new tablespace named TEST. 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.
** Use the following query to identify current datafiles: SELECT NAME FROM V$DATAFILE;
** Use the following query to create another tablespace and datafile: CREATE TABLESPACE TEST2 DATAFILE '<data file path>\TEST2.DBF' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
** Use the following queries to verify tablespace and datafile creation: SELECT * FROM V$TABLESPACE; SELECT NAME FROM V$DATAFILE;
* Manage tablespaces.
** Use Enterprise Manager to take the TEST tablespace offline using Normal mode.
** Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
** Use Enterprise Manager to place the TEST tablespace online.
** Use Enterprise Manager to make the TEST tablespace readonly.
** Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
** Use Enterprise Manager to make the TEST tablespace writable.
** Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
** Use the following query to take the TEST2 tablespace offline: ALTER TABLESPACE TEST2 OFFLINE NORMAL;
** Use the following query to display tablespace status: SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
** Use the following query to place the TEST2 tablespace online: ALTER TABLESPACE TEST2 ONLINE;
** Use the following query to make the TEST2 tablespace readonly: ALTER TABLESPACE TEST2 READ ONLY;
** Use the following query to display tablespace status: SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
** Use the following query to make the TEST2 tablespace writable: ALTER TABLESPACE TEST2 READ WRITE;
** Use the following query to display tablespace status: SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
* Delete tablespaces.
** Use Enterprise Manager to delete the TEST tablespace and associated data files.
** Use the following query to delete the TEST2 tablespace and associated data files: DROP TABLESPACE TEST2 INCLUDING CONTENTS AND DATAFILES;
* Use Oracle-managed files.
** Review Oracle: Using Oracle-Managed Files.
** Enable Oracle-managed files using the following query: ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_HOME%\ORADATA\ORCL';
** Create a new tablespace using the following query: CREATE TABLESPACE TEST3;
** View datafile information using the following query: SELECT NAME FROM V$DATAFILE;
** Remove the new tablespace using the following query: DROP TABLESPACE TEST3 INCLUDING CONTENTS AND DATAFILES;

### Segments, Extents, and Data Blocks

* Review Oracle: Data Blocks, Extents, and Segments.
* Review Oracle: Displaying Information About Space Usage for Schema Objects.
* Describe the DBA_SEGMENTS view with the following query: DESCRIBE DBA_SEGMENTS;
* Describe the DBA_EXTENTS view with the following query: DESCRIBE DBA_EXTENTS;
* Select tablespace, segment, extent, block and storage allocation for the HR schema using the following query: SELECT TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, EXTENTS, BLOCKS, BYTES FROM DBA_SEGMENTS WHERE OWNER = 'HR' ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME;
* Select tablespace, segment, extent ID, block and storage allocation for the HR schema using the following query: SELECT TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, EXTENT_ID, BLOCKS, BYTES FROM DBA_EXTENTS WHERE OWNER = 'HR' ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME, EXTENT_ID;

## See Also

* Oracle: Managing Tablespaces
* Oracle: Managing Data Files and Temp Files