• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


  • Categories

  • Archive

  • Tablespace and Datafiles in Oracle Database : Back to Basics

    Posted by "" in "db_basics" on 2008-02-08

    Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestEmail this to someone
    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

    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.


    19 Responses to “Tablespace and Datafiles in Oracle Database : Back to Basics”

    1. hany says:

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

      thank you

    2. Pronab says:

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

    3. lakshmikanthan says:

      thank u sir
      continue u more messages sir


    4. rohitpatel9999 says:

      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.

    5. skg says:

      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)


    6. vijay09 says:

      for ex how we can delete the datafiles in tablespaces?

    7. vijay09 says:

      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 .

    8. usman says:

      thanks sir plz keep it up for bignners of database

      from usman khalid batth
      universty of punjab pakistan

    9. lakshmikanthan says:

      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 (Production)

      Forms 6.0 (Form Compiler): Release – Production

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

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

    10. tebogo says:

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

    11. Lakshman says:

      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.

    12. Lakshya Dyal says:

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

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

    14. master chan says:

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

    15. neetadubey says:


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

    16. Ram says:

      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?


    17. Atul Kumar says:

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

    18. S.Pavan Teja says:


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


    19. varaprasad says:

      thanks you so much sir,

      Its very useful to me.

      just one help please give the description of user creation.

    Leave a Reply

  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP