Step1. Coalesce free extents in tablespaces, before and after each reorg.
select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_tablespaces
order by tablespace_name;
Step 2: To determine the fragmentation level in a database - here are some fragmentation assessment criteria:
1. High numbers of extents (acceptable < 1024 extents for very large objects - look out for extents per object > 5)
ALL OBJECTS WITH NUMBER OF EXTENTS > 500 (except temporary and rollback segments))
Select owner||'.'||segment_name,segment_type, tablespace_name, count(*) exn,
round(sum(bytes/1024/1024),0) mbytes from dba_extents
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and segment_type not in ('TEMPORARY','ROLLBACK')
having count(*) > 500 group by owner||'.'||segment_name, segment_type, tablespace_name order by 4 desc, 1, 2;
2. High percentages of chained rows per table (acceptable < 3 percent- look out for percentages > 0.1 percent) - analyze the tables first
(ALL TABLES WITH PERCENTAGE OF CHAINED ROWS > 0.1% (acceptable < 3%))
select owner||'.'||table_name, nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 chained_rows_percent, nvl(chain_cnt,0)/1000 num_chained_rows,
nvl(num_rows,0)/1000 num_rows from dba_tables
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 > 0.1
order by 2 desc, 1;
3. High percentages of free space inside blocks (look out for FREESPACE/BLOCK > 2*PCTFREE)
(ALL TABLES WITH FREESPACE/BLOCK > 2*PCTFREE)
select owner||'.'||table_name,
nvl(avg_space,0)/p.value*100 avg_space_percent, nvl(pct_free,0) percent_free,
nvl(pct_used,0) percent_used, nvl(avg_row_len,0) avg_row_len,
nvl(num_rows,0)/1000 num_rows from dba_tables, v$parameter p
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and p.name = 'db_block_size' and num_rows > 1000
and nvl(avg_space,0)/p.value*100 > 2*pct_free order by 2 desc, 5 desc;
NOTE-- avg_space is average free space in a block below HWM
4. High percentages of free space above highwatermark (look out for EMPTY BLOCKS ABOVE HWM > 50 percent)
(ALL TABLES WITH PERCENTAGE OF EMPTY BLOCKS (ABOVE HWM) > 50% (waisted space))
(select owner||'.'||table_name, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 empty_blocks_percent, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)
*(nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024) waisted_space_mb,
nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024 table_size_mb,
nvl(t.avg_row_len,0) avg_row_len, nvl(t.num_rows,0)/1000 num_rows
from dba_tables t, v$parameter p where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER')
and p.name = 'db_block_size'
and t.num_rows > 1000 and nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 > 50
order by 2 desc, 3 desc, 5 desc;
NOTE: -- empty blocks above HWM
1. Before doing the reorg take count of invalid objects in each schema as well for APPS, APPS_MRC, APPSVIEW. The reorgs will invalidate lot of objects in APPS, APPS_MRC, APPSVIEW schemas.
select owner, count(*) from dba_objects where status='INVALID' and owner like 'APP%' group by owner ;
select count(*) from dba_objects where status=’INVALID’ and owner=’
2. Check for the partitioned tables by using the following query.
select table_name ,partitioned from dba_tables where table_name=
Based on the output we need to build the separate syntax to move the partioned tables and associated indexes.
3.ANALYSIS ON DATA TYPES:
Do the analysis of data types in the given schema other than standard (date, char, number, varchar2, rowid) data types.
Identify different types of datatypes for each schema by executing below sql
Select distinct data_type from dba_tab_columns where owner=’AAAA’;
Based on the output of this SQL we will decide which reorg mechanisim need to be followed.
4.ANALYSIS OF CHAINED ROWS:
Do the analysis for chained rows in the schema.
select owner,table_name, chain_cnt from dba_tables
where chain_cnt > 1 and owner in ('ONT','BOM','MRP') order by chain_cnt;
OWNER TABLE_NAME CHAIN_CNT
--------------- ------------------------------ ------------------
BOM BOM_OPERATIONAL_ROUTINGS 11
Modify the move syntax for those tables by adding PCTUSED 40 PCTFREE 25 clause.
Syntax:
alter table owner.
storage (PCTUSED 40 PCTFREE 25 pctincrease 0);
5.FOR LOB DATA TYPE:
The default manual rebuild scripts generated by alter table move will be as below and it fails with below error. So use above SQL for LOB data types. This need to be done only for the index on LOB column and rest of the indexes can be done by manual scripts. So change the script with above sql where is LOB index with appropriate tablename and LOB column name.
alter index AR.SYS_IL0000716146C00221$$ rebuild tablespace ARXS1 storage(initial 128k next 128k pctincrease 0);
ERROR at line 1:
RA-02327: cannot create index on expression with datatype LOB
You can get the LOB column details by using the following query:
select table_name, column_name,segment_name from dba_lobs where Index_name='SYS_IL0000716146C00221$$' and owner='AR';
TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- -------------------- ------------------------------
HZ_PARTY_INTERFACE BUSINESS_REPORT SYS_LOB0000716146C00221$$
Syntax:
alter table AR.HZ_PARTY_INTERFACE move lob(BUSINESS_REPORT)
store as lobsegment (tablespace ARDS1 storage(initial 512k next 512k pctincrease 0));
NOTE: We have to rebuild other indexes after moving the lobsegment to another tablespace.
6.FOR LONG DATA TYPE:
If the datatype is LONG, then we have to export/import the table/schema
8. If we find any partitioned tables prepare the scripts to move the partioned tables and indexes.
Alter table table_name move partition partition_name
9. For general reorg use below syntax for tables and their associated indexes.
10.
Syntax:
Alter table owner.
Alter indeex owner.
Use same syntax for all the tables and indexes
10.. After completing reorgs, check invalids in schema as well for APPS, APPS_MRC, APPSVIEW and compile them manually or if the count is too high run adadmin compile apps schema.
select owner,count(*) from dba_objects where status='INVALID'and owner like'APP%' group by owner;
select count(*) from dba_objects where status=’INVALID’ and owner=’
