Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column?
A.The procedure maybe used for some component store locate component data to the SYSAUX tablespace from its current tablespace.
B. The procedure may be used for some component store locate component data from the SYSAUX tablespace to another tablespace.
C. All the components may be moved into SYSAUX tablespace.
D. All the components may be moved from the SYSAUX tablespace.
【考点分析】
考察了SYSAUX表空间所含组件的的管理方法
【原理概念】
当数据库创建时,SYSAUX表空间被作为SYSTEM的辅助表空间安装。已经安装并使用独立表空间的数据库组件都可以放在SYSAUX表空间中。如果SYSAUX表空间不可用,数据库内核功能仍然运转。使用SYSAUX表空间的特性将会失败或功能可用性会受到限制。
监控SYSAUX表空间中的内容可以使用V$SYSAUX_OCCUPANTS视图,视图列出了如下的内容
在组件安装时,也可以选择不放在SYSAUX 表空间中。如果决定把组件从SYSAUX表空间移动指定的表空间中,可以使用在V$SYSAUX_OCCUPANTS 视图中移动组件的存储过程来进行。反之,用于移动存储过程也可以把组件从其他表空间移动到SYSAUX表空间中。
再次,附上官方文档的解释:
“Moving Occupants Out Of or into the SYSAUX Tablespace
You will have an option at component install time to specify that you do not want the component to reside in SYSAUX. Also, if you later decide that the component should be relocated to a designated tablespace, you can use the move procedure for that component, as specified in the V$SYSAUX_OCCUPANTS view, to perform the move.
The move procedure also lets you move a component from another tablespace into the SYSAUX tablespace.
”
【实验参考】
1.查看V$SYSAUX_OCCUPANTS 视图的结构
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL>
SQL>
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
CON_ID NUMBER
2.查看V$SYSAUX_OCCUPANTS 视图的内容,可以看到有些组件没有move_procedure
SQL> set linesize 100
SQL> set pagesize 100
SQL> col OCCUPANT_NAME for a30
SQL> col MOVE_PROCEDURE for a50
SQL> select OCCUPANT_NAME,MOVE_PROCEDURE from v$sysaux_occupants;
OCCUPANT_NAME MOVE_PROCEDURE
------------------------------ --------------------------------------------------
LOGMNR SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME
AUDSYS
PL/SCOPE
STREAMS
AUDIT_TABLES DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO DBMS_AW.MOVE_AWMETA
XSOQHIST DBMS_XSOQ.OlapiMoveProc
XSAMD DBMS_AMD.Move_OLAP_Catalog
SM/AWR
SM/ADVISOR
SM/OPTSTAT
SM/OTHER
STATSPACK
SDO MDSYS.MOVE_SDO
WM DBMS_WM.move_proc
ORDIM ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
EM emd_maintenance.move_em_tblspc
TEXT DRI_MOVE_CTXSYS
ULTRASEARCH MOVE_WK
ULTRASEARCH_DEMO_USER MOVE_WK
EXPRESSION_FILTER
EM_MONITORING_USER
TSM
SQL_MANAGEMENT_BASE
AUTO_TASK
JOB_SCHEDULER
32 rows selected.
SQL>
3.以AUDIT_TABLES为例,我们使用move_procedure将审计功能移动到其他表空间
SQL>
SQL> create tablespace hoegh datafile '/u01/app/oracle/oradata/HOEGH/hoegh01.dbf'
2 size 30m autoextend on;
Tablespace created.
SQL>
SQL> exec DBMS_AUDIT_MGMT.move_dbaudit_tables('HOEGH');
PL/SQL procedure successfully completed.
SQL>
4.开启审计功能,然后查看审计表所在表空间
SQL> audit select table;
Audit succeeded.
SQL>
SQL> select segment_name,segment_type from dba_segments where TABLESPACE_NAME='HOEGH'
2 and segment_type='TABLE';
SEGMENT_NAME
----------------------------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
FGA_LOG$
TABLE
AUD$
TABLE
SQL>
可以看到审计记录的表已经在HOEGH表空间。
【答案剖析】
A,如果决定把组件从指定的表空间移动到SYSAUX表空间中,可以使用在V$SYSAUX_OCCUPANTS 视图中移动组件的存储过程来进行,所以A正确
B,如果决定把组件从SYSAUX表空间移动到指定的表空间中,可以使用在V$SYSAUX_OCCUPANTS 视图中移动组件的存储过程来进行,所以B正确
C,有些组件没有move_procedure,所以C错误
D,理由同C
【答案】 A,B
~~~~~~~ the end~~~~~~~~~
hoegh
2016.05.13