In Oracle the SYSAUX tablespace is an auxiliary storage location for the SYSTEM tablespace. Many of the Oracle database components which are not critical to the core functionality reside in the SYSAUX tablespace. If the SYSAUX tablespace becomes unavailable, core database will still function, though pieces of the database which rely on SYSAUX components will fail or have limited capabilities. You can view the components of the SYSAUX tablespace through the view V$SYSAUX_OCCUPANTS as described below:
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
SQL>
Procedure shown in the MOVE_PROCEDURE column of V$SYSAUX_OCCUPANTS details procedure name to move the product. This article will detail the steps to move the LGNMR component from SYSAUX tablespaces to its own tablespace storage.
- Logon to your database server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Wed Jan 11 15:04:41 EST 2017 on pts/2
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$
- Connect to SQLPLUS as sysdba.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 09:03:45 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Create a new tablespace called LOGMINER.
SQL> create tablespace LOGMINER datafile ‘/u01/oradata/orcl/orcl/LOGMNR_data01.dbf’ size 1g;
Tablespace created.
SQL>
- Extract the move_procedure from V$SYSAUX_OCCUPANTS view for LOGMNR.
SQL> select occupant_name, schema_name, move_procedure from v$sysaux_occupants where occupant_name=’LOGMNR’;
OCCUPANT_NAME
—————————————————————-
SCHEMA_NAME
—————————————————————-
MOVE_PROCEDURE
—————————————————————-
LOGMNR
SYSTEM
SYS.DBMS_LOGMNR_D.SET_TABLESPACE
SQL>
- Execute procedure passing the new tablespace name.
execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);
SQL> execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);
PL/SQL procedure successfully completed.
SQL>
- This completes movement of LOGMNR from sysaux tablespace.
Larry Catt
OCP