OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Shrinking database

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shrinking database [message #223552]

Fri, 09 March 2007 07:35

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I wanted to know that If I archive old data on the basis of date coulmn and remove the old data from the concern tables then it would be possible to re-claim the space or not.(archive/drop/export/import)

When I remove these records then it would provide free space at the object level and in the beginning of file but still remain there (in the datafile in the beginning of datafile)

I will use the export and import for extracting required (remaining) data and populating back data to the concern tables to re-claim the space and re-organise table including index.

I will re-size option of datafile also by specifying the follwing statement

ALTER DATABASE DATAFILE FILENAME RESIZE SIZE

I will be doing this exercise for few tables as a first step. There are so many other tables are availbale in the schema.

I wish to know about the follwing comment also.

To guarantee to be able to shrink the datafiles would be to re-organise ALL the objects left in the tablespace after the archiving exercise so that their data is moved to the beginning of the datafiles so they can be shrunk OR move ALL the objects left in the tablespace after the archiving exercise into another new tablespace so the old one can be dropped.

ALTER TABLE ALTER TABLE tablename MOVE�., ALTER INDEX indexname REBUILD,etc., type commands

Please comment it also on the following statement too.

This will be required for ALL remaining objects in the old tablespace along with a re-ANALYZE of any objects with statistics.

My main concern is to re-claim the space and improve performance by shrinking database.

I will appreciate if you can send your suggestion/comment/opinion about it.

Please let me know if thing could not describe in a proper way.

Report message to a moderator

Re: Shrinking database [message #223554 is a reply to message #223552]

Fri, 09 March 2007 07:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Version?
in 10g, you can directly shrink the table (search for enable row movement).
>>My main concern is to re-claim the space and improve performance by shrinking database.
Not exactly true.
Shrinking will reclaim the space. But does not gurantee that performance would increase (unless you reset HWM and collect statss again. In that case, you have to do this very frequently, all your life Smile

).

You can look into online redefinition methods (dbms_redefinition).
If you are willing to archive old data, best method is to employ partitions and eliminate almost all the steps you stated before.

Report message to a moderator

Re: Shrinking database [message #223563 is a reply to message #223552]

Fri, 09 March 2007 09:44

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Currently, We have Oracle 8.1.7.4.0

How to reset HWM and collect stats again.

I think that online redefinition methods (dbms_redefinition) feature is not available in this version.

Moreover, the partinioning concept would not be useful looking at the application point of view. It is required lot of changes in the application(front-end level) as well as reports.

Your further suggestions or ideas would be highly appreciated.

Report message to a moderator

Re: Shrinking database [message #223565 is a reply to message #223563]

Fri, 09 March 2007 09:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Not much options in 8i.
>>How to reset HWM and collect stats again.
export/import and alter table move (is that available in 8i? not sure). Then use DBMS_STATS.
Search the forum for HWM for more info.
>>Moreover, the partinioning concept would not be useful looking at the application point of view. It is required lot of changes in the application(front-end level) as well as reports.
No changes would be required in your "front-end". That is the whole idea of partitioning Smile

.
Everything is in backend and is transparent to application.
You can dynamically add/drop/archive partitions based on date.
But since you are using the stone-age version of database, not sure about the Paritiion options available.

Report message to a moderator

Re: Shrinking database [message #223643 is a reply to message #223552]

Fri, 09 March 2007 23:45

knam
Messages: 5
Registered: March 2007
Location: Austria

Junior Member

hello
the fasted way is: make a Export / dmp from the database or user. create all tablespace new. import all.

Report message to a moderator

Re: Shrinking database [message #223972 is a reply to message #223643]

Mon, 12 March 2007 08:17

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I wanted to verify that the indexes would also be re-created using export/import, if I am not wrong.

Please reply on it.

Report message to a moderator

Re: Shrinking database [message #223978 is a reply to message #223972]

Mon, 12 March 2007 08:57

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Yes, they will.

Report message to a moderator

Re: Shrinking database [message #223979 is a reply to message #223643]

Mon, 12 March 2007 08:57

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Moreover, Is there any limitation to take an export of database in terms of size of the database in Oracle 8.1.7

Report message to a moderator

Re: Shrinking database [message #223983 is a reply to message #223979]

Mon, 12 March 2007 09:27

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Nothing specific in Oracle.

Report message to a moderator

Re: Shrinking database [message #223999 is a reply to message #223552]

Mon, 12 March 2007 10:47

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I wanted to know also that CLOB datatype would be handled through export/import or moving data from one tablepscae to another tablepsace using Oracle 8.1.7

Please send your reply.

Report message to a moderator

Previous Topic:	DataTypes
Next Topic:	Moving data across datafiles

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:40:34 CDT 2024