以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.
通过分离这些组件和功能,SYSTEM表空间的负荷得以减轻.避免了因反复创建一些相关对象及组件引起的SYSTEM表空间碎片问题。
SYSAUX 表空间也是在在DB 创建或者升级时自动创建的。 如果在手工使用SQL创建DB时没有指定SYSAUX tablespace,那么创建语句会报错。 无法执行。
在正常操作下, 不能drop 和rename SYSAUX 表空间。 如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限.
使用SYSAUX表空间的组件 以前版本所在表空间
Analytical Workspace Object Table SYSTEM
Enterprise Manager Repository OEM_REPOSITORY
LogMiner SYSTEM
Logical Standby SYSTEM
OLAP API History Tables CWMLITE
Oracle Data Mining ODM
Oracle Spatial SYSTEM
Oracle Streams SYSTEM
Oracle Text DRSYS
Oracle Ultra Search DRSYS
Oracle interMedia ORDPLUGINS Components SYSTEM
Oracle interMedia ORDSYS Components SYSTEM
Oracle interMedia SI_INFORMTN_SCHEMA Components SYSTEM
Server Manageability Components New in Oracle Database 10g
Statspack Repository User-defined
Unified Job Scheduler New in Oracle Database 10g
Workspace Manager SYSTEM
在DB 创建时指定SYSAUX 表空间,必须指定如下4个属性:
(1). PERMANENT
(2). READ WRITE
(3). EXTENT MANAGMENT LOCAL
(4). SEGMENT SPACE MANAGMENT AUTO
我们不能使用alter tablespace 来修改这4个属性,同样也不能drop 和rename SYSAUX 表空间。
1. 不能删除
SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace
2. 不能重命名
SQL> alter tablespace SYSAUX rename to OPT_TBS;
alter tablespace SYSAUX rename to OPT_TBS
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace
3. 不能置为read only
SQL> alter tablesapce SYSAUX read only;
alter tablesapce SYSAUX read only
*
ERROR at line 1:
ORA-00940: invalid ALTER command
我们可以使用 v$sysaux_occupants 视图来查看SYSAUX 表空间里的组件信息,如:
set linesize 120 pages 999;
col OCCUPANT_NAME for a30
col SCHEMA_NAME for a20
col MOVE_PROCEDURE for a50
SQL>SELECT t.OCCUPANT_NAME,SUM(t.SPACE_USAGE_KBYTES)/1024/1024
FROM gV$SYSAUX_OCCUPANTS t
GROUP BY t.OCCUPANT_NAME
ORDER BY 2 DESC;
通过以下sql分析sysaux表空间的使用情况:
WITH
ts_total_space AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks,
SUM(maxbytes) as maxbytes
FROM dba_data_files
GROUP BY TableSpace_name),
ts_free_space AS (SELECT
ddf.TableSpace_name,
NVL(SUM(dfs.bytes),0) as bytes,
NVL(SUM(dfs.blocks),0) as blocks
FROM
dba_data_files ddf,
dba_free_space dfs
WHERE ddf.file_id = dfs.file_id(+)
GROUP BY ddf.TableSpace_name),
ts_total_segments AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM dba_segments
GROUP BY TableSpace_name),
ts_total_extents AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM dba_extents
GROUP BY TableSpace_name)
SELECT
dt.TableSpace_name as "TSname",
dt.status as "TSstatus",
ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",
ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",
ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",
ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",
ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",
ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",
ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",
CASE
WHEN ttsp.maxbytes = 0 THEN 'No' ELSE 'Yes'
END as "AutoExtFile",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))
END as "TSMaxSizeMb",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))
END as "TSMaxUsedPrct",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))
END as "TSMaxFreePrct"
FROM
dba_TableSpaces dt,
ts_total_space ttsp,
ts_free_space tfs,
ts_total_segments ttse,
ts_total_extents tte
WHERE dt.TableSpace_name = ttsp.TableSpace_name(+)
AND dt.TableSpace_name = tfs.TableSpace_name(+)
AND dt.TableSpace_name = ttse.TableSpace_name(+)
AND dt.TableSpace_name = tte.TableSpace_name(+)
AND dt.TableSpace_name = 'SYSAUX';
SQL> desc v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
OCCUPANT_NAME VARCHAR2(64)
OCCUPANT_DESC VARCHAR2(64)
SCHEMA_NAME VARCHAR2(64)
MOVE_PROCEDURE VARCHAR2(64)
MOVE_PROCEDURE_DESC VARCHAR2(64)
SPACE_USAGE_KBYTES NUMBER
这些组件占据这SYSAUX 表空间,所以这些组件的大小也就决定SYSAUX 表空间的大小。 根据这些组件创建时的初始化大小,SYSAUX 至少需要400M的空间。
还有一点要注意,就是这里的schema_name 对应的是用户名。
在 v$sysaux_occupants 视图里有个 move_procudure的过程。 这个过程就是用迁移组件信息的。就是对于已经安装好的组件,如果我们想把这些组件放到其他的空间,就可以使用这个存储过程。如果没有对应的过程,就不可移动。
AWR 是SYSAUX 中占用空间最多的组件。 对于一个有10个并发session 的系统,就需要200M 的空间。当然,也可以修改AWR的保存策略来控制AWR所占用空间的大小。
如果希望转移这些系统对象的表空间,可以使用相对应的系统包实现:
(现在以LOGMNR 为例)
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants;
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
LOGMNR LogMiner 8064 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY Logical Standby 1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 5376
PL/SCOPE PL/SQL Identifier Collection 1600
STREAMS Oracle Streams 1024
AUDIT_TABLES DB audit tables 0 DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB XDB 130944 XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO Analytical Workspace Object Table 46208 DBMS_AW.MOVE_AWMETA
XSOQHIST OLAP API History Tables 46208 DBMS_XSOQ.OlapiMoveProc
XSAMD OLAP Catalog 3712 DBMS_AMD.Move_OLAP_Catalog
SM/AWR Server Manageability - Automatic Workload Repository 358464
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
SM/ADVISOR Server Manageability - Advisor Framework 176128
SM/OPTSTAT Server Manageability - Optimizer Statistics History 218176
SM/OTHER Server Manageability - Other Components 8256
STATSPACK Statspack Repository 0
SDO Oracle Spatial 54336 MDSYS.MOVE_SDO
WM Workspace Manager 3136 DBMS_WM.move_proc
ORDIM Oracle Multimedia ORDSYS Components 448 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA Oracle Multimedia ORDDATA Components 13056 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components 0 ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components 0 ordsys.ord_admin.move_ordim_tblspc
EM Enterprise Manager Repository 100544 emd_maintenance.move_em_tblspc
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
TEXT Oracle Text 3456 DRI_MOVE_CTXSYS
ULTRASEARCH Oracle Ultra Search 0 MOVE_WK
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0 MOVE_WK
EXPRESSION_FILTER Expression Filter System 3712
EM_MONITORING_USER Enterprise Manager Monitoring User 1792
TSM Oracle Transparent Session Migration User 0
SQL_MANAGEMENT_BASE SQL Management Base Schema 1728
AUTO_TASK Automated Maintenance Tasks 320
JOB_SCHEDULER Unified Job Scheduler 17344
31 rows selected.
执行转移包:
SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');
PL/SQL procedure successfully completed.
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants;(再次查看转移情况)
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
LOGMNR LogMiner 0 SYS.DBMS_LOGMNR_D.SET_TABLESPACE --注意,这里占空的空间变成了0. 数据迁移到了USERS 表空间
LOGSTDBY Logical Standby 1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 5376
PL/SCOPE PL/SQL Identifier Collection 1600
STREAMS Oracle Streams 1024
AUDIT_TABLES DB audit tables 0 DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB XDB 130944 XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO Analytical Workspace Object Table 46208 DBMS_AW.MOVE_AWMETA
XSOQHIST OLAP API History Tables 46208 DBMS_XSOQ.OlapiMoveProc
XSAMD OLAP Catalog 3712 DBMS_AMD.Move_OLAP_Catalog
SM/AWR Server Manageability - Automatic Workload Repository 358464
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
SM/ADVISOR Server Manageability - Advisor Framework 176128
SM/OPTSTAT Server Manageability - Optimizer Statistics History 218176
SM/OTHER Server Manageability - Other Components 8256
STATSPACK Statspack Repository 0
SDO Oracle Spatial 54336 MDSYS.MOVE_SDO
WM Workspace Manager 3136 DBMS_WM.move_proc
ORDIM Oracle Multimedia ORDSYS Components 448 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA Oracle Multimedia ORDDATA Components 13056 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components 0 ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components 0 ordsys.ord_admin.move_ordim_tblspc
EM Enterprise Manager Repository 100544 emd_maintenance.move_em_tblspc
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
TEXT Oracle Text 3456 DRI_MOVE_CTXSYS
ULTRASEARCH Oracle Ultra Search 0 MOVE_WK
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0 MOVE_WK
EXPRESSION_FILTER Expression Filter System 3712
EM_MONITORING_USER Enterprise Manager Monitoring User 1792
TSM Oracle Transparent Session Migration User 0
SQL_MANAGEMENT_BASE SQL Management Base Schema 1728
AUTO_TASK Automated Maintenance Tasks 320
JOB_SCHEDULER Unified Job Scheduler 17344
31 rows selected.
再转移回来:
SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX'); 还原到 SYSAUX 表空间
PL/SQL procedure successfully completed.
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants order by 3;
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
STATSPACK Statspack Repository 0
ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components 0 ordsys.ord_admin.move_ordim_tblspc
TSM Oracle Transparent Session Migration User 0
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0 MOVE_WK
ULTRASEARCH Oracle Ultra Search 0 MOVE_WK
AUDIT_TABLES DB audit tables 0 DBMS_AUDIT_MGMT.move_dbaudit_tables
ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components 0 ordsys.ord_admin.move_ordim_tblspc
AUTO_TASK Automated Maintenance Tasks 320
ORDIM Oracle Multimedia ORDSYS Components 448 ordsys.ord_admin.move_ordim_tblspc
STREAMS Oracle Streams 1024
LOGSTDBY Logical Standby 1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
PL/SCOPE PL/SQL Identifier Collection 1600
SQL_MANAGEMENT_BASE SQL Management Base Schema 1728
EM_MONITORING_USER Enterprise Manager Monitoring User 1792
WM Workspace Manager 3136 DBMS_WM.move_proc
TEXT Oracle Text 3456 DRI_MOVE_CTXSYS
XSAMD OLAP Catalog 3712 DBMS_AMD.Move_OLAP_Catalog
EXPRESSION_FILTER Expression Filter System 3712
SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 5376
LOGMNR LogMiner 8064 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
SM/OTHER Server Manageability - Other Components 8256
ORDIM/ORDDATA Oracle Multimedia ORDDATA Components 13056 ordsys.ord_admin.move_ordim_tblspc
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
JOB_SCHEDULER Unified Job Scheduler 17344
AO Analytical Workspace Object Table 46208 DBMS_AW.MOVE_AWMETA
XSOQHIST OLAP API History Tables 46208 DBMS_XSOQ.OlapiMoveProc
SDO Oracle Spatial 54336 MDSYS.MOVE_SDO
EM Enterprise Manager Repository 100544 emd_maintenance.move_em_tblspc
XDB XDB 130944 XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SM/ADVISOR Server Manageability - Advisor Framework 176128
SM/OPTSTAT Server Manageability - Optimizer Statistics History 218176
SM/AWR Server Manageability - Automatic Workload Repository 359616
31 rows selected.
以上可以看到:AWR报告和OPTSTAT、ADVISOR 占用空间最大,可以做出相应调整。
1)修改 统计信息(OPTSTAT)的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> exec dbms_stats.alter_stats_history_retention(7);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
7
2)修改AWR快照的保存时间为7天(7*24*60),每小时收集一次,也可以通过EM界面查看和修改
值得注意的是:
RETENTION 参数的取值范围要求是1天到100年,即快照至少需要保留1天,最长可以保留100年;
INTERVAL 参数的取值范围要求是10分钟到100年,即快照采集间隔最少可以是10分钟,最长可以是100年
当RETENTION参数被赋予少于1天或超过100年的时候都会给出提示性错误ORA-13510
col SNAP_INTERVAL for a20
col RETENTION for a20
select * from dba_hist_wr_control;(查看AWR报告收集信息规律)
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
2727749626 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时
RETENTION=+00008 00:00:00.0 表示采样数据保留期限是8天
SQL> begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60, -- 每小时一次
retention => 7*24*60, --保留7天
topnsql => 100
);
end;
注意:如果快照太多,占用太多空间,也可以删除一些快照:
select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10758, --根据情况设置
high_snap_id => 10900,
dbid => 387090299); DBID根据实际情况
end;
/
注意:如果AWR没有用的话,可以将采样时间间隔调整为最长100年,同时把采样数据保留时间调整到最小1天的方式实现 “禁用”AWR。
SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 36500*24*60,
retention => 1*24*60);
END;
/
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00001 00:00:00.0 DEFAULT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM和SYSAUX经常处于高使用率问题
set linesize 200
SELECT df.tablespace_name , (df.totalspace - NVL(fs.freespace, 0)) ,NVL(fs.freespace, 0) ,df.totalspace ,ROUND(100 * (1 - NVL(fs.freespace, 0) / df.totalspace), 2)
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) freespace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY 5 DESC;
TABLESPACE_NAME (DF.TOTALSPACE-NVL(FS.FREESPACE,0)) NVL(FS.FREESPACE,0) TOTALSPACE ROUND(100*(1-NVL(FS.FREESPACE,0)/DF.TOTALSPACE),2)
------------------------------ ----------------------------------- ------------------- ---------- --------------------------------------------------
SYSAUX 1235 85 1320 93.56
USERS 21762 2252 24014 90.62
PJBJ 173280 23328 196608 88.13
PJBJ_FULL 23905 27295 51200 46.69
SYSTEM 1199 2937 4136 28.99
FULLCAR 7482 23238 30720 24.36
UNDOTBS1 775 27115 27890 2.78
VEHICLE 44 20436 20480 .21
SQL> select tablespace_name, AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files where tablespace_name in ('SYSTEM','SYSAUX');
TABLESPACE_NAME AUT INCREMENT_BY
------------------------------ --- ------------
SYSAUX YES 1280
SYSTEM YES 1280
SYSTEM NO 0
AUTOEXTENTSIBLE 对应的值为YES 表示可以自动扩展,为NO 表示不自动扩展,INCREMENT_BY 表示每次自动扩展的空间大小。
1 ) 增加system 表空间的数据文件:
alter tablespace system add datafile “'D:/oracleXE/oradata/XE/system_01.dbf' size 1024M;
2)增加已存在数据文件大小
alter database datafile 'D:/oracleXE/oradata/XE/system_01.dbf' resize 2048M;
2)修改sysaux表空间,使其自动扩展:
alter database datafile 'D:/oracleXE/oradata/XE/system_01.dbf' autoextend on;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1710203/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29289867/viewspace-1710203/