当前表空间使用率

Status    Tablespace Name        TS Type      Ext. Mgt.  Seg. Mgt.    Tablespace Size    Used (in bytes) Pct. Used
--------- ---------------------- ------------ ---------- --------- ------------------ ------------------ ---------
ONLINE    RECHARGE               PERMANENT    LOCAL      AUTO          37,580,963,840        475,529,216         1
ONLINE    SYSAUX                 PERMANENT    LOCAL      AUTO           1,247,805,440      1,228,210,176        98
ONLINE    SYSTEM                 PERMANENT    LOCAL      MANUAL           996,147,200        990,380,032        99
ONLINE    TEMP                   TEMPORARY    LOCAL      MANUAL         2,052,063,232      2,051,014,656       100
ONLINE    UNDOTBS1               UNDO         LOCAL      MANUAL         3,213,885,440      3,211,788,288       100
ONLINE    USERS                  PERMANENT    LOCAL      AUTO               5,242,880          1,376,256        26
                                                                   ------------------ ------------------ ---------
avg                                                                                                             71
sum                                                                    45,096,108,032      7,958,298,624

6 rows selected.


ptc.used 指定块中数据使用空间的最低百分比

sysaux, system 使用率奇高

查询 sysaux 是否具有自动扩展功能

SQL> select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files;

TABLESPACE AUT INCREMENT_BY
---------- --- ------------
USERS      YES          160
UNDOTBS1   YES          640
SYSAUX     YES         1280
SYSTEM     YES         1280
RECHARGE   NO             0
RECHARGE   NO             0

6 rows selected.

 

sysaux, system 都具有自动扩展功能, 100% 使用率, 暂时可不处理 假如 sysaux, system 表空间不具备自动扩展功能, 只能够通过增加数据文件方法进行表空间扩容. alter tablespace system add datafile 'xxxxxx' size xxxM;

系统使用了 awr 功能, snap_shot 被保留到 sysaux 空间中

SQL> select count( distinct snap_id ) from dba_hist_snapshot order by 1;

COUNT(DISTINCTSNAP_ID)
----------------------
                   217

当前备份副本为 217 次

查询 awr 保留规则 

SQL> col snap_interval format a25;
SQL> col retention format a25;
SQL>  select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                 TOPNSQL
---------- ------------------------- ------------------------- ----------
1058431128 +00000 01:00:00.0         +00008 00:00:00.0         DEFAULT

建议减少备份次数, 减少保留备份天数 

exec dbms_workload_repository.modify_snapshot_settings(interval=>240, retention=>3*24*60);