Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andre.

Asked: July 03, 2001 - 11:07 am UTC

Last updated: August 23, 2021 - 4:46 am UTC

Version: 8.1.5

Viewed 100K+ times! This question is

You Asked

Hi!

Where do I find if session is in restricted mode ? After I executed the "alter system enable restricted session;" command .




and Tom said...

tkyte@TKYTE816> select logins from v$instance;

LOGINS
----------
RESTRICTED

tkyte@TKYTE816> alter system disable restricted session;

System altered.

tkyte@TKYTE816> select logins from v$instance;

LOGINS
----------
ALLOWED


Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ALTER SYSTEM QUIESCE RESTRICTED

Reader, November 08, 2003 - 7:22 pm UTC

Tom, Where ALTER SYSTEM QUIESCE RESTRICTED; command will be useful? Also, what is the difference from using alter system enable restricted session? I read the doc and still unable to understand the difference between the two. Thanks.


Tom Kyte
November 08, 2003 - 9:46 pm UTC

quiesce restricted lets you safely update code and ddl in the database without shutting down and kicking everyone out.

the restricted session only lets people with those privs log in. you would "kick out" people without that and just let dba's in.

ALTER SYSTEM QUIESCE RESTRICTED

Johnny, February 27, 2004 - 6:29 pm UTC

'alter system enable restricted session' does not "kick out" user automatically. It just blocks new user who does not have the 'restrict session' privilege to login, similar to 'ALTER SYSTEM QUIESCE RESTRICTED', which blocks new non-dba users. However, I can scarely see the instances when non-dba users have been granted to the 'restrict session' privilege.

There is a practical advantage to introduce the QUIESCE state, however. It allows all current transactions to complete, and then freezes the user's session. To use 'restrict session' to do the same job, DBA might need to have a procedure that is able to keep monitoring the status of the existing transactions and kill the session as soon as the transaction gets completed.

Tom Kyte
February 27, 2004 - 7:22 pm UTC

I said "you would KICK OUT", not that "it kicks out".

"the restricted session only lets people with those privs log in. you would
"kick out" people without that and just let dba's in. "

you just said the same thing I did.

restricted session and triggers

A reader, October 25, 2005 - 9:35 am UTC

Hi

I have been testing if restricted session can avoid trigger firing.

I create two users, scott and anna, scott has access to anna´s table and I create a before update trigger in scott schema but referencing anna´s table.

I logon as SYS and enabled restricted session, tried to logon as scott and it gives an error, so far so good.

I then logon as system and updated anna´s table and the trigger..... fired!

Is there anyway to avoid trigger firing other than disable it?

Tom Kyte
October 26, 2005 - 7:22 am UTC

why would you think restricted session would obviate triggers? all it does is prevent people that don't have restricted session privileges from connecting.

You disable triggers to prevent them firing.

Enabling INTERNAL_QUIESCE resource plan

Rajiv Aggarwal, January 13, 2007 - 5:20 pm UTC

To do quiesce a database until 10GR1, we need to have resource manager plan active since startup. I want to understand why is that. What if I just change the resource_manager_plan to INTERNAL_QUIESCE , what will I miss? I know I need to manually check what all transactions are pending commit/rollback and kill any active sqls before starting my DDL changes on a very active database.

It is very active database and we cannot afford to take bounce of the database to enable resource manager plan. So I wanted this as an alternative.

Appreciate your response.

Thanks

Enabling INTERNAL_QUIESCE resource plan

Rajiv Aggarwal, January 14, 2007 - 2:43 am UTC

To add on above question, I don't have resource manager plan set on my database (resource_manager_plan parameter is null). I want to know instead of bouncing the database with SYSTEM_PLAN and then quiesceing the db, just do change the plan from NULL to "INTERNAL_QUIESE".



Ricardinho, June 26, 2008 - 4:04 am UTC

Hi Tom
Despite quiesce is finished, I still see active transactions in the system from v$session
oracle 10.2.0.3.0

SQL> ALTER SYSTEM QUIESCE RESTRICTED;

System altered.

SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';

COUNT(*)
----------
437

SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';

COUNT(*)
----------
439

SQL> select count(*) from v$session where username='ATM' and status='ACTIVE';

COUNT(*)
----------
440 

Tom Kyte
June 26, 2008 - 4:08 pm UTC

that doesn't show active transactions.


ops$tkyte%ORA10GR2> select status, taddr, count(*) from v$session group by status, taddr;

STATUS   TADDR      COUNT(*)
-------- -------- ----------
ACTIVE                    15

ops$tkyte%ORA10GR2> update t set user_id=user_id;

5 rows updated.

ops$tkyte%ORA10GR2> select status, taddr, count(*) from v$session group by status, taddr;

STATUS   TADDR      COUNT(*)
-------- -------- ----------
ACTIVE   3F2D6A98          1
ACTIVE                    14


first one shows I have 15 active sessions.
second one shows I have 15 active sessions and ONE transaction

Ricardinho, June 27, 2008 - 3:55 am UTC

thanks tom but I am still confused.

1-) select count(*) from v$session where status='ACTIVE'

2-) select count(*) from v$session where taddr is not null

The first query shows total act¿ve sessions including transactions.
and
The second query show only active transactions.
Is this right?

Tom Kyte
June 27, 2008 - 9:17 am UTC

the first one shows active sessions. Active sessions do NOT have to have a transaction going.

if you issue "select count(*) from very_big_table", that will show you as active, but you haven't modified anything, so you have not created a transaction state object.

You can be active, but read only.


And further, you can be INACTIVE and have a transaction. If I issue "update t set x = 5" hit enter and go to lunch, I'll be inactive - but having a transaction.


look in v$transaction for transactions.

Alexander, June 27, 2008 - 10:49 am UTC

Tom,

I too sometimes struggle with identifying whether or not a connection is "doing anything" or not.

Just yesterday we had a connection from SYS by the job scheduler, it appeared as active, but we could find any evidence that this was true (no sql). It was blocking someone from creating an index.

The documentation states that the status column in v$session when active is

"ACTIVE - Session currently executing SQL"

But you said active doesn't have to have a transaction, but you can't execute sql without a transaction right?


Tom Kyte
June 27, 2008 - 11:30 am UTC

you do not need a transaction state when you haven't modified anything

If you are read only, only reading, you do not have a transaction object.

begin
  loop
     null;
  end loop;
end;
/



run that, you'll be very active, but you obviously do not need to have a transaction.

You need a transaction object/state when you modify something or you "set transaction" or request a transaction id.




A reader, June 30, 2008 - 2:06 am UTC

Hi Tom,
Is there any diffrence between the two statements.
Do both show the amount of active transactions?


select count(*) from v$transaction

select count(*) from v$session where taddr is not null
Tom Kyte
July 06, 2008 - 6:35 pm UTC

a session can have more than one transaction....


ops$tkyte%ORA11GR1> create table t ( x int );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace procedure p( p_n in number )
  2  as
  3          pragma autonomous_transaction;
  4          l_vtrans number;
  5          l_vsess  number;
  6  begin
  7          insert into t values ( p_n );
  8          if (p_n = 0)
  9          then
 10                  select count(*) into l_vtrans from v$transaction;
 11                  select count(*) into l_vsess from v$session where taddr is not null;
 12                  dbms_output.put_line( 'vtrans = ' || l_vtrans || ' vsess = ' || l_vsess );
 13          else
 14                  p(p_n-1);
 15          end if;
 16          commit;
 17  end;
 18  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec p(5)
vtrans = 6 vsess = 1

PL/SQL procedure successfully completed.






does restricted session have something like set transaction read only

Kdev, September 10, 2008 - 3:39 am UTC

Hi Tom,
can we set some thing like this for a specific user and for all sessions initiate from this user;

set transaction read only; this need to be apply to the session once a new session initiated.

This is to avoid 2 way commit phase on a distributed transaction and a delay.
Tom Kyte
September 11, 2008 - 10:43 am UTC

no, it does not

quiesce, restricted, suspend

A reader, March 14, 2012 - 1:48 am UTC

Hi Tom,

A little bit confused about 'quiesce', 'restricted' and 'suspend'.

Could you please elaborate more on their difference and scenario of usage?

Beside, are below my understanding correct.
1. restricted: must be shutdown first and start up again by 'startup restrict'?
need special privilege to login
2. quiesce: can change to this mode at any time without downtime?
as long as login as DBA, no issue for this session?
3. suspend: will disable IO operations on datafile, redo log file, control file?

Basically what kind of activities(like backup, batch update, imp/exp) should be done under which of them?
Tom Kyte
March 14, 2012 - 6:50 am UTC

1) restricted limits who may log into the running database. You shutdown, startup in restricted mode - log in as a user who can, do something and then un-restrict the database

2) quiesce causes the database to allow any current transactions to complete, prevents new ones from starting - and when all transactions are complete - returns control to the quiescing session so it can do stuff. You then un-quiesce the database to resume operations

3) suspend immediately suspends IO calls - all of them. designed exclusively to work with 3rd party storage solutions like splitting mirrors and the like


None of them are for batch update, imp/exp. Suspend *might* be part of a backup if you use BCV's or the like.

restrict and quiesce are there to allow the dba to do something like a DDL change without competing for locks.

when Database went into restricted mode

Dhamu, February 12, 2015 - 11:54 am UTC

Hi Tom,

Please let me know, is there any way to find when the database was put into restricted mode.

Alert log have no information on this.

Thanks
Dhamu

Confused because Pluggable DB does not show RESTRICTED

Andrea Morris, August 19, 2021 - 11:41 pm UTC

I put the database in restricted session, but only the container of my single-tenant database (cdb$root) shows restricted when I query v$instance. When I am connected to a pluggable database (e.g. pdbappdb) I see logins as allowed. I did get an error showing that all instances were in restricted session when I tried to connect to the pluggable database. That's what I really care about---no access. But it confused me at first.

sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 19 18:00:08 2021
Version 18.9.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.9.0.0.0

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select instance_name, host_name, logins from v$instance
2 /

INSTANCE_NAME
----------------
HOST_NAME LOGINS
---------------------------------------------------------------- ----------
MYDB
myhost.myuniversity.edu RESTRICTED


SQL> alter session set container=pdbappdb
2 /

Session altered.

SQL> select instance_name, host_name, logins from v$instance
2 /

INSTANCE_NAME
----------------
HOST_NAME LOGINS
---------------------------------------------------------------- ----------
MYDB
myhost.myuniversity.edu ALLOWED

Connor McDonald
August 23, 2021 - 4:46 am UTC

It might seem counterintuitive but we go this way to mimic the behaviour you see on a non-pluggable database.

On a non-pluggable database:

A) startup mount
=> logins = ALLOWED, but of course no-one can get into the db except sysdba

B) startup restrict
=> database is fully open, but logins = RESTRICTED

C) startup OR alter database open (from mount)
=> logins => ALLOWED

When you do "startup restrict" on the CDB, you'll see that the pluggables are only opened in MOUNT state, which is why the LOGINS = ALLOWED (just like A above).