Reorganize Oracle tables

Here i am sharing the steps to reorganize the tables to release the space.

Tables:

a) EMP

b) DEPT

c) ORDERS

d) DEMO

e) NEWJOIN

Free Space requied=10GB

Steps:

1) Take the Backup

  a)Export backup

  b)Full Backup

2) Take the Dump of Tables

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,sum(BYTES/1024/1024)"size MB" from dba_segments where SEGMENT_NAME in('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and owner='SCOTT'

group by OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME

order by OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME

3) Take the Dump of Indexes

select owner,segment_name,segment_type,tablespace_name,BYTES/1024/1024 from dba_segments where segment_name in (select INDEX_NAME from dba_indexes where table_name in ('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and owner='SCOTT') and owner='SCOTT' order by segment_name

4) Check the available space in the Tablespaces

set pages 200

set lines 200

col n_extent format 999,999,999

ttitle 'Free Space by Tablespace'

col col1    format a28  heading "Tablespace"

col col2    format 999,999,999,999    heading "Bytes(KB)"

col col3    format 999,999,999    heading "Used(KB)"

col col4    format 999,999,999    heading "Free(KB)"

col col5    format 999.9    heading "Free(%)"

break on report

compute sum of col2 col3 col4 FREE_MAX  on report

select

-- b.file_id "File #",

b.tablespace_name col1,

b.bytes / 1024 col2,

((b.bytes - sum(nvl(a.bytes,0)))) / 1024 col3,

(sum(nvl(a.bytes,0))) / 1024 col4,

(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 col5,

max(a.bytes)/1024 FREE_MAX, count(*)

from sys.dba_free_space a,

(select  tablespace_name, sum(bytes) bytes from sys.dba_data_files

group by tablespace_name ) b

-- where a.file_id(+) = b.file_id

where a.tablespace_name = b.tablespace_name

group by b.tablespace_name, b.bytes

order by 5;

SQL> set lines 200

SQL> col file_name for a60

SQL> col tablespace_name for a25

SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='GLOBAL_RMS';

!bdf | grep /rms

Check the availablity of Space and Manage the Space as per the requirement

 

 

5) Check the status of the objects

SELECT COUNT(*) FROM DBA_OBJECTS;

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='VALID';

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';

select owner,object_type,status,count(*) from dba_objects where owner='SCOTT' group by owner,object_type,status order by object_type;

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM dba_objects where status='INVALID' AND OWNER='SCOTT';

6) Check the Count of the objects

select count(*) from SCOTT.EMP;

select count(*) from SCOTT.DEPT;

select count(*) from SCOTT.ORDERS;

select count(*) from SCOTT.DEMO;

select count(*) from SCOTT.NEWJOIN;

7) Prepare the Script to move the TABLES

    a) Non-Partition

select ' alter table ' ||OWNER||'.'||TABLE_NAME||' move tablespace GLOBAL_RMS;'
from dba_tables where TABLE_NAME in ('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and owner='SCOTT' and PARTITIONED='NO'

b)Partition

select 'alter table ' ||table_owner||'.'||table_name || ' move  partition ' || partition_name || ' tablespace GLOBAL_RMS;' from dba_tab_partitions where table_name in('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and TABLE_OWNER='SCOTT'

Execute the script and generate the spool file.

Check the spool file for errors.

8) Prepare the Script to move the Indexes

   a) Non-Partition

select 'alter index ' ||owner||'.'||index_name || ' rebuild TABLESPACE GLOBAL_RMS;'
from dba_indexes where table_name in ('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and owner='SCOTT'

   b)Partition

select 'alter index ' ||index_owner||'.'||index_name || ' rebuild  partition ' || partition_name || ' tablespace GLOBAL_RMS;'

from dba_ind_partitions

where index_name in (select INDEX_NAME from dba_indexes where table_name in ('EMP',

'DEPT',

'ORDERS',

'DEMO',

'NEWJOIN')

and owner='SCOTT' and PARTITIONED='YES')

Execute the script and generate the spool file.

Check the spool file for errors.

9) Confirm the Status

 Confirm the Status and count of the objects using Step 4)and 5

If it is same then activity is successfull

10) RollBack Plan

Restore the Table from the Export Backup