Re: multiple datafiles with autoextend

sag1rk_at_yahoo.com (Sagi) wrote in message news:<54d80104.0212030859.3f4adb58_at_posting.google.com>...
> 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

Thank you very much. Your explanation with the example is self demonstrating. Thanks again Received on Wed Dec 04 2002 - 22:07:46 CET