|
Replies:
26
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Jun 10, 2008 11:46 AM
Last Post By: Eduardo Legatti
|
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
shrink,alter table move
Posted:
Jun 9, 2008 12:52 AM
|
|
|
|
hi
This is the scenerio
a,b,c: distinct blocks
f:free blocks
Assume the extent is like:
aaafffccccbbfb
after shrink does it become like
aaabbbccccffff
or
aaafffccccbbbf
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 1:14 AM
in response to: John Carew
|
|
|
|
Hi,
It becomes
aaabbbcccc"HWM"ffff
Aman....
|
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 1:26 AM
in response to: Aman....
|
|
|
|
alter table move also performs same task right?
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 1:44 AM
in response to: John Carew
|
|
|
|
Yes it does but it also invalidates the indexes.The Shrink command doesn't do that.And its an online operation as comparable to table move.
Aman....
|
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 1:51 AM
in response to: Aman....
|
|
|
|
How about this?
aaffcbbbb
after shrink or alter table move does it become like:
aabbcbbff
|
|
|
Posts:
19,251
Registered:
08/22/02
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 1:53 AM
in response to: John Carew
|
|
|
|
Didn't you get the answer in one of your [url=http://forums.oracle.com/forums/search.jspa?threadID=&q=shrink&objID=c84&dateRange=all&userID=Ricardinho&numResults=15]numerous previous thread[/url] on same subject, did you ?
Nicolas.
|
|
|
Posts:
4,830
Registered:
07/22/07
|
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 2:57 AM
in response to: Pavan Kumar
|
|
|
|
but this scenorio is diffrent
aaffcbbbb
after shrink or alter table move on block b
does it become like:
aabbcbbff
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 3:14 AM
in response to: Pavan Kumar
|
|
|
|
Pavan,
You misunderstood me.What I meant to say was that don't post any link which has Oracle documentation hosted over it like many university site do.The docuementation hosted there can't be checked for updates and for errors.So for posting Oracle documentation links,use Oracle doc site only ie tahiti.oracle.com.
Rest post any link which can give idea/reference/suggestion/help the OP.
Aman....
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 3:15 AM
in response to: John Carew
|
|
|
|
Ricardinho,
Check the link that Pavan has given and with that check back the same topic in oracle docs.
Aman....
|
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 3:21 AM
in response to: Aman....
|
|
|
|
I checked that link.
But couldnt find solutıon to my last questıon
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 3:32 AM
in response to: John Carew
|
|
|
Ricardo,
Both the things are same.They both are used to remove the fragmentation.The way they work may be different but the output of either will be the same.Free space will be on one side only ie after the hight water mark.You do it by move or by shrink,you will end up with the same thing.You put free space anywhere in the scenerio,it will come up to one end only.If it wont than I guess we are back from where we started.
That said,check back the Oracle doc for both the commands.Also search for the same topic in http://hemantoracledba.blogspot.com/2008/03/alter-table-shrink-space.html
HTH
Aman.... |
|
|
Posts:
1,657
Registered:
06/13/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 7:01 AM
in response to: Aman....
|
|
|
|
The usage of my tablespace was 100%.
After I perform full table shrink.60gb free space obtained.
Despıte there ıs empty space ın the datafıle, I coulnt resıze it.
I could only resıze it by performing alter table move.
I thought there ıs no dıffrence between shrink and alter table move, except index recreatıon.
so why?
|
|
|
Posts:
9,551
Registered:
05/20/01
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 7:06 AM
in response to: John Carew
|
|
|
|
Interesting.
Can you post a small "working demo" for the same?I shall try that later in the night myself and will try to reply.
Aman....
|
|
|
Posts:
404
Registered:
05/03/07
|
|
|
|
Re: shrink,alter table move
Posted:
Jun 9, 2008 7:21 AM
in response to: John Carew
|
|
|
Hi Aman
They both are used to remove the fragmentation
Aman i think shrink is only valid in ASSM. it does not reduce fragmentation. it just deallocate empty space from the extent. If an extent is totally empty it will drop it. The reason this feature is support in ASSM is that ASSM can support extent of different sizes but its does create gaps in tablespace.
I think coalese is the command for fragmentation removal
please correct me if my concept is wrong.
regards
Nick |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
