4 Replies Latest reply: Aug 18, 2009 2:58 AM by 706055 RSS

    MAX EXTENTS  for a table

    706055
      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,
        • 1. Re: MAX EXTENTS  for a table
          706055
          I am very eager to know the answer for this. please help me and let me know if anybody on forum has tried this.
          • 2. Re: MAX EXTENTS  for a table
            26741
            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
            • 3. Re: MAX EXTENTS  for a table
              JustinCave
              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:
              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.
              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.

              Justin
              • 4. Re: MAX EXTENTS  for a table
                706055
                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.

                Regards,