There were many request coming in for Reorg Hence thought of documenting handy article to check fragmentation and perform reorg .
After performing Reorganization we need to also gather fresh statistics . Check invalid objects pre and post Reorganization .
Article will have 2 main topics mainly
1) Ways to check Fragmentation
2) Performing Reorganization .
For Lob i have documented in my previous Blog below
https://abdul-hafeez-kalsekar.blogspot.com/2024/01/oracle-database-lob-maintenance.html
Before proceeding to check fragmentation , please ensure we have latest statistics ,
Please note Shrink wont work on compressed table .
Ways to check Fragmentations :
==> High water mark(HWM) check query :
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
Enter value for owner: ABDUL
Enter value for table_name: TABLE1
==> Check Fragmented Table
col TABLE_NAME for a30
col fragmented_size for a15
col table_size for a15
col Used_size for a15
select table_name,round(((blocks*8)/1024/1024),2)||'GB' "table_size",round((num_rows*avg_row_len/1024/1024/1024),2)||'GB' "Used_size",
(round(((blocks*8/1024/1024)),2)-round((num_rows*avg_row_len/1024/1024/1024),2))|| 'GB' "fragmented_size" from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT') and last_analyzed is not null and num_rows>0 and table_name='SECURE' and owner='HAFEEZ'
and blocks >0
order by 4 asc
/
We can also check Table Fragmentation using Oracle Script documented in (Doc ID 1019716.6)
I have also documented script in below article
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/12/script-to-report-table-fragmentation.html
==> Checking Partition table fragmentation :
SELECT table_name,
partition_name,
ROUND ( (blocks / 1024 * 16), 2) "size (mb)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (mb)",
( ROUND ( (blocks / 1024 * 16), 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (mb)"
FROM dba_tab_partitions
WHERE (ROUND ( (blocks / 1024 * 16), 2) >
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
AND table_name = 'TBK_XXX'
ORDER BY 5 DESC;
select
table_owner
,table_name
,partition_name
,high_value
,compression
,compress_for
,avg_row_len
,round(((blocks*16/1024)),2)/1024 "TOTAL_SIZE_GB" --The amount of space used by the partition in gigabytes.
,round((num_rows*avg_row_len/1024/1024),2)/1024 "ACTUAL_SIZE_GB" -- The amount of space used by the partition in gigabytes, calculated based on the number of rows and the average row length.
,round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 "FRAGMENTED_SPACE_GB" --The amount of space that is not used by the partition, in gigabytes.
,decode(round(((blocks*16/1024)),2),0,0, (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100) "percentage" --The percentage of unused space in the partition, calculated by dividing the unused space by the total space and multiplying by 100.
from dba_tab_partitions
WHERE
1=1
and table_owner='DEV_DW'
and table_name='TEST_TABLE'
and partition_name='SYS_P21822227'
and round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 > 0
order by 6 desc
;
==> Checking Top Fragmented Tables
select t1.owner,t1.table_name,nvl(t1.blocks*8,1) "size fragmented KB",round(nvl((t1.num_rows*t1.avg_row_len/1024),1),2) "actaul size KB",round(nvl(t1.blocks*8,1) - nvl((t1.num_rows*t1.avg_row_len/1024),1),2) "fragmentation KB"
from dba_tables t1
order by t1.owner,round(nvl(t1.blocks*8,1) - nvl((t1.num_rows*t1.avg_row_len/1024),1),2) desc ;
select blocks,last_analyzed,owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
and owner in ('HAFEEZ')
order by 4 ;
==> Checking All Fragmented Tables
select table_name, avg_row_len, round(((blocks16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rowsavg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE", (round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/round(((blocks16/1024)),2))100 "percentage"
from all_tables where round(( (blocks16/1024) ),2) > 0 order by 6 desc ;
set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0) “TOTAL_SIZE”, round((num_rows*avg_row_len
/1024/1024),0) “ACTUAL_SIZE”, round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) “FRAGMENTED_SPACE” from
dba_tables where owner not in (‘SYS’,’SYSTEM’,’FDBA’,’PERFSTAT’,’DBMON’) and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;
==> Checking Index fragmentation :
You can run the ANALYZE INDEX <index> VALIDATE STRUCTURE command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.
you may decide that index should be rebuilt if more than 20% of its rows are deleted:
ANALYZE INDEX &&index_name VALIDATE STRUCTURE;
col name heading 'Index Name' format a30
col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
col lf_rows_used heading 'Used|Leaf Rows' format 99999999
col ibadness heading '% Deleted|Leaf Rows' format 999.99999
SELECT name,
del_lf_rows,
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
FROM index_stats
where name = upper('&index_name');
undefine index_name
select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name';
==> Checking Fragmentation using segment space advisor
Below is script used from Oracle Doc ID 854234.1
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
SET LINES 300
SET PAGES 999
COL SEGNAME FOR A15
COL PARTITION FOR A10
COL TYPE FOR A10
COL MESSAGE FOR A60
SELECT DAO.ATTR2 SEGNAME,
DAO.ATTR3 PARTITION,
DAO.TYPE,
DAF.MESSAGE
FROM DBA_ADVISOR_FINDINGS DAF,
DBA_ADVISOR_OBJECTS DAO
WHERE DAO.TASK_ID = DAF.TASK_ID AND
DAO.OBJECT_ID = DAF.OBJECT_ID AND
DAF.TASK_NAME IN ('Manual_Employees');
Ways to remove Fragmentations :
Main ways commonly followed are :
Shrink
Alter table Move
CTAS method
Datapump or exp/imp
==> Options to Perform Reorg of Table
SQL> ALTER TABLE table name ENABLE ROW MOVEMENT;
SQL> ALTER TABLE table name SHRINK SPACE CASCADE;
SQL> ALTER TABLE table name DISABLE ROW MOVEMENT;
ALTER TABLE mytable MOVE PARALLEL (DEGREE 8) ONLINE UPDATE INDEXES
==> Options to Perform Reorg of Partition Table
ALTER TABLE TEST_TABLE enable row movement;
ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE;
ALTER TABLE order MOVE PARTITION SYS_P21674395 UPDATE INDEXES;
ALTER TABLE order MOVE PARTITION SYS_P21674395 ONLINE UPDATE INDEXES;
ALTER TABLE TEST_TABLE move PARTITION SYS_P21822227 NOCOMPRESS UPDATE INDEXES;
ALTER TABLE order MOVE PARTITION SYS_P21674395 COMPRESS FOR QUERY HIGH UPDATE INDEXES;
==> Options to Perform rebuild of index / Remove Index Fragmentation
ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES;
ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX index name SHRINK SPACE;
ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE parallel 10 ;
Check the progress of Alter Shrink Space Command
-- Following command will search alter table command and give you output how much it covered according to table size in first command.
-- GB_read give you how much it covered yet.
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where UPPER(c.SQL_TEXT) like UPPER('%ALTER TABLE%')
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;
--If you have session id of session from which command running then use following command:
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where a.SID =
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;
Online Redefinition to perform Reorg :
A primary key is mandatory since materialized views and logs are created during the start of redefinition.
The user performing the re-organization requires the following
privileges:
* CREATE ANY TABLE
* ALTER ANY TABLE
* DROP ANY TABLE
* LOCK ANY TABLE
* SELECT ANY TABLE
* CREATE ANY INDEX
* CREATE ANY TRIGGER
GENERAL STEPS TO ONLINE REDEFINE A TABLE
1) Determine if the table to be moved can be redefined online (DBMS_REDEFINITION.CAN_REDEF_TABLE)
2) Create the interim table
The interim table need not be the same 'shape' (have similar structure) as the original table
3) Start the redefinition of the table (DBMS_REDEFINITION.START_REDEF_TABLE)
4) Copy the dependents from the original table to the interim table (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS)
5) Execute a final synchronization between the original and interim tables (DBMS_REDEFINITION.SYNC_INTERIM_TABLE)
This step will minimize the amount of time needed to execute the finish of the redefinition
6) Finish the redefinition (DBMS_REDEFINITION.FINISH_REDEF_TABLE)
7) Verify that the redefinition succeeded by comparing the original table (formerly interim) to the interim table (formerly original)
8) Drop the interim table
---------------------------------------------------------------------
-- Test if table can be redefined...
---------------------------------------------------------------------
SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE
(
uname=>'SCOTT',
tname=>'emp' );
end;
/
---------------------------------------------------------------------
-- CREATE THE INTERIM TABLE
---------------------------------------------------------------------
create table emp_work (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));
---------------------------------------------------------------------
-- Start table redefinition...
---------------------------------------------------------------------
During this phase Oracle will copy (and transform) the data from the production table to the interim table. Oracle will also create a materialized view (snapshot) log on the table to track DML changes.
SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.start_redef_table(
uname=>'SCOTT',
orig_table=>'emp',
int_table=>'emp_work');
end;
/
---------------------------------------------------------------------
-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE
---------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname=>'SCOTT',
orig_table=>'emp',
int_table=>'emp_work',
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_num_errors);
end;
/
--------------------------------------------------------------------
-- Do necessary changes: here we Create constraints on the interim table ( skip thisif only reorganization is needec )
---------------------------------------------------------------------
SQL> alter table emp_work add constraint int_empx_pk primary key(empno);
SQL> alter table emp_work add constraint 2 int_empx_fk foreign key(deptno) references dept(deptno);
SQL> alter table emp_work MODIFY CONSTRAINT int_empx_fk 2 DISABLE KEEP INDEX;
---------------------------------------------------------------------
-- Sync intermediate changes to interim table (optional)
---------------------------------------------------------------------
This step will apply changes captured in the materialized view log to the interim table. Perform this step frequently for high transaction tables.
SQL> exec dbms_redefinition.sync_interim_table('scott', 'emp', 'emp_work');
---------------------------------------------------------------------
-- Finish the redefinition process (this will swap the two tables)...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');
---------------------------------------------------------------------
-- Drop the interim working table...
---------------------------------------------------------------------
DROP TABLE emp_work;
Redefine table without primary Key :
The Reason the table failed is because the Online redefinition happens either by using a primary key or using ROWID,
The Default is the Primary Key,
If we dont haveprimary key we need to use rowid option . For that "options_flag=>DBMS_REDEFINITION.cons_use_rowid" needs to be added in DBMS_REDEFINITION.CAN_REDEF_TABLE and DBMS_REDEFINITION.start_redef_table function
Eg
SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname=>'SCOTT',
tname=>'emp',
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/
SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.start_redef_table(
uname=>'SCOTT',
orig_table=>'emp',
int_table=>'emp_work',
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/
-- To perform online redefinition in parallel
alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;
References :
https://oracle-base.com/articles/12c/online-move-partitions-and-subpartitions-12cr1#google_vignette
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/maintenance-partition-tables-indexes.html#GUID-CDAA2363-B83A-408C-82C9-3E3FA3928D2D
Or
https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/maintenance-partition-tables-indexes.html#GUID-BAFFE31C-07A2-4ED6-BDCF-8ECB79D7FE7D
Attempt to reduce the size of a LOB segment after the table / LOB becomes sparse -> How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)
How to Re-Organize a Table Online (Doc ID 177407.1)
How to release unused space (SHRINK) occupied by a LOB segment by adding / dropping columns? (Doc ID 1417697.1)
How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)
