-
Oracle not releasing space after DROP TABLE
We have a few big tables in a schema. Now when we are dropping the tables the space acquired by the tables is not released by ORACLE. When we try to resize the datafile of that schema, it is not allowing us to do it and throwing an error.
But we need the space to be released and allocated to some other schema.
We are using ORACLE 9.2.0.5 on LINUX AS 2.1 on ITANIUM server.
Please give and workaround for this problem.
Thanks in advance,
Anomitro
-
have you tried coalescing the tablespace to try and get the space used own.
You dont mention if you are using LMT's or not
-
-
Hello All,
I'm using LMT.
I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:
ORA-03297: file contains used data beyond requested RESIZE value
And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.
I've tried ALTER TABLESPACE .. COALESCE , but the result is same.
I've time and space constraints so I cannot do an EXP & IMP to release space.
Please help me to solve the problem.
Thanks,
Ano
-
create a new tablespace
alter table xyz move new tablespace
alter index xyz rebulid new tablespace
drop old tablespace
rm datafiles
Jeff Hunter
-
My tablespace almost 100GB and I cannot create another tablespace to move the tables due to space problem.
I've restarted the database but it didn't release the space.
Now I'm creating 1 small tablespace and moving 1 table to see whether it is releasing the space from the old tablespace.
ANY OTHER WORKAROUND TO RELEASE SPACE ????
Thanks,
Ano
-
there is free space, but not at the end of the datafile - you have to create another tablespace and move them
-
Originally posted by Anomitro
Hello All,
I'm using LMT.
I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:
ORA-03297: file contains used data beyond requested RESIZE value
And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.
I've tried ALTER TABLESPACE .. COALESCE , but the result is same.
I've time and space constraints so I cannot do an EXP & IMP to release space.
Please help me to solve the problem.
Thanks,
Ano
If you don't have the space, then you are stuck. Did someone ask you to do this online?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..
Abhay.
Yeah, that might help. Remember to rebuild the unusable indexes (if any).
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
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
|