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?
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
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?
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';
Click Here to Expand Forum to Full Width