compress all the tables inside a tablespace

1-calculate all the tablespace tables sizes before compression:

select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='TABLESPACE_NAME' and segment_type='TABLE' group by segment_name,owner order by 1;

2-generate a script to compress all the tables inside the tablespace given that the table was not compressed before:

 select 'Alter table '||owner||'.'|| table_name||' move compress;' from dba_tables where TABLESPACE_NAME=' TABLESPACE_NAME ' and compression='DISABLED';

3-rebuild all the indexes that was unusable due to the compression:

  select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name=' TABLESPACE_NAME ' and segment_type='TABLE' group by segment_name,owner order by 1;

4-calculate the sizes of all the tables inside the tablespace after compression:

select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name=' TABLESPACE_NAME ' and segment_type='TABLE' group by segment_name,owner order by 1;

Don't  forget to rebuild all unusable indexes after finishing this process:

select 'Alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE'

pelase note that compression is useful when you have alot of redundant data available and will be so beneficial.