Shulou(Shulou.com)06/01 Report--

How to understand Oracle SYSAUX tablespaces, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

one。 SYSAUX description

In the Oracle 10g version, an auxiliary tablespace for SYSTEM tablespaces, SYSAUX tablespaces, was introduced.

Some other metadata components, such as OEM,Streams, are stored in the SYSAUX tablespace by default in the SYSAUX tablespace. This also reduces the load on SYSTEM tablespaces. Therefore, SYSAUX tablespaces are also created automatically when DB is created or upgraded. If you do not specify SYSAUX tablespace when you manually create a DB using SQL, the creation statement will report an error. Unable to execute.

Under normal operation, drop and rename SYSAUX tablespaces are not allowed. If the SYSAUX tablespace is not available, the core functions of the database can continue to run. It's just that some of the functions stored in SYSAUX tablespaces are limited, as we said earlier, OEM.

When you specify the SYSAUX tablespace when DB is created, you must specify the following four attributes:

(1)。 PERMANENT

(2)。 READ WRITE

(3)。 EXTENT MANAGMENT LOCAL

(4)。 SEGMENT SPACE MANAGMENT AUTO

We cannot use alter tablespace to modify these four properties, nor can we drop and rename SYSAUX tablespaces.

We can use the v$sysaux_occupants view to view component information in the SYSAUX tablespace, such as:

SQL > select occupant_name,schema_name,move_procedure from v$sysaux_occupants

Occupant_name schema_name move_procedure

Logmnr system sys.dbms_logmnr_d.set_tablespace

Logstdby system sys.dbms_logstdby.set_tablespace

Streams sys

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

Odm dmsys move_odm

Sdo mdsys mdsys.move_sdo

Wm wmsys dbms_wm.move_proc

Ordim ordsys

Ordim/plugins ordplugins

Ordim/sqlmm si_informtn_schema

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

Job_scheduler sys

26 rows selected.

These components occupy the SYSAUX table space, so the size of these components determines the size of the SYSAUX table space. Depending on the initialization size of these components when they are created, SYSAUX requires at least 400m of space.

Another thing to note is that the schema_name here corresponds to the user name.

SQL > desc dba_users

Name Null? Type

-

USERNAME NOT NULL VARCHAR2 (30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2 (30)

ACCOUNT_STATUS NOT NULL VARCHAR2 (32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2 (30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2 (30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2 (30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 (30)

EXTERNAL_NAME VARCHAR2 (4000)

SQL > select username,account_status,default_tablespace from dba_users

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPA

-

MDDATA EXPIRED & LOCKED USERS

MDSYS EXPIRED & LOCKED SYSAUX

ORDSYS EXPIRED & LOCKED SYSAUX

CTXSYS EXPIRED & LOCKED SYSAUX

ANONYMOUS EXPIRED & LOCKED SYSAUX

EXFSYS EXPIRED & LOCKED SYSAUX

OUTLN EXPIRED & LOCKED SYSTEM

DIP EXPIRED & LOCKED USERS

DMSYS EXPIRED & LOCKED SYSAUX

DBSNMP OPEN SYSAUX

SCOTT EXPIRED & LOCKED USERS

WMSYS EXPIRED & LOCKED SYSAUX

SYSMAN OPEN SYSAUX

XDB EXPIRED & LOCKED SYSAUX

TSMSYS EXPIRED & LOCKED USERS

ORDPLUGINS EXPIRED & LOCKED SYSAUX

MGMT_VIEW OPEN SYSTEM

SI_INFORMTN_SCHEMA EXPIRED & LOCKED SYSAUX

OLAPSYS EXPIRED & LOCKED SYSAUX

SYS OPEN SYSTEM

SYSTEM OPEN SYSTEM

21 rows selected.

The description of these components is not shown here because the display is incomplete. If you are interested, you can check it for yourself.

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

There is a move_procudure process in the v$sysaux_occupants view. This process is done by migrating component information. This stored procedure can be used for components that have already been installed if we want to put them in another space. If there is no corresponding process, it cannot be moved.

Doing so controls the size of the SYSAUX tablespace. Like our AWR. AWR is the most space-consuming component in SYSAUX. For a system with 10 concurrent session, 200m of space is required. Of course, you can also modify the save policy of AWR to control the amount of space occupied by AWR.

two。 Example:

2.1. Migrate Logminer from SYSAUX tablespace to users tablespace and restore it back

(1) View the previous information:

SQL > select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR'

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES

LOGMNR LogMiner SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 6080

(2) move

SQL > exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE ('USERS')

PL/SQL procedure successfully completed.

(3) Verification

SQL > select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR'

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES

LOGMNR LogMiner SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 0

Notice that the empty space here becomes 0. Data is migrated to USERS tablespace

(4) restore to SYSAUX tablespace

SQL > exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE ('SYSAUX')

PL/SQL procedure successfully completed.

(5) Verification

SQL > select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR'

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES

LOGMNR LogMiner SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 6080

The size has changed again.

2.2 SYSAUX cannot drop

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.3 SYSAUX cannot be renamed

SQL > alter tablespace SYSAUX rename to DAVE

Alter tablespace SYSAUX rename to DAVE

*

ERROR at line 1:

ORA-13502: Cannot rename SYSAUX tablespace

2.3 SYSAUX cannot be changed to read-only

SQL > alter tablesapce SYSAUX read only

Alter tablesapce SYSAUX read only

*

ERROR at line 1:

ORA-00940: invalid ALTER command

After reading the above, have you mastered how to understand Oracle SYSAUX tablespaces? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.