Hello Guru's
I am working on oracle express 10 G on windows , This is my test machine. I am a beginner to oracle Dba activities,
Problem:
I want to restrict the size of a table (say max upto 400k)
My Approach:
1. I create a Tablepsace with uniform extent size (40K each)
2. I created a table in this tablespace with max extents 10 ( so that 40K * 10 = 400K)
SCOTT@xe>CREATE TABLESPACE TB
2 DATAFILE 'F:\Oracle\oradata\XE\TB.DBF' SIZE 100M
3 EXTENT MANAGEMENT LOCAL
4 UNIFORM SIZE 40K
5 ONLINE ;
Tablespace created.
SCOTT@xe>
SCOTT@xe>DROP USER G CASCADE ;
User dropped.
SCOTT@xe>CREATE USER G IDENTIFIED BY G DEFAULT TABLESPACE TB ;
User created.
SCOTT@xe>ALTER USER G QUOTA UNLIMITED ON TB;
User altered.
SCOTT@xe>GRANT CREATE SESSION , CREATE TABLE TO G;
Grant succeeded.
SCOTT@xe>CONNECT G/G@xe
Connected.
G@xe>CREATE TABLE t ( A number) TABLESPACE TB storage (minextents 1 maxextents 10);
Table created.
G@xe>SELECT SEGMENT_NAME, MIN_EXTENTS, MAX_EXTENTS , SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
2 FROM USER_SEGMENTS ;
SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE TABLESPACE_NAME BLOCKS
--------------------------------------------------------------------------------- ----------- ----------- ------------------ ------------------------------ ----------
T 1 2147483645 TABLE TB 5
G@xe>
Issue is:
I want the maximun extents that could be llocated to this table must be 10 , BUT from user_segments I could see MAX_EXTENTS = 2147483645 (default value).
When i pump data into this table MAX_EXTENTS exceeds 10 (i.e user Defined value)
I have no idea why this is the case;
Regards,