This content has been marked as final. Show 4 replies
When creating a segment (Table/Index) in a Locally Managed Tablespace (created with "EXTENT MANAGEMENT LOCAL" and visible in DBA_TABLESPACES under EXTENT_MANAGEMENT), Oracle ignores the specification of MAXEXTENTS.
All segments in an LMT would default to MAXEXTENTS UNLIMITED (which really means 2 billion).
(DEFAULT STORAGE at the Tablespace level cannot be set if it is LOCAL AUTOALLOCATE or LOCAL UNIFORM)
See [The 10gR2 documentation on the STORAGE clause|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses009.htm#i997450]
In your 10gXE install you will not be able to create a DICTIONARY MANAGED Tablespace as the SYSTEM Tablespace itself is LOCALLY MANAGED.
If you want to limit the size of a table you could
a. Create a Tablespace with a set size for datafiles (ie, set AUTOEXTEND OFF for the datafiles)
b. Create the Table in that Tablespace
Edited by: Hemant K Chitale on Aug 18, 2009 3:31 PM
MAXEXTENTS is ignored for locally managed tablespaces. See [this note in the SQL reference|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses009.htm#i997450]
Note:Why do you want to prevent the table from exceeding 400k? That seems like a pretty odd business requirement-- I've never come across a need for that sort of thing. If you explain the problem you're trying to solve in a bit more detail, perhaps we can suggest an alternate solution.
The storage_clause is interpreted differently for locally managed tablespaces. At creation, Oracle ignores MAXEXTENTS and uses the remaining > parameter values to calculate the initial size of the segment. For more information, see CREATE TABLESPACE.
Thanks Hemant and Justin- for the reply.
1. preventing the table from exceeding <given size> was just an example.
2. This is not a business requirement,
My question was more to know -
"Where i am doing wrong , Why Max_extent is not working , are there any other DB parameter which actually over-writting my table setting"
Thank you for you reply and time.