purging sql

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/


select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD PARALLEL 16;'  from dba_indexes where status='UNUSABLE';
select 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild PARTITION '||PARTITION_NAME||' PARALLEL 16;'  from dba_ind_partitions where status='UNUSABLE';
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' NOPARALLEL;'  from dba_indexes where status='UNUSABLE';
select 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' NOPARALLEL;'  from dba_ind_partitions where status='UNUSABLE';			


begin
dbms_stats.gather_table_stats(OWNNAME => 'NPCSPROD', TABNAME  => 'RECONCILED_TXN' ,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, granularity => 'auto', CASCADE => TRUE, DEGREE => 10);
end;
/


set lines 200
col SEGMENT_NAME format a40
set pages 200
select owner, segment_name, segment_type, round(sum(bytes)/(1024*1024),0)"SIZE MB" from dba_segments 
where owner='SIEBEL' group by owner, segment_name, segment_type order by "SIZE MB";