Transcript Tablespaces

IT 21003 Database Administration
Section 03
Tablespaces and the DBA
 Important for DBAs – logical units of database
storage
Made up of physical operating-system files (one or
more)
 Can be made
READ ONLY (data warehouse)
PERMANENT or TEMPORARY (holds sort data)
 Can be put Online or Offline
Tablespaces: The Issues
 Schemes for space allocation for users and
applications
Give users resource quotas on tablespaces
Provide separate default storage parameters for each
tablespace
 Design tablespaces for different styles of activity
 Group tables belonging to one application
 Put tables of high activity on certain physical devices
Tablespaces: The Issues
 Positioning index and table data – spread the I/O
Spread large active tables across disks (table striping)
 Tablespaces for rollback segments
Have separately designed tablespaces for rollback
segments
 Tablespaces for TEMPORARY segments
 Used to temporarily store sort runs for many types of
queries
Points to Consider
 Tablespaces are transferable across databases
with version 8i and up
 May go offline when certain errors occur
 Access to data is restricted if an index is in an
offline tablespace
 Recovery can be performed tablespace by
tablespace
The SYSTEM tablespace – at least 60mb
 Every database must have a tablespace called
SYSTEM
Automatically built by CREATE DATABASE with default
storage characteristics as specified in the file sql.bsq
 The SYSTEM tablespace contains
 The Data Dictionary
 SYSTEM rollback segments
 Bootstrap segment
The SYSTEM tablespace – cont’d
 May be enlarged – with careful forethought
Do not store any user data in SYSTEM
Reserved only for Oracle
 Must always be online for normal operations
 Can be set to be locally managed with the
 Extent Management Local statement in the Create
Database clause
Creating Additional Tablespaces
 The creator must have DBA role or be granted the
CREATE TABLESPACE system privilege
 Example CREATE TABLESPACE statement
Create Tablespace user_data_01
Datafile ‘e:\oraclass\database\user_data_01.dbf’ size
200M
Autoextend on next maxsize unlimited
Default storage (Initial 1M
Next 2M
Pctincrease 0
Maxextents 100)
Permanent;
The SYSTEM tablespace – Example cont’d
 Uses a 200 MB file as storage for the tablespace
This will automatically expand as it becomes full
 Objects without their own storage definition will be
allocated an initial 1MB of space, and all other
extents will be 2MB
 Objects can expand to a maximum of 100 extents
Creating the first additional tablespace(s)
 Non-SYSTEM tablespace data cannot make use
of the system rollback segment, so…
 Create the tablespace(s)
 Create rollback segments as required (at least one) in
the new tablespace(s)
 Bring the rollback segments online so that transactions
on non-SYSTEM tablespace data can use a rollback
segment
 This is not online by default
 Edit the parameter file to include names of the new
rollback segments in order to make them active when
opening the database
Altering Tablespaces
 Why alter tablespaces?
 Add a file to satisfy additional space requirements
 Rename files because they need to be moved to other devices
 Make the tablespace available or unavailable to users
 Perform an online backup of the tablespace
 Change the way in which files behave when, and if, they expand
 Change the maximum size of any auto-expandable files
 Change to read-only or read/write
 Force coalescence of free space
 Change the default storage parameters
 Change a tablespace from temporary to permanent
 Change the extent management
 Particularly important for temporary tablespaces
Altering Tablespaces: Adding Datafiles
 Enlarging tablespaces (adding datafiles)
Alter Tablespace tablespacename
Add Datafile ‘filename.dbf’ [Size interger [k|m] ]
 Tablespace may be online or offline, but the
database must be open
The extra file is automatically enabled and available
Default file size is 10MB
 If SIZE is specified the file must not exist and is
created with the specified size
Altering Tablespaces: Relocating Files
 Renaming files in a tablespace
Alter Tablespace tablespacename
Rename Datafile ‘/usr1/data2.dbf’ To ‘/usr2/data3.dbf’;
 Renaming (relocating) a datafile actually requires
a number of operations
 Take the tablespace offline
 Use the OS to copy the old file to the new location
 Use the RENAME DATAFILE statement
 Make the tablespace ONLINE
 Delete the old file using the OS
 Backup the database and control files
 This method requires the database to be OPEN
Altering Tablespaces: Relocating Files cont’d
 Files can also be renamed when the database is
in a MOUNT state and OPEN
Alter Database Rename File ’/usr1/system_data2.dbs’
To ‘/usr2/system_data3.dbf’;
 This statement does not physically move the files
01-27-2005
Altering Tablespaces: Changing the Default Storage
Alter Tablespace usr1
Default Storage ( Initial 1M
Next 2M
Minextents 1
Maxextents 100
Pctincrease 50);
The new values have no retro-effect
 They do not change any existing storage allocation
Altering Tablespaces: Online and Offline
 Bring tablespaces online
Alter Tablespace tablespacename Online;
 Tablespace becomes available for use
 Will be successful if there are no errors in the
datafiles in the tablespace
Altering Tablespaces: Online and Offline
 Bring tablespaces offline
Alter Tablespace tablespacename Offline
[ Normal | Immediate | Temporary | For Recover ];
 Taking a tablespace offline enables the following
actions:
 Make the data temporarily unavailable to users
 Change disks
 Handle disk failures
Altering Tablespaces: Offline Options
 NORMAL
No media recovery necessary
The datafiles in the tablespace are checkpointed
NOARCHIVELOG mode
 IMMEDIATE
 Not available in NOARCHIVELOG mode because the
tablespace will require media recovery before
subsequent use
 No checkpoint is performed and files do not need to
exist
 ARCIVELOG mode only
Altering Tablespaces: Offline Options cont’d
 TEMPORARY
Can be done if one of the datafiles is already offline
All remaining datafiles within the tablespace are
checkpointed
Media recovery will be required for any of the files that
are offline
ARCIVELOG mode only
 FOR RECOVER
 Takes tablespaces offline for point in time tablespace
recovery
 ARCIVELOG mode only
Dropping Tablespaces
Drop Tablespace tablespacename;
 If data is present, the INCLUDING CONTENTS
clause must be used
Drop Tablespace tablespacename Including
Contents;
Dropping Tablespaces cont’d
 If foreign key constraints are acting on the data
from tables in other tablespaces, use the
CASCADE CONTENTS clause
Drop Tablespace tablespacename
Including Contents Cascade Contents;
Dropping Tablespaces cont’d
 The DROP TABLESPACE command is a DDL
statement; hence, no rollback is possible!
 A Permanent Action
 Recommended to take the tablespace offline before
dropping it, and backup the database before and after
the drop
 Cannot be dropped if active rollback segments are
present
Read-Only Tablespaces
Alter Tablespace tablespacename Read Only;
 Useful for
 Static Data
 Data Warehouse
 Preventing Updates
 Simplifying the backup strategy
Read-Only Tablespaces
 Prerequisites
 Tablespace must be online
 No active transactions in the tablespace
 No active rollback segments in the tablespace
 No online backup currently running
Making a Tablespace Read-Only
 Make sure the prerequisites are met
 Set the tablespace to be read-only
 Backup the tablespace
 Amend the backup strategy and scripts
 If could also be useful to scan all blocks in the
tablespace to force block clean-out before making
the tablespace read-only
 To return a tablespace to read/write status
Alter Tablespace tablespacename Read Write;
Temporary Tablespaces
 Scratch areas used by Oracle
 Tablespaces specially designed to hold data from intermediate sort
runs
 Large sorts may require too much space to be managed within
memory
 The creation of an index could require a temporary segment
twice as large as the storage allocated for the actual index
 The temporary sort data will be placed transiently on disk then
 Beware: The default tablespace for temporary data is SYSTEM
 The SYSTEM tablespace could become fragmented due to extents
being continually allocated and deallocated to the sort runs
 Each user should have a specified temporary tablespace for their
sort data
Temporary Tablespaces cont’d
 The temporary tablespace should have a default
storage pattern so that each extent is exactly the
same size (uniform extents)
 Makes it easier for Oracle to find free space extents of
the required size
Locally Managed Temporary Tablespaces
 Preferred method
Create Temporary Tablespace tablespacename Tempfile
‘tablespacename_fl.tmp’ Size 50M
Extent Management Local Uniform Size 8M;
 Extent management must be local when using this syntax
 This tablespace cannot be renamed or made read-only
 Media recovery does not recognize tempfiles because
 There is no need to back them up
 They are easy to re-create anytime needed
Coalescing Free Space in Tablespaces
 Free-space fragmentation needs to be avoided
 Caused by allocation and deallocation of storage to
objects leaving small pieces of free space
 Each piece may not be large enough for any future allocation
of contiguous storage
 Free space coalescion can be forced by
Alter Tablespace tablespacename Coalesce;
Retrieving Information about Fragmentation
Select tablespace_name As tablespace,
file_id As File_No, Count(*) As Pieces,
Max(blocks) As Max, Min(blocks) As Min,
Avg(block) As Ave, Sum(blocks) As Tot
From dba_free_space
Where tablespace_name = ‘&tablespace’
Group By tablespace_name, file_id;
 The higher the pieces amount the greater the
fragmentation
Oracle Files
 Datafiles - operating-system files that are used to
 Store Oracle Data
 Position the physical separation of objects
 Indexes from data
 Enable the “Table Striping” technique
 Support Table Partitioning
 Redo Log Files store (all) changes to database
data and contain
 Before Images
 After Images
 Commit and Rollback Status
 Checkpoint Information
Oracle Files cont’d
 Control files store startup and backup/recovery
information
 Parameter files store initialization parameters
 Password files
 Trace, log, and networking files
 Datafiles
 Datafiles per Tablespace
 Datafiles per Database
 Datafile Size
 DB-BLOCK_SIZE
 Tablespaces per Database
1023
65533
4M Blocks
32KB
64000
Resizable Datafiles
 To manually increase or decrease the size of a
datafile:
Alter Database Datafile filename Resize 100M;
 To initiate automatic extension of a datafile:
Alter Database Datafile filename Autoextend On Next nM Maxsize 80M;
(Where n = the default number of blocks in one Oracle block)
Resizable Datafiles cont’d
 To curtail automatic extension:
Alter Database Datafile filename Autoextend Off;
To allow unlimited expansion:
Alter Database Datafile filename Autoextend On Maxsize Unlimited;
General Database Storage Patterns
 Oracle supports the use of:
 Devices Partitions
 RAID systems
 Striping Techniques
 Operating-System Driven
 Database Driven
 Device Partitions can give a 5-20% performance
improvement
 RAID configurations supported:
RAID 1
RAID 5
RAID 0+1
 Striping techniques are best done by the OS
Control Files
 Created at database creation time and checked
each time Oracle is started and the database is
opened
 Relatively small binary files
 Size depends partly on the number of redo log files,
database files, and information associated with backup
and recovery
 Contain information about:
Database Files
Redo Log Files (Name and Position)
Timestamps of Creations
Checkpoint Information
Backup and Recovery Information
 Can become quite large in size
Control Files cont’d
 Place a control file on each disk to guard against
media failure
 Copies are automatically written in synchronous fashion
by Oracle with negligible performance overhead
Redo Log Files
 Contain:
Before Images
After Images
Commit Information
Rollback Information
Checkpoint Information
 Safeguard changes to the database
 Log files can be archived to enable full recovery
 Incurs storage and management overhead
 Can provide for total recovery
Redo Log Files cont’d
 Should be entirely dualed (mirrored) on separate
disks
Log files can be volatile
 Used to store redo log entries in a sequential
manner
Minimum of two log files required
Used in a circular fashion
 Contain roll forward data and backup of rollback
segment data
 Minimum size is 50KB (Never make them this small)
 Default size is 500KB
 Typically 10 – 100 MB
Questions?