Oracle SYSAUX表空间中组件迁移

SYSAUX中表空间组件:
SYSAUX属于系统辅助表空间,缓解SYSTEM表空间的负载,SYSAUX表空间中存在很多系统以及功能组件。

#V$SYSAUX_OCCUPANTS
`V$SYSAUX_OCCUPANTS` displays `SYSAUX` tablespace occupant information.
通过该视图可以查询出SYSAUX表空间中各个组件的信息

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

OCCUPANT_NAME(组件名字)
SCHEMA_NAME(用户名)
SPACE_USAGE_KBYTES(占用空间)
MOVE_PROCEDURE(移动组件的存储过程) 


select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES,MOVE_PROCEDURE from V$SYSAUX_OCCUPANTS;
OCCUPANT_NAME             SCHEMA_NAME          SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------- -------------------- ------------------ --------------------
LOGMNR                    SYSTEM                            13696 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                  SYSTEM                             1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME             SYS                                 256
PL/SCOPE                  SYS                                1600
STREAMS                   SYS                                1024
AUDIT_TABLES              SYS                                   0 DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB                       XDB                              130176 XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO                        SYS                               39104 DBMS_AW.MOVE_AWMETA
XSOQHIST                  SYS                               39104 DBMS_XSOQ.OlapiMoveProc
XSAMD                     OLAPSYS                            9024 DBMS_AMD.Move_OLAP_Catalog
SM/AWR                    SYS                               23104
SM/ADVISOR                SYS                                7808
SM/OPTSTAT                SYS                                8640
SM/OTHER                  SYS                                7808
STATSPACK                 PERFSTAT                              0
SDO                       MDSYS                             76032 MDSYS.MOVE_SDO
WM                        WMSYS                              3584 DBMS_WM.move_proc
ORDIM                     ORDSYS                              448 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA             ORDDATA                           13888 ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS          ORDPLUGINS                            0 ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA  SI_INFORMTN_SCHEMA                    0 ordsys.ord_admin.move_ordim_tblspc
EM                        SYSMAN                            47168 emd_maintenance.move_em_tblspc
TEXT                      CTXSYS                             3840 DRI_MOVE_CTXSYS
ULTRASEARCH               WKSYS                                 0 MOVE_WK
ULTRASEARCH_DEMO_USER     WK_TEST                               0 MOVE_WK
EXPRESSION_FILTER         EXFSYS                             3712
EM_MONITORING_USER        DBSNMP                              512
TSM                       TSMSYS                                0
SQL_MANAGEMENT_BASE       SYS                                1728
AUTO_TASK                 SYS                                 320
JOB_SCHEDULER             SYS                                 384

move_procudure字段，是用于迁移组件信息的,就是对于已经安装好的组件，如果想把这些组件放到其他的空间，可以使用这个存储过程。如果没有对应的存储过程，说明该组件不可移动。

尝试迁移LOGMNR

查看迁移之前的信息：

SQL> select OCCUPANT_NAME,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';
OCCUPANT_NAME        SCHEMA_NAME          MOVE_PROCEDURE                                                   SPACE_USAGE_KBYTES
-------------------- -------------------- ---------------------------------------------------------------- ------------------
LOGMNR               SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                                              13696

调用系统包SYS.DBMS_LOGMNR_D.SET_TABLESPACE迁移

SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');
PL/SQL procedure successfully completed.123

验证迁移后的大小

SQL> select OCCUPANT_NAME,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';
OCCUPANT_NAME        SCHEMA_NAME          MOVE_PROCEDURE                                                   SPACE_USAGE_KBYTES
-------------------- -------------------- ---------------------------------------------------------------- ------------------
LOGMNR               SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                                                  0

–注意，这里占空的空间变成了0. 数据迁移到了USERS 表空间

还原到SYSAUX 表空间

SQL>  exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX');
PL/SQL procedure successfully completed.123

验证还原后的大小

SQL> select OCCUPANT_NAME,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';
OCCUPANT_NAME        SCHEMA_NAME          MOVE_PROCEDURE                                                   SPACE_USAGE_KBYTES
-------------------- -------------------- ---------------------------------------------------------------- ------------------
LOGMNR               SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                                              13696