Shrink datafile space from dropped table

Shrink datafile space from dropped table
==================================================

select sum(bytes) / 1024 / 1024 / 1024 from dba_segments where tablespace_name='USR';
114.127 GB

Select Sum(Bytes) / 1024 / 1024 / 1024 From V$datafile Where Name Like '%USERS01%';

251.13 GB

Select Sum(Bytes) / 1024 / 1024 / 1024 From Dba_Free_Space where Tablespace_Name='USR';
137 GB

137 GB free space, how to shrink it.



==================================================

ALTER TABLE XYZ ENABLE ROW MOVEMENT;
ALTER TABLE XYZ SHRINK SPACE CASCADE;

alter database datafile 'D:\ARID0910\USERS01.ORA' resize 102400M;


===================
create a temperory table space.
move all three user to the new tablespace
move all the tables to new tablespace.
now drop the old tablespace and
create a new table space with same name and
restore all ur user and tables.



====================================================



-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;


====================================================

you have two options besides import/export. Basically, only the first step differs:
1. As Douglas Paiva de Sousa stated, you can use DBMS_REDEFINITION to re-create the objects left in the tablespace to the same tablespace or a new one;
2. Without DBMS_REDEFINITION you can create an old-fashioned script based on the dictionary views that moves all the tables (and indexes if there is any in there) left in that tablespace to another one with ALTER TABLE MOVE TABLESPACE. The indexes must be rebuilt afterwards as they become invalid in the process.

If You moved the objects to a new tablespace, then the next step is to rename the new tablespace to the old one.

As a last step the datafile size should be changed to a lower value; at that point will only the Oracle DB release physical disc space for the OS.

The first one is better from the point of view of system accessibility as this can be done while the system using the given tables is online;
the second possibility may render that system useless so it requires them to be offline.


=======================================================


You can use the free space in the tablespace for new extents, but if You want to release it to the Operating System You must do some kind

of migration of the data.
Either while the system(s) using the tablespace are online or while offline.
The second is much more easier, as You can generate the script to do that from the data dictionary-but You must have enough free space in

the OS to hold one more copy of them and the accessing system(s) must be offline.
So this will be a planned downtime for them...

The steps in more detail:
create tablespace users2 ...

run the following query, then execute its results:

select 'ALTER TABLE ' || o.owner || '.' || o.TABLE_NAME || ' enable row movement; '
from dba_tables o
where o.TABLESPACE_NAME = 'USERS'

then execute the following query's results:

select 'ALTER TABLE ' || o.owner || '.' || o.TABLE_NAME || ' move tablespace users2; '
from dba_tables o
where o.TABLESPACE_NAME = 'USERS'

in case there are indexes in the TS also, run query and execute results:
select 'ALTER INDEX ' || o.owner || '.' || o.INDEX_NAME || ' rebuild tablespace users2; '
from dba_indexes o
where o.TABLESPACE_NAME = 'USERS'

then run the last two queries changed for the original users TS in order table, index to move them back (OR simply rename the new

tablespace to the old one if You checked aand it contains nothing).
After that You can do the datafile resize if it is necessary.

As a last step You should check the SPs, packages and functions for invalidity.


==============================================================

If YES, when you drop a table, its goes to "recyclebin" for "flash-back transactions", then you need to PURGE the table from recyclebin.

to list tables on recyclebin:
select object_name from recyclebin;

to clear users area(ALL objects on recyclebin):
PURGE RECYCLEBIN; (need sysdba privs)

to clear only a table:
PURGE TABLE TABLE_NAME;

==============================================================


To resize a datafile, you need to free the last blocks on the file, lik this:
A=table A
B=Table B
C=Index C
F=Free space
Datafile_blocks= AAABBBBBAABBCFFFFCCB

Droping table "A" you get:
FFFBBBBBFFBBCFFFFCCB

Then you get free space on Database, but not on Filesystem.

Moving segments "C" and "B" to another tablespace or to first blocks you can resize de datafile.

use this scritp to MAP the datafile segmets:
select file_id, block_id first_block, block_id+blocks-1 last_block,substr(segment_name,1,20) SegName
from dba_extents
where tablespace_name = 'USR' /*tablespace name*/
and file_id=5 /*id of datafile, see on dba_data_files table*/
union all
select file_id, block_id, block_id+blocks-1, 'FREE'
from dba_free_space
where tablespace_name = 'USR'
and file_id= /*Id of datafile*/
order by file_id, first_block

==============================================================