CODEWITHSUNDEEP
oracle-databaseoracle11gtablespace
Ali
Ali

Reputation: 1879

Release unused space of USERS tablespace in oracle

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.

Upvotes: 0

Views: 3912

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

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;

Upvotes: 2

Related Questions