no one else is logged into the database you can use Restricted Mode
First you must check what users have permission to login to restricted mode:
select * from dba_sys_privs where privilege=’RESTRICTED SESSION’;
If a user that you want to use doesn’t have this permission, use:
grant RESTRICTED SESSION to ROBERT;
You can also revoke permission from users than shouldn’t have it:
revoke RESTRICTED SESSION from ANTOHER_USER;
Ok, now you can switch the database into restricted mode.
There are two ways you can do this.
Shutdown the database and open in restricted mode:
shutdown immediate;
startup restrict;
This method has the advantage that you will be sure that nobody without restricted session permission will be online.
But of course, you may not want or can’t restart the database, so the second way is to switch the database into restricted mode when it’sopen:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
Using the statement below you can check in what state the database is:
select logins from v$instance;
In this method the important issue is to know that after alter system Oracle doesn’t kill existing sessions, so you must do this on your own, e.g using this script:
declare
cursor sesje is
select g.SID sid, g.SERIAL# serial
from v$session g
where g.USERNAME != ’SYS’
and g.USERNAME is not null;
begin
for sesja in sesje loop
begin
execute immediate 'ALTER SYSTEM KILL SESSION ''' || sesja.sid || ',' || sesja.serial || ''' immediate';
exception
when others then
null;
end;
end loop;
end;
In the first and second method you must know that by default, if the database is in restricted mode you can’t login into her via listener, even if you have the needed permission. If you try , you will see this error message:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
To login via listener you must modify tnsnames file and add one little thing: (UR=A)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
