Script check dung lượng tablespace, table, index

1. Check tablespace size

Select * from sysibmadm.tbsp_utilization where tbsp_name='MyTblSpace';

Dung lượng sử dụng, dung lượng trống:

db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \
substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \
smallint((float(tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100)as Percent_Free_Space, \
int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization"

Ouput:

TABLESPACE_NAME                TYPE       STATUS               SIZE_MEG     PERCENT_FREE_SPACE MEG_FREE_SPACE
------------------------------ ---------- -------------------- -------------------- -    -----------------------------   ---------------------
SYSCATSPACE                      DMS             NORMAL                     240                                       0                           0
TEMPSPACE1                       DMS             NORMAL                      100                                     99                         99
USERSPACE1                       DMS             NORMAL                   10240                                    32                     3363
SYSTOOLSPACE                   SMS              NORMAL                          0                                      0                           0
SYSTOOLSTMPSPACE           SMS             NORMAL                           0                                      0                           0

Dung lượng theo %:

db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \
substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \
decimal((float(tbsp_total_size_kb - tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100,3,1)as Percent_used_Space, \
int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization where tbsp_type='DMS'"

Output

TABLESPACE_NAME                TYPE       STATUS                        SIZE_MEG             PERCENT_USED_SPACE MEG_FREE_SPACE
------------------------------------------ ---------- -------------------- ----------- -------------------- ---------------------------------- ----------------------------------
SYSCATSPACE                           DMS        NORMAL                                 64                          61.1                                                   24
USERSPACE1                             DMS        NORMAL                                 32                          53.1                                                   15
TBS32K                                         DMS        NORMAL                              69996                      77.8                                            15507
IDX_TBS                                       DMS        NORMAL                              33619                       99.0                                                315
SYSTOOLSPACE                        DMS        NORMAL                                 32                          0.2                                                     31

2.Check data size

select 

  char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime 

  ,substr(t.tabschema,1,8)||'.'||substr(t.tabname,1,24) as tabname 

  ,card as rows_per_table 

  ,decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb 

  ,decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as allocated_mb 

from 

  syscat.tables t , syscat.tablespaces b 

where t.tbspace=b.tbspace 

order by 5 desc with ur;

3. Check table size

select 

   a.CARD*(sum(b.AVGCOLLEN)+10)/1024/1024 as Tablesize_in_MB 

from 

   syscat.tables as a, syscat.columns as b 

where 

   a.TABNAME = b.TABNAME and b.TABNAME = 'TABLE_NAME' group by a.CARD;

4. Check index size

select 

  rtrim(substr(i.tabschema,1,8))||'.'||rtrim(substr( i.tabname, 1,24)) as tabname 

 ,decimal(sum(i.nleaf)/( 1024 / (b.pagesize/1024)),12,2) as indx_used_per_table_mb 

from 

   syscat.indexes i, syscat.tables t , syscat.tablespaces b 

where 

   i.tabschema is not null and i.tabname=t.tabname 

   and i.tabschema=t.tabschema and t.tbspace=b.tbspace 

group by 

   i.tabname,i.tabschema, b.pagesize order by 2 desc with ur;

==================================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

#OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #tự học oracle