Re: Interpreting AUTOEXTENSION increment in DBA_DATA_FILES and altering it.

Originally posted by Dr Deadpan
> 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

Your db_block_size is 8192 so the increment_by 1280 is interpreted in number of Oracle blocks and not the actual bytes i.e. 1280 * 8192 = 10485760 or 10485760 / 8192 = 1280. maxblocks and user_blocks are also number of Oracle blocks.

You can use DBA studio (in standalone) to adjust datafile maxsize and increment by. Use "Show SQL" option to view the SQL statement.

Regards
/Rauf Sarwar

--
Posted via http://dbforums.com