reclaim space from nonsystem tablespace

1. create a new tablespace
create tablespace move_users datafile '/u01/datafile/move_users_01.dbf' size 10m autoextend on maxsize 30G extent management local segment space management auto;

2. grant quota on move_users
select 'alter user ' || owner || ' quota unlimited on move_users;'
from dba_segments
where tablespace_name='TBS_NAME'
and segment_type in ('TABLE', 'INDEX')
group by owner
;

run the output.

3. move segments out of table space TBS_NAME
spool MOVE_SEG.sql
select 'alter table '||owner||'."'||table_name||
'" move lob('||column_name||') store as (tablespace move_users);'
from dba_lobs
where tablespace_name = 'TBS_NAME'
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" move nologging tablespace move_users;'
from dba_segments
where tablespace_name = 'TBS_NAME'
and segment_type in ('TABLE')
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" rebuild nologging tablespace move_users;'
from dba_segments
where tablespace_name = 'TBS_NAME'
and segment_type in ('INDEX')
;
spool off

run @MOVE_SEG.sql

4. check if TBS_NAME is empty
select segment_type, owner , segment_name
from dba_segments
where tablespace_name = 'TBS_NAME'
;

5. resize datafile of TBS_NAME
select 'alter database datafile ' || '''' || file_name || '''' || ' resize 10m ;'
from dba_data_files where tablespace_name = 'TBS_NAME';

run output.

6. move segment back to TBS_NAME
spool MOVE_BACK_SEG.sql
select 'alter table '||owner||'."'||table_name||
'" move lob('||column_name||') store as (tablespace TBS_NAME);'
from dba_lobs
where tablespace_name = 'MOVE_USERS'
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" move nologging tablespace TBS_NAME;'
from dba_segments
where tablespace_name = 'MOVE_USERS'
and segment_type in ('TABLE')
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" rebuild nologging tablespace TBS_NAME;'
from dba_segments
where tablespace_name = 'MOVE_USERS'
and segment_type in ('INDEX')
;
spool off

run @MOVE_BACK_SEG.sql

7. check if MOVE_USERS is empty
select segment_type, owner , segment_name
from dba_segments
where tablespace_name = 'MOVE_USERS'
;

8. resize MOVE_USERS
select 'alter database datafile ' || '''' || file_name || '''' || ' resize 10m ;'
from dba_data_files where tablespace_name = 'MOVE_USERS';

run output

9. drop MOVE_USERS
drop tablespace MOVE_USERS including contents and datafiles;