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

Here is very article go through it

http://asktom.oracle.com/pls/ask/f?p...:153612348067,

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

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

Quote:

---

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?

Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..

Abhay.

Quote:

---

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).

I've seen some code around to determine the table that occupies the top-most block (away from the office - I don't have it to hand).

I think I'd look at the size of the top-most table compared to the size of the free space when moving within the same TS. If the table is bigger than free space "lower down" the TS, then moving it will push up the high-water mark of the TS - this isn't what you want.