Space release plan with downtime from data files in oracle

 
Steps to release space from the datafiles of Oracle:
Note: You can also these steps when database is online in Enterprise Edition but some time when we move any object then its index become unstable So, we need to rebuild the index. Its better to take the following steps in scheduled downtime.
 
Step 1: Shutdown the database.

Shutdown immediate;

Step 2: Start the database in restricted mode. So no application user can interface.

startup restrict;

-- start the database in restrict mode. ( so no body should be able to do activity during maintenance)
-- Verified:
SQL>SELECT logins from v$instance;LOGINS
----------
RESTRICTED

Step 3: Created one new table space on DATABASE with new data-file.

create tablespace data_temp datafile 'E:\oracle\oradata\data_temp.dbf'
size 10M autoextend on next 10M maxsize 200M;

Check the size of data files and add more data files if need.

Step 4: Monitor the alert.log file in new session during this activity.

Step 5: Find the list of invalid object in the database.

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;

Step 6: Find the file_id for all datafiles present in that tablespace

select file_id from dba_data_Files where tablespace_name = 'DATA' ;

Step 7: Find the object list present in DATABASE present in datafiles.

set line 200 pages 200
column owner for a8
column segment_name for a25
select a.file_id,b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE,sum(b.bytes)/1024/1024 from dba_segments b,dba_extents a where
a.file_id in ( select file_id from dba_data_Files where tablespace_name = 'USERS') and
a.segment_name = b.segment_name and a.owner = b.owner
group by a.file_id,b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE
order by 1,5 desc;

-- Manually put file_id do one by one file as you wish
select b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE,sum(b.bytes)/1024/1024 from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143) and
a.segment_name = b.segment_name and a.owner = b.owner
group by b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE
order by 4 desc;

Step 8: Created a dynamic script for objects to move into new table space you created in previous step 3 (DATA TEMP).

select distinct 'Alter table '||b.owner||'.'||b.SEGMENT_NAME||' move tablespace DATA_TEMP;' from dba_segments b,dba_extents a where
a.file_id in ( select file_id from dba_data_Files where tablespace_name = 'USERS') and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type ='TABLE';

-- Manually put file id
select distinct 'Alter table '||b.owner||'.'||b.SEGMENT_NAME||' move tablespace DATA_TEMP;' from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143) and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type ='TABLE';


OUTPUT:

Alter table APPLSYS.FND_DOC_SEQUENCE_AUDIT move tablespace DATA_TEMP;
Alter table CE.CE_ARCH_INTERFACE_LINES move tablespace DATA_TEMP;
Alter table ONT.OE_ORDER_LINES_HISTORY move tablespace DATA_TEMP;
Alter table AR.HZ_CUST_SITE_USES_ALL_M move tablespace DATA_TEMP;
Alter table AR.HZ_CUST_PROFILE_AMTS_M move tablespace DATA_TEMP;
Alter table CZ.CZ_PRICING_STRUCTURES move tablespace DATA_TEMP;
Alter table APPLSYS.FND_STATS_HIST move tablespace DATA_TEMP;

Step 9: Script to generate alter for indexes present in tablespace.

select 'Alter INDEX '||b.owner||'.'||b.SEGMENT_NAME||' REBUILD tablespace DATA_TEMP;' from dba_segments b,dba_extents a where
a.file_id in ( select file_id from dba_data_Files where tablespace_name = 'USERS') and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type = 'INDEX';

--Manually for index
select 'Alter INDEX '||b.owner||'.'||b.SEGMENT_NAME||' REBUILD tablespace DATA_TEMP;' from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143) and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type = 'INDEX';

Step 10: For lob objects present in Tablespace.

SELECT 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as '||segment_name||' (tablespace DATA_TEMP);'
from all_lobs where owner='SCOTT';

No lob objects found in the list of tables.

Step 11: Start movement of objects  to new tablespace from the following command.

Alter table APPLSYS.FND_DOC_SEQUENCE_AUDIT move tablespace DATA_TEMP;
Alter table CE.CE_ARCH_INTERFACE_LINES move tablespace DATA_TEMP;
Alter table ONT.OE_ORDER_LINES_HISTORY move tablespace DATA_TEMP;
Alter table AR.HZ_CUST_SITE_USES_ALL_M move tablespace DATA_TEMP;
Alter table AR.HZ_CUST_PROFILE_AMTS_M move tablespace DATA_TEMP;
Alter table CZ.CZ_PRICING_STRUCTURES move tablespace DATA_TEMP;
Alter table APPLSYS.FND_STATS_HIST move tablespace DATA_TEMP;

Step 12: After movement of tables check the invalid objects.

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;

Step 13: Compile the invalid object.

Run the ?/rdbms/admin/utlrp.sql

Step 14: Check the size of datafiles.

select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.file_id in ( select file_id from dba_data_Files where tablespace_name = 'USERS');

--Manually :
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.file_id in (138,139,140,141,142,143);

Step 15: Resize the datafile.

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id=  b.file_id
and a.file_id in ( select file_id from dba_data_Files where tablespace_name = 'USERS')
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

-- Manually
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id=  b.file_id
and a.file_id in (138,139,140,141,142,1435)
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Step 16: Check the status of indexes is stable otherwise execute alter command.

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

Step 17: Disable the restricted session.

SQL> alter system disable restricted session;
Verified:
==========
SELECT logins from v$instance;
LOGINS
----------
ALLOWED


Note: If need to compile object manually. Follows the following commands:

Compile the object manually:

alter MATERIALIZED VIEW APPS.GET_REGION_DISTRICT_MV compile;
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;Last: