Oracle DBA Daily Experiences

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.
SQL> STARTUP RESTRICT;

– shows database is in restricted mode
12:49:30 @> select logins from v$instance;

LOGINS
———-
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
– find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
———— ————————————-
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.

One can view the password change history by querying oracle’s internal table SYS.USER_HISTORY$, note this table gets updated only if the user is assigned a profile that has password reuse limit (i.e. PASSWORD_REUSE_TIME set not to UNLIMITED)

SYS@TEST> SELECT name, password_date
FROM sys.user$, sys.user_history$
WHERE user$.user# = user_history$.user#;

NAME PASSWORD_
—————————— ———–
SCOTT 31-JAN-08
SCOTT 11-JUN-08

If you have ‘&’ as part of your data that is part of the string and needs to inserted/updated/deleted and you are using SQL*Plus, you would need to do the following as ‘&’ has a special meaning in SQL*Plus as by default SQL*Plus considers it as a variable.

E.g: SQL> select ‘xxxx &abc’ from dual;
In the example SQL*Plus will prompt to enter a value for variable abc

So to ensure ‘&’ is taken in it’s literal value and it doesn’t prompt, one can do one of the following if you need to run SQL scripts from SQL*Plus

1) SET DEFINE OFF — Turns off substitution of variables
2) SET DEFINE # — Change the substitution character to # or to a character that is not part of the script
3) Change the above sample SQL to the following
SQL> select ‘xxxx &’ || ‘abc’ from dual;

Using the command it will show the processes that has the file open.

E.g:
$ fuser /u01/oradata/TEST/system01.dbf
/u01/oradata/TEST/system01.dbf: 2650128 2662644 2670618 2699278
$ ps -ef | egrep “2650128|2662644|2670618|2699278″
oracle 2650128 1 0 Jun 29 – 38:04 ora_mmon_TEST
oracle 2662644 1 0 Jun 29 – 64:44 ora_dbw0_TEST
oracle 2670618 1 0 Jun 29 – 16:36 ora_ckpt_TEST
oracle 2699278 1 0 Jun 29 – 15:02 ora_smon_TEST

One of the reason for using this would if one uses NFS to store datafiles you may find even though you drop the tablespace with the option to include datafiles it doesn’t free space as there may be oracle processes that still have handle open on the datafile as you will notice .nfs9999 file remaining in the directory where the datafile resides. So to free up space you may need to find the user sessions that have an handle open on the datafiles so by using fuser .nfs9999 you can find the process id and then map it back to the oracle session.