This discussion is archived
1 2 Previous Next 15 Replies Latest reply: May 23, 2011 10:28 AM by jgarry RSS

How to change the INITIAL_EXTENT without recreate the table

863281 Newbie
Currently Being Moderated
Hi ALL:

So far we have several table with huge data .
And after we import these table to our testing environment.
We need to truncate the table first .
But we found the initial_extent is also allocated a huge block.
So far the only way we know is drop table and recreate table .

But cause we have much table need to do the same thing .
I don't thing drop/create table is a good way.

Is there any better way to release these space ?

Thanks
  • 1. Re: How to change the INITIAL_EXTENT without recreate the table
    Aman.... Oracle ACE
    Currently Being Moderated
    Welcome to OTN forums. Please ensure that you read the sticky post (Forum Etiquette) for sure and also the FAQ ( on the top right of this page) .

    Now, what's your oracle version and o/s?

    Second, why you want to change the intial_extent?

    Aman....
  • 2. Re: How to change the INITIAL_EXTENT without recreate the table
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please check below
    http://dbaforums.org/oracle/index.php?showtopic=8597

    Regard
    Helios
  • 3. Re: How to change the INITIAL_EXTENT without recreate the table
    863281 Newbie
    Currently Being Moderated
    sorry...forget mention the version.
    We are using oracle 10g.

    As I said , after import huge table into testing environment.
    Even we truncate these table, these table's initial_extent are still huge and also occupy huge space.
    Our DBA tell me that drop table and create is the only way to release these space .
    But it is not a good method if we have more than 1000 tables need to be shrink..


    Thanks
  • 4. Re: How to change the INITIAL_EXTENT without recreate the table
    sybrand_b Guru
    Currently Being Moderated
    Your DBA is right: it is the only method.
    And why do you think you need to shrink more than 1000 tables? What is it going to solve?
    Most likely nothing, as those tables will start growing again.
    An important part in developing a database is planning.
    You should consider things before you just start 'doing something*.
    Alas, the remorse comes after the sin.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: How to change the INITIAL_EXTENT without recreate the table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user3440068 wrote:
    We are using oracle 10g.

    As I said , after import huge table into testing environment.
    Take a look at your export command - is it using the "compress = y" option. This creates text that specifies one very large extent.
    Even we truncate these table, these table's initial_extent are still huge and also occupy huge space.
    Our DBA tell me that drop table and create is the only way to release these space .
    But it is not a good method if we have more than 1000 tables need to be shrink..
    I would have thought that after a truncate something like the following would free the space (even dropping the space below the initial allocation setting)
    alter table XXX deallocate unused keep 1m;
    But do check this note if you see some objects with more space left than you "keep": Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?

    Regards
    Jonathan Lewis
  • 6. Re: How to change the INITIAL_EXTENT without recreate the table
    863281 Newbie
    Currently Being Moderated
    Sorry....
    I didn't describe very detail.
    Cause our system has huge data with more than 3000 tables.
    And as our system development , we need to always dump the latest data into our testing environment.
    Sometimes we don't need these data , just need a empty system to test.
    And as our many projects, we need to create many testing environment in one table space.
    So we hope these table after truncated can release space for other testing environment.
  • 7. Re: How to change the INITIAL_EXTENT without recreate the table
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Do a one time cleanup whereby you DROP and CREATE the tables.
    OR
    the one time cleanup could be a TRUNCATE and DEALLOCATE UNUSED. (you might still have some tables with more than 8 blocks, as explained by Jonathan Lewis in the other thread, to which he provided a link).

    Thereafter, at every cycle, use your current TRUNCATE and IMPORT method.

    Hemant K Chitale

    Edited by: Hemant K Chitale on May 20, 2011 4:21 PM
    Added the "OR" for the one time cleanup
  • 8. Re: How to change the INITIAL_EXTENT without recreate the table
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    the one time cleanup could be a TRUNCATE and DEALLOCATE UNUSED. (you might still have some tables with more than 8 blocks, as explained by Jonathan Lewis in the other thread, to which he provided a link).

    Thereafter, at every cycle, use your current TRUNCATE and IMPORT method.
    In a similar vein, but allowing for some change in the production structure, change your import strategy (assuming it uses an empty database every time) to:

    <ul>
    import structure only
    -- optionally drop indexes
    truncate all tables
    deallocate unused
    import data
    </ul>

    Since you've tried truncating, presumably you've already worked around the problem of truncating with foreign keys - which this strategy would also need to handle.

    Regards
    Jonathan Lewis
  • 9. Re: How to change the INITIAL_EXTENT without recreate the table
    863281 Newbie
    Currently Being Moderated
    Our system has some special property .
    Cause through its inner system, some table may add column if user want to do some adjustment.

    So far we create a DDL sql to do the shirk things ,but we can't always check the consistency .
    We have met once that we recreate table with the older DDL , then the system crashed .
    So that we hope we can have better way to do the shrink things.
    And only half tables can be truncated and others are still need to keep for the whole system's setting parameter

    Edited by: user3440068 on 2011/5/20 上午 1:33
  • 10. Re: How to change the INITIAL_EXTENT without recreate the table
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I am not suggesting that you recreate the table every time.

    Your current table definitions are correct, I presume. So use the current definitions, which you can extract with DBMS_METADATA or 3rd party tools -- for a one time run. If you recreate the tables (and NOT set the large INITIAL_EXTENT in the CREATE statements !) with the current definition and you later do an ADD COLUMN, you don't have to recreate the table again.

    And ... maintain a code repository with the correct definitions, by version, everytime there is a change !

    Hemant K Chitale

    Edited by: Hemant K Chitale on May 20, 2011 4:45 PM

    Edited by: Hemant K Chitale on May 20, 2011 5:36 PM
    Added "(and NOT set the large INITIAL_EXTENT in the CREATE statements !)"
  • 11. Re: How to change the INITIAL_EXTENT without recreate the table
    jgarry Guru
    Currently Being Moderated
    Hemant K Chitale wrote:
    I am not suggesting that you recreate the table every time.

    Your current table definitions are correct, I presume. So use the current definitions, which you can extract with DBMS_METADATA or 3rd party tools -- for a one time run. If you recreate the tables (and NOT set the large INITIAL_EXTENT in the CREATE statements !) with the current definition and you later do an ADD COLUMN, you don't have to recreate the table again.
    I think he's saying the current ones are huge. Perhaps he can truncate and alter move, since that allows the INITIAL change. Of course if he can truncate he can drop and recreate, with the proper code repository you suggest, but he has to get there somehow.

    >
    And ... maintain a code repository with the correct definitions, by version, everytime there is a change !

    Hemant K Chitale

    Edited by: Hemant K Chitale on May 20, 2011 4:45 PM

    Edited by: Hemant K Chitale on May 20, 2011 5:36 PM
    Added "(and NOT set the large INITIAL_EXTENT in the CREATE statements !)"
  • 12. Re: How to change the INITIAL_EXTENT without recreate the table
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    No, unfortunately, a TRUNCATE and MOVE doesn't reset the INITIAL.
    SQL> create tablespace X_TBS datafile '/home/oracle/app/oracle/oradata/orcl/X_TBS.dbf'
      2  size 500M extent management local autoallocate segment space management auto;
    
    Tablespace created.
    
    SQL> connect hemant/hemant
    Connected.
    SQL> create table X_LARGE_EXT (col_1 number, col_2 varchar2(5))                       
      2  tablespace X_TBS storage (initial 128M next 64M);
    
    Table created.
    
    SQL> insert into X_LARGE_EXT values (1,'ABC');
    c
    1 row created.
    
    SQL> commit;
    SP2-0042: unknown command "ccommit" - rest of line ignored.
    SQL> commit;
    
    Commit complete.
    
    SQL> select bytes, extents from user_segments where segment_name = 'X_LARGE_EXT';
    
         BYTES    EXTENTS
    ---------- ----------
     134217728          2
    
    SQL> select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT';
    select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    
    
    SQL> select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
      2  group by bytes order by 1;
    
         BYTES   COUNT(*)
    ---------- ----------
      67108864          2
    
    SQL> select initial_extent , next_extent from user_tables where table_name = 'X_LARGE_EXT';
    
    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
         134217728    67108864
    
    SQL> truncate table X_LARGE_EXT;
    
    Table truncated.
    
    SQL> select bytes, extents from user_segments where segment_name = 'X_LARGE_EXT';
    
         BYTES    EXTENTS
    ---------- ----------
     134217728          2
    
    SQL> select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
      2  group by bytes order by 1;
    
         BYTES   COUNT(*)
    ---------- ----------
      67108864          2
    
    SQL> select initial_extent , next_extent from user_tables where table_name = 'X_LARGE_EXT';
    
    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
         134217728    67108864
    
    SQL> alter table X_LARGE_EXT move;
    
    Table altered.
    
    SQL> select bytes, extents from user_segments where segment_name = 'X_LARGE_EXT';
    
         BYTES    EXTENTS
    ---------- ----------
     134217728          2
    
    SQL>  select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
      2  group by bytes order by 1;
    
         BYTES   COUNT(*)
    ---------- ----------
      67108864          2
    
    SQL> select initial_extent , next_extent from user_tables where table_name = 'X_LARGE_EXT';
    
    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
         134217728    67108864
    
    SQL> alter table X_LARG_EXT deallocate unused;
    alter table X_LARG_EXT deallocate unused
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> alter table X_LARGE_EXT deallocate unused;
    
    Table altered.
    
    SQL> select bytes, extents from user_segments where segment_name = 'X_LARGE_EXT';
    
         BYTES    EXTENTS
    ---------- ----------
     134217728          2
    
    SQL> select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
      2  group by bytes order by 1;
    
         BYTES   COUNT(*)
    ---------- ----------
      67108864          2
    
    SQL> select initial_extent , next_extent from user_tables where table_name = 'X_LARGE_EXT';
    
    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
         134217728    67108864
    
    SQL> alter table X_LARGE_EXT shrink space;
    alter table X_LARGE_EXT shrink space
    *
    ERROR at line 1:
    ORA-10636: ROW MOVEMENT is not enabled
    
    
    SQL> alter table X_LARGE_EXT enable row movement;
    
    Table altered.
    
    SQL> alter table X_LARGE_EXT shrink space;
    
    Table altered.
    
    SQL> select bytes, extents from user_segments where segment_name = 'X_LARGE_EXT';
    
         BYTES    EXTENTS
    ---------- ----------
        327680          1
    
    SQL> select bytes, count(*) from user_extents where segment_name = 'X_LARGE_EXT'
      2  group by bytes order by 1;
    
         BYTES   COUNT(*)
    ---------- ----------
        327680          1
    
    SQL> select initial_extent , next_extent from user_tables where table_name = 'X_LARGE_EXT';
    
    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
         134217728    67108864
    
    SQL> select 327680/8192 from dual;
    
    327680/8192
    -----------
             40
    
    SQL> 
    TRUNCATE and MOVE don't reset the size. Nor do they change the initial. SHRINK does reset the actual size without changing the "defined" initial.

    Such is the behaviour if a table (or index, too, I would expect) was created with an INITIAL size.


    Also see the discussion at
    Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    and
    http://hemantoracledba.blogspot.com/2009/07/sizing-or-growing-table-in-autoallocate.html


    Hemant K Chitale

    Edited by: Hemant K Chitale on May 22, 2011 11:54 AM
  • 13. Re: How to change the INITIAL_EXTENT without recreate the table
    863281 Newbie
    Currently Being Moderated
    hi

    after I shirk space , I got error msg.
    Error report:
    SQL Error: ORA-10631: SHRINK clause should not be specified for this object
    10631. 00000 - "SHRINK clause should not be specified for this object"
    *Cause:    It is incorrect to issue shrink on the object
    *Action:   Verify the object name and type and reissue the command

    I will check this first.
    Thanks
  • 14. Re: How to change the INITIAL_EXTENT without recreate the table
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    SHRINK SPACE works only if the table has been created in an ASSM Tablespace (SEGMENT_SPACE_MANAGEMENT='AUTO').


    Hemant K Chitale
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points