DanBrother的部落格

Oracle Session Control Shell Script (BASH)

session_control.sh
>>
###########################################################################################################
#!/bin/bash
# This script will allow you to choose whether to enable or disable restricted sessions in TESTDB Database.
###########################################################################################################
echo "************************************"
echo "* Database Session Control Program *"
echo "************************************"
echo "1) Enable Restricted Session."
echo "2) Disable Restricted Session."
read -p "Please specify your option: " option
export ORACLE_SID=TESTDB
case $option in
     1)
         sqlplus -s "/ as sysdba" @session_control.sql ENABLE ;;
     2)
         sqlplus -s "/ as sysdba" @session_control.sql DISABLE ;;
esac



session_control.sql
>>
SET ECHO OFF SERVEROUTPUT ON FEEDBACK OFF VERIFY OFF
DECLARE
v_action constant varchar2(10):='&1';
BEGIN
  execute immediate 'ALTER SYSTEM '||v_action||' RESTRICTED SESSION';

  CASE v_action
      WHEN 'ENABLE' THEN
           DBMS_OUTPUT.PUT_LINE('** Database is Now In [RESTRICTED SESSION] Mode! **');
      WHEN 'DISABLE' THEN
           DBMS_OUTPUT.PUT_LINE('** Database is Now In [NORMAL SESSION] Mode! **');
  END CASE;
END;
/
SET FEEDBACK OFF
EXIT

Demo:

[oracle@TESTSVR ~]$ ./session_control.sh
************************************
* Database Session Control Program *
************************************
1) Enable Restricted Session.
2) Disable Restricted Session.
Please specify your option: 1
** Database is Now In [RESTRICTED SESSION] Mode! **

[oracle@TESTSVR ~]$ ./session_control.sh
************************************
* Database Session Control Program *
************************************
1) Enable Restricted Session.
2) Disable Restricted Session.
Please specify your option: 2
** Database is Now In [NORMAL SESSION] Mode! **

[Note]
If an instance has been put in RESTRICTED mode, in order for the user with restricted session privilege to connect remotely to the database,
(UR=A)  dynamically registered handler clause needs to be added to the connection string in the tnsnames.ora file, as follows:

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TESTSVR)(PORT = 1521))
    (CONNECT_DATA =
      (UR = A)
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )