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
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
/
==> 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 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
set echo off
set feedback off
set verify off
set linesize 80
set serveroutput on size unlimited
spool /home/oracle/scripts/segment_advisor.txt
declare
v_task_name varchar2(100);
v_task_desc varchar2(500);
v_objid number;
begin
begin
v_task_name := 'SEGMENT_ADVISOR_RUN';
v_task_desc := 'MANUAL SEGMENT ADVISOR RUN';
-- Create Segment Advisor task.
dbms_advisor.create_task
(
advisor_name => 'Segment Advisor',
task_name => v_task_name,
task_desc => v_task_desc
);
-- Add all segments to the task.
for s in (select segment_name, segment_type
from DBA_segments
where segment_type in ('TABLE', 'INDEX', 'LOB')
and owner != 'SYS' and owner != 'SYSTEM' and owner != 'OLAPSYS' and owner != 'SYSMAN' and owner != 'ODM' and owner != 'RMAN' and owner != 'ORACLE_OCM' and owner != 'EXFSYS' and owner != 'OUTLN' and owner != 'DBSNMP' and owner != 'OPS' and owner != 'DIP' and owner != 'ORDSYS' and owner != 'WMSYS' and owner != 'XDB' and owner != 'CTXSYS' and owner != 'DMSYS' and owner != 'SCOTT' and owner != 'TSMSYS' and owner != 'MDSYS' and owner != 'WKSYS' and owner != 'ORDDATA' and owner != 'OWBSYS' and owner != 'ORDPLUGINS' and owner != 'SI_INFORMTN_SCHEMA' and owner != 'PUBLIC' and owner != 'OWBSYS_AUDIT' and owner != 'APPQOSSYS' and owner != 'APEX_030200' and owner != 'FLOWS_030000' and owner != 'WK_TEST' and owner != 'SWBAPPS' and owner != 'WEBDB' and owner != 'OAS_PUBLIC' and owner != 'FLOWS_FILES' and owner != 'QMS')
loop
dbms_advisor.create_object
(
task_name => v_task_name,
object_type => s.segment_type,
attr1 => user,
attr2 => s.segment_name,
attr3 => null,
attr4 => null,
attr5 => null,
object_id => v_objid
);
end loop;
-- Set task parameter to recommend all.
dbms_advisor.set_task_parameter
(
task_name => v_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE'
);
-- Run Segment Advisor.
dbms_advisor.execute_task(v_task_name);
exception when others then
dbms_output.put_line('Exception: ' || SQLERRM);
end;
-- Output findings.
dbms_output.put_line(chr(10));
dbms_output.put_line('Segment Advisor Recommendations');
dbms_output.put_line('--------------------------------------------------------------------------------');
for r in (select segment_owner, segment_name, segment_type, partition_name,
tablespace_name, allocated_space, used_space,
reclaimable_space, chain_rowexcess, recommendations, c1, c2, c3
from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
where segment_owner != 'SYS' and segment_owner != 'SYSTEM' and segment_owner != 'OLAPSYS' and segment_owner != 'SYSMAN' and segment_owner != 'ODM' and segment_owner != 'RMAN' and segment_owner != 'ORACLE_OCM' and segment_owner != 'EXFSYS' and segment_owner != 'OUTLN' and segment_owner != 'DBSNMP' and segment_owner != 'OPS' and segment_owner != 'DIP' and segment_owner != 'ORDSYS' and segment_owner != 'WMSYS' and segment_owner != 'XDB' and segment_owner != 'CTXSYS' and segment_owner != 'DMSYS' and segment_owner != 'SCOTT' and segment_owner != 'TSMSYS' and segment_owner != 'MDSYS' and segment_owner != 'WKSYS' and segment_owner != 'ORDDATA' and segment_owner != 'OWBSYS' and segment_owner != 'ORDPLUGINS' and segment_owner != 'SI_INFORMTN_SCHEMA' and segment_owner != 'PUBLIC' and segment_owner != 'OWBSYS_AUDIT' and segment_owner != 'APPQOSSYS' and segment_owner != 'APEX_030200' and segment_owner != 'FLOWS_030000' and segment_owner != 'WK_TEST' and segment_owner != 'SWBAPPS' and segment_owner != 'WEBDB' and segment_owner != 'OAS_PUBLIC' and segment_owner != 'FLOWS_FILES' and segment_owner != 'QMS'
order by reclaimable_space desc)
loop
dbms_output.put_line('');
dbms_output.put_line('Owner : ' || r.segment_owner);
dbms_output.put_line('Segment : ' || r.segment_name);
dbms_output.put_line('Segment Type : ' || r.segment_type);
dbms_output.put_line('Partition Name : ' || r.partition_name);
dbms_output.put_line('Tablespace : ' || r.tablespace_name);
dbms_output.put_line('Allocated Space : ' || r.allocated_space);
dbms_output.put_line('Used Space : ' || r.used_space);
dbms_output.put_line('Reclaimable Space in MB : ' || r.reclaimable_space/1024/1024);
dbms_output.put_line('Chain Rowexcess : ' || r.chain_rowexcess);
dbms_output.put_line('Recommendations : ' || r.recommendations);
dbms_output.put_line('Run First : ' || r.c3);
dbms_output.put_line('Run Second : ' || r.c2);
dbms_output.put_line('Run Third : ' || r.c1);
dbms_output.put_line('--------------------------------------------------------------------------------');
end loop;
-- Remove Segment Advisor task.
dbms_advisor.delete_task(v_task_name);
end;
/
spool off;
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)
