Query on Tablespace Partitions.

The below script is helpful in case if you want to move partitions or partition index to a new tablespace and dynamically delete old partition from the oracle database or in the case when you want to change your table's default tablespace.
Find all object in a Tablespace:
Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments
where TABLESPACE_NAME = 'SDH_HRMS_DBF'
group by OWNER, SEGMENT_NAME;
Dynamically Move Index to a new Tablespace:
select 'Alter index '||INDEX_OWNER||'.'||INDEX_NAME||'
rebuild partition '||PARTITION_NAME||' tablespace CDR_DETAIL_INDEX1;'
from dba_ind_partitions where TABLESPACE_NAME = 'DW_CDR_DETAIL';
Change a table’s default tablespace:
ALTER TABLE {OWNER}.{TABLE NAME}
MODIFY DEFAULT ATTRIBUTES TABLESPACE {NEW TABLESPACE NAME};
This does not move the table it only changes the attribute for the default tablespace.
This is helpful if you are adding partitions, but not specifying the tablespace.
Dynamically Move Partitions To A New Tablespace:
------------------------------------------------
SELECT def_tablespace_name FROM dba_part_tables
where owner = 'O1DW'
and table_name = 'ACME_FAILED_DETAIL';
Change a partitioned table’s tablespace:
ALTER TABLE O1DW.ACME_FAILED_DETAIL
MODIFY DEFAULT ATTRIBUTES TABLESPACE ACME_FAILED_BIG1;
Move existing partitions to a new tablespace and compress the data:
select 'ALTER TABLE '||table_owner||'.'||table_name
||' move partition '||partition_name
||' TABLESPACE ACME_FAILED_BIG1 COMPRESS FOR ALL OPERATIONS update indexes NOLOGGING;'
from dba_tab_partitions
where table_owner = 'O1DW'
and table_name = 'ACME_FAILED_DETAIL'
and TABLESPACE_NAME <> 'ACME_FAILED_BIG1'
order by PARTITION_POSITION;

Dynamically Delete Old Partitions
---------------------------------
select 'ALTER TABLE '||table_owner||'.'||table_name
||' DROP PARTITION '
||partition_name||’ UPDATE INDEXES;’
from dba_tab_partitions
where table_owner = 'O1DW'
and table_name = 'ACME_FAILED_DETAIL'
order by PARTITION_POSITION;
Modify to your specific needs.

Which schemas are taking up all of the space
-------------------------------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner", obj_cnt "Objects",
decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
 (select owner, ceil(sum(bytes)/1024/1024) seg_size
  from dba_segments group by owner) seg
  where obj.owner  = seg.owner(+)
  order by 3 desc ,2 desc, 1;