DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008

    Add a datafile to a tablespace: little questions

    Hello at all,

    I have a tablespace called DATA which is nerly full. So it needs to be expanded. thios is not possible because of reaching the max file size. So a new datafile needs to be added. This tablespace is autoincremented. Next increment will not be possible because of missing space in the datafile.

    No I created a new datafile:
    SQL> alter tablespace DATA
      2  add datafile '...\DATA02.DBF' SIZE 1000M
      3  autoextend on
      4  next 128M
      5  maxsize 16000M;
    It was created without problems but I have a question regarding that:

    The query
    select tablespace_name,bytes,increment_by,maxbytes from dba_data_files;
    DATA                          |1,7125E+10|       32768|1,7180E+10
    DATA                          |1048576000|       32768|1,6777E+10
    and a script for showing free space of eacht tablespace provides:
    Tablespace|Max File Size (MB)|Current File Size (MB)|Increment by (MB)|Remaining File Size (MB)|TBS free Space (MB)|TBS free Space (%)                                              
    DATA      |          16383,99|              16332,00|           128,00|                   51,99|            1027,88|              6,29                                              
    DATA      |          16000,00|               1000,00|           128,00|                15000,00|            1027,88|            102,79
    OK, here's the questions:

    I am not sure why the Tablespace DATA ist shown twice?!
    In the first line of the last query response I see that the Tablespace Free Space is about 1000M. Before creating the new datafile it was only as big as the remaining file size. So this should be fine. What happens now when next increment operation starts: does it use the added datafile? I am not sure about it. It looks like...

    Thanks for your help.

  2. #2
    Join Date
    Sep 2002
    when more space is requird in the tablespace it finds it whereever it is available, so yes in your new datafile

  3. #3
    Join Date
    Jul 2002
    Lake Worth, FL

    Talking Rtfm

    You did not check the fine Oracle reference manual to lookup the definition of DBA_FREE_SPACE view:
    DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
    Related View: USER_FREE_SPACE describes the free extents in the tablespaces accessible to the current user.
    Column            Datatype      Description 
    -------           ------------  ---------------------------------------
    TABLESPACE_NAME   VARCHAR2(30)  Name of the tablespace containing the extent
    FILE_ID           NUMBER        File identifier number of the file containing the extent
    BLOCK_ID          NUMBER        Starting block number of the extent
    BYTES             NUMBER        Size of the extent (in bytes)
    BLOCKS            NUMBER        Size of the extent (in Oracle blocks)
    RELATIVE_FNO      NUMBER        Relative file number of the file containing the extent
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    You are seeing two rows saying "DATA" because of you are querying dba_data_files where... oh!... there are two rows e.g. datafiles for tablespace DATA.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.