Shrink down tablespace size by exp/imp
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Shrink down tablespace size by exp/imp

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    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?

    thanks,

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    Posts
    257
    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?

    Thanks,

  4. #4
    Join Date
    Feb 2004
    Posts
    35
    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