First ensure the tables has statistics collected accurately For that user. If not the following queries may not have results accurately. ################################################################################ 1. Find the tablespace usage ################################################################################ SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC; ################################################################################ 2. Find the row chaining on the objects belongs to that tablespace or user (logical) ################################################################################ CLEAR SET HEAD ON SET VERIFY OFF set pages 100 set lines 79 PROMPT PROMPT Table Chaining Report in tablespace PROMPT col owner form a12 col table_name form a20 col empty_blocks form 999,999 heading "Empty Blks" col blocks form 999,999 heading "Blks" col pct form 99 select owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks from dba_tables where chain_cnt > 0 and owner='&Username' / #################################################### 3. Find the table level fragmentation (logical) #################################################### select table_name,round((blocks*8),2) "size (mb)" , round((num_rows*avg_row_len/1024/1024),2) "actual_data (mb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024/1024),2)) "wasted_space (mb)", ((round((blocks*8),2) - round((num_rows*avg_row_len/1024/1024),2))/round((blocks*8),2))*100-10 "Reclaimable Space (mb)" from dba_tables where owner='&Username' and ((round((blocks*8),2) - round((num_rows*avg_row_len/1024/1024),2))/round((blocks*8),2))*100-10 > 10 order by 4 desc; (if the above query does not work, keep your query) ##################################################################### Next Step : To clear logical fragmentation found in step 2 and step 3 1. Take the top 10 tables that found in step 2 2. Take the top tables or all tables that found in step 3 ##################################################################### Before performing online move for tables above. check limitations for move/shrink. ########################################## Limitations with online move/shrink:- ########################################## If you see any of the tables part of the following queries then do not perform the shrink/move operation, its best to use export/import. --Tables that are part of a cluster --Certain types of materialized views --Certain types of IOTs. --Replace the tablenames below. SQL> select owner,object_name,object_type from dba_objects where owner='&Username' and object_name in ('&Tablename'); --Tables with LONG columns SQL> select owner,object_name,object_type from dba_objects where owner='&Username' and object_name in ('&Tablename') and object_name in (select table_name from dba_tab_columns where DATA_TYPE='LONG'); --Tables with function-based indexes. SQL> select owner,object_name,object_type from dba_objects where owner='&Username' and object_name in ('&Tablename') and (select table_name from dba_indexes where index_type in ('FUNCTION-BASED NORMAL','FUNCTION-BASED BITMAP')); If you see the table is not part of any above then proceed to shrink below. ######################################################################## Next Step: Perform the following for each table WARNING: Watch out the temporary tablespace free space during the activity ######################################################################## ALTER TABLE OWNER.TABLENAME ENABLE ROW MOVEMENT; ALTER TABLE OWNER.TABLENAME MOVE; (in 10g or 11g - tables will be locked during whole period) ALTER INDEX OWNER.INDEX REBUILD ONLINE; (indexes need to be rebuild ) or ALTER TABLE OWNER.TABLENAME SHIRNK SPACE CASCADE; -- (in 10g or 11g less locking than ""move"" preferably for small tables etc and also indexes will be rebuild.) ALTER TABLE OWNER.TABLENAME DISABLE ROW MOVEMENT; ######################################################################## Verify Step:- Once all tables completed, run the query in Step 1 and 2 and see Chaining/Fragmentation might have gone. Tablespace usage and free space may not have changed. Dont worry about that, it can be deal with all objects in that tablespace to rearrange or move them not few like above. #########################################################################