RE: shrink tablespace on 9i
- From: "Fernandes, Aldrin" <afernand@xxxxxxxx>
- To: <ora-apps-dba@xxxxxxxxxxxxx>
- Date: Fri, 17 Aug 2007 08:59:14 -0600
I have done this in production but for a different application and found it to
be worthwhile from a performance and space perspective, however cannot quantify
with E*Biz yet, but it is something I am looking at, to optimize space usage on
specific tablespaces.
I have done the reorg via ALTER commands and not IMP/EXP as it could be risky.
The above process is easier if we with the OATM model as the fewer tablespaces
to deal with.
Steps are:
++ Arrange for disk to house the largest tablespace.
++ Create the REORG DATA & IDX tablespaces, you can let the object sizing be
dictated by the tablespace.
++ Confirm the old tablespaces are empty.
++ Drop and recreate them.
++ Repeat Step 2.
For 10gr2 the last step doesn't need to be performed, just RENAME the REORG
tablespaces.
Thanks,
Aldrin
Your friendly neighbourhood DBA
-----Original Message-----
From: ora-apps-dba-bounce@xxxxxxxxxxxxx
[mailto:ora-apps-dba-bounce@xxxxxxxxxxxxx] On Behalf Of Zydek, Steven
Sent: Friday, August 17, 2007 7:32 AM
To: ora-apps-dba@xxxxxxxxxxxxx
Subject: RE: shrink tablespace on 9i
Are the performance and space saving benefits worth the hassle?? Does anyone
have any experience going through this in production? Also, what about in 10g?
Is it a similar process? I thought there was a "shrink" command to deal with
this in 10g?
Thanks for the info,
+Steve
-----Original Message-----
From: ora-apps-dba-bounce@xxxxxxxxxxxxx
[mailto:ora-apps-dba-bounce@xxxxxxxxxxxxx] On Behalf Of Jurijs Velikanovs
Sent: Friday, August 17, 2007 3:38 AM
To: ora-apps-dba@xxxxxxxxxxxxx
Subject: Re: shrink tablespace on 9i
It is possible to complete without full exp/imp in 9i but it is a little bit
tricky tasks.
You need to move all free space you have in a tablespace to the end of data
file.
To do that you can create additional tablespace move all objects to that one
then move objects back.
After ther use alter database datafile 'XXX' rezise YYY MB; to the value
allowed.
To move tables use "alter table move" for indexes "alter index rebuild".
For queues tables/indexes you need to drop and rebuild queues (temporary
storing active messages in other queues).
Not easy but possible to accomplish.
Good luck,
J.
On 8/17/07, Kenan Öztürk <mynameiskenan@xxxxxxxxx> wrote:
> Hi all,
>
> question about database maintenance?
>
> is it possible to shrink tablespace on 9i, is there any command to do
> that or ony way to do this is export/import tablespace?
>
> Regards
>
>
> ________________________________
> Take the Internet to Go: Yahoo!Go puts the Internet in your pocket:
> mail, news, photos & more.
>
>
--
Yury
+371 29268222 (+2 GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
This email contains confidential information that is proprietary to EPCOR and
its subsidiary companies in all respects. This information is intended only
for the person(s) named in the destination address. Unauthorized distribution,
copying or disclosure is strictly prohibited. If you receive this e-mail in
error please delete it immediately.
Other related posts:
