In our company we have a test server on which Oracle RDBMS 11g is installed. My question is related to reclaiming wasted space.

I ran the following query:

   table_name,round((blocks*8),2) "size (kb)" , 
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

...which shows me the total wasted space for each table. So we have approx. 84 GB of wasted space overall in our database.

I want to know how to reclaim this wasted space without being able to use Enterprise Manager, because I don't have access to EM.

I have also noticed that the indexes on the database were last rebuilt in 2010. Could rebuilding the indexes solve the wasted space problem?

  • You can also use this to reorganize the table ALTER TABLE <table_name> MOVE – Welsige Sep 10 '19 at 19:41
  • But @Welsige table size remains the same IIRC, and the question isn't about reorganizing, it's about reclaiming space. – Aaron Bertrand Sep 10 '19 at 21:47

This is all covered in the documentation, but basically:

  • Enable row movement for each table using ALTER TABLE ... ENABLE ROW MOVEMENT;
  • Shrink the object (TABLE/MATERIALISED VIEW/etc) with ALTER <object type> <object name> SHRINK SPACE;
  • Resize the tablespace datafiles with ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' RESIZE 500M;

Tim Hall covers this in great length, with every conceivable option, in this article - a great resource.

  • I have so many tables and also list of datafiles which needs to be shrink, It will be time consuming. So is there any other way through which i can shrink whole database? – Chris Pop Jul 25 '18 at 10:19
  • Script it by generating the DDL with a query. – Philᵀᴹ Jul 25 '18 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.