DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2001

    Shrink down tablespace size by exp/imp

    I never quite understand this:

    The previous DBA created DBs with export/import. However, when he did export from DB#1, he specified compress=y and import it into DB#2. Thus in the subsequent exports from DB#2, they all have big initial extents. I notice even if I export only schema from DB#2 with compress=n and import into other DBs, the result is still big tablespace, just like the source DB DB#2 (even though there is no data in it).

    How do people actually reoganize the tablespace and shrink down the size if the tablespace doesn't really have much data in it?


  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    use "alter table my_table move pctfree 0" (or whatever for pctfree), followed by "alter index my_table_idx1 rebuild pctfree 10" for each index.

    exp/imp sucks, and "compress=y" is purest evil
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2001
    I have 400+ tables. Does that mean I have to "move" 400+ tables? Is there antyhing that can achieve the same thing at tablespace level?


  4. #4
    Join Date
    Feb 2004
    use the following scripts to generate the required move script:

    select 'alter table '||table_name||' move tablespace newtbs;'
    from dba_tables where tablespace_name='OLDTBS';

    select 'alter index '||index_name||' rebuild tablespace newtbs;'
    from dba_indexes where tablespace_name='OLDTBS';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.