Advanced Compression in 11G

Script to find the Compression eligible objects
==================================================

set linesize 150
set pagesize 100
col owner format a15
col segment_name format a32
col segment_type for a15
col tablespace_name format a20
set lines 100
select     owner,
segment_name,
segment_type,
tablespace_name,
Size_in_GB
from    (select owner,
        segment_name,
        segment_type,
        tablespace_name,
        round((bytes/1024/1024/1024),2) Size_in_GB
   from    dba_segments
   where   owner not in ('SYS','SYSTEM')
   and segment_type in ('TABLE')
   and segment_name not in (select table_name from dba_tables where (compression='ENABLED' or table_name like '%$%'))
   order by 5 desc)
   where rownum <= 40
/

Use below command to Move the obejctes
=======================================

alter table APPLSYS.WF_NOTIFICATION_OUT move compress for OLTP;

Check the UNUSABLE indexes and rebuild
=======================================

select 'alter index ' || TABLE_OWNER || '.' || INDEX_NAME || ' rebuild parallel 4 ;' from dba_indexes where status='UNUSABLE';

run gather stats on compressed objects

Monitor the respective tablespaces and temp tablespace ,and check pre and post invalids

li.sh  --to check the invalids
======

echo "Purpose: To spool out the invalid objects before and after applying the patch"
if [ $# -ne 2 ]; then
        echo "Usage: $0 <appspass> <pre|post>"
        exit 1;
fi
genInvList(){
USER=apps
PASS=$1
TYPE=$2
DATEFMT=`date +%Y%m%d-%H%M`

if [ "x$ORACLE_SID" = "x" ]; then
        if [ "x$TWO_TASK" = "x" ]; then
                echo "set up the env first, duh!"
                exit
        else
                DB=`echo $TWO_TASK`
        fi
else
        DB=`echo $ORACLE_SID`
fi


sqlplus -s /nolog<<_END_
conn ${USER}/${PASS}@${DB}
set pages 10000
col owner               for a14
col object_type for a20
col status              for a10
col OBJECT_NAME for a30
spool ${DB}-${TYPE}-invalid-${DATEFMT}.lst
select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
select instance_name,status from v\$instance;
select owner, object_type, object_name
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
select owner, count(status)
from dba_objects
where status = 'INVALID'
group by owner;
select 'Total Invalids: '||count(*) from dba_objects where status != 'VALID';
spool off
exit
_END_
}
genInvList $1 $2

exp : li   appspwd     nameof thefile