Dear Readers,
In this article, we will see the Oracle : Important Queries to check Table Fragmentation (SecureCRT).
Menubar Name : Fragmentation
Tab name : Fragmented_tablesList in HTML
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
col spoolname new_value spoolname select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual; spool '&spoolname' set pagesize 200 set markup html on WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER='SCOTT'), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB FROM DBA_SEGMENTS WHERE 1=1 AND OWNER='SCOTT' AND SEGMENT_TYPE='TABLE') SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb FROM STAT_SPACE STSP, SEG_SPACE SESP WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 AND STSP.TABLE_NAME = SESP.SEGMENT_NAME AND STSP.OWNER = SESP.OWNER ORDER BY CLAIM_GB desc; spool off exit |
Output :
Tab name : BLOCKS Size
|
1 2 3 4 5 6 7 8 9 10 11 |
set linesize 300 col table_name for a30 col "Blocks Size" for a30 select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size" from dba_tables where owner='&OWNER' and table_name = upper('&table_name'); TABLE_NAME Blocks Size Blocks Size ------------------------------ ------------------------------ ------------------------------ EMPTEST123 1231264 KB 1.174224853515625 GB |
Tab name : Rows Size
|
1 2 3 4 5 6 7 8 |
set linesize 300 select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size" from dba_tables where owner='&OWNER' and table_name = upper('&table_name'); TABLE_NAME Rows Size Rows Size ------------------------------ ------------------------------------------------------------------------ ---------------------------- EMPTEST123 10597.63 KB .0101066875457763671875 GB |
Tab name : Fragmented_tables_List in SQLPLUS for specific Schema
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
BREAK ON REPORT BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT COMPUTE SUM LABEL TOTAL OF SEGSIZE_GB TBL_STAT_SPACE CLAIM_GB ON tablespace_name COL TABLESPACE_NAME FORMAT A30 HEADING "Tablespace Name" COL TABLE_NAME FORMAT A30 HEADING "Table Name" COL BLOCKS FORMAT 999,999,999,999 HEADING "Alloc|DB|Blocks" COL AVG_ROW_LEN FORMAT 9999 HEADING "Avg|Row|Length" COL NUM_ROWS FORMAT 999,999,999,999 HEADING "Num Rows" COL TBL_STAT_SPACE FORMAT 99,999.9999 HEADING "Space Calc|Statistics|in GB" COL SEGSIZE_GB FORMAT 99,999.9999 HEADING "Space Calc|Allocated|in GB" col claim_gb format 99,999.9999 heading "Claimable|Space|in GB" set pagesize 300 WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER='&OWNER'), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB FROM DBA_SEGMENTS WHERE 1=1 AND OWNER='&OWNER' AND SEGMENT_TYPE='TABLE') SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb FROM STAT_SPACE STSP, SEG_SPACE SESP WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 AND STSP.TABLE_NAME = SESP.SEGMENT_NAME AND STSP.OWNER = SESP.OWNER ORDER BY TABLESPACE_NAME asc, CLAIM_GB desc; |
|
1 2 3 4 5 6 7 |
Alloc Avg Space Calc Space Calc Claimable DB Row Statistics Allocated Space Tablespace Name Table Name Blocks Num Rows Length in GB in GB in GB ------------------------------ ------------------------------ ---------------- ---------------- ------ ------------ ------------ ------------ SCOTT_TBS_DATA01 EMP123_LOG 4,061,184 40,262,464 727 27.2606 30.9844 3.7238 ****************************** ------------ ------------ ------------ TOTAL 27.2606 30.9844 3.7238 |
Tab name : Fragmented_tables in Database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
BREAK ON REPORT BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT COMPUTE SUM LABEL TOTAL OF SEGSIZE_GB TBL_STAT_SPACE CLAIM_GB ON tablespace_name COL TABLESPACE_NAME FORMAT A30 HEADING "Tablespace Name" COL TABLE_NAME FORMAT A30 HEADING "Table Name" COL BLOCKS FORMAT 999,999,999,999 HEADING "Alloc|DB|Blocks" COL AVG_ROW_LEN FORMAT 9999 HEADING "Avg|Row|Length" COL NUM_ROWS FORMAT 999,999,999,999 HEADING "Num Rows" COL TBL_STAT_SPACE FORMAT 99,999.9999 HEADING "Space Calc|Statistics|in GB" COL SEGSIZE_GB FORMAT 99,999.9999 HEADING "Space Calc|Allocated|in GB" col claim_gb format 99,999.9999 heading "Claimable|Space|in GB" set pagesize 300 set linesize 300 WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES ), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB FROM DBA_SEGMENTS WHERE 1=1 AND SEGMENT_TYPE='TABLE') SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb FROM STAT_SPACE STSP, SEG_SPACE SESP WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 AND STSP.TABLE_NAME = SESP.SEGMENT_NAME AND STSP.OWNER = SESP.OWNER ORDER BY TABLESPACE_NAME asc, CLAIM_GB desc; |
|
1 2 3 4 5 6 7 |
Alloc Avg Space Calc Space Calc Claimable DB Row Statistics Allocated Space Tablespace Name Table Name Blocks Num Rows Length in GB in GB in GB ------------------------------ ------------------------------ ---------------- ---------------- ------ ------------ ------------ ------------ SCOTT_TBS_DATA01 EMP123_LOG 4,061,184 40,262,464 727 27.2606 30.9844 3.7238 ****************************** ------------ ------------ ------------ TOTAL 27.2606 30.9844 3.7238 |
Tab name : Tablespace_Fragmentation_Read_Notes_Above_Query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
---- Below 30% is considered to be highly fragmented, please de-fragment it. ---- 100% means no fragmentation ---- If that tablespace belongs to undo tablespace then drop and recreate it. ---- In case of sysaux OR system add new datafiles. ---- In case of user tablespaces, take precaution to de-fragment it. ---- Before Deframenting discuss with Team and take proper action set linesize 300 set pagesize 300 SELECT tablespace_name, count(*) free_chunks, decode(round((max(bytes) / 1024000),2), null,0, round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index FROM sys.dba_free_space group by tablespace_name order by 2 desc, 1; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX ------------------------------ ----------- ------------- ------------------- SYSAUX 2111 380.93 9.88 DEMO_INDEX 183 4063.23 13.12 DEMO1_QA3_INDEX01 142 2620.42 19.14 UNDOTBS1 120 4063.23 15.15 AWS_DATA01 91 768.96 30.29 DEMO_DATA 40 4063.23 16.76 DEMO1_QA3_DATA01 7 4063.23 32.79 DEMO_DATA01 7 4063.23 29.56 SYSTEM 5 91.9 41.79 SDO_INDEX01 2 101.25 84.06 DEMO1_MTID_DATA01 2 41.98 83.64 AWSTEMP_DATA 1 101.38 100 USERS 1 83.58 100 |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
