-
Reallocate extents - possible ?
Hi ! I have an unusual problem here, let me expose it : each month my data goes to a tablespace specific for the current month : so, the data referring january/2005 are being inserted/updated/deleted in a tablespace named TS_200501, and when February comes, a tablespace TS_200502 will be made, and no more DMLs will occurs against January data. So, to save space (the datafiles for these things are very huge)in February 1st I want to shrink the datafiles for January’s tablespace.
The problem here is : sometimes, in this tablespace are made INSERTs for a table (say, TABLE A), and this occupies (say) extents 001 from 099 in a data file. Later, more huge INSERTs are made for table B, occupying extents 100 to 1500 (in the same data file, say). Later yet another small INSERT is made in TABLE A, occupying extents 1501 to 1502, and finally TABLE B data is deleted. You can see the problem now : due only to the extents 1051 & 1502, I can’t shrink these data file to fewer than the space occupied by the last extent, but there is a LARGE unused space in this data file, which I want to free back to disk, to use in others tablespaces. The common answer is "MOVE TABLE A", but the system is intensive OLTP (very few off-time allowed), and A is constantly being referenced, A have many indexes (they will be invalidated) , so MOVE is not an option. What I can do ?
Regards,
Chiappa
-
What is your oracle release?
Tamil
-
Ops, sorry : 9ir2 EE here.
-
Re: Reallocate extents - possible ?
Originally posted by JChiappa
What I can do ?
Buy more storage. If you had a volume for every period dedicated to that period, then you would not worry about some extra gigs not being used. What is more important saving disk space or performance and uptime? I assume that you keep previous months data online so it can be queried. If so separating it on different volumes would potentially spread out the load.
Are you using partitioning? It would seem that it would be helpful in this case.
-
I remember when this place was cool.
-
Use dbms_redefinition pkg for re-orging the table.
Then shrink your tablespace's data files.
Tamil
-
Gandolf, yes, "buy more storage" is the default option, but I have two full-years of data here, so "a few Gbs" in Jan, a little more in Feb, etc, etc, at the end represents a quite significative amount. And more : if I show the space saving´s figures, surelly my damagement WILL give to me some "off-peak time" to do it.
Mr. Hanky : yes, partitioning can be useful here (I must yet rebuild/move with this option to free the space, but just last month´s partition. But, while not partitioned, , I found a better option (apparently) : 9i gave to us DBMS_REDEFINITION, and according to the docs (and the tip in http://groups.google.com/groups?selm...&output=gplain ), with it we can online "move" a table : I will test, let´s see if it works in my case!
Regards,
Chiappa
-
Surely you are already using partitioning though -- how else are you sending different rows to different tablespaces? Unless you are using one table per month, of course.
-
How about "ALTER TABLE my_table shrink space" ?
The above statement will work only in ASSM.
Tamil
-
And how wil the above suggestion help when he specificaly mentioned:
Originally posted by JChiappa
Ops, sorry : 9ir2 EE here.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|