Tablespace and Datafiles in Oracle Database : Back to Basics

Image taken from Oracle Documentation

Tablespace , Datafile, Oracle database object

Tablespace : is logical storage unit in Oracle Database.
ii) Tablespace connsit of one or more datafiles (check below)
iii) Information about Tablespace can be obtained from view DBA_TABLESPACES, USER_TABLESPACES, DBA_TEMP_FILES (for Temporary Tablespaces)
iv) Tablespace is further divided in to logical units Segments (Segment is divided in to Extent and Extent in to Block) . To know more about Segment, Extents and Blocks click here
v) Various type of tablespace are BIGFILE, SYSTEM, SYSAUX, UNDO

Datafiles : is physical structure to store oracle data
ii) One or more physical datafile are logically grouped together to make a tablespace
iii) Information about Datafile can be obtained from view DBA_DATA_FILES
iv) A Datafile can be associated with only one tablespace

Adding Tablespace in database
Use CREATE TABLESPACE command to add tablespace in Database like
CREATE TABLESPACE <tablespace_name> DATAFILE <location_of_datafile>
CREATE TABLESPACE my_tablespace DATAFILE ‘/u01/oracle/oradata/data01.dbf’;

To create undo tablespace
CREATE UNDOTABLESPACE <tablespace_name> DATAFILE <location_of_datafile> SIZE <size> [AUTOEXTEND ON|OFF] [RETENTION GURANTEE|NOGURANTEE]

To create Temporary tablespace
CREATE TEMPORARYTABLESPACE <tablespace_name> TEMPFILE <location_of_datafile> SIZE <size> [AUTOEXTEND ON|OFF]

Adding Datafile in a Tablespace
Use ALTER TABLESPACE to add datafile in tablespace like
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile>
ALTER TABLESPACE my_tablespace ADD DATAFILE ‘/u01/oracle/oradata/data02.dbf’;

To add temporary file in TEMP table space
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>;

Modify Datafile
You can modify datafile using ALTER DATABASE command like
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;

ALTER DATABASE DATAFILE ‘/u01/oracle/oradata/data02.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.

Related
http://www.psoug.org/reference/tablespaces.html

Did you get a chance to download Free Interview Questions related to Database? If not, download it here http://k21academy.com/oracle-dba-12c-interview-question

Database interview questions

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

20 comments
hany says February 10, 2008

thanks alot sir. , this is very useful for me, please continue this series (back to basic)

thank you

Reply
Pronab says June 24, 2008

Thank’s lot for your webside.Please send me yore oracle dba document

Reply
lakshmikanthan says June 24, 2008

hi
thank u sir
continue u more messages sir

by
lakshmi

Reply
rohitpatel9999 says August 19, 2008

Please advise.
Whether datafile (of temporary tablespace) should be set as AUTOEXTEND=ON or AUTOEXTEND=OFF? What is advisable?
I appreciate any hint or related link to Oracle doc.
Thanks.

Reply
skg says September 14, 2008

Hi,
Can you please confirm, Is it correct

Table Space is broken into Segments which contains similar data files….
i.e Table Space contains different Segments

Segment is broken into Extends, One data file data can be stored in only one Extend (we can store each Extends on different Disks also.)

and then Extends are broken to Data blocks as this is the form in which our table data is stored

TABLE SPACE (contains Different segments)
SEGMENT (contains similar Data Files)
EXTEND (For every individual Data File)
Data Blocks(Block that is stored)

Thanks
SKG

Reply
vijay09 says October 23, 2008

for ex how we can delete the datafiles in tablespaces?

Reply
vijay09 says October 23, 2008

my DB disk size is 110 GB. now we have stored 90 GB.

what files we can delete ?how we can delete?

i have deleted trace files in bdump,cdump,udump.

after that my DB size also is 90 GB.

3 days back Functional user has run one report.it has taken 16 gb memory in undo tablespace.
thats why it is showing 90GB.

plz give me solution .

Reply
usman says November 11, 2008

thanks sir plz keep it up for bignners of database

from usman khalid batth
universty of punjab pakistan

Reply
lakshmikanthan says February 16, 2009

When i have been trying to run custom (f60gen module=CUSTOM.pll userid=apps/apps module_type=library output_file=CUSTOM.plx) that time following error was gone(segmentation fault)

[applydev@Server2 ~]$ cd $FND_TOP/bin
[applydev@Server2 bin]$ f60gen module=CUSTOM.pll userid=apps/apps module_type=library output_file=CUSTOM.plx
Forms 6.0 (Form Compiler) Version 6.0.8.25.2 (Production)

Forms 6.0 (Form Compiler): Release – Production

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production
PL/SQL Version 8.0.6.3.0 (Production)
Oracle Procedure Builder V6.0.8.21.0 Build #0 – Production
Oracle Virtual Graphics System Version 6.0.5.39.0 (Production)
Oracle Multimedia Version 6.0.8.25.0 (Production)
Oracle Tools Integration Version 6.0.8.17.0 (Production)
Oracle Tools Common Area Version 6.0.5.32.0
Oracle CORE Version 4.0.6.0.0 – Production
Compiling library CUSTOM…
Segmentation fault

Reply
tebogo says October 21, 2009

I am trying to deinstall oracle 11G on unix but the oracle user interface i am using does not give me an option to deinstall what might be the problem and how do resolve it please help..

Reply
Lakshman says December 17, 2009

iam trying add a tablespace with new datafiles. when i executing command for adding tablespace with new01.dbf; it is giving error like doest not exist.

Reply
Lakshya Dyal says March 5, 2010

Thanx sir , it is a nice article it clears my all doubts

Reply
Jithendra kumar says October 23, 2010

Hi,
Can we perform resizing of datafile (ie reducing the size than the actual size given).
In this cae whether there wil be dataloss or block,segment disarrangement.

Reply
master chan says November 4, 2010

This is very important to me continue like this
Thank you very very much.

Reply
neetadubey says December 7, 2011

sir,

I want to know that why we use table space in oracle.and how to know about the data which is stored in tablespace.

Reply
Ram says January 23, 2012

Hi Atul,

During the installation of UCM10g Content Server we are getting the below error while creating the tablespaces in oracle DB.

ORA-01031: insufficient privileges

The user account is Admin user of the DB, but still we are not able to create the database.

Any inputs on that?

Thanks

Reply
Atul Kumar says January 24, 2012

@ Ram,
Can this user create tablespace in database ? (Try creating tablespace manually using this user from sqlplus)

Reply
S.Pavan Teja says April 30, 2012

Hi!

This data is very useful to us…
Thank You very much..

Teja.

Reply
varaprasad says July 1, 2012

thanks you so much sir,

Its very useful to me.

just one help please give the description of user creation.

Reply
Prathyusha says March 17, 2015

Thank you for this article. It is very helpful for beginners like me

Reply
Add Your Reply

Not found