Shrinking of Database
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.
Did you consider "alter table ... shrink space cascade" command"?
I think this feature is available in Oracle 10g.
Currently, We have Oracle 18.104.22.168.0
Can you please let me know how to reset HWM and collect stats again.
I think that online redefinition methods also (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 input would be highly appreciated.
1. Why would you want to do this?
Simply dropping an partition is fine. The space will be re-used later by any new partitions or objects that you have or will create.
"My main concern is to re-claim the space and improve performance by shrinking database."
Shrinking the Database won't increase your performance. Do you have a Performance problem currently?
Obviously you do not use partitioning, which is highly recommended in such cases.
If I was you, I would delete the data and then move the segments in a new tablespace (assuming you have enough space to do that, since for a while you will have to keep the moved data and the old tablespace) and then drop the old tablespace.
Alter table move tablespace should do it ( although I do not remember if it is present in 8.1.7)
I agree with ixion that that will not definitely lead to better performance, but should definitely speed up the full table scanns
I have decided to perform this steps to achieve my goal
A. Create a new tablespace TEMP_DATA with autoextend option of datafile
B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily
C. Truncate all the tables from current schema
D. Resize the current tablespace, if required.
E. Move the data from TEMP_DATA back to the current
F. Re-build all the concern unusable or invalidated
G. Drop the TEMP_DATA tablespace
I wanted to verify whether step C is required to follow or not.
I beleive that export/import is an alternate and better option also for above steps.
Please send your comment/suggestion/feeback on the same.
Your opinion would be highly appreciated.
1) You do not need to move the data back to the original tablespace. You just "alter table TABLE_NAME move tablespace NEW_TABLESPACE_NAME"
and that for all the tables. This creates new segment, moves all the data in the new segment and finally drops the old one.
Then you drop the old tablespace.
Now if you want to move the datafiles back to the original disk just
alter tablespace NEW_TABLESPACE offline
move the files to the original disk
alter tablespace NEW_TABLESPACE rename datafile 'old name' to 'the new path and name";
alter tablespace NEW_TABLESPACE online;
The only difference is that the tables will now reside in a tablespace with a different name, which does not actually makes any problems
This will save one data moving.
2) I am not really sure that exp/imp will be more efficient. I even seriously doubt it
I would recommend to look at the documentation about the alter table move tablespace command. I belive it can be done on-line and I guess the indexes becomes unusable
Yes, It can be done online using below statement
ALTER TABLE tablename MOVE TABLEPSACE
But, if it does not have enough capacity or space then it needs to move to another tablespace.
I would like to know also that Oracle 8.1.7 can handle also CLOB datatype using the mentioned methids for moving data from one tablespace to another.
Please make your comment on it.
Click Here to Expand Forum to Full Width