一、将LogMiner从sysaux表空间转移到表空间jerry，再转移回来
1.1 查看相关信息
SQL> select t.OCCUPANT_NAME,t.SCHEMA_NAME,t.MOVE_PROCEDURE from v$sysaux_occupants t;
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME SYS
PL/SCOPE SYS
STREAMS SYS
AUDIT_TABLES SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO SYS DBMS_AW.MOVE_AWMETA
XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc
XSAMD OLAPSYS DBMS_AMD.Move_OLAP_Catalog
SM/AWR SYS
SM/ADVISOR SYS
SM/OPTSTAT SYS
SM/OTHER SYS
STATSPACK PERFSTAT
SDO MDSYS MDSYS.MOVE_SDO
WM WMSYS DBMS_WM.move_proc
ORDIM ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
ORDIM/SI_INFORMTN_SCHEMA SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
EM SYSMAN emd_maintenance.move_em_tblspc
TEXT CTXSYS DRI_MOVE_CTXSYS
ULTRASEARCH WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER WK_TEST MOVE_WK
EXPRESSION_FILTER EXFSYS
EM_MONITORING_USER DBSNMP
TSM TSMSYS
SQL_MANAGEMENT_BASE SYS
AUTO_TASK SYS
JOB_SCHEDULER SYS
31 rows selected
1.2 移动
SQL> exec sys.dbms_logmnr_d.set_tablespace('JERRY');
PL/SQL procedure successfully completed
1.3 校验
SQL> select t.OCCUPANT_NAME,t.SCHEMA_NAME,t.MOVE_PROCEDURE,t.SPACE_USAGE_KBYTES from v$sysaux_occupants t where t.OCCUPANT_NAME='LOGMNR';
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES
----------------------- ------------------------------ ------------------ --------------------
LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 0
这里占用的空间为0，数据迁移到了JERRY表空间
1.4 还原到SYSAUX表空间
SQL> exec sys.dbms_logmnr_d.set_tablespace('SYSAUX');
PL/SQL procedure successfully completed
1.5校验
SQL> select t.OCCUPANT_NAME,t.SCHEMA_NAME,t.MOVE_PROCEDURE,t.SPACE_USAGE_KBYTES from v$sysaux_occupants t where t.OCCUPANT_NAME='LOGMNR';
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------
LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 8064
二、SYSAUX表空间不能DROP,RENAME,和READONLY
SQL> drop tablespace sysaux including contents and datafiles;
drop tablespace sysaux including contents and datafiles
ORA-13501: Cannot drop SYSAUX tablespace
SQL> alter tablespace sysaux rename to jerry;
alter tablespace sysaux rename to jerry
ORA-02154: a tablespace with the name 'JERRY' is found
SQL> alter tablespace sysaux rename to jerry1;
alter tablespace sysaux rename to jerry1
ORA-13502: Cannot rename SYSAUX tablespace
SQL> alter tablespace sysaux read only;
alter tablespace sysaux read only
ORA-13505: SYSAUX tablespace can not be made read only
阅读(1218) | 评论(0) | 转发(0) |
