Home » RDBMS Server » Server Administration » Shrinking database
Shrinking database [message #223552] Fri, 09 March 2007 07:35 Go to next message
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.
Re: Shrinking database [message #223554 is a reply to message #223552] Fri, 09 March 2007 07:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.

Re: Shrinking database [message #223563 is a reply to message #223552] Fri, 09 March 2007 09:44 Go to previous messageGo to next message
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.

Re: Shrinking database [message #223565 is a reply to message #223563] Fri, 09 March 2007 09:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Shrinking database [message #223643 is a reply to message #223552] Fri, 09 March 2007 23:45 Go to previous messageGo to next message
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.


Re: Shrinking database [message #223972 is a reply to message #223643] Mon, 12 March 2007 08:17 Go to previous messageGo to next message
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.
Re: Shrinking database [message #223978 is a reply to message #223972] Mon, 12 March 2007 08:57 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, they will.
Re: Shrinking database [message #223979 is a reply to message #223643] Mon, 12 March 2007 08:57 Go to previous messageGo to next message
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
Re: Shrinking database [message #223983 is a reply to message #223979] Mon, 12 March 2007 09:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nothing specific in Oracle.
Re: Shrinking database [message #223999 is a reply to message #223552] Mon, 12 March 2007 10:47 Go to previous message
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.
Previous Topic: DataTypes
Next Topic: Moving data across datafiles
Goto Forum:
  


Current Time: Wed Dec 07 08:34:35 CST 2016

Total time taken to generate the page: 0.09234 seconds