Oracle Session Control Shell Script (BASH)－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! **

[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)
)
)