devbox
寸_铁
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

查看 Oracle 表空间的使用情况_oracle查看表空间使用情况

作者：寸_铁 | 2024-07-22 21:27:47

赞

踩

oracle查看表空间使用情况

查看 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

  • 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
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113

二、查看表空间的利用率

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%

  • 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
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/寸_铁/article/detail/866848
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号