Tuesday, February 11, 2025

PostgreSql: Useful Commands-

 1)   ************************* Users ***************************
 -- List of users with roles assigned:
SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
     ELSE
    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
select * from pg_roles where rolname='database_user';  ---- rolcanlogin : True/False
SELECT usename, datname, state FROM pg_stat_activity;
SELECT usename,datname,state,client_addr,xact_start,query_start, backend_start, state_change,query FROM pg_stat_activity
where usename not like 'rds%';
-----------------------------------------------
2) *************************** Table *******************************
---- Table size:
SELECT pg_size_pretty( pg_total_relation_size('tablename') );

SELECT count(*)  FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%';
SELECT schemaname,tablename,tableowner  FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%' order by tablename;
SELECT schemaname,count(*)  FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%' group by schemaname  ;


3) ****************************** Performance queries ******************

long running query:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

another query with Idle session.

It is. And no, idle queries aren’t considered LRQ at all. When state is idle, the query shows information for the last executed query.
 For the purpose of the article -identify LRQ- the query should be modified to the following:

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state != 'idle' and (now() - pg_stat_activity.query_start) > interval '5 minu


How to check which query is taking long time in PostgreSQL?

Using log_min_duration_statement parameter: To use the log_min_duration_statement parameter to trace slow queries in PostgreSQL. 
If you configure log_min_duration_statement in postgresql. conf as 3500,
PostgreSQL will categorize queries lasting more than 3.5 seconds as slow queries and record them in the log file.

4 ********************** Kill/Delete/blocking/locks sessions *************************

In order to cancel these long running queries you should execute:

SELECT pg_cancel_backend(__pid__);

It may take a few seconds to stop the query entirely using the pg_cancel_backend command.

If the you find the process is stuck you can kill it by running:

SELECT pg_terminate_backend(__pid__);


pg_locks: Information on current locks held within the database by open transactions, with one row per lockable object.
pg_blocking_pids(): A function that can find the process IDs (PIDs) of sessions that are blocking the PostgreSQL server process of a supplied PID.
pg_cancel_backend(): Function that cancels the currently running query by sending a SIGINT to a process ID.
pg_terminate_backend(): Terminate a backend process completely (the query and usually the connection) on the database (uses SIGTERM instead of SIGINT).
-------
show the blocked processes on your database along with the actual query that is blocking them is:
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

granted: True if lock is held, false if the process is waiting.
locktype: The type of object that is locked – (see here) relation, page, object, tranasctionid, userlock etc.

------

Joining the pg_lock view to pg_stat_activity for the query and pg_class for the table names can be useful to gather more context as to what is locking on your database at any point in time

select 
    relname as relation_name, 
    query, 
    pg_locks.* 
from pg_locks
join pg_class on pg_locks.relation = pg_class.oid
join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid;


5************************ Cron Jobs *******************************

Pg cron: setup and
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

show all; --- to see all parameter values.

select * from cron.job_run_details order by start_time desc;

select * from cron.job;

SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;




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

Monday, February 10, 2025

Random- finding users in DB and validate in AD

 first generate the list of DB users to the file for 10.11 and save to a text file in a location


open CMD

cd to the save txt file location and run below command.


for /f %i in (user_list.txt) do (echo %i & net user /domain %i |findstr Full) >>"user_log_output_2.txt"


for /f %i in (list-users.txt)

do 

command=

(echo %i & net user /domain %i |findstr Full) >>"user_log_output_2.txt"

Mysql: Handy commands when looking for any issues (mysqlbinlog)/diag report/mysql setting.

 

1) 

mysqlbinlog commands:

--- to start from specific time:

mysqlbinlog --start-datetime="2023-07-28 12:00:00" --base64-output=AUTO --verbose ctdayasd001-bin.000005 >/home/oracle/siva/asd_bin005.log


mysqlbinlog -d database_name --base64-output=AUTO --verbose mysqld-bin.000001 >/home/oracle/siva/test.log


--debug-check option will check for open file and memory use after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-check mysqld-bin.000001


--debug-info option will display additional debugging option after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-info mysqld-bin.000001

~


2) to run diag collect: like AWR for current time.

runs for 300 seconds(5 mins) for every 30 seconds to collect the details and report it.

mysql -u root -p -H -e"CALL sys.diagnostics(300, 30, 'current');" > /opt/mysql/current_Host_instance_report.html



3)

--- HTML report to check the mysql settings:

vi collect-mysql-det.sql

SELECT VERSION(), NOW(), @@time_zone, @@system_time_zone\G

/*!50503 SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH) AS DATA, SUM(INDEX_LENGTH) AS IDXS, ((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/(1024*1024)) AS TOTALMB

FROM information_schema.TABLES GROUP BY ENGINE*/;

select benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds

SHOW GLOBAL VARIABLES;

SELECT * FROM INFORMATION_SCHEMA.PLUGINS;

/*!80001 SELECT * FROM mysql.component*/;

-- part of an IC?

/*!50706 SELECT * FROM `performance_schema`.`replication_group_members`*/;

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

SELECT SLEEP(180);

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

STATUS;


MySQL MEB FULL Backup and Restore Testing (incremental)

 



MySQL MEB FULL Backup and Restore Testing


Here for this, we are testing the scenarios on Test server (TestServer). The backup and restore is little different from Oracle point of view. So we need to make sure we are testing MySQL as is with available functionality.  Do follow below steps to test the scenario to make sure we can put “Incremental Backups” in line.

1. Restore the database from Prod backup on to Test Server (TestServer). Once restored, Make sure can login into MySQL database and query tables and rows. Edit the shell with backup location.


Use this script: /opt/mysql/mysql-meb-restore_auto.sh


2. Once confirmed, take MEB backup for Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/mysql-meb-backup.sh FULL 14 


3. Once backed up, make sure you can login into MySQL database and query tables and rows and truncate below tables.

select table_schema,table_name,table_rows from information_schema.tables where

table_schema='scott' and table_rows < 20000 order by 3 desc;


select count(*) from scott.Table1;

select count(*) from scott.Table2;

select count(*) from scott.Table3;

select count(*) from scott.Table4;


example: 

truncate scott.Table1;


4. We now take the incremental backup on the Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/ mysql-meb-incr-backup.sh INCR 14


5. Now, we try to restore MySQL as full backup and test it. edit the shell with backup location.

Use this script: 

/opt/mysql/ mysql-meb-restore_auto_host.sh


6. Once restored, login and see the table rows of the above truncated tables. You should see all rows.


7. Now, do the incremental restore to get to the current state. Edit the shell with backup location.

Use script:

/opt/mysql/mysql-meb-restore_auto_incremental.sh


8. Once restore, login to MySQL database and check the truncate table row counts.


Point-in-time recovery:

Point-in-time is manual process in MySQL not like that we do in Oracle.

MySQL doesn’t recommend using timestamp to do point-in-time recovery. Instead we need to restore the database to a logfile before the event logfile happened and then apply the changes of the event logfile using start lsn and end lsn. Below link you can take a look for Point-in-time recovery.

• Using the --start-datetime or --stop-datetime option to specify the range of binary log segment to replay is not recommended: there is a higher risk of missing binary log events when using the option. Use --start-position and --stop-position instead.



Links for More Information: 

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/mysqlbackup.incremental.html

https://dev.mysql.com/doc/mysql-enterprise-backup/8.4/en/backup-incremental-options.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/restore.incremental.html

https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/advanced.point.html




Tuesday, August 20, 2024

PL/sql code to delete rows from table.

 -- DDL TO CREATE DEBUGGING TABLE -----> Monitor First time

-- create TABLE TRACK_TAB(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESC VARCHAR2(60))

-- create index TEST_TAB_INDX on TEST_TAB( CREATION_DATE)

DECLARE

nCount NUMBER:=0;

iCount NUMBER:=0;

 batchSize NUMBER:=5000;

daysOld NUMBER:=30;


 sql1 VARCHAR2(2000);

 starttime timestamp;

BEGIN

sql1 := 'DELETE FROM TEST_TAB WHERE CREATION_DATE < sysdate - ' || daysOld ||' and rownum <= ' || batchSize;

LOOP

starttime:=sysdate;

EXECUTE IMMEDIATE sql1;

nCount := sql%rowcount;

iCount:=iCount+nCount;

insert into TRACK_TAB values (batchSize,iCount,starttime, sysdate,'DELETE old TEST_TAB records ');


commit;

 EXIT WHEN nCount = 0;


END LOOP;

end;

Friday, April 28, 2023

Mysql: find tables with no primary key

 SELECT t.table_schema,
       t.table_name,
       k.constraint_name,
       k.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage k
          ON t.table_schema = k.table_schema
             AND t.table_name = k.table_name
             AND k.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
                                       'performance_schema' )
       AND k.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE'
       order by table_schema desc;

Wednesday, April 19, 2023

how to skip commented lines in linux in loop for shell script.

How to skip commented input lines from a input file in loop in Linux script:
 
below is the simple code with small example:


#!/bin/ksh
# Script to check required ORACLE - RHEL RPMs:
#
while read rpm_c
  do
case "$rpm_c" in \#*) continue ;; esac
rpm_check=`rpm -qa $rpm_c | wc -l`
if [[ $rpm_check != 0 ]]; then
rpm_check=YES
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c   $'\e[1;32m' $rpm_check $'\e[0m'
else
rpm_check=NO
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c  $'\e[1;31m'  $rpm_check $'\e[0m'
fi
done<RHEL-7_ORACLE19C_RPMS.log


input in the logfile:


libgcc
libstdc++
libstdc++-devel
libxcb
make
smartmontools
sysstat
#
#Optional Packages for Red Hat Enterprise Linux 7:
#
#ipmiutil (for Intelligent Platform Management Interface)
#net-tools (for Oracle RAC and Oracle Clusterware)
#nfs-utils (for Oracle ACFS)
#python (for Oracle ACFS Remote)

Mysql: Kill a session

 How to find and kill a session in Mysql.


select * from information_schema. processlist;


if we want to generate kill statement for 'IDs' the use below sql statement.


 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST , '   ',command,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

Saturday, April 15, 2023

Mysql: Kill a session

 In Mysql, If we want to kill the multiple sessions quickly. Use below sql statement to generate the "KILL statements for the ID's and decide based on the once to kill.


 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST ,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

 

example:

KILL 1  localhost;

KILL 24905  localhost2:1234;

Mysql: Replication setup between two mysql servers( Master - Slave ) setup.

Setting up replication between two mysql servers is easy. Not many steps involved.

below are the steps that need to be done to have replication working in Master-Slave configuration.


1) In order to setup the replication between the 2 servers. First we need to make sure everything is stopped on the source(Master) and the Target(Slave) side.

2) Bounce the Mysql on both Source and target.

3) Take the backup of the Source database( I used MEB method for the backup).

4) Restore the backup of the Source database in to Target database ( Used MEB backup).

5) we need to add Master configuration on the source side by editing /etc/my.cnf file



On Source(Master side):

server-id=121212   # if it is not present

sync_binlog=1  # enable "1" if replication is used for master

binlog_checksum = NONE

binlog_format = ROW

log-bin=/opt/mysql/log/hostname-bin.log

log-bin-index=/opt/mysql/log/hostname-bin.index

log_error=/opt/mysql/log/mysqld.log    --- if not present

expire_logs_days = 10

log_error_verbosity=2   # added it so that you can watch for errors and warnings. "1" just prints errors.


6) On source side, create replication user for the target with IP like below and grant replication grant.


mysql> create user 'TSTREPL_user'@'targetIP' identified by 'TSTREPL#1234';    --- run on master

mysql> grant replication slave on *.* to 'TSTREPL_user'@'targetIP';

mysql> Flush privileges;

 mysql> FLUSH TABLES WITH READ LOCK;

   show variables like 'sql_log_bin';

  set sql_log_bin=0;  --- to disable binary logging    we are doing this to disable replication for sometime till we setup slave with master backup and config etc.

   -- when ready to run command on step 5 on Slave-side

   mysql> unlock tables;


7) On Target side/ Slave side.

Run the below command to connect to Source side/Master.

CHANGE MASTER TO MASTER_HOST='sourceIP',MASTER_USER='TSTREPL_user',MASTER_PASSWORD='TSTREPL#1234',MASTER_LOG_FILE='hostname-bin.000001',MASTER_LOG_POS=150; 


8) On Source Side(Master side), reset the Master position and Log.

reset master

show master status \G;

(confirm bin is now reset to 000001)

 

9) On Target Side(Slave Side).

start slave;

show slave status \G;

  

10) Test replication by creating a table and insert rows on Master side(Source side) and check on slave side(target side).

On master..

use mysql;

create table TEST (id int);

    insert into TEST(1);

    select count(*) from TEST;  --- 1 row


11) On Target side (Slave), Validate the table got created and row got inserted.

use mysql;

select count(*) from TEST;  --- 1 row


end......

Mysql: generating sql statements for each row from simple select statement

 In Mysql, to generate sql statements for each row of the select statement. we need to use group_concat function. It is easier to generate the sql statements in oracle but it is little different in Mysql. 

Below is the statement that we need to use to make it work.

select group_concat(concat('select count(*) from ',table_schema,'.',table_name,';') ) from 
 information_schema.tables where table_schema='TEST' group by table_name;


output will be :

select count(*) from table_schema.table_name;     -- for each row from the above statement.

mysql: stored procedure block to get table row counts

 In Mysql we cannot use PLSQL. we need to make use of procedures to get the desired results that we need.

To get the table row counts of all the tables in mysql. Use below code.  Put below code in *.sql file and run as "source *.sql" after logging into mysql.

added a few check points to see the proc simulation.

------ code  

use mysql


drop procedure TestExampleProc2;

DELIMITER //
CREATE PROCEDURE TestExampleProc2()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE tabname,table_n,tabsch,tabcount,tabsch_n VARCHAR(70);
      DECLARE cur CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables where table_schema not in ('mysql','Performance_schema','sys','information_schema') and table_type = 'BASE TABLE'  order by table_schema asc;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN cur;
      label: LOOP
      FETCH cur INTO tabsch,tabname;
      set @table_n = tabname;
      set @tabsch_n = tabsch;
      set @query = CONCAT('select count(*) into @tabcount from ', @tabsch_n,".",@table_n);
     -- select @tabsch_n,@table_n;
     -- select @query;
      PREPARE stmt1 FROM @query;
      execute stmt1;
     -- select @query;
      select tabsch,tabname,@tabcount;
      DEALLOCATE PREPARE stmt1;
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;
      CLOSE cur;
   END//
DELIMITER ;


tee  mysql_table_count.log

call TestExampleProc2();

notee;

----------------------------- code ends

this will create a logfile with all the table counts.


Tuesday, March 14, 2023

Script: auto-generate a sql script for all individual sql scripts with spool file and pause

 

#!/bin/ksh


# USAGE: Script is used to generate a file to add spool to all files.


# check for filenames for spaces:


x=$(find . -type f -name "* *" |wc -l)

if [ $x != 0 ]; then

echo $x

j=$(find . -type f -name "* *")

echo -e "Correct the filenames with spaces:  \n$j "

exit;

fi


touch run_all_scripts.sql

echo "set echo on" >>run_all_scripts.sql

echo "set serveroutput on size unlimited" >>run_all_scripts.sql 



for i in `ls *.sql | grep -v run_all_scripts.sql`

do

j=${i%.*}

echo spool ELS_GIL_$j"."log >>run_all_scripts.sql

echo "@$i" >>run_all_scripts.sql

echo "spool off" >>run_all_scripts.sql

echo "pause;" >>run_all_scripts.sql

echo " " >>run_all_scripts.sql

done


echo "set echo off" >>run_all_scripts.sql

echo $'\e[1;33m\e[4m' "Script executed fine- check the file to confirm" $'\e[0m'

Tuesday, March 8, 2022

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;




Friday, September 3, 2021

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.

Monday, March 22, 2021

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;



Thursday, October 22, 2020

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;

Saturday, October 17, 2020

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;

Tuesday, October 13, 2020

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.

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

PostgreSql: Useful Commands-

 1)   ************************* Users ***************************  -- List of users with roles assigned: SELECT usename AS role_name,   CASE...