Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle 9i and Shrink Bloated Datafiles

Posted on 2006-11-07
12
Medium Priority
?
12,871 Views
Last Modified: 2012-06-27
I have a DB (Oracle 9i) which has recently been growing at an exponential rate.  We finally found the source of the problem and have run the appropriate scripts to clear out the garbage data but now need to reclaim the ‘Whitespace’ in the data files.  I know that I can do an export of the DB then drop it and re-import but I was wondering if there is an easier way of dealing with this?  The problem is isolated to a single Tablespace / Datafile.

Thanks!
0
Comment
Question by:Orwellian23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 18

Accepted Solution

by:
rbrooker earned 750 total points
ID: 17892333
Hi,

you can create a tablespace that is the size you think the problem tablespace should be and then move the segments from one to the other, rebuild the problem tablespace and move the segments back.

to move a table :
alter table my_table move tablespace <NEW_TABLESPACE>;
alter index my_index rebuild storage <NEW_TABLESPACE>;

( drop problem tablespace, rebuild problem tablespace )

alter table my_table move tablespace <ORIGINAL_TABLESPACE>;
alter index my_index rebuild storage <ORIGINAL_TABLESPACE>;

good luck :)

0
 

Author Comment

by:Orwellian23
ID: 17892420
The tablespace in question contains hundreds of tables, can every table be moved using a single command or would I need to run this Alter statement for each one?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 17892454
You can try resizing the datafile but I wouldn't hold out much hope of it being able to decrease the file much.

ALTER TABLESPACE <tablespace> RESIZE 80G;

I suggest just doing it in small chunks until you can't do it any more.

I think rbrooker's approach is the best.  Just write a SQL script to generate all the move commands, spool it out and execute the spooled file.

(untested):
-----------------
select 'alter table ' || table_name || ' move <NEW_TABLESPACE>;' from user_tables where tablespace_name='<OLD_TABLESPACE>';
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:rbrooker
ID: 17892464
Hi,

you can :

select 'alter table ' || table_name || ' move tablespace <NEW_TABLESPACE>;'
from dba_tables
where tablespace_name = '<ORIGINAL_TABLESPACE>';

select 'alter index ' || index_name || ' rebuild tablespace <NEW_TABLESPACE>;'
from dba_indexes
where tablespace_name = '<ORIGINAL_TABLESPACE>';

this will produce a list of tables that you can spool to a file and then run as a script.
note: if a table contains a LONG column, this will not work, and those tables will have to be exported and imported.  I am unsure on how LOBs will be treated as well.  this will get the majority tho, so what needs to be exported / imported may in fact be a very small list.
0
 

Author Comment

by:Orwellian23
ID: 17892592
Would that footnote also apply to BLOB datatypes?
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17892768
I dont know, i do not deal with BLOBs / LOBs generally, it would probably be trial and error.
maybe there is another expert out there who knows?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 17893105
You should be able to move tables with LOB datatypes.  I don't have access to 9i any more but just tried it on 10.2 and it works just fine.
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 17896877
Hi,
  You can try moving one by one like with the output of the query:
select 'alter table ' || owner || '.' || table_name || ' move tablespace NEW_TABLESPACE ;' || chr(10) ||
 'alter table ' || owner || '.' || table_name || ' move tablespace ORIGINAL_TABLESPACE ;'
from dba_tables where tablespace_name='ORIGINAL_TABLESPACE' ;
or you can use user_tables also.

In this case, your NEW tablespace should be big enough to hold the biggest table instead of all the tables at once.

HTH
Regards,
Badri.
0
 

Expert Comment

by:suhong
ID: 21519504
Just a try:

you can try alter table shrink space,
after coalesce the bloated  tablespaces
then then resize datafile.

0
 

Expert Comment

by:suhong
ID: 21519603
Just a try:

you can try alter table shrink space,
after coalesce the bloated  tablespaces
then then resize datafile.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 21519776
suhong,

I don't have any 9i around to verify but I believe the 'shrink space' command is new to 10g.
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 21548321
Shrink space is 10g command.. not in 9i.
You can use the MOVE clause of alter table .. to move the table to a different tablespace temporarily and then back to the original tablespace.
You can use alter table <table_name> move tablespace <tablespace>
This SQL will invalidate the indexes on the table and you will need to rebuild the same

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question