查看 Oracle 表空间的使用情况
一、查看表空间信息
SQL> select tablespace_name,file_name,user_bytes/1024/1024 sizeMB
from dba_data_files
order by tablespace_name;
TABLESPACE_NAME FILE_NAME SIZEMB
------------------------- ------------------------------------ ------------------
AUDIT_TBS +DATA/hisdb/datafile/audit_tbs.266.1034788047 1023
DATA_AIS +DATA/hisdb/datafile/data_ais.263.1034788049 29
DATA_APPLYOUT +DATA/hisdb/datafile/data_applyout.323.1034788051 25599
DATA_AQU +DATA/hisdb/datafile/data_aqu.322.1034788067 39
DATA_CAS +DATA/hisdb/datafile/data_cas.321.1034788067 5119
DATA_COM +DATA/hisdb/datafile/data_com.320.1034788067 5119
DATA_EMR +DATA/hisdb/datafile/data_emr.319.1034788069 5119
DATA_EXECDRUG +DATA/hisdb/datafile/data_execdrug.318.1034788069 25599
DATA_EXECUNDRUG +DATA/hisdb/datafile/data_execundrug.317.1034788087 20479
DATA_FEEDETAIL+DATA/hisdb/datafile/data_feedetail.316.1034788101 15359
DATA_FEEINFO +DATA/hisdb/datafile/data_feeinfo.315.1034788109 15359
DATA_FIN +DATA/hisdb/datafile/data_fin.314.1034788119 13639
DATA_GOA +DATA/hisdb/datafile/data_goa.313.1034788125 39
DATA_ITEMLIST +DATA/hisdb/datafile/data_itemlist.312.1034788125 25599
DATA_LIS +DATA/hisdb/datafile/data_lis.311.1034788143 5119
DATA_LOG +DATA/hisdb/datafile/data_log.310.1034788143 5119
DATA_MEDICINELIST +DATA/hisdb/datafile/data_medicinelist.309.1034788143 20479
DATA_MET +DATA/hisdb/datafile/data_met.308.1034788157 20479
DATA_ORDER +DATA/hisdb/datafile/data_order.305.103478822520479
DATA_ORDER +DATA/hisdb/datafile/data_order.304.103478824320479
DATA_ORDER +DATA/hisdb/datafile/data_order.306.103478819731999
DATA_ORDER +DATA/hisdb/datafile/data_order.307.103478816931999
DATA_ORDER +DATA/hisdb/datafile/data_order2.dbf 5119
DATA_ORDER +DATA/hisdb/datafile/data_order3.dbf 5119
DATA_OTHER +DATA/hisdb/datafile/data_other.303.1034788255 399
DATA_OUTPUT +DATA/hisdb/datafile/data_output.302.1034788255 25599
DATA_PHA +DATA/hisdb/datafile/data_pha.301.1034788271 10239
DATA_RECIPEDETAIL +DATA/hisdb/datafile/data_recipedetail.300.1034788275 10239
DATA_RECORD +DATA/hisdb/datafile/data_record.299.1034788281 20479
DATA_SEM +DATA/hisdb/datafile/data_sem.298.1034788293 29
DATA_USER +DATA/hisdb/datafile/data_user.297.1034788293 8919
EMR5 +DATA/hisdb/datafile/emr5.dbf 5999
EMR5 +DATA/hisdb/datafile/emr503.dbf 32699
EMR5 +DATA/hisdb/datafile/emr502.dbf 32699
EMR5 +DATA/hisdb/datafile/emr501.dbf 30719
EMR52012 +DATA/hisdb/datafile/emr52012.dbf 199
EMR52013 +DATA/hisdb/datafile/emr52013.dbf 199
EMR52014 +DATA/hisdb/datafile/emr52014.dbf 199
EMR52015 +DATA/hisdb/datafile/emr52015.dbf 199
EMR52016 +DATA/hisdb/datafile/emr52016.dbf 199
EMR52017 +DATA/hisdb/datafile/emr52017.dbf 199
EMR52018 +DATA/hisdb/datafile/emr52018.dbf 199
EMR52019 +DATA/hisdb/datafile/emr52019.dbf 199
EMR52020 +DATA/hisdb/datafile/emr5202003.dbf 10239
EMR52020 +DATA/hisdb/datafile/emr5202002.dbf 30719
EMR52020 +DATA/hisdb/datafile/emr52020.dbf 18569
EMR52020 +DATA/hisdb/datafile/emr5202001.dbf 30719
EMR52020 +DATA/hisdb/datafile/emr5202104.dbf.dbf 32699
--================================================================================
-- 表空间 EMR52021 对应的文件 +DATA/hisdb/datafile/emr5202101.dbf、
-- +DATA/hisdb/datafile/emr5202102.dbf、+DATA/hisdb/datafile/emr5202103.dbf、
-- +DATA/hisdb/datafile/emr5202104.dbf、+DATA/hisdb/datafile/emr5202105.dbf、
-- +DATA/hisdb/datafile/emr5202106.dbf 都已经达到上限 32GB
EMR52021 +DATA/hisdb/datafile/emr5202106.dbf 32699
EMR52021 +DATA/hisdb/datafile/emr5202105.dbf 32699
EMR52021 +DATA/hisdb/datafile/emr5202104.dbf 32699
EMR52021 +DATA/hisdb/datafile/emr5202103.dbf 32699
EMR52021 +DATA/hisdb/datafile/emr5202102.dbf 32766.9375
EMR52021 +DATA/hisdb/datafile/emr5202101.dbf 30719
--==================================================================================
EMR52022 +DATA/hisdb/datafile/emr5202201.dbf 32699
INDEX_AIS +DATA/hisdb/datafile/index_ais.296.1034788297 29
INDEX_APPLYOUT+DATA/hisdb/datafile/index_applyout.295.1034788297 15239
INDEX_AQU +DATA/hisdb/datafile/index_aqu.294.1034788309 29
INDEX_CAS +DATA/hisdb/datafile/index_cas.293.1034788309 5119
INDEX_COM +DATA/hisdb/datafile/index_com.292.1034788309 5119
INDEX_EMR +DATA/hisdb/datafile/index_emr.291.1034788311 29
INDEX_EXECDRUG+DATA/hisdb/datafile/index_execdrug.290.1034788311 10239
INDEX_EXECUNDRUG +DATA/hisdb/datafile/index_execundrug.289.1034788317 10239
INDEX_FEEDETAIL +DATA/hisdb/datafile/index_feedetail.288.1034788321 12287
INDEX_FEEINFO +DATA/hisdb/datafile/index_feeinfo.287.1034788329 13939
INDEX_FIN +DATA/hisdb/datafile/index_fin.286.1034788337 6569
INDEX_GOA +DATA/hisdb/datafile/index_goa.285.1034788343 29
INDEX_ITEMLIST+DATA/hisdb/datafile/index_itemlist.284.1034788343 15359
INDEX_LIS +DATA/hisdb/datafile/index_lis.283.1034788355 5119
INDEX_LOG +DATA/hisdb/datafile/index_log.282.1034788355 5119
INDEX_MEDICINELIST +DATA/hisdb/datafile/index_medicinelist.281.1034788355 10949
INDEX_MET +DATA/hisdb/datafile/index_met.280.1034788361 10179
INDEX_ORDER +DATA/hisdb/datafile/index_order.279.1034788369 10239
INDEX_OTHER +DATA/hisdb/datafile/index_other.278.10347883755119
INDEX_OUTPUT +DATA/hisdb/datafile/index_output.277.1034788375 8679
INDEX_PHA +DATA/hisdb/datafile/index_pha.276.1034788381 16159
INDEX_RECIPEDETAIL +DATA/hisdb/datafile/index_recipedetail.258.1034788387 5119
INDEX_RECORD +DATA/hisdb/datafile/index_record.325.1034788389 3059
INDEX_SEM +DATA/hisdb/datafile/index_sem.268.1034788391 29
INDEX_USER +DATA/hisdb/datafile/index_user.271.1034788391 299
NDQS +DATA/hisdb/datafile/ndqsdata01.dbf 499
NEUCBUS +DATA/hisdb/datafile/neucbus_data1 23119
--==================================================================================
-- 表空间 NEUICU 对应的文件 +DATA/hisdb/datafile/neuicu_data1 达到上限 32GB
NEUICU +DATA/hisdb/datafile/neuicu_data1 32766.9375
--===================================================================================
NFEMR +DATA/hisdb/datafile/nfemr.dbf 199
NTSDATA +DATA/hisdb/datafile/ntsdata01.dbf 2047
--====================================================================================
-- 表空间 SYSAUX 对应的文件 +DATA/hisdb/datafile/sysaux.326.1034787445 和
-- +DATA/hisdb/datafile/sysaux001 达到上限 32GB
SYSAUX +DATA/hisdb/datafile/sysaux.326.1034787445 32739
SYSAUX +DATA/hisdb/datafile/sysaux001 32699
--===================================================================================
--===================================================================================
-- 表空间 SYSTEM 对应的文件 +DATA/hisdb/datafile/system.275.1034787445 达到上限 32GB
SYSTEM +DATA/hisdb/datafile/system.275.1034787445 32749
--==================================================================================
UNDOTBS1 +DATA/hisdb/datafile/undotbs1.265.1034787445 30719
UNDOTBS2 +DATA/hisdb/datafile/undotbs2.264.1034787607 20479
USERS +DATA/hisdb/datafile/users.274.1034787445 30719
二、查看表空间的利用率
SELECT total.tablespace_name,Round(total.MB,2) AS Total_MB,Round(total.MB - free.MB,2) AS Used_MB,Round(( 1 - free.MB / total.MB ) * 100,2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,(SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
-------------------- ---------- ---------- -----------------------------------------
DATA_USER 8459 7027.94 83.08%
DATA_COM 1970 1092.75 55.47%
INDEX_MET 9212 7032.19 76.34%
INDEX_EMR 1300 3.88 .3%
EMR52012 200 1 .5%
EMR52014 200 1 .5%
EMR52018 200 1 .5%
EMR52021 200 1 .5%
EMR52023 132800 103337.75 77.81%
UNDOTBS1 1465 171.31 11.69%
SYSAUX 18660 17771.06 95.24%
DATA_FIN 13974 13300.5 95.18%
DATA_MET 9446 4894 51.81%
DATA_AQU 1300 .63 .05%
DATA_SEM 1300 .88 .07%
DATA_LIS 1300 89.56 6.89%
INDEX_FEEINFO 16872 7872.31 46.66%
EMR52024 34724 32740.25 94.29%
DATA_RECORD 10674 10103.38 94.65%
DATA_EXECUNDRUG 10608 6652.44 62.71%
INDEX_PHA 10112 4997 49.42%
INDEX_FEEDETAIL 8300 7784.19 93.79%
INDEX_ORDER 8300 6880.19 82.89%
USERS 14908.75 14183.19 95.13%
DATA_GOA 1300 5.38 .41%
DATA_OTHER 1900 157.5 8.29%
DATA_MEDICINELIST 16354 11622.25 71.07%
DATA_APPLYOUT 15920 14579.38 91.58%
DATA_ORDER 63730 62025.81 97.33%
DATA_RECIPEDETAIL 9127 4375.44 47.94%
INDEX_COM 2500 997.13 39.89%
INDEX_FIN 7618 5891 77.33%
INDEX_AQU 1300 .5 .04%
INDEX_OTHER 2600 20.25 .78%
INDEX_MEDICINELIST 15684 6032.25 38.46%
INDEX_EXECDRUG 6072 5696.19 93.81%
EMR5 33250 20830.69 62.65%
EMR52022 21456 18064.25 84.19%
SYSTEM 1480 905.56 61.19%
DATA_FEEDETAIL 7564 7184.44 94.98%
INDEX_LIS 1300 63.06 4.85%
INDEX_RECIPEDETAIL 2000 1824.13 91.21%
EMR52013 200 1 .5%
DATA_CAS 2600 147.88 5.69%
DATA_AIS 1300 9.44 .73%
DATA_OUTPUT 14602 13799.38 94.5%
INDEX_GOA 1300 6.31 .49%
INDEX_SEM 1300 .38 .03%
INDEX_CAS 1300 89.25 6.87%
INDEX_AIS 1300 1.81 .14%
EMR52016 200 1 .5%