oracle 查看表大小与缩表
访问量:1208
创建时间:2023-03-01
查看表大小
有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：
select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_BIZKEY';
[oracle@esbprddb ~]$ sqlplus abcd/abcd@127.0.0.1:1521/ESBPRDPDB
SQL> select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_BIZKEY';
ROUND(SUM(BYTES)/1024/1024,2)||'M'
--------------------------------------------------------------------------------
35408M
SQL> select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_BODY_PAYLOAD';
ROUND(SUM(BYTES)/1024/1024,2)||'M'
--------------------------------------------------------------------------------
137840M
SQL> select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_HEADER_PAYLOAD';
ROUND(SUM(BYTES)/1024/1024,2)||'M'
--------------------------------------------------------------------------------
29128M
SQL> select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_INSTANCE';
ROUND(SUM(BYTES)/1024/1024,2)||'M'
--------------------------------------------------------------------------------
45416M
SQL> select round(sum(BYTES)/1024/1024,2)||'M' from dba_segments where segment_name='abcd_LOG_OUTBOUND';
ROUND(SUM(BYTES)/1024/1024,2)||'M'
--------------------------------------------------------------------------------
35474M
或者：
SQL> set linesize 2000;
SQL> column SEGMENT_NAME format a240;
SQL> select segment_name,Sum(bytes)/1024/1024 from user_extents group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024
---------------------------------------- --------------------
abcd_LOG_BIZKEY 35408
abcd_LOG_OUTBOUND 35474
LOBZ_TRACKID_IDX1 18789
LOBZ_SERVNO_IDX1 14902
LOBP_PLID_IDX2 7096
abcd_LOG_HEADER_PAYLOAD 29128
LOBP_TRACKID_IDX1 12136
SYS_LOB0000094670C00003$$ 178639
abcd_LOG_INSTANCE 45416
abcd_LOG_BODY_PAYLOAD 137840
SYS_LOB0000094681C00003$$ 18204.1875
LOBZ_TRACKID_IDX1 18789
...省略部分输出...
计算表实际使用的空间
SQL> analyze table abcd_LOG_BIZKEY compute statistics;
SQL> select num_rows * avg_row_len from user_tables where table_name = 'abcd_LOG_BIZKEY';
LOB 表查询
在Oracle中，LOB(Large Object，大型对象)类型的字段现在用得越来越多了。因为这种类型的字段，容量大(最多能容纳4GB的数据)，且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域(如图象、档案等)。 LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图象、影音等)。 而CLOB，即字符型大型对象(Character Large Object)，则与字符集相关，适于存贮文本型的数据(如历史档案、大部头著作等)。
#查询CLOB表
SQL> SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'CLOB') GROUP BY SEGMENT_NAME ORDER BY 2 DESC;
SEGMENT_NAME G
---------------------------------------- ----------
abcd_LOG_BODY_PAYLOAD 134.61
abcd_LOG_HEADER_PAYLOAD 28.45
abcd_LOG_ERROR_PAYLOAD 1.13
abcd_BIZKEY_JOB_FAILED .44
abcd_USER_LOG .02
abcd_MN_RULES_RUNING 0
abcd_SERVICES 0
abcd_MN_NOTICES 0
abcd_SERVICES_BBAK 0
abcd_SERVICES_BAK 0
abcd_CACHE_REFRESH 0
SEGMENT_NAME G
---------------------------------------- ----------
abcd_NTY_JOB 0
abcd_SERV_RETRY_JOB 0
abcd_ESS_INSTANCE 0
abcd_NTY_JOB_HIS 0
15 rows selected.
#从DBA_SEGMENTS 查看
SQL> SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G FROM DBA_SEGMENTS GROUP BY SEGMENT_NAME ORDER BY 2 DESC;
SEGMENT_NAME G
---------------------------------------- ----------
SYS_LOB0000094670C00003$$ 174.45
abcd_LOG_BODY_PAYLOAD 134.61
abcd_LOG_INSTANCE 44.35
abcd_LOG_OUTBOUND 34.64
abcd_LOG_BIZKEY 34.58
abcd_LOG_HEADER_PAYLOAD 28.45
LOBZ_TRACKID_IDX1 18.35
SYS_LOB0000094681C00003$$ 17.78
LOBZ_SERVNO_IDX1 14.55
### abcd_LOG_BODY_PAYLOAD 表的列 PAYLOAD_DATA 存储在 SYS_LOB0000094670C00003$$ 中
set linesize 2000;
column TABLE_NAME format a25;
column COLUMN_NAME format a20;
column SEGMENT_NAME format a30;
column SEGMENT_TYPE format a20;
column G format 9999999999;
SQL> SELECT B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME, a.SEGMENT_TYPE, ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G FROM DBA_SEGMENTS A LEFT JOIN DBA_LOBS B ON A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.SEGMENT_NAME HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1 GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;
TABLE_NAME COLUMN_NAME SEGMENT_NAME SEGMENT_TYPE G
------------------------- -------------------- ------------------------------ -------------------- -----------
LOS_BIZ_STATUS_IDX3 INDEX PARTITION 1
LOS_TOKEN_IDX4 INDEX PARTITION 3
LOS_TOKEN_IDX5 INDEX PARTITION 5
LOS_START_TIME_IDX6 INDEX 2
LOBZ_SERVNO_IDX1 INDEX PARTITION 15
LOBP_PLID_IDX2 INDEX PARTITION 7
LOHP_PLID_IDX2 INDEX PARTITION 2
LOS_INST_STATUS_IDX2 INDEX PARTITION 1
LOO_INST_STATUS_IDX2 INDEX PARTITION 8
LOO_SERVNO_IDX1 INDEX PARTITION 6
LOBZ_TRACKID_IDX1 INDEX PARTITION 18
TABLE_NAME COLUMN_NAME SEGMENT_NAME SEGMENT_TYPE G
------------------------- -------------------- ------------------------------ -------------------- -----------
abcd_SERVER_INFO TABLE 2
abcd_LOG_BIZKEY TABLE PARTITION 35
abcd_LOG_ERROR_PAYLOAD TABLE 1
LOHP_TRACKID_IDX1 INDEX PARTITION 3
LOS_REQ_FROM_IDX8 INDEX 2
WLI_QS_REPORT_DATA TABLE 6
LOBP_TRACKID_IDX1 INDEX PARTITION 12
abcd_LOG_ERROR_PAYLOAD PAYLOAD_DATA SYS_LOB0000094681C00003$$ LOBSEGMENT 18
LOO_BIZ_STATUS_IDX3 INDEX PARTITION 4
LOO_SERVNO_IDX2 INDEX PARTITION 5
LOS_END_TIME_IDX7 INDEX 3
TABLE_NAME COLUMN_NAME SEGMENT_NAME SEGMENT_TYPE G
------------------------- -------------------- ------------------------------ -------------------- -----------
LOBZ_COL1_IDX3 INDEX PARTITION 8
abcd_LOG_BODY_PAYLOAD TABLE PARTITION 135
abcd_BIZKEY_JOB TABLE 1
LOBZ_UIDX1 INDEX 7
abcd_LOG_INSTANCE TABLE PARTITION 44
LOBZ_COL2_IDX4 INDEX PARTITION 7
abcd_LOG_BODY_PAYLOAD PAYLOAD_DATA SYS_LOB0000094670C00003$$ LOB PARTITION 174
abcd_LOG_HEADER_PAYLOAD TABLE PARTITION 28
LOS_SERVNO_IDX1 INDEX PARTITION 4
abcd_LOG_OUTBOUND TABLE PARTITION 35
shrink的lob列的表 生产上如果使用表频繁的DML操作不建议shrink,会卡死，引起严重的业务后果
比如说在你大量的删除PAY_LOG_DETAILS后（高水位没有下降），需要执行收缩，收缩的时候建议在不要再业务高峰期（否则可能引起很大的性能问题）
Shrink对应的表语句如下：
注意：由于在线上，不能进行有表锁的操作，所以我并没有采用这种办法
alter table TABLE_NAME enable ROW MOVEMENT;--启动行移动功能
alter table TABLE_NAME shrink space compact; --只整理碎片 不回收空间
-- 重置高水位，此时不能有DML操作
alter table TABLE_NAME shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行
alter table TABLE_NAME disable ROW MOVEMENT;--关闭行移动
参考文档：https://www.cnblogs.com/hmwh/p/12380211.html
登陆评论:
使用GITHUB登陆