Scribd
Upload
15 views7 pages

Tablespaces

The document provides an overview of tablespaces in databases, explaining their role as logical structures that group and manage different types of data, including system, user, undo, and temporary data. It details the relationship between tablespaces and datafiles, including how to create, resize, and remove tablespaces using both command-line and graphical tools. Additionally, it highlights the importance of the SYSTEM tablespace and the function of temporary tablespaces in handling transient data during operations.

Uploaded by

Steven
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views7 pages

Tablespaces

The document provides an overview of tablespaces in databases, explaining their role as logical structures that group and manage different types of data, including system, user, undo, and temporary data. It details the relationship between tablespaces and datafiles, including how to create, resize, and remove tablespaces using both command-line and graphical tools. Additionally, it highlights the importance of the SYSTEM tablespace and the function of temporary tablespaces in handling transient data during operations.

Uploaded by

Steven
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Tablespaces

Miroslav Biňas
(c) 2016

Introduction

● database is centralized repository of


related data

[Link]

Data in Database

System Data User Data Undo Data Temp Data

Database

[Link]
Data in Database

● different data types in db


○ system data - data dictionary tables
maintained by db
○ feature data - based on db features
○ user data - consists of db objects
created by db users
○ undo data - consists of previous
values generates by transactions
○ temp data - used for temp storage
of data for special operations such
as sorting
[Link]

Tablespaces in Database

System Data User Data Undo Data Temp Data


(SYSTEM) (USERS) (UNDOTBS1) (TEMP)

Database

[Link]

Tablespaces in Database

● database consists of one or more


logical structures called tablespaces
● every tablespace has name
● logically separate/group the data
● helps with data management and
admin tasks

[Link]
Datafiles and Tablespaces I.

Datafile 1 Datafile 2 Datafile N

Tablespace

[Link]

Datafiles and Tablespaces I.

● each tablespace consists of one or


more files called datafiles
○ usually with .dbf extension
○ filename does not need to match to
the tablespace name in any way,
but it's easier
● datafiles can grow automatically
○ the max size can be limited
○ more datafiles can be added later

[Link]

Datafiles and Tablespaces I.

● database stores data logically into


tablespaces and physically into
datafiles

[Link]
Datafiles and Tablespaces II.

Table 1

Datafile 1 Datafile 2 Datafile 3

Table 2

Tablespace

[Link]

Datafiles and Tablespaces II.

● datafile is organized into data blocks


○ each block contains data from just
one table

[Link]

Introduction to
Tablespace Management
● can be done in two ways
○ with CLI tools (e.g. sqlplus, SQL
Developer)
○ with GUI tools (e.g. SQL Developer)
● admin tasks
○ create/remove tablespace
○ make tablespace online/offline
○ resize tablespace datafile
○ add/rename/move datafiles

[Link]
Obtaining Tablespace Info

● tablespace info views


○ v$tablespace
○ dba_tablespaces
● data file information views
○ v$datafile
○ dba_data_files
● temp file information views
○ v$tempfile
○ dba_temp_files

[Link]

Create New Tablespace

● syntax:
CREATE [TYPE] TABLESPACE name
DATAFILE '/path/'
SIZE size;
● by default, user tablespace is created
● other types: UNDO, TEMPORARY
● example
CREATE TABLESPACE tbs
DATAFILE '/path/[Link]'
SIZE 10M;

[Link]

Resize Tablespace

ALTER DATABASE
DATAFILE '/path/[Link]'
RESIZE 15M;

[Link]
Remove Tablespace

● drop tablespace only (logicaly)


DROP TABLESPACE tbs;
● drop tablespace with it's datafile
(physicaly)
DROP TABLESPACE tbs
INCLUDING CONTENTS
AND DATAFILES;

[Link]

Questions?

[Link]

Tablespace SYSTEM

● auto created in every Oracle db


● always online when db is open
● contains data dictionary tables for
entire database
○ metadata

[Link]
Temporary Tablespace

● contains temp data for operations such


as JOIN, UNION, ORDER BY, …
○ data are created and removed
automatically based on operation

[Link]

You might also like