What’s up Internet? I am Manish from RebellionRider.com and I am back once again with another oracle
database tutorial for you. And as I promised you all awhile back, this time we will learn
the concepts of Tablespace – one video at a time. So let’s take a look at the first
video in the series of tablespace in oracle database, titled “The Introduction”.
So what are tablespace? Oracle database stores schema objects such
as tables, indexes, Views etc. etc. logically into the tablespace and physically in datafiles
associated with corresponding tablespace. So we can say Tablespace are logical storage
units made up of one or more datafiles. I know this whole deal with datafiles and
tablespaces is slightly confusing but don’t worry. Let me try to explain it to you.
Datafiles are physical files stored on your disk created by oracle database and has .dbf
extension. These files are not only capable for storing tables but also indexes, synonyms,
views and other schema objects created by you. These are physical files because they
have existence on your OS’s file system and you can see them. These files are written
by database writer (DBWR) processes and used by oracle database for proper functioning
of your database system. Do not try to modify these files manually. A datafile cannot be
shared between multiple tablespaces which means that every datafile belongs to a specific
tablespace. Then there comes the Tablespace. Tablespaces
are logical entity in your database and logically organized data which is physically stored
in datafiles. They are called logical storage units because they are not visible in the
OS’s file system. A tablespace belongs to only one database, and has at least one datafile
that is used to store data for the associated tablespace. You can also define tablespaces
as logical storage units made up of one or more datafiles. One tablespace can have up
to 1022 datafiles this number also depends upon your OS.
Hope, things are clear now. Next are the types of Tablespaces in Oracle Database
We can differentiate tablespace on the basis of two factors
1. Type of Data 2. Size of Data
Type of data consists 3 kinds of tablespace including
1. Permanent Tablespace 2. Temporary Tablespace
3. Undo Tablespace And on the basis of Size of Data we have 2
kinds of tablespace 1. Big file tablespace
2. Small file tablespace Permanent Tablespace: Is the tablespace which
contains persistent schema object which means the data stored in the permanent tablespace
persists beyond the duration of a session or transaction. Objects in permanent tablespaces
are stored in datafiles. Temporary tablespace: On The contrary temporary
tablespace are the tablespaces which contain schema objects only for the duration of a
session which means that data stored in the temporary tablespace exists only for the duration
of a session or a transaction. Objects in temporary tablespaces are stored in tempfiles.
Undo Tablespace: Then there comes Undo tablespace. Undo tablespace is a special type of tablespace
used by oracle database to manage undo data if you are running your database in automatic
undo management mode. Undo tablespace stores data permanently which means that undo tablespace
are permanent in nature. Undo tablespace play a vital role in providing
• Read consistency for SELECT statements that access tables that in turn consist of
rows which are in the process of being modified. • The ability to rollback a transaction
that has failed to commit. Next we have Big-file tablespace and small
file tablespace. Big-File tablespace: The new concept started
from Oracle 10g. Big file tablespace is best suited for storing large amounts of data.
Big file tablespace can have maximum 1 datafile which means bigfile tablespaces are built
on single data files which can be as many as 232 data blocks in size. So, a bigfile
tablespace that uses 8KB data blocks can be as much as 32TB in size.
Small-File tablespace: This is the default type of tablespace in oracle database. Small
file tablespace can have multiple datafiles and each datafile can be as many as 222 data
blocks in size. A small file tablespace can have maximum up to 1022 data files but this
number depends on your Operating system also. You can create Permanent tablespace, temporary
tablespace or undo tablespace either as big-file tablespace or small-file tablespace but by
default they are always small-file tablespace. The SYSTEM And SYSAUX tablespaces are always
created when the database is created. One or more temporary tablespaces are usually
created in a database along with an undo tablespace and several application tablespaces. Because
SYSTEM and SYSAUX are the only tablespaces always created with the database, they are
the default tablespaces. That’s all for today on tablespaces in Oracle
Database. Hope you enjoyed this video and learned something. Please hit the like button
if you like this video and you can also share this video with your friends and help me reach
out to more people. You can also follow me on my social media all the links are in the
description box & most importantly don’t forget to subscribe to my channel.
Will see you soon with second video in the series of tablespace till then take care.
This is Manish from RebellionRider.