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:^[1]

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.
1. Use Enterprise Manager Database Control / Server / Storage to identify current control file copies.
2. Use the following query to identify current control file copies:
  SELECT * FROM V$CONTROLFILE;

Tablespaces and Datafiles

Identify existing tablespaces and datafiles.
1. Use Enterprise Manager Database Control / Server / Storage to identify current tablespaces and usage.
2. Use the following query to identify current tablespaces and usage:
  SELECT * FROM V$TABLESPACE;
3. Use the following query to identify current datafiles:
  SELECT NAME FROM V$DATAFILE;
Create new tablespaces.
1. 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.
2. Use the following query to identify current datafiles:
  SELECT NAME FROM V$DATAFILE;
3. 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;
4. Use the following queries to verify tablespace and datafile creation:
  SELECT * FROM V$TABLESPACE;
  SELECT NAME FROM V$DATAFILE;
Manage tablespaces.
1. Use Enterprise Manager to take the TEST tablespace offline using Normal mode.
2. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
3. Use Enterprise Manager to place the TEST tablespace online.
4. Use Enterprise Manager to make the TEST tablespace readonly.
5. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
6. Use Enterprise Manager to make the TEST tablespace writable.
7. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
8. Use the following query to take the TEST2 tablespace offline:
  ALTER TABLESPACE TEST2 OFFLINE NORMAL;
9. Use the following query to display tablespace status:
  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
10. Use the following query to place the TEST2 tablespace online:
  ALTER TABLESPACE TEST2 ONLINE;
11. Use the following query to make the TEST2 tablespace readonly:
  ALTER TABLESPACE TEST2 READ ONLY;
12. Use the following query to display tablespace status:
  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
13. Use the following query to make the TEST2 tablespace writable:
  ALTER TABLESPACE TEST2 READ WRITE;
14. Use the following query to display tablespace status:
  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
Delete tablespaces.
1. Use Enterprise Manager to delete the TEST tablespace and associated data files.
2. Use the following query to delete the TEST2 tablespace and associated data files:
  DROP TABLESPACE TEST2 INCLUDING CONTENTS AND DATAFILES;
Use Oracle-managed files.
1. Review Oracle: Using Oracle-Managed Files.
2. Enable Oracle-managed files using the following query:
  ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_HOME%\ORADATA\ORCL';
3. Create a new tablespace using the following query:
  CREATE TABLESPACE TEST3;
4. View datafile information using the following query:
  SELECT NAME FROM V$DATAFILE;
5. 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;