Table spaces | Oracle DBA Tutorial pdf

Table spaces

The database’s data is stored logically in table spaces and physically in data files corresponding to the table spaces. One table space can have multiple data file but one data file must belong to only one table space. A single object (say a table) may span multiple data files but must reside within a single table space.

Creating table space

CREATE TABLESPACE supermarket DATAFILE
'e:\oracle\oradata\mdb\supermarket.dbf' SIZE 30M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE
MANAGEMENT AUTO
CREATE TABLESPACE supermarket DATAFILE
'e:\oracle\oradata\mdb\supermarket.dbf' SIZE 30M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

Dropping table space

DROP TABLESPACE supermarket INCLUDING CONTENTS AND DATAFILES

Renaming table space

From Oracle 10g onwards, you can rename a table space (except System and Sysaux)
ALTER TABLESPACE old_name RENAME TO new_name
When you rename a table space, all corresponding data dictionary entries are updated.

Availability of table space

ALTER TABLESPACE supermarket OFFLINE NORMAL/TEMORARY/IMMEDIATE/FOR RECOVER
You can’t place System table space in offline mode.
To make a table space read only,
ALTER TABLESPACE supermarket READ ONLY
To change it to read write mode,
ALTER TABLESPACE supermarket READ WRITE

Adding space to table space

ALTER TABLESPACE supermarket_DATA ADD DATAFILE
'c:\oracle\oradata\mydb\supermarket_data2.dbf' SIZE 30M
ALTER DATABASE DATAFILE
'c:\oracle\oradata\mydb\supermarket_data2.dbf' RESIZE 300M

Table space related data dictionary views

DBA_TABLESPACES – all table space information
V$_TABLESPACE
DBA_FREE_SPACE
V$SORT_USAGE
DBA_SEGMENTS
DBA_USERS – shows default and temporary table space for users

Renaming and relocating file

Follow these steps to rename data file (for single table space except System
table space)
=> ALTER TABLESPACE supermarket OFFLINE
=> Copy or move the file to new location with OS commands
=> ALTER DATABASE RENAME FILE
'c:\oracle\oradata\mydb\supermarket_data2.dbf' TO
'c:\oracle\oradata\mydb\supermarket_new.dbf'
or
ALTER TABLESPACE supermarket RENAME DATAFILE
'c:\oracle\oradata\mydb\supermarket_data2.dbf' TO
'c:\oracle\oradata\mydb\supermarket_new.dbf'
=> ALTER TABLESPACE supermarket ONLINE
In case of System table space or table spaces with multiple data files
=> Shutdown database
=> Copy or move the file to new location with OS commands
=> Startup database in mount state
=> ALTER DATABASE RENAME FILE
'c:\oracle\oradata\mydb\supermarket_data2.dbf' TO
'c:\oracle\oradata\mydb\supermarket_new.dbf'
=> Open database
Please note in case of Windows, the file may get locked unless database is shutdown.

Data file related data dictionary views

V$DATAFILE
V$TEMPFILE
DBA_DATA_FILES
DBA_TEMP_FILES