Table reorganization
|After many changes to table data, |logically sequential data may be on non-sequential physical data |pages so that the database manager must perform additional |read operations to access data. Additional read |operations are also required if a significant number of rows |have been deleted. In such a case, you might consider |reorganizing the table to match the index and to reclaim space. |
A) Identify Tables needs to reorg:
1) table1
2) table2
3) table3
4) table4
B) Check free space
Free Space requied=10GB
Steps:
1)Take the Backup
a)Export backup
or
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('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR'
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 ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR') and owner='HR' 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='TEST';
!bdf | grep /data1
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='HR' group by owner,object_type,status order by object_type;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM dba_objects where status='INVALID' AND OWNER='HR';
6)Check the Count of the objects
select count(*) from HR.table1;
select count(*) from HR.table2;
select count(*) from HR.table3;
select count(*) from HR.table4;
select count(*) from HR.table4;
7) Prepare the Script to move the TABLES
a) Non-Partition
select ' alter table ' ||OWNER||'.'||TABLE_NAME||' move tablespace GLOBAL_data1;'
from dba_tables where TABLE_NAME in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR' and PARTITIONED='NO'
b)Partition
select 'alter table ' ||table_owner||'.'||table_name || ' move partition ' || partition_name || ' tablespace GLOBAL_data1;' from dba_tab_partitions where table_name in('table1',
'table2',
'table3',
'table4',
'table4')
and TABLE_OWNER='HR'
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_data1;'
from dba_indexes where table_name in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR'
b)Partition
select 'alter index ' ||index_owner||'.'||index_name || ' rebuild partition ' || partition_name || ' tablespace GLOBAL_data1;'
from dba_ind_partitions
where index_name in (select INDEX_NAME from dba_indexes where table_name in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR' and PARTITIONED='YES')
Execute the script and generate the spool file.
Check the spool file for errors.
10) Confirm the Status
Confirm the Status and count of the objects using Step 4)and 5
If it is same then activity is successfull
11)RollBack Plan
Restore the Table from the Export Backup