Release unused space of USERS tablespace in oracle

0
votes

I have lots of table with lots of records in oracle 11g. (more than 2 billions) After applying some queries and creating some indexes I am so close to insufficient disk space. Right now for executing each query ORA-01652 error for USERS tablespace appears. I cannot add more datafile to USERS tablespace anymore because of insufficient disk space. I am sure that there are lots of unused space available on this tablespace that is not usable somehow. (I deleted some tables and indexes nothing happened) My question is how can I release this space? Thank you very much.

1
oracleoracle11gtablespace
identify the largest tables, send an email threatening to drop or truncate them, and see who complains. Good chance you'll be able to recover a good chunk of space ;) – tbone

1 Answers

2
votes

I don't know if you can to it for an entire tablespace but for a single table the command is:

ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT; -- By defaut ROW MOVEMENT is disabled when you create a table.
ALTER TABLE MY_TABLE SHRINK SPACE CASCADE;
ALTER TABLE MY_TABLE DEALLOCATE UNUSED;

Maybe you have to loop over ALL_TABLES in your schema.

Then you can gain disc space by rebuilding your indexes.

ALTER INDEX THE_INDEX REBUILD;