Identify fragmented tables and resolve fragmentation

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: How to identify fragmented tables

Symptoms

1. Run the below statement to identified fragmented tables

--Query1

-- TABLE Size with Fragmentation
select table_name,round(blocks*8,2)||'kb' "size fragmented" ,round((num_rows*avg_row_len/1024),2)||'kb' "actual size" from dba_tables where owner='ECS';

and 

-- Query2 to order most fragmented tables by size
-- Most Fragmented Tables 

select table_name,round(blocks*8,2) - round((num_rows*avg_row_len/1024),2) " Fragmented Data in KB" from dba_tables where owner='ECS' order by 2 desc;

2. Then on the most fragmented tables and indexes identified from the above statement, Run 

Alter table xx move tablespace xx;
Alter index ee rebuild tablespace ee;

Then I re run the above statements and I noticed not that much space reclaimed from mostly fragmented tables.

Solution:

1. analyze on all the tables of the schema before running the script to get the correct size.

2. Determine fragmented tables by running:

select table_name,round(blocks*8,2)||'kb' "size fragmented" ,round((num_rows*avg_row_len/1024),2)||'kb' "actual size" from dba_tables where owner='ECS';

3. The move table method is the best way to remove fragmentation as much as possible.

4. Fragmentation cannot be completely removed in case there chained/migrated rows.

Chaining
---------
Occurs when the row is too large to fit into one data block when it is first
inserted. In this case, Oracle stores the data for the row in a chain of data 
blocks (one or more) reserved for that segment. Row chaining most often occurs
with large rows, such as rows that contain a column of datatype LONG, LONG RAW,
LOB, etc. Row chaining in these cases is unavoidable.


A row is too large to fit into a single database block. For example, if you use a 4KB block size
for your database,and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store
the row in pieces. Some conditions that will cause row chaining are:

In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved 
for that segment.So, instead of just having a forwarding address on one block and the data on another
we have data on two or more blocks.


How you can identify the Chained and Migrated rows ?
=====================================================
You can use the ANALYZE command to list the Chained/Migrated rows in a table, but you should consider the followings:

A-For conventional Non index_organized tables you can create chained-rows table using the following script locally or in another schema:

create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date );


Note : If you created the chained-rows table in another schema then in the analyze command prefix the name by the schema name.


sql>analyze table <Non IOT > list chained rows into <chained-rows table name> ;

Reference:
Article-ID: Note 337651.1
Title: How to find Objects Fragmentated below High water mark


    

Get Oracle Certifications for all Exams
Free Online Exams.com