oracle實驗記錄 （oracle的兩種受限制登陸訪問）

一 restrict 登陸
SQL> startup restrict
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   788068 bytes
Variable Size             145488284 bytes
Database Buffers           41943040 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

SQL> conn zz/a850624
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
必須要  RESTRICTED SESSION privilege
SQL> alter system disable restricted session;

System altered.
SQL> conn zz/a850624
Connected.
SQL>

 

二 QUIESCE DATABASE登陸

SQL> select active_state from v$instance;

ACTIVE_ST
---------
NORMAL~~~~~~~~~~~~~~正常

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968
resource_limit                       boolean     TRUE
resource_manager_plan                string~~~~~~~~~~~~~~~~~~要求是活動的 資源計劃
                                                          他是用資料庫資源管理器控制                                                                         

instance

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968
resource_limit                       boolean     TRUE
resource_manager_plan                string      SYSTEM_PLAN*******************

SQL> alter system quiesce restricted;

System altered.

SQL> select active_state from v$instance;

ACTIVE_ST
---------
QUIESCED

SQL> conn xh/a831115
會等待 hung住了\

SQL> alter system unquiesce;~~~另一個SESSION  SYSDBA登陸

System altered.

SQL>

SQL> conn xh/a831115       hang的連線成功
Connected.

SQL> select active_state from v$instance;  SYSDBA登陸

ACTIVE_ST
---------
NORMAL

SQL> alter system quiesce restricted;~~~~~~~~~SYSDBA

System altered.

SQL> select * from t1;~~~~~~~~~ USER   XH 執行後又hung住了

SQL> alter system unquiesce;    SYSDBA

System altered.

 

SQL> select * from t1; USER XH  output

         A
----------
         2
         2
         2
         2
         2

SQL> update  t1 set a=6;~~~~~~~~~~USER XH

15 rows updated.~~~~~~~~未commit

SQL>

SQL> alter system quiesce restricted; SYSDBA HANG 等待 所有事務執行完畢
SQL> commit; XH

Commit complete.

SQL> alter system quiesce restricted; SYSDBA

System altered.

SQL> select sid,state from v$session where username='XH';

       SID STATE
---------- -------------------
       161 WAITING~~~~~~~~~~~~~~~~~~~~狀態

SQL> select sid ,event from v$session_event where event like '%QUIESCE%';
可以確定 那些SESSION  WAITING quiesce database

當停頓了資料庫，INTERNAL_QUIESCE資源計劃被啟用了。除SYS_GROUPS其它所有的資源組中的ACTIVE_SESS_POOL_P1應被設定為0。因SYS和SYSTEM使用者都屬於SYS_GROUPS組，所以只

有它們可以連線到資料庫。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613307/，如需轉載，請註明出處，否則將追究法律責任。