Chapter 7 Managing Database Storage Structures

Understand Tablespaces and Datafiles
The Oracle Data Storage Model
Logical View: Tablespace -> Segment -> Extent -> Oracle block
Physical View: Datafile -> Operating system block

One tablespace can contain many segments and be made up of many datafiles.
This means that any one segment may be spread across multiple datafiles, and any one datafile may contain all of or parts of many segements.

Segment (database object that stores data):
table segment
index segment
undo segment

one table - one segment (normal)
one table - many segments (partition table)
many table - one segment (cluster)

Automatic Storage Management (ASM)
datafiles: local file systems, clustered file systems, ASM disk groups, and raw devices.

ASM stripes files, not volumes. Mirroring is optinal, striping is not.

ASM disk (phyiscal disk)
ASM disk group (logical disk)

Create and Manage Tablespaces
CREATE SMALLFILE TABLESPACE "TBSNAME"
DATAFILE 'D:\app\Administrator\oradata\ORCL\tbsname01.dbf'
SIZE 100M AUTOEXTEND on NEXT 10M MAXSIZE 200M
(Below are optional default)
LOGGING
EXTENT MANAGEMENT LOCAL (Bitmaps for allocating extent, default extent allocation automatic)
SEGMENT SPACE MANAGEMENT AUTO (Bitmaps for allocating appropriate block for row insertion)
DEFAULT NOCOMPRESS;

Altering Tablespace
Renaming
ALTER TABLESPACE tbsoldname RENAME TO tbsnewname;

Resizing
ALTER DATABASE DATAFILE filename RESIZE n[MGT];

Taking Online/Offline
ALTER TABLESPACE tbsname OFFLINE (NORMAL IMMEDIATE TEMPORARY];

Flagging as Read-Write or Read Only
ALTER TABLESPACE tbsname [READ WRITE READ ONLY];

Change alert thresholds
using DBMS_SERVER_ALERT.SET_THRESHOLD stored procedure.

Dropping Tablespaces
DROP TABLESPACE tbsname [INCLUDING CONTENTS [AND DATAFILES]];

Oracle-Managed Files (OMF): system generated datafile name
DB_CREATE_FILE_DEST will be overridden by datafile name specified on the CREATE TABLESPACE command.

Manage Space in Tablespaces

First: Space is assigned to a tablespace by sizing datafiles.
Second: Space within a tablespace is assigned to segments by allocating extents.
Third: Space within a segment is assigned to rows by searching an appropriate block through bitmaps to insert a row.

Extent Management(Local (Bitmaps) or Dictionary (SYS.UET$,SYS.FET$)):About Extent Usage
default Local, Extent allocation: default automatic, uniform size

Dictionary -> Local possible

Segment Space Managment(Automatic (Bitmaps) or Manual (Freelist)):About Block Usage
default AUTO (Bitmaps)

Manual -> Automatic not possible