R12 Tablespace maintanance

How to maintain tablespaces in Oracle Apps R12

--check the segment size in specific tablespace
-----------------------------------------------------------------

col username format a8 justify c heading 'Username'
col extents format 999,999,990 justify c heading '# of Extents'
col segment_type format a15 justify c heading 'Segment Type'
col segment_name format a28 justify c heading 'Segment Name'
col kbytes format 999,999,990 justify c heading 'KB Used'
select
owner username,
segment_name segment_name,
segment_type segment_type,
bytes/1024 kbytes,
extents extents
from
dba_segments
where
tablespace_name = 'APPS_TS_TX_DATA'
order by
kbytes asc
/

select extent_id, file_id, block_id, blocks from dba_extents
where owner=upper('APPLSYS') and segment_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

--check if segments are QUEUE type or if they have type LONG columns. If this queries return results then you should not touch this segments.
-----------------------------------------------------------------

select queue_table from dba_queue_tables
where owner=upper('APPLSYS') and queue_table = upper('WF_ITEM_ATTRIBUTE_VALUES');

select table_name, column_name,data_type from dba_tab_columns
where owner=upper('APPLSYS') and table_name = upper('WF_ITEM_ATTRIBUTE_VALUES')
and data_type in ('LONG','LONG RAW');

--Check the segment type
-----------------------------------------------------------------

select segment_name, segment_type from dba_segments
where owner=upper('APPLSYS') and segment_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

--If segment type is table them move it and rebuild indexes
-----------------------------------------------------------------

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES move;

select owner, index_name, status from dba_indexes
where table_owner = upper('APPLSYS') and
table_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

alter index APPLSYS.WF_ITEM_ATTRIBUTE_VALUES_PK rebuild;