Re: LMTs -- DataFile Specification
From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 10 Mar 2008 23:54:14 +0800
Message-Id: <200803101554.m2AFsT6O014537@smtp42.singnet.com.sg>
------------------------------ ---------- -------------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ---
ALLOCATIO PLU SEGMEN DEF_TAB_
RETENTION BIG
--------- --- ------ -------- -----------
---
SYSTEM NO AUTO DISABLED NOT
APPLY NO SQL>
SQL> select * from dba_data_files where tablespace_name = 'X';
---------- ------------------------------ ---------- ----------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------
ONLINE_
Date: Mon, 10 Mar 2008 23:54:14 +0800
Message-Id: <200803101554.m2AFsT6O014537@smtp42.singnet.com.sg>
The "AUTOEXTEND .... " is part of the Datafile Specification
which is independent
of the Extent Management clause .
To specify MAXSIZE, the AUTOEXTEND ON would be mandatory, but the NEXT should not be mandatory.
See :
SQL> l
1 create tablespace x
2* datafile 'C:\OR10G2DB\x.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M
SQL> /
Tablespace created.
SQL> set pages600
SQL> select * from dba_tablespaces where tablespace_name = 'X'
2 /
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENTMIN_EXTENTS
------------------------------ ---------- -------------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ---
ALLOCATIO PLU SEGMEN DEF_TAB_
RETENTION BIG
--------- --- ------ -------- -----------
---
X 8192 65536
1
2147483645 65536 ONLINE PERMANENT
LOGGING NO LOCAL
SYSTEM NO AUTO DISABLED NOT
APPLY NO SQL>
SQL> select * from dba_data_files where tablespace_name = 'X';
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS
STATUS
---------- ------------------------------ ---------- ----------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------
ONLINE_
C:\OR10G2DB\X.DBF
7 X 104857600 12800
AVAILABLE
7
YES 524288000 64000 1 104792064 12792
ONLINE
SQL>
The MaxSize does not require a Next, but does require an AutoExtend ON
Extent Management doesn't have to be specified as it can default (10g
using LOCAL).
Hemant K Chitale
At 10:30 PM Monday, Joe Smith wrote:
>CREATE TABLESPACE data >DATAFILE '/FS/data_s01.dbf' size 2000m autoextend on next 1m maxsize 12000m, > '/FS/data_s02.dbf' size 2000m autoextend on next 1m > maxsize 12000m >EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M >SEGMENT SPACE MANAGEMENT AUTO; > >How do you control the size of LMTs. > >If I remove the "autoextend on next 1m" I can't use the "maxsize" keyword. > >How do I restrict the size of the datafiles for LMTs >
Hemant K Chitale
http://hemantoracledba.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 10 2008 - 10:54:14 CDT