TableSpace - Useful Info

Here is mail on TableSpace in Oracle from one of my Friends who is working for one of the Successful MNCs(Originated in India) as a DBA.

Hello Sharma,

Hope that I am not late to reply.

Database is logically divided into a number of tablespaces.

Physically at O/s Level , you can see datafiles, where every datafile belong to a uniue tablespace.
When u create a database, a couple of tablespaces are created for system internal purpose. Apart from these, u can create new tablespaces. Generally different tablespaces are created to store tables/indexes of different database users or different types of tables ( if single Database user Application)

Example: Create tablespace TS_U1 datafile c:\oracle\ts_u1_01.dbf size 100M. --- u can see this datafile in c:\oracle of size 100MB.

Now create a user U1 and define his default tablespace as TS-U1. This means that All the tables created by User U1, will get stored in tablespace TS_U1, ie, c:\oracle\ts_u1_01.dbf in O/s level.

Tables and indexes are the only objects which consume space and are called segments. Tablespaces are to provide storage space for these segments. So, Ur database size is size of all tables & indexes, views & procedures dont occupy any space.

Now , if 100M is full, you can either add anothere datafile to the existing tablespace or extend the existing datafile to 200M...etc.

Single user can have quota on more than one tablespace. Also, generally a different tablespace is created for indexes to avoid contention , because when a query is fired it may like to use both table and index to read from datafile.

If both tables and indexes are in different tablespaces , then they will be in different datafiles ( datafile can only belong to a single tablespace), so it avoids contention while Oracle tries to read both table & index at a time.

Tablespace is some thing which physically hosts the table, not just info.

Also, there are storage features like RAID1,0,5,1+0 etc., ( Mirroring & Striping )etc., To distribute database properly different tablespaces are used with diff datafiles, accross different disks.

I hope I answered ur question. Reply me if U did not understand anything . I can suggest U some documentation if u like.

:-)

Regards,
Chaitanya Gouru

Hi Chinnu,

Tablespace stores information about table, index and so on. Whether Tablespace is restricted to single table or all the tables existing in a database or set of tables in database. Can you gimme some thought on this?

Thanx in advance
sarma
09324 444 164


@Koteswara sarma