oracle 10g SYSAUX tablespace

        The SYSAUX(System Auxiliary) tablespace is a new feature of Oracle 10g database,before 10g release,there wasn't this tablespace.After you created your database via DBCA(Database Configuration Assistant) tool,the SYSAUX tablespace was created for you automatically.

       But what's the mainly function of that tablespace? Why Oracle present the SYSAUX tablespace? Does it bring some benefit for the database or the DBA?

       Yep,the answer is ture.The SYSAUX tablespace is an auxiliary tablespace of the SYSTEM tablespace.In the earlier release before 10g,there is only way to store the components and other metadata in the SYSTEM tablespace.But now,with the SYSAUX tablespace,Oracle separate some components from SYSTEM tablespace,bring 'sth' from the SYSTEM tablespace to SYSAUX tablespace.By that,the workload of SYSTEM tablespace becomes light,decreases the space fragment in the SYSTEM tablespace,and it becomes  easy to manage the SYSTEM tablespace for DBAs.

       In addition,if the status of SYSAUX tablespace becomes offline(in fact we can bring it offline explicitly) or invalid,the Oracle database can works normally still.

       Now,let's find out which components were stored in the SYSTEM tablespace logically.First,we should be familar with the V$SYSAUX_OCCUPANTS dictionary table. V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.

       So,we  need to do is only to issue a query against of the V$SYSAUX_OCCUPANTS view.

111

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

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

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  6144
LOGSTDBY                       SYSTEM               SYS.DBMS_LOGSTDBY.SET_TABLESPACE                   896
STREAMS                        SYS                                                                     512
XDB                            XDB                  XDB.DBMS_XDB.MOVEXDB_TABLESPACE                  49216
AO                             SYS                  DBMS_AW.MOVE_AWMETA                                768
XSOQHIST                       SYS                  DBMS_XSOQ.OlapiMoveProc                            768
XSAMD                          OLAPSYS              DBMS_AMD.Move_OLAP_Catalog                           0
SM/AWR                         SYS                                                                  250496
SM/ADVISOR                     SYS                                                                  176384
SM/OPTSTAT                     SYS                                                                  289216
SM/OTHER                       SYS                                                                   23424

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
STATSPACK                      PERFSTAT                                                                  0
ODM                            DMSYS                MOVE_ODM                                             0
SDO                            MDSYS                MDSYS.MOVE_SDO                                   22400
WM                             WMSYS                DBMS_WM.move_proc                                 7296
ORDIM                          ORDSYS                                                                  512
ORDIM/PLUGINS                  ORDPLUGINS                                                                0
ORDIM/SQLMM                    SI_INFORMTN_SCHEMA                                                        0
EM                             SYSMAN               emd_maintenance.move_em_tblspc                  164800
TEXT                           CTXSYS               DRI_MOVE_CTXSYS                                      0
ULTRASEARCH                    WKSYS                MOVE_WK                                              0
ULTRASEARCH_DEMO_USER          WK_TEST              MOVE_WK                                              0

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
EXPRESSION_FILTER              EXFSYS                                                                 3712
EM_MONITORING_USER             DBSNMP                                                                 1856
TSM                            TSMSYS                                                                    0
JOB_SCHEDULER                  SYS                                                                    1024

26 rows selected.

SQL> 

 

222   For a test purpose,we can bring the LogMiner component to the EXAMPLES tablespace.

 SQL> exec dbms_logmnr_d.set_tablespace('example');

PL/SQL procedure successfully completed.

SQL>

and then we issue a  query against the V$SYSAUX_OCCUPANTS to obtain the effect:

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes

  2  from v$sysaux_occupants

  3  where occupant_name like 'LOGMNR%'

  4  ;

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES

------------------------------ -------------------- ----------------------------------- ------------------

LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                     0

SQL> 

333 ok,the SPACE_USAGE_KBYTES value of the LOGMNR   is 0,instead of  the original value of 6144.

and then,let's bring it back to the SYSAUX tablespace.

SQL> exec dbms_logmnr_d.set_tablespace('SYSAUX');

PL/SQL procedure successfully completed.

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes
  2  from v$sysaux_occupants
  3  where occupant_name like 'LOGMNR%'
  4  ;

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  6144

SQL> 
444 Furthermore,we can do some more experiences on the SYSAUX tablespce.
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace


SQL> 

555 we can't drop the SYSAUX tablespace.

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> 

666 we can  switch the status(online to offline or else.) of the SYSAUX tablespace manually.

SQL> alter tablespace sysaux read only;
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only


SQL> 

777 we can not alter the SYSAUX tablespace to read only status.

SQL> alter tablespace sysaux rename to new_sys;
alter tablespace sysaux rename to new_sys
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace


SQL> 

888  Also,we can not rename the SYSAUX tablespace.

 

———————————-The End—————————–

revoke sysdba from user_name

把sysdba权限授予给user1之后,orapwSID文件中记录了user1的信息,但是将sysdba权限从user1收回之后,orapwSID文件中依然有user1用户。这是为什么呢???
可是联机文档上明明说:
A user’s name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

以下是我的实验步骤:
1看到字典里面记录的只有SYS用户拥有SYSDBA和SYSOPER系统权限:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
—————————— —– —–
SYS                            TRUE  TRUE

2给普通用户user1授予sysdba的权限:
SQL> grant sysdba to user1;

Grant succeeded.

3再次查看字典验证,看到授权成功,user1也拥有了sysdba权限:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
—————————— —– —–
SYS                            TRUE  TRUE
USER1                          TRUE  FALSE

4并且user1也记录到了orapwSID文件(我的ORACLE_SID=asher)
SQL> !strings $ORACLE_HOME/dbs/orapwasher
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
USER1
BBE7786A584F9103


5并且user1也可以以sysdba登录:
SQL> conn user1/user1 as sysdba;
Connected.
SQL> show user;
USER is “SYS”
SQL> conn sys/oracle as sysdba;
Connected.

6收回权限:
SQL> revoke sysdba from user1;

Revoke succeeded.
7接着查看字典,发现user1已经没了sysdba的权限:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
—————————— —– —–
SYS                            TRUE  TRUE

8可是user1依然在orapwSID文件中存在:
SQL> !strings $ORACLE_HOME/dbs/orapwasher
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
USER1
BBE7786A584F9103

9重启数据再次查看:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> statup;
SP2-0042: unknown command “statup” – rest of line ignored.
SQL> startup;
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1219016 bytes
Variable Size              75499064 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.

10查看字典:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
—————————— —– —–
SYS                            TRUE  TRUE
11orapwSID文件依然存在:
SQL> !strings $ORACLE_HOME/dbs/orapwasher
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
USER1
BBE7786A584F9103

SQL>

以上所有实验环境:
SQL> !uname -a
Linux RHEL4 2.6.9-89.ELsmp #1 SMP Mon Apr 20 10:34:33 EDT 2009 i686 i686 i386 GNU/Linux

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

请各位看官指点迷津!谢谢!此环境下,我的数据库没有屏蔽操作系统认证(不知道跟这个有没有关系?)!实验的结果跟联机文档上说的出现冲突,我就很是纳闷???user1根本没有赋予sysoper的权限,所以我就没有revoke sysoper from user1,我想跟这个也没有关系吧?再次谢过!希望这个问题能得到高手的解决~~~