Add a datafile to a tablespace: little questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Add a datafile to a tablespace: little questions

  1. #1
    Join Date
    Oct 2008
    Posts
    1

    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:
    Code:
    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
    Code:
    select tablespace_name,bytes,increment_by,maxbytes from dba_data_files;
    provides
    Code:
    TABLESPACE_NAME               |     BYTES|INCREMENT_BY|  MAXBYTES
    ------------------------------|----------|------------|----------
    ...
    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:
    Code:
    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
    Location
    England
    Posts
    7,333
    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
    Location
    Lake Worth, FL
    Posts
    1,460

    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.
    Code:
    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
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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