Oracle Database：SYSAUX表空间的使用详情
查看当前系统中SYSAUX相关的表空间的使用率
|
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 |
SQL> set linesize 400 SQL> col Tablespace_Name for a23 SQL> SQL> SELECT * FROM ( 2 SELECT D.TABLESPACE_NAME, 3 SPACE || 'M' "SUM_SPACE(M)", 4 BLOCKS "SUM_BLOCKS", 5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 7 "USED_RATE(%)", 8 FREE_SPACE || 'M' "FREE_SPACE(M)" 9 FROM ( SELECT TABLESPACE_NAME, 10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 11 SUM (BLOCKS) BLOCKS 13 FROM DBA_DATA_FILES 12 GROUP BY TABLESPACE_NAME) D, 14 ( SELECT TABLESPACE_NAME, 15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 16 FROM DBA_FREE_SPACE 17 GROUP BY TABLESPACE_NAME) F 18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 19 UNION ALL 20 SELECT D.TABLESPACE_NAME, 21 SPACE || 'M' "SUM_SPACE(M)", 22 BLOCKS SUM_BLOCKS, 23 USED_SPACE || 'M' "USED_SPACE(M)", 24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 26 FROM ( SELECT TABLESPACE_NAME, 27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 28 SUM (BLOCKS) BLOCKS 30 FROM DBA_TEMP_FILES 29 GROUP BY TABLESPACE_NAME) D, 31 ( SELECT TABLESPACE_NAME, 32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 34 FROM V$TEMP_SPACE_HEADER 35 GROUP BY TABLESPACE_NAME) F 36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 37 ORDER BY 1) 38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP'); TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M) --------------- ----------------------------------------- ---------- ----------------------------------------- ----------------------------------------- ----------------------------------------- SYSAUX 73254M 9376512 50928.56M 69.52% 22325.44M SYSTEM 1580M 202240 1039.56M 65.79% 540.44M TEMP 2118M 271104 8M .38% 2110M UNDOTBS1 1905M 243840 75.06M 3.94% 1829.94M SQL> |
SYSAUX的使用率将近50G左右。
查看SYSAUX的具体使用消耗在了什么上面：
|
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 |
SQL> SELECT occupant_name "Item", 2 space_usage_kbytes / 1048576 "Space Used (GB)", 3 schema_name "Schema", 4 move_procedure "Move Procedure" 5 FROM v$sysaux_occupants 6 ORDER BY 2; Item Space Used (GB) Schema Move Procedure ------------------------ --------------- ---------------------------------------------------------------- ---------------------------------------------------------------- AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc ULTRASEARCH 0 WKSYS MOVE_WK TSM 0 TSMSYS ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK STATSPACK 0 PERFSTAT AUTO_TASK .000305176 SYS ORDIM .000427246 ORDSYS ordsys.ord_admin.move_ordim_tblspc STREAMS .000976563 SYS EM_MONITORING_USER .001281738 DBSNMP Item Space Used (GB) Schema Move Procedure ------------------------ --------------- ---------------------------------------------------------------- ---------------------------------------------------------------- LOGSTDBY .001342773 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE PL/SCOPE .001525879 SYS SQL_MANAGEMENT_BASE .001647949 SYS SMON_SCN_TIME .003173828 SYS WM .003417969 WMSYS DBMS_WM.move_proc EXPRESSION_FILTER .003540039 EXFSYS TEXT .003540039 CTXSYS DRI_MOVE_CTXSYS XSAMD .005004883 OLAPSYS DBMS_AMD.Move_OLAP_Catalog SM/OTHER .008117676 SYS LOGMNR .013061523 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE ORDIM/ORDDATA .013244629 ORDDATA ordsys.ord_admin.move_ordim_tblspc Item Space Used (GB) Schema Move Procedure ------------------------ --------------- ---------------------------------------------------------------- ---------------------------------------------------------------- JOB_SCHEDULER .026550293 SYS XSOQHIST .03729248 SYS DBMS_XSOQ.OlapiMoveProc AO .03729248 SYS DBMS_AW.MOVE_AWMETA EM .065490723 SYSMAN emd_maintenance.move_em_tblspc SDO .072509766 MDSYS MDSYS.MOVE_SDO XDB .124023438 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE SM/ADVISOR .313659668 SYS SM/OPTSTAT .422302246 SYS SM/AWR 48.5065918 SYS 已选择31行。 SQL> |
可以看到，占用空间[……]
- 发表于：
-
作者：
Adamhuan
- 分类： Oracle Database
- 标签： AWR, database, dba_hist_snapshot, dba_hist_wr_control, dbms_stats.alter_stats_history_retention, dbms_stats.get_stats_history_retention, dbms_workload_repository.drop_snapshot_range, dual, execdbms_workload_repository.modify_snapshot_settings, Oracle, SYSAUX, wrm$_wr_control, 使用, 使用率, 使用详情, 数据库, 详情, 调优, 调整, 过高, 配置
- 发表评论: Oracle Database：SYSAUX表空间的使用详情