Tablespaces
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.

Oracle Database Tutorial 63 : Introduction to Tablespace In Oracle Database

9 thoughts on “Oracle Database Tutorial 63 : Introduction to Tablespace In Oracle Database

  • June 11, 2016 at 7:38 am
    Permalink

    thanksssssssss a lot……its awesome……in one day i have viewed all vdeos…so interesting

    Reply
  • September 16, 2016 at 10:53 pm
    Permalink

    Can a schema share multiple tablespaces ?

    Reply
  • November 2, 2016 at 7:00 pm
    Permalink

    Manish, thanks for the tutorial, but it would be great to see more detailed videos. For example, there is no any information in this video about locally managed tablespaces and dictionary managed. It is not big deal already, but still it can be helpful to know.

    Reply
  • February 8, 2018 at 6:55 pm
    Permalink

    Thank you Manish,your videos are really good and interesting 🙂 Keep posting more useful videos….

    Reply
  • June 13, 2018 at 11:36 pm
    Permalink

    thanks yaar , tere video bahot easy hai

    Reply
  • February 4, 2019 at 10:24 pm
    Permalink

    Please slow down when you speak, it makes it easier to understand what you're saying. Good video otherwise.

    Reply
  • February 27, 2019 at 10:21 pm
    Permalink

    Hello Sir!! Thank you for your videos, i had a confusion regarding Tablespace & Datafiles, now i am clearly satisfy Thanks. Love from Pakistan.

    Reply
  • July 25, 2019 at 9:57 pm
    Permalink

    your tutorials makes things easy to understand. Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *