Interpreting AUTOEXTENSION increment in DBA_DATA

From: Dr Deadpan <drdeadpan_at_yahoo.com>
Date: 2 Jul 2003 08:50:07 -0700
Message-ID: <a944d23e.0307020750.62e463f@posting.google.com>

Hi,

I have 2 (simple) questions. In our Oracle 8.1.7 Database , the SYSTEM tabelspace had following DATAFILE clause in the CREATE DATABASE statement

DATAFILE 'E:\Oracle\oradata\oc40test\system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 532M Upon querying DBA_DATA_FILES, I got the following

SQL> select * from dba_data_files where tablespace_name = 'SYSTEM';

FILE_NAME

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ----------

RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------

E:\ORACLE\ORADATA\OC40TEST\SYSTEM01.DBF

         1 SYSTEM                          536870912      65536
AVAILABLE
           1 YES  557842432      68096         1280  536862720

65535

As you can see, the tablespace is close to capacity and I was thinking of altering the MAXSIZE parameter of the AUTOEXTEND clause. Notice that BYTES is 536870912 ( 512MB) and MAXBYTES is 557842432 (532MB)
as expected but increment by is 1280 when I expected it be 10485760 bytes(10240 K) as specified. So, how do I interpret this?

I wanted to alter the datafile to a MAXSIZE of 1000MB with increments of
of about 20MB. So can I just use a alter database command like alter database datafile 'E:\ORACLE\ORADATA\OC40TEST\SYSTEM01.DBF' AUTOEXTEND ON NEXT 20MB MAXSIZE 1000MB to do this? DO I need the REUSE clause too?

Thanks in advance.

DrD Received on Wed Jul 02 2003 - 10:50:07 CDT

Interpreting AUTOEXTENSION increment in DBA_DATA_FILES and altering it.