Passing Values manually in the pl/sql code.

this script has a limitation when running from sqlplus command line. But no issues when running using sql developer.

--- Limitation on substitution variable value length with 240 characters. so proc list should be limited.

 --- JUST EDIT THE TOP 3 LINES AND MAKE SURE THEY ARE CORRECT FOR THAT ENVIRONMENT: 

--variable M2 varchar2(30);

--DEFINE M2 = &&1;

-- DEFINE SCHEMA_NAME = &&2;

DEFINE SCHEMA_NAME = SCHEMA_NAME;

DEFINE USERS_LIST = 'BIMUSER,AIO98J,JMUSER'

DEFINE PROC_LIST = 'UPDATE_COL_PROC,UPDATE_TAB_PROC'

SET TRIMSPOOL ON TRIMOUT ON DEFINE "&" CONCAT "." SERVEROUTPUT ON;

COL SPOOL_NAME  NEW_VALUE SPOOL_NAME;

SET FEEDBACK OFF

SET HEADING OFF

VARIABLE SPOOL_NAME  VARCHAR2(100);

SELECT ''||(SELECT PDB_NAME FROM DBA_PDBS)||'-&&SCHEMA_NAME-ASSIGN_GRANTS_TO_APP-SPECIALISTS.LOG' SPOOL_NAME  FROM DUAL;

SPOOL &SPOOL_NAME

ALTER SESSION SET CURRENT_SCHEMA=&&SCHEMA_NAME;

SET LINESIZE 200

SET SERVEROUTPUT ON SIZE 1000000

SET FEEDBACK ON ECHO OFF VERIFY OFF

DECLARE

check_user varchar2(10);

  CURSOR C_F IS (

SELECT REGEXP_SUBSTR ('&USERS_LIST', '[^,]+', 1, LEVEL) AS USER_L

FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&USERS_LIST','[^,]*'))+1);

CURSOR C_G IS (

SELECT REGEXP_SUBSTR ('&PROC_LIST', '[^,]+', 1, LEVEL) AS PROC_L

FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&PROC_LIST','[^,]*'))+1);

BEGIN

FOR X IN  C_F

 LOOP

     select count(*) into check_user from dba_users where username in (''||X.USER_L||'');

  if (check_user = 1) then

      DBMS_OUTPUT.PUT_LINE ('USER:-> '||X.USER_L||' - Exists in the DB');

   BEGIN

      FOR Y IN C_G

         LOOP

             BEGIN

                 EXECUTE IMMEDIATE 'GRANT EXECUTE ON  "&&SCHEMA_NAME".'||Y.PROC_L||' TO  '||X.USER_L||'';

                 COMMIT;

              EXCEPTION

                 WHEN NO_DATA_FOUND THEN

                    DBMS_OUTPUT.PUT_LINE ('ROW NOT FOUND FROM THE LIST');

                 WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE ('ERROR OCCURRED WHEN RUNNING FOR USER: '||X.USER_L||' for '||Y.PROC_L||'');

                    ROLLBACK;

                    CONTINUE;

                    END;

         END LOOP;

 COMMIT;

      EXCEPTION

        WHEN OTHERS THEN

         ROLLBACK;

         CONTINUE;

   END;

 else

  DBMS_OUTPUT.PUT_LINE (' USER:-> '||X.USER_L||' - Does not exists in the DB. ');

 end if;

END LOOP;

END;

/

SPOOL OFF;

UNDEFINE;

CRS_LIMIT_NPROC=16384 in s_crsconfig_hostname_env.txt file

 

Looks like there is a bug in the Oracle. Even after increasing the OS variables, “ORACLE” is not picking up the values.

Looks like Listener has a default max limit of 16384 in Oracle file , so it will not allowing any listener connections more than the said limit.

 

• ps -ef| grep LOCAL |wc -l               ---------- make sure this is less than 16384  ( if this value is close then new sessions will not be made using listener.

          Note: this is nothing to do with processes that added in the linux config files.

------Here are the steps we need to do to increase the value in this file.

 

$grid_home/crs/install/s_crsconfig_hostname_env.txt

 

Change this value “CRS_LIMIT_NPROC=16384"  To more value to accept more connections.

 This value is picked up by CRS when we start the oracle DB.

Note: we need to Bounce the CRS to take effect.

avoid Cartesian merge join in the explain plan.

Cartesian Merge Join are sometimes more expensive. takes long time to run and uses resources.

To avoid the cartesian merge join you can disable  using below command to make it run in that session. You dont want to apply this change to the DB as it would need oracle support to disable it.

alter session set "_optimizer_cartesian_enabled"=FALSE;

sql output to csv. (with comma in the column data).

 

Script to generate csv output. this scripts covers comma's in the column value to print it correct.

set pagesize 1000

set linesize 1000

set feedback off

set heading off

set echo off

select 'ABC_xyz.csv' spool_name  from dual;

spool &spool_name

select '"sysdate",

'||'"col_name_1",

'||'"col_name_2",

'||'"col_name_3",

'||'"col_name_4",

'||'"col_name_5"' from dual

union all

select distinct '"'||sysdate||'",'

||'"'||col_name_1||'",'

||'"'||col_name_2||'",'

||'"'||col_name_3||'",'

||'"'||col_name_4||'",'

||'"'||to_char(B.col_name_5,'DD-MON-YYYY HH24:MI:SS')||'"'

FROM Table A;

spool off;

Script to find parent-child relationships between tables in oracle.

 Edit the below script and provide the inputs when executed 

********************************************************************

accept tablename prompt 'Enter the table name:'

accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255

set verify off

col GIVEN_TABLE for a30

col PARENT_TABLE for a30

col CHILD_TABLE for a30

col CHILD_TABLE_OWNER for a20

col GIVEN_TABLE_OWNER for a20

col PARENT_TABLE_OWNER for a20

col PARENT_TAB_CONST for a30

col CHILD_TAB_FK_CONST for a30

col G_TAB_columns for a30

col GIVEN_FK_CONST_NAME for a30

col G_TAB_FK_COLUMNS for a30

col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.

prompt -----------------------------------------------

SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns

,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST

FROM dba_constraints p, dba_constraints c,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and p.table_name=t.table_name

and p.constraint_name=t.constraint_name

AND p.table_name = UPPER('&tablename')

and p.owner=('&tableowner')

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.

prompt ------------------------------------------------

SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE

,p.constraint_name PARENT_TAB_CONST

FROM dba_constraints p, dba_constraints c ,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and c.table_name=t.table_name

and c.constraint_name=t.constraint_name

AND c.table_name = UPPER('&tablename')

and c.owner='&tableowner'

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

***********************************************************

script to Block users from logging to schema directly from sql developer or Toad

Edit below script to work as per the need: 

CREATE OR REPLACE TRIGGER BLOCK_USER_LOGIN

AFTER LOGON ON Schema_NAME

DECLARE

v_prog sys.v$session.program%TYPE;

v_name sys.v$session.osuser%TYPE;

BEGIN

SELECT program INTO v_prog

FROM v$session a,dba_users b

where 

a.username=b.username and

(a.program like 'SQL D%' or a.program like 'TOAD') and

AND  a.audsid != 0;

 

IF (v_prog) LIKE '%SQL Dev%'    

THEN

insert into UNFI_TEST_LOGON_DETAILS values (v_prog,sysdate);

commit;

--RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login as schema owner.');

END IF;

END;

/

to drop the changes....

drop trigger BLOCK_USER_LOGIN;

mysql: script to email when sessions in mysql instance are high or reached defined max value.

 Edit once you have copied to Vi Editor

1) session_count.sql

***************************************

tee ./logs/session.log

select count(*) from information_schema.processlist;

notee;

**********************************

2 ) Session_count.sh

**********************************************************************

#!/bin/ksh

#########################################################################

# PROGRAM NAME: session_count.sh   for MySQL

#

# USAGE: ./session_count.sh 

#

#########################################################################

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H%M")

hostname=`hostname -s`

MAILTO=USER_SIVA@ABC.com

PWD=/opt/siva/scripts/sessions  # put script dir path if you want to run from "crontab"

repdir=$PWD

LOG=$repdir/logs

if [ ! -d $LOG ]; then

mkdir  $LOG

else 

rm $LOG/session.log

if [ -f $LOG/session_details.html ]; then

rm $LOG/session_details.html

fi

fi

####### ******************** Below list of changes are req:

conf_file=/etc/my.cnf

BACKUP_USER=TEST_USER     # can be root.

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`     # to hide password

Vald_sess_C=200             # max value limit to email.

log_ret=30                  # retention for logfiles

Logfile=mysql_Session_"$hostname"_$DATE.log

echo " started $DATE" >>$LOG/$Logfile

#### Changes are done:

####### ******************** run Active file:

if [ -f $LOG/bkp_active_file ]

 then

   echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO

   exit 0

fi

###

touch $LOG/bkp_active_file

mysql -Ns -u TEST_USER -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1 

####### ******************** Check the session count:

Check_Sess_C=`cat $LOG/session.log`

if [ $Check_Sess_C -gt $Vald_sess_C ];

then

mysql -H -u TEST_USER -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Email Output to Mail:

( echo " HOST: $hostname - MySQL Sessions are High - Details: "

  echo "To: USER_SIVA@ABC.com"

  echo "From:  <$hostname> "

   echo "MIME-Version: 1.0"

   echo "Content-Type: text/html; charset=US-ASCII"

echo "<html>"

echo "<body>"

   echo "<font size="+2">" "<font color="red">"

   echo "<u>"

   echo "Mysql sessions are high - Count - $Check_Sess_C ."

   echo "</u>"

   echo "<br/>"

   echo "<font size="-1">" "<font color="black">"

   echo "<pre>"

   cat $LOG/session_details.html

   echo  "<pre>"

   echo "Successful end of $repdir/session_count.sh"

echo "</body>"

echo "</html>"

 ) | /usr/sbin/sendmail $MAILTO

### End of Email

fi

####### ******************** Purge Log directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

####### ******************** Remove run active file:

if [ -f $LOG/bkp_active_file ]

 then

rm $LOG/bkp_active_file

echo " removed run-active file" >> $LOG/$Logfile

fi

exit

### All steps are Done.

***************************************************************