Originally posted by kpate
DBA before me did unlimited on tablespace and now one of the tablespace has reached to
12 g
Unlimited... on whay. Tablespace default parameter, datafile size...

12 GB.. is this for a particular file or the database on a whole?

I'd repost the results of the following queries to give us a larger understanding in what you are delaing with. A simple Import/Export, probably won't effectiviely deal with future fragmentation.

SELECT
t.tablespace_name "Tablespace",
t.status "Status",
ROUND((MAX(d.bytes)/1024/1024) - (NVL(SUM(f.bytes),0)/1024/1024),2) "Used MB",
ROUND(NVL(SUM(f.bytes),0)/1024/1024,2) "Free MB" ,
t.initial_extent "Initial Extent",
t.next_extent "Next Extent",
t.min_extents "Min Extents",
t.max_extents "Max Extents",
t.pct_increase "Pct Increase" ,
SUBSTR(d.file_name,1,80) "Datafile name"
FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id
GROUP BY t.tablespace_name , d.file_name , t.initial_extent , t.next_extent ,
t.min_extents , t.max_extents , t.pct_increase , t.status
ORDER BY 1,3 DESC;

Select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) "MB", initial_extent, next_extent, extents from dba_segments
where owner not in ('SYS', 'SYSTEM')
and extents > 100;

Cheeers,