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.

when more space is requird in the tablespace it finds it whereever it is available, so yes in your new datafile

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

---

:rolleyes:

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.