| Does CTAS uses unused space?? [message #430762] |
Thu, 12 November 2009 04:54  |
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
|
Junior Member |
|
|
I have been using CTAS option for very big size tables. But I have few queries on the space utilization while using CTAS option.
Does it reuses the fragmented space in the tablespace if there is any. I mean to say if my table space is fragmented due to frequent addition and deletion of objects like tables and not shrinked then obviously there would be some unused space left. If I then use CTAS option to create new table then would it use that unused space or it would simply use new space leaving the defragemented or unused space untouched.
Any suggestions would really be helpful.
Regards,
Sameer.
|
|
|
|
|
|
| Re: Does CTAS uses unused space?? [message #430787 is a reply to message #430762] |
Thu, 12 November 2009 05:58  |
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
|
Junior Member |
|
|
Thank you for the quick reply,
I think I may not have explained my problem well. I will try this time in detail.
Actually what happened is that I have a schema which was occupying space of approx 150 gb. On checking its actual used space from dba_segments I found that it only used 70 gb. Basically I use ctas to populate tables into this schema from other schemas.
Could it be the case that ctas operation is resulting in the unexpected growth in the size of this schema? Or could it be simply because ctas is not using the free space which may have been there because of deletion of older tables in the schema where I have not done shrink operations...
Here's the sample block of code I have used..
CREATE TABLE abc.xyz
STORAGE (INITIAL 10M NEXT 1M MAXEXTENTS UNLIMITED)
PARALLEL (DEGREE 4)AS
SELECT a.*
FROM
def.xyz a,
ghi.pqr b
WHERE a.er= b.er
NB:The real code is a bit more dynamic than this.
Regards,
Sameer
|
|
|
|
|
|
| Re: Does CTAS uses unused space?? [message #431048 is a reply to message #430825] |
Fri, 13 November 2009 17:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Or, put more simply, space freed up by dropping objects can be used by CTAS, and in fact by any other statement that creates a segment.
Just make sure your tablespaces are Locally Managed, not Dictionary Managed.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
