This content has been marked as final. Show 19 replies
That's correct. The shrink space is alter table option. You need to
alter table .. moveto relocate the table segment.
>>Through shrinking table, we won't be able to really reduce the datafile high level water mark in order to resize it
I think that reduce the datafile it's possible, because that HWM are adjusted to an appropriate location and unused space is deallocated from the segments, so it is available for the tablespace to reallocate to other objects as needed ... Have you tried to make a test?
SHRINK may cause Oracle to deallocate one or more extents of a table.
However, remember that a tablespace contains multiple segments -- each table,
index, lobsegment etc is a segment with multiple extents.
Thus, at the datafile HWM, you could very well have extent(s) belonging to some
other table(s) apart from the one(s) you did a SHRINK for.
It would be those other extents which would prevent you from resizing the datafile.
>>It would be those other extents which would prevent you from resizing the datafile.
Ok, I mean if all segments contained in a tablespace has been shrunk, so, I think it's possible to reduce the size of the datafile ...
The catch is even each table segment have one extent but the location of this extent in the tablespace could still prevent resize the datafile.
For example, you have 100MB datafile. You create one table A with 50MB size, then added another table B with 50MB.
The rough datafile mapping will looks like,
AAAAABBBBBAssuming each character stand for 10MB.
After shrink space, the two tables are only 10MB each now, the datafile mapping become,
A----B----can you resize the datafile to 20MB now ? The answer is No.
Unless you alter table B move to 20MB mark, you still can only resize to 60MB.
>>Unless you alter table B move to 20MB mark, you still can only resize to 60MB.
Great explanation ... So, at least, to reduce the size of a datafile from 100MB to 60MB it's possible in this case.
If, after you go through all the effort to SHRINK all or most segments in the
tablespace (datafile), you find that you need to or you decide to do a MOVE,
it might have made more sense to have actually done the MOVE upfront.
A MOVE also does an implicit shrink, except that it requires downtime.
A SHRINK doesn't require downtime.
SHRINK Advantages :
downtime not required
SHRINK Disadvantages :
High undo and redo.
Cannot be done nologging.
There are other restrictions on which table can be SHRINKed (see the docs).
Might still require call(s) to MOVE if you want to resize the datafile(s).
MOVE Advantages :
can be done with nologging and parallel
avoiding undo with append
MOVE Disadvantages :
Requires double the space (if you do tables serially, it requires only as much as the largest table, not the sum of all tables)
Requires an index rebuild
all the advises really help especially example from yingkuan..
so i figure that the shrink only have the following advantages?
1. can be done online
2. increase peformance by reducing time taken for full table scan.
what other advantages which i missed out?
let's say right now my table has this mapping
b=empty - due to some delete
so now when oracle is trying to insert some record into the table, can it insert between the gap between the first a and 2rd a?
Yes, those free blocks will go to the freelist(according to the extent management option) and oracle will use for new inserts .
is it possible to do it in ASM? I mean is it possible to to resize/move ASM's datafiles on disks without requiring any downtime at prod database. I wonder it may be possible with some online mirroring algorithms or something like that
I have not come across that in ASM this thing is allowed that a DBA is allowed to move the files like in o/s.ASM already has the files in striped set so there wont be one complete file stored anywhere whch one can move and moreoever,in asm the striping is actualy with asm Allocation Units AU which are moved depending upon the hardware configuration on the fly to maintain the performance across the disk so I dont think that DBA has to be involved there.
Resizing of a file I guess should not be an issue as even in ASM the file is still a file which has allthe options available as like a data file on a nomral o/s.So that should not be a problem.
Thx, aman...as most of time i explained myself wrong.
Let say we are using usual file system files as storage. i mount a faster disc drive to my server and want to move some of my datafiles to this new drive. Is it possible to do it online? (i mean everything online)
Lets look from an other point of view, in a highend storage box, when you install new disks, you may mirror data to newly installed disks online and when synchronization completes you can remove old disks from mirror configuration. This allow you to migrate from a disk group to an other online.
We can assume OS drives as disks of ASM. Is it also possible for ASM to migrate data from one OS drive to an other without requiring any downtime at production database. Is there any method for this, like the one mentioned at hardware level.
What i want to achive is to migrate my physical data from an OS drive to another online
I understood you now cpmpletely.Sorry I have a real small brain so io is very slow :).To really tell you the truth,I don't have the idea at the moment but give me some time and I shall come back to you for this with some sure short answer.Till the time lets wait for others to share about it.
Believe me you have a nice and well tune brain. Other case probably you would be a politician, not dba:)