Re: multiple datafiles with autoextend

vissuyk_at_yahoo.com (Vissu) wrote in message news:<2bedd6a7.0212021902.7ebe0ec3_at_posting.google.com>...
> Hi,
>
> I noticed that a datafile created with AUTOEXTEND ON has been extended
> by the database. But then I've added a new datafile to the tablespace.
> Will Oracle use the newly added datafile or will it keep on extending
> the old one. Both files have autoextend on.
>
> Thanks for any help
>
> Vissu

Hi,

Yes it would use the newly added file.

In my example given below, you can see that I have created TESTA.DBF, TESTB.DBF and TESTC.DBF each with 1MB and AUTOEXTEND ON with MaxSize as 5,5 and 10MB.

In the example you can see that TESTC.DBF, which was added after i created table is being used in one of the INSERT operations eventhough there was space in TESTA.DBF and TESTB.DBF. I mean though they have not reached their max extent.

As far as I know it uses the datafiles in a dynamic way.

create tablespace test
datafile 'c:\SAGI\testa.dbf' size 1m reuse autoextend on next 128k maxsize 5m,
'c:\SAGI\testb.dbf' size 1m reuse
autoextend on next 128k maxsize 5m
/

COLUMN FILE_NAME FORMAT A30
select file_name, file_id, bytes/1024/1024 "In MB", autoextensible, maxbytes/1024/1024 "Max In MB" from dba_data_files
where tablespace_name='TEST'
/

FILE_NAME                         FILE_ID      In MB AUT  Max In MB
------------------------------ ---------- ---------- --- ----------
C:\SAGI\TESTA.DBF                         8          1 YES          5
C:\SAGI\TESTB.DBF                         9          1 YES          5

CREATE TABLE T
TABLESPACE TEST
AS SELECT * FROM ALL_OBJECTS
WHERE 1=0 ; Table created.

SQL> INSERT /*+ APPEND */ INTO T
  2 SELECT * FROM ALL_OBJECTS ; 24922 rows created.

SQL> COMMIT ; Commit complete.

SQL> ALTER TABLESPACE TEST
  2 ADD DATAFILE 'C:\SAGI\TESTC.DBF' SIZE 1M REUSE   3 AUTOEXTEND ON NEXT 512K MAXSIZE 10M ; Tablespace altered.

SQL> INSERT /*+ APPEND */ INTO T
  2 SELECT * FROM T ; 24922 rows created.

  1 SELECT A.SEGMENT_NAME, A.FILE_ID, SUM(A.BYTES)   2 FROM DBA_EXTENTS A
  3 WHERE A.SEGMENT_NAME='T'
  4 GROUP BY A.SEGMENT_NAME, A.FILE_ID
  5* ORDER BY 2
SQL> /

SEGMENT_NAME                      FILE_ID SUM(A.BYTES)
------------------------------ ---------- ------------
T                                       8      2088960
T                                       9      3153920
T                                      10      2613248

SQL> select file_name, file_id, bytes/1024/1024 "In MB",   2 autoextensible, maxbytes/1024/1024 "Max In MB"   3 from dba_data_files
  4 where tablespace_name='TEST'
  5 /

FILE_NAME                         FILE_ID      In MB AUT  Max In MB
------------------------------ ---------- ---------- --- ----------
C:\SAGI\TESTA.DBF                         8          2 YES          5
C:\SAGI\TESTB.DBF                         9      3.125 YES          5
C:\SAGI\TESTC.DBF                        10        2.5 YES         10

Please tell me whether or not my example was helpful. Your feedback is a key for my learning.

Regards,
Sagi Received on Tue Dec 03 2002 - 17:59:58 CET