Fragmentation in oracle
check the indexes and LOB on tables and also PCT free value for the tables...
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name'; -- keep a track to match after fragmentation
Check the indexes on the table
select index_name from dba_indexes where table_name='&TABLE_NAME';
STEP 3: Check actual table size, fragmented size and percentage of fragmentation in a table.
REPORTE_SKU_UNQ
IDETRNEE IDETRNEEP1
HP_VTEX_ERROR_JASON NO index
LOG_ACCESOS no index
EDSR REPORTE_SKU_UNQ
EPMM IDETRNEE
EDSR HP_VTEX_ERROR_JASON
LOG_ACCESOS
Let's find the name of the LOB segments and their corresponding indexes:(The LOB indexes are internal data structures only. See this post.)
SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME FROM DBA_LOBS WHERE TABLE_NAME='REPORTE_SKU_UNQ' AND OWNER IN ('EDSR');
SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME FROM DBA_LOBS WHERE TABLE_NAME='IDETRNEE' AND OWNER IN ('EPMM');
SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME FROM DBA_LOBS WHERE TABLE_NAME='HP_VTEX_ERROR_JASON' AND OWNER IN ('EDSR');
SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME FROM DBA_LOBS WHERE TABLE_NAME='LOG_ACCESOS' AND OWNER IN ('EDBATOOLS');
alter table "EDSR"."REPORTE_SKU_UNQ" enable row movement;
alter table "EDSR"."REPORTE_SKU_UNQ" shrink space compact;
alter table "EDSR"."REPORTE_SKU_UNQ" disable row movement;
Alter table "EDSR"."REPORTE_SKU_UNQ" shrink space cascade;
exec dbms_stats.gather_table_stats('EDSR', 'REPORTE_SKU_UNQ', cascade => TRUE);
========rightformat to defragement the tables ===========
OWNER TABLE_NAME size (Gb) actual_data (Gb) wasted_space (Gb)
-------------------- ------------------------------ ---------- ---------------- -----------------
EDSR REPORTE_SKU_UNQ 81.0902405 0 81.0902405
EPMM IDETRNEE 131.693947 101.628764 30.0651832
EDSR HP_VTEX_ERROR_JASON 43.2466049 22.3318148 20.9147901
EDBATOOLS LOG_ACCESOS 96.3369827 81.0252001 15.3117827
EDSR HP_VTEX_CARGA_PRECIOS_DIF_HIST 46.7762375 34.4881199 12.2881175
alter table "EDSR"."REPORTE_SKU_UNQ" enable row movement;
alter table "EDSR"."REPORTE_SKU_UNQ" shrink space compact;
Alter table "EDSR"."REPORTE_SKU_UNQ" shrink space cascade;
alter table "EDSR"."REPORTE_SKU_UNQ" disable row movement;
exec dbms_stats.gather_table_stats('EDSR', 'REPORTE_SKU_UNQ', cascade => TRUE);
alter table "EPMM"."IDETRNEE" enable row movement;
alter table "EPMM"."IDETRNEE" shrink space compact;
Alter table "EPMM"."IDETRNEE" shrink space cascade;
alter table "EPMM"."IDETRNEE" disable row movement;
exec dbms_stats.gather_table_stats('EPMM', 'IDETRNEE', cascade => TRUE);
alter table "EDSR"."HP_VTEX_ERROR_JASON" enable row movement;
alter table "EDSR"."HP_VTEX_ERROR_JASON" shrink space compact;
Alter table "EDSR"."HP_VTEX_ERROR_JASON" shrink space cascade;
alter table "EDSR"."HP_VTEX_ERROR_JASON" disable row movement;
exec dbms_stats.gather_table_stats('EDSR', 'HP_VTEX_ERROR_JASON', cascade => TRUE);
alter table "EDBATOOLS"."LOG_ACCESOS" enable row movement;
alter table "EDBATOOLS"."LOG_ACCESOS" shrink space compact;
Alter table "EDBATOOLS"."LOG_ACCESOS" shrink space cascade;
alter table "EDBATOOLS"."LOG_ACCESOS" disable row movement;
exec dbms_stats.gather_table_stats('EDBATOOLS', 'LOG_ACCESOS', cascade => TRUE);
alter table "EDSR"."HP_VTEX_CARGA_PRECIOS_DIF_HIST" enable row movement;
alter table "EDSR"."HP_VTEX_CARGA_PRECIOS_DIF_HIST" shrink space compact;
Alter table "EDSR"."HP_VTEX_CARGA_PRECIOS_DIF_HIST" shrink space cascade;
alter table "EDSR"."HP_VTEX_CARGA_PRECIOS_DIF_HIST" disable row movement;
exec dbms_stats.gather_table_stats('EDSR', 'HP_VTEX_CARGA_PRECIOS_DIF_HIST', cascade => TRUE);
SQL> select index_name from dba_indexes where table_name='&TABLE_NAME';
select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME';
41294.5 41 gb size of the index
select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;
select owner,table_name,tablespace_name,pct_free,status from dba_tables where table_name='REPORTE_SKU_UNQ';
select owner,table_name,tablespace_name,pct_free,status from dba_tables where table_name in ('REPORTE_SKU_UNQ','IDETRNEE','HP_VTEX_ERROR_JASON','LOG_ACCESOS'); ssh ibmmoasim87@10.86.1.15 Winter@2020 col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / set pages 200 set lines 200 col OWNER format a20 col TABLE_NAME format a30 select owner,table_name,round((blocks*8),2)/1024/1024 "size (Gb)" , round((num_rows*avg_row_len/1024),2)/1024/1024 "actual_data (Gb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024 "wasted_space (Gb)" from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name in (select segment_name from (select owner, segment_name, bytes/1024/1024 meg from dba_segments where segment_type = 'TABLE' 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' order by bytes/1024/1024 desc) where rownum <= 20) order by 5 desc; OWNER TABLE_NAME size (Gb) actual_data (Gb) wasted_space (Gb) -------------------- ------------------------------ ---------- ---------------- ----------------- INKAFARMA CTX_PRODUCTOS_TRX 365.351959 233.127382 132.224577 INKAFARMA CTX_DSCTOS_PROD_TRX 201.39772 81.3065079 120.091212 INKAFARMA CTX_HEADER_TRX 279.54377 160.944894 118.598876 INKAFARMA CTX_IMPSTO_PROD_TRX 157.544823 88.987469 68.5573537 INKAFARMA CTX_DESCUENTOS_TRX 106.620827 45.3130752 61.3077516 INKAFARMA CTX_PAGOS_TRX 108.847061 56.133143 52.7139181 INKAFARMA CTX_IMPSTO_TRX 81.4876099 57.9801892 23.5074206 CONVERSION TMP_E3SXCONT 66.796936 51.0552948 15.7416412 QA_CONVERSION TMP_E3SXCONT 64.3545532 49.532947 14.8216062 select K.* from (select owner||'.'||table_name name,round((blocks*8)/1024,2) "TOTAL_SIZE(MB)" , round((num_rows*avg_row_len/1024/1024),2) "USADO(MB)", (round((blocks*8)/1024,2) - round((num_rows*avg_row_len/1024/1024),2)) "FRAG_SPACE(MB)", round((round((blocks*8)/1024,2) - round((num_rows*avg_row_len/1024/1024),2))*100/round((blocks*8)/1024,2)) "FRAG(%)" from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and (round((blocks*8)/1024,2) - round((num_rows*avg_row_len/1024/1024),2))>0 order by 4 desc) k where rownum<21 order by 5; ------------------------------------------------------------- -------------- ---------- -------------- ---------- INKAFARMA.CTX_PRODUCTOS_TRX 374120.41 238722.44 135397.97 36 INKAFARMA.CTX_DSCTOS_PROD_TRX 206231.27 83257.86 122973.41 60 INKAFARMA.CTX_HEADER_TRX 286252.82 164807.57 121445.25 42 QA_CONVERSION.LGTT_SALDO_PRODUCTO_ASR_FRAC_H 387388.41 313227.49 74160.92 19 INKAFARMA.CTX_IMPSTO_PROD_TRX 161325.9 91123.17 70202.73 44 INKAFARMA.CTX_DESCUENTOS_TRX 109179.73 46400.59 62779.14 58 INKAFARMA.CTX_PAGOS_TRX 111459.39 57480.34 53979.05 48 ECVENTA.LGTT_SALDO_PRODUCTO 223354.87 176733.45 46621.42 21 INKAFARMA.CTX_IMPSTO_TRX 83443.31 59371.71 24071.6 29 CONVERSION.TMP_E3SXCONT 68400.06 52280.62 16119.44 24 We have four options to reorganize fragmented tables: 1. alter table ... move + rebuild indexes 2. export / truncate / import 3. create table as select ( CTAS) 4. dbms_redefinition we will consider option 1 to defrag the tables Before we start , we need to keep an eye on cooresponding tablespace , temp and archive space proactively. sql> alter table <<table_name>> move; now check the status of indexes of the said table . If they are unusable. we need to rebuild those indexes. SQL> select status,index_name from user_indexes where table_name = <<table_name>>; STATUS INDEX_NAME -------- ------------------------------ UNUSABLE <<index_name>> SQL> alter index <<index_name>> rebuild; after this gather the stats of the said table SQL > exec dbms_stats.gather_table_stats('<<Owner_name>>','<<Table_NAme>>'); QA_CONVERSION.LGTT_SALDO_PRODUCTO_ASR_FRAC_H CONVERSION.TMP_E3SXCONT QA_CONVERSION.TMP_E3SXCONT QA_CONVERSION.TMP_E3SXCONT_20200209 QA_CONVERSION.TMP_E3SXCONT_20200208 QA_CONVERSION.TMP_E3SXCONT_20200207 QA_CONVERSION.TMP_E3SXCONT_20200206 QA_CONVERSION.TMP_E3SXCONT_20200205 QA_CONVERSION.TMP_E3SXCONT_20200204 QA_CONVERSION.TMP_E3SXCONT_20200202 QA_CONVERSION.E3SXCONT_20191113 QA_CONVERSION.BORRA_F24_FULL CONVERSION.BORRA_F24_FULL CONVERSION.LGTR_PRODUCTO_LOCAL QA_CONVERSION.LGTR_PRODUCTO_LOCAL CONVERSION.LGTM_MATERIALES_CENTRO QA_CONVERSION.LGTM_MATERIALES_CENTRO QA_CONVERSION.LGTT_SALDO_PRODUCTO_ASR_FRAC CONVERSION.LGTT_SALDO_PRODUCTO_ASR_FRAC NAME TOTAL_SIZE(MB) USADO(MB) FRAG_SPACE(MB) FRAG(%) ------------------------------------------------------------- -------------- ---------- -------------- ---------- INKAFARMA.CTX_PRODUCTOS_TRX 374120.41 238722.44 135397.97 36 INKAFARMA.CTX_DSCTOS_PROD_TRX 206231.27 83257.86 122973.41 60 INKAFARMA.CTX_HEADER_TRX 286252.82 164807.57 121445.25 42 QA_CONVERSION.LGTT_SALDO_PRODUCTO_ASR_FRAC_H 387388.41 313227.49 74160.92 19 INKAFARMA.CTX_IMPSTO_PROD_TRX 161325.9 91123.17 70202.73 44 INKAFARMA.CTX_DESCUENTOS_TRX 109179.73 46400.59 62779.14 58 INKAFARMA.CTX_PAGOS_TRX 111459.39 57480.34 53979.05 48 ECVENTA.LGTT_SALDO_PRODUCTO 223354.87 176733.45 46621.42 21 INKAFARMA.CTX_IMPSTO_TRX 83443.31 59371.71 24071.6 29 CONVERSION.TMP_E3SXCONT 68400.06 52280.62 16119.44 24 QA_CONVERSION.TMP_E3SXCONT_20200209 65898.11 50336.02 15562.09 24 QA_CONVERSION.TMP_E3SXCONT_20200208 65897.24 50335.32 15561.92 24 QA_CONVERSION.TMP_E3SXCONT_20200207 65829.68 50283.15 15546.53 24 QA_CONVERSION.TMP_E3SXCONT_20200206 65792.74 50255.22 15537.52 24 QA_CONVERSION.TMP_E3SXCONT_20200205 65751.04 50223.96 15527.08 24 QA_CONVERSION.TMP_E3SXCONT_20200204 65745.06 50219.56 15525.5 24 QA_CONVERSION.TMP_E3SXCONT_20200202 65702.77 50187.18 15515.59 24 QA_CONVERSION.TMP_E3SXCONT 65899.06 50721.74 15177.32 23 QA_CONVERSION.E3SXCONT_20191113 33036.75 25104.1 7932.65 24 INKAFARMA.CUA_AUDITORIA_REN 36065.85 29264.22 6801.63 19 set pages 200 set lines 200 col OWNER format a20 col TABLE_NAME format a30 select owner,table_name,round((blocks*8),2)/1024/1024 "size (Gb)" , round((num_rows*avg_row_len/1024),2)/1024/1024 "actual_data (Gb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024 "wasted_space (Gb)" from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name in (select segment_name from (select owner, segment_name, bytes/1024/1024 meg from dba_segments where segment_type = 'TABLE' 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' order by bytes/1024/1024 desc) where rownum <= 20) order by 5 desc; OWNER TABLE_NAME size (Gb) actual_data (Gb) wasted_space (Gb) -------------------- ------------------------------ ---------- ---------------- ----------------- INKAFARMA CTX_PRODUCTOS_TRX 365.351959 233.127382 132.224577 INKAFARMA CTX_DSCTOS_PROD_TRX 201.39772 81.3065079 120.091212 INKAFARMA CTX_HEADER_TRX 279.54377 160.944894 118.598876 INKAFARMA CTX_IMPSTO_PROD_TRX 157.544823 88.987469 68.5573537 INKAFARMA CTX_DESCUENTOS_TRX 106.620827 45.3130752 61.3077516 INKAFARMA CTX_PAGOS_TRX 108.847061 56.133143 52.7139181 INKAFARMA CTX_IMPSTO_TRX 81.4876099 57.9801892 23.5074206 alter table "PERFSTAT"."STATS$EVENT_HISTOGRAM" enable row movement; alter table "PERFSTAT"."STATS$EVENT_HISTOGRAM" shrink space compact; alter table "PERFSTAT"."STATS$EVENT_HISTOGRAM" disable row movement; alter table SYSADM.PS_GROUP_LINE ENABLE ROW MOVEMENT; alter table SYSADM.PS_GROUP_LINE shrink space compact; alter table SYSADM.PS_GROUP_LINE shrink space cascade; alter table SYSADM.PS_GROUP_LINE DISABLE ROW MOVEMENT;" alter table "INKAFARMA"."CTX_PRODUCTOS_TRX INKAFARMA CTX_DSCTOS_PROD_TRX INKAFARMA CTX_HEADER_TRX INKAFARMA CTX_IMPSTO_PROD_TRX column table_name format a25 column index_name format a25 column column_name format a25 select table_name,index_name,column_name from dba_ind_columns where table_owner='INKAFARMA' order by table_name,column_position; column table_owner format a15 column table_name format a20 column index_name format a20 column column_name format a20 Select owner,table_name,index_name,column_name FROM dba_ind_columns order by owner,table_name,column_name where owner=INKAFARMA AND table_name=CTX_PRODUCTOS_TRX; select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,PARTITIONED from dba_indexes where OWNER='INKAFARMA' AND table_name='CTX_PRODUCTOS_TRX';select table_name,round((blocks*8),2) “size (mb)” , round((num_rows*avg_row_len/1024),2) “actual_data (mb)”, (round((blocks*8),2) – round((num_rows*avg_row_len/1024),2)) “wasted_space (mb)” from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and owner=’&a’ order by 4 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;ssh ibmmoasim87@10.20.11.22 Winter@2020 Take the database size. schemas and tables which are in fragmented and how much we can reclaim.. find the tablespaces on which these scheams are running and tablespaces current space details and after reorg how much we recliamed. check lobs for tables. and find out the indexes. set pages 200 set lines 200 col OWNER format a20 col TABLE_NAME format a30 select owner,table_name,round((blocks*8),2)/1024/1024 "size (Gb)" , round((num_rows*avg_row_len/1024),2)/1024/1024 "actual_data (Gb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024 "wasted_space (Gb)" from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name in (select segment_name from (select owner, segment_name, bytes/1024/1024 meg from dba_segments where segment_type = 'TABLE' 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' order by bytes/1024/1024 desc) where rownum <= 20) order by 5 desc; OWNER TABLE_NAME size (Gb) actual_data (Gb) wasted_space (Gb) -------------------- ------------------------------ ---------- ---------------- ----------------- EDSR REPORTE_SKU_UNQ 81.0902405 0 81.0902405 EPMM IDETRNEE 131.693947 101.628764 30.0651832 EDSR HP_VTEX_ERROR_JASON 43.2466049 22.3318148 20.9147901 EDBATOOLS LOG_ACCESOS 96.3369827 81.0252001 15.3117827 EDSR HP_VTEX_CARGA_PRECIOS_DIF_HIST 45.8874817 33.8347077 12.052774 select owner,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name in ('EDSR') group by owner,segment_name; select owner, table_name, segment_name, index_name from dba_lobs where table_name='REPORTE_SKU_UNQ'; select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024 from dba_lobs a, dba_segments b where a.segment_name = b.segment_name and a.owner = b.owner and table_name='REPORTE_SKU_UNQ' order by b.bytes/1024/1024 ; select owner ,object_type,status, count(*),bytes/1024/1204 from dba_objects where owner='EDSR' group by owner, object_type, status; set pages 50000 lines 32767 select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb', ((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name' / select 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 table_name='SXMSPMAST' order by 4 desc / select 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 table_name='SXMSPHIST' order by 4 desc / SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='SXMSPMAST'; SUM(BYTES/1024/1024/1024) ------------------------- 11.1923828 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='SXMSPHIST'; select object_name,object_type,owner from dba_objects where object_name='VBRP'; OBJECT_NAME OBJECT_TYPE OWNER -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------------------ VBRP TABLE SAPSR3 select owner,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name in ('VBRP') group by owner,segment_name; set pages 50000 lines 32767 select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb', ((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name' /
Comments
Post a Comment