Wednesday, December 28, 2022

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Sessions are returned this error while connecting to instances(s) that are running in restricted mode. DBAs may enable restricted sessions to perform some maintenance tasks or in any other needed situation. We can query (g)v$instance to find out if an instance has restricted mode enabled.

SQL> select logins from gv$instance;
 
LOGINS
----------
RESTRICTED

An instance can be put in restricted mode at startup, or restricted mode can be enabled or disabled while an instance is running.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup restrict
ORACLE instance started.
 
Total System Global Area 5016387584 bytes
Fixed Size                  3842376 bytes
Variable Size            1258294968 bytes
Database Buffers         3741319168 bytes
Redo Buffers               12931072 bytes
Database mounted.
Database opened.
 
SQL> select logins from gv$instance;
 
LOGINS
----------
RESTRICTED


SQL> alter system disable restricted session;
 
System altered.
 
SQL> select logins from gv$instance;
 
LOGINS
----------
ALLOWED
 
SQL> alter system enable restricted session;
 
System altered.
 
LOGINS
----------
RESTRICTED

No comments:

Post a Comment

Popular Posts - All Times