Oracle Tablespaces Explained

July 9, 2008  |  dba

In a DBMS (database management system) a tablespace is a logical group of data files in a database. Common database contains at least one tablespace but usually two or more. In a database, a tablespace plays a role similar to that of a folder on the hard drive of a computer by grouping similar functional objects. Oracle, automatically create a tablespace called SYSTEM that contains most of the information about the structure and contents of the database. A small database could be entirely contained in the SYSTEM tablespace (before 10g because after 10g Oracle needs at least 2 tablespaces, the SYSTEM and the SYSAUX).

Usually Oracle Database also has a TEMP tablespace (temporary) used for sorting data on disk in case you run out of memory assigned for sorting data when you query database. You may also have an UNDO tablespace to hold the before image of any changed data (used in rollback operations, flashback query, etc).

Oracle Database templates also come with USERS tablespace. This tablespace is a tablespace for containing your tables and all other objects in case you don’t create your own tablespace to contain them.

In order to an user create objects in a tablespace it must be granted quota space on that tablespace. By default when a user is created he does not have quota on any tablespace, so it must be granted explicitally by setting the size of quota or unlimited quota. There’s also a role called UNLIMITED TABLESPACE that assigns unlimited size on all database tablespaces to the user that’s granted, although I think it’s stupid to do so… i’m a security freak πŸ˜‰ !

You can see any users quota by querying dba_ts_quotas dictionary view

[sq’]
select tablespace_name, max_bytes
from dba_ts_quotas
where username=’MYUSER’;
[/sql]

You can grant quota on tablespace to an user with the following command:

alter user MYUSER quota 100M on MYTABLESPACE;

There’s Locally managed tablespaces and dictionary managed tablespaces. I don’t want to be boring about comparing both, but the main differences are:

Locally Managed:

  • available since Oracle 8i
  • db blocks managed in the datafile
  • uniform extent management allowed
  • no undo generated
  • fragmentation reduced

.

Dictionary Managed:

  • currently available for backward compatibility
  • db blocks managed in data dictionary
  • no mechanism for uniform extent creation
  • undo created resulting in locks and competition for resources

.

By the way, you can also have read-only tablespaces and big file tablespaces, two new types since 10g. May be someday I talk about them.

I hope I gave a simple explanation about tablespaces… If someone want to add something about the subject just comment this post.


7 Comments


  1. Thank you! I am currently attending Oracle University and I was really struggling with visualizing what the hell a tablespace is. It was making me insane because I am a totally visual learner and the courses are not really designed for people like me.

    The comparison to it being like a folder made a light go on in my head. I can’t thank you enough. Now it makes perfect sense to me.

    Yvonne

  2. Hi Yvonne:

    Thank you too for the blog reading πŸ™‚ Hope I can help you in future again…

    Joao

  3. Thanx, that was really simple explanation))))
    Just what I was looking for)

  4. Hi joao,

    Thanks for the above post.

    I am just starting to learn Oracle, and I have a question.

    What is meant by ‘similar functional objects’ in your post? Suppose i am creating an oracle database for a company. And this company has departments like HR, Training, Marketing… etc.

    So, when i create the table spaces, should i create tablespaces for just company or for each and every department or is it going to be more..?? πŸ˜€

    I just starting my learning curve on oracle, so sorry if im asking a dumb question..

    • Hi Madhuranga…

      When i say “similar functional objects” it can be several ways of organizing data. Some people create tablespaces for their different database objects… to make for instance their indexes to be apart from tables.
      Usually I also take indexes apart of my tables. There’s an huge discussion if this improve or not performance… but I do it like this to have clearly my database objects organized.
      With the example you show, i would create a tablespace DATAHR that would hold the data related to HR, one DATATRAIN for training, DATAMKT, etc… but only if the number of tables justify the existence of a dedicated tablespace for them. to hold a dozen of tables i usually don’t create a separate tablespace, unless those tables will have +100000 records…

      Regards
      Joao

  5. Thank you for providing such a good Explanation to me.

    Thank you very much.

  6. Actually no matter if someone doesn’t know then its up to other
    people that they will help, so here it happens.

Leave a Reply