Partitions/Shell Script

Oracle : Check Partition Summary More than N Days (60 Days)

4 years ago by Rakesh SahuOracle DB Admin

Dear Readers, In this article, we will see the following Check Partition Summary More than N Days (60 Days). Script :

cat parti_last_60days.sh
#!/bin/bash
#Script to Check Partition In last 60 Days

#./Partition Report.sh DEMO

## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
rm /tmp/part1.log /tmp/part.log
touch /tmp/part1.log
export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1'
export ORACLE_SID='ORCL11'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexpert@ktexperts.com'
export LOG='/tmp'


_OWNER=$1

function sql_script
{
echo " "
echo "Started To Run Partition Check Sql Query"
echo " "
sqlplus vinod/vinod <<EOF > /tmp/t.log
SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
spool /tmp/schema.log
select table_name from dba_tables where owner='$_OWNER';
spool off;
EOF
sed -i '1d' /tmp/schema.log
sed -i '$d' /tmp/schema.log
wc=`wc -l /tmp/schema.log |awk '{ print $1 }'`

for (( c=1; c<=$wc; c++ ))
do
_tbls=`sed ''$c'!d' /tmp/schema.log`
_tbls1=` echo -n "${_tbls//[[:space:]]/}"`
if [ "$_tbls1" ]; then
echo "Checking Partition details on $_tbls1@$_OWNER"
sqlplus vinod/vinod <<EOF > /tmp/t.log
set line 2000 pages 2000
SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on
col OBJECT_NAME for a40
col SUBOBJECT_NAME for a40
col OWNER for a30
col CREATED for a30
spool /tmp/part.log;
SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4;
spool off
EOF
fi
sed -i '1d' /tmp/part.log
sed -i '$d' /tmp/part.log
#echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log
if [ ! -s /tmp/part.log ]; then
#echo " " >> /tmp/part1.log
#echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log
#echo " " >> /tmp/part1.log
_var='FALSE'
else
echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log
echo " " >> /tmp/part1.log
echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log
echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log
echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log
cat /tmp/part.log >> /tmp/part1.log
fi
done

echo " "
echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------"
}

function send_mail
{
cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST}
}

#MAIN
sql_script
send_mail

cat parti_last_60days.sh

#!/bin/bash

#Script to Check Partition In last 60 Days

#./Partition Report.sh DEMO

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

rm /tmp/part1.log /tmp/part.log

touch /tmp/part1.log

export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1'

export ORACLE_SID='ORCL11'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexpert@ktexperts.com'

export LOG='/tmp'

_OWNER=$1

function sql_script

{

echo " "

echo "Started To Run Partition Check Sql Query"

echo " "

sqlplus vinod/vinod <<EOF > /tmp/t.log

SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0

spool /tmp/schema.log

select table_name from dba_tables where owner='$_OWNER';

spool off;

EOF

sed -i '1d' /tmp/schema.log

sed -i '$d' /tmp/schema.log

wc=`wc -l /tmp/schema.log |awk '{ print $1 }'`

for (( c=1; c<=$wc; c++ ))

_tbls=`sed ''$c'!d' /tmp/schema.log`

_tbls1=` echo -n "${_tbls//[[:space:]]/}"`

if [ "$_tbls1" ]; then

echo "Checking Partition details on $_tbls1@$_OWNER"

sqlplus vinod/vinod <<EOF > /tmp/t.log

set line 2000 pages 2000

SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on

col OBJECT_NAME for a40

col SUBOBJECT_NAME for a40

col OWNER for a30

col CREATED for a30

spool /tmp/part.log;

SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4;

spool off

EOF

sed -i '1d' /tmp/part.log

sed -i '$d' /tmp/part.log

#echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log

if [ ! -s /tmp/part.log ]; then

#echo " " >> /tmp/part1.log

#echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log

#echo " " >> /tmp/part1.log

_var='FALSE'

else

echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log

echo " " >> /tmp/part1.log

echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log

echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log

echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log

cat /tmp/part.log >> /tmp/part1.log

done

echo " "

echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------"

}

function send_mail

{

cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST}

}

#MAIN

sql_script

send_mail

Out Put :

################   Partition Check on DEPART table of DEMO schema 60 Days Back ################# 
 
-------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                               Partition_Name                           OWNER                          PARTITION_CREATED_DATE
-------------------------------------------------------------------------------------------------------------------------------------------
DEPART                                   SYS_P21641                               DEMO                           11-JUN-20
DEPART                                   SYS_P13521                               DEMO                           27-JUL-20
DEPART                                   SYS_P13530                               DEMO                           28-JUL-20
DEPART                                   SYS_P13587                               DEMO                           29-JUL-20
DEPART                                   SYS_P13631                               DEMO                           04-AUG-20
DEPART                                   SYS_P13668                               DEMO                           05-AUG-20
DEPART                                   SYS_P13768                               DEMO                           06-AUG-20
DEPART                                   SYS_P13856                               DEMO                           19-AUG-20
DEPART                                   SYS_P13894                               DEMO                           20-AUG-20
DEPART                                   SYS_P13928                               DEMO                           11-SEP-20
DEPART                                   SYS_P14004                               DEMO                           12-SEP-20
DEPART                                   SYS_P14067                               DEMO                           14-SEP-20
DEPART                                   SYS_P14072                               DEMO                           15-SEP-20
################   Partition Check on EMPBKP table of DEMO schema 60 Days Back ################# 
 
-------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                               Partition_Name                           OWNER                          PARTITION_CREATED_DATE
-------------------------------------------------------------------------------------------------------------------------------------------
EMPBKP                                   SYS_P21661                               DEMO                           11-JUN-20
EMPBKP                                   SYS_P13561                               DEMO                           28-JUL-20
EMPBKP                                   SYS_P13632                               DEMO                           04-AUG-20
EMPBKP                                   SYS_P13672                               DEMO                           05-AUG-20
EMPBKP                                   SYS_P13772                               DEMO                           06-AUG-20
EMPBKP                                   SYS_P13857                               DEMO                           19-AUG-20
EMPBKP                                   SYS_P13903                               DEMO                           20-AUG-20
EMPBKP                                   SYS_P13949                               DEMO                           11-SEP-20
EMPBKP                                   SYS_P14009                               DEMO                           12-SEP-20

################ Partition Check on DEPART table of DEMO schema 60 Days Back #################

-------------------------------------------------------------------------------------------------------------------------------------------

TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE

-------------------------------------------------------------------------------------------------------------------------------------------

DEPART SYS_P21641 DEMO 11-JUN-20

DEPART SYS_P13521 DEMO 27-JUL-20

DEPART SYS_P13530 DEMO 28-JUL-20

DEPART SYS_P13587 DEMO 29-JUL-20

DEPART SYS_P13631 DEMO 04-AUG-20

DEPART SYS_P13668 DEMO 05-AUG-20

DEPART SYS_P13768 DEMO 06-AUG-20

DEPART SYS_P13856 DEMO 19-AUG-20

DEPART SYS_P13894 DEMO 20-AUG-20

DEPART SYS_P13928 DEMO 11-SEP-20

DEPART SYS_P14004 DEMO 12-SEP-20

DEPART SYS_P14067 DEMO 14-SEP-20

DEPART SYS_P14072 DEMO 15-SEP-20

################ Partition Check on EMPBKP table of DEMO schema 60 Days Back #################

-------------------------------------------------------------------------------------------------------------------------------------------

TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE

-------------------------------------------------------------------------------------------------------------------------------------------

EMPBKP SYS_P21661 DEMO 11-JUN-20

EMPBKP SYS_P13561 DEMO 28-JUL-20

EMPBKP SYS_P13632 DEMO 04-AUG-20

EMPBKP SYS_P13672 DEMO 05-AUG-20

EMPBKP SYS_P13772 DEMO 06-AUG-20

EMPBKP SYS_P13857 DEMO 19-AUG-20

EMPBKP SYS_P13903 DEMO 20-AUG-20

EMPBKP SYS_P13949 DEMO 11-SEP-20

EMPBKP SYS_P14009 DEMO 12-SEP-20

Thank you for giving your valuable time to read the above information. Follow us on Website … Read More

UNDO segments not Releasing Space

4 years ago by Rakesh MongaOracle DB Admin

Dear Readers, In this article, we will see UNDO segments not Releasing Space . UNDERSTANDING: There are three status of UNDO segments: ACTIVE: Currently in Used EXPIRED: Extend cannot be Used UNEXPIRED: unexpired extents were reused The UNDO records are not deleted when they are… Read More

Oracle : How to Kill Blocking Sessions in RAC

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will see Kill blocking sessions in RAC . Kill blocking sessions in RAC:

set serveroutput on
set feedback off
set linesize 300

declare
blockcount number;
sqlreport varchar(3000);
sqloutput varchar(5000);
v_rec varchar(3000);

cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID
from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET
from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN
FROM gv$session where username is not null and username=v and sid=b order by inst_id;
begin
DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');
DBMS_OUTPUT.put_line('-----------------------------------------------');
select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,
(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
IF blockcount > 0 THEN
DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');
DBMS_OUTPUT.put_line('-------------------------------------------------------');
for v_rec in blk_curs LOOP
dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');
DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');
for v_rec2 in distval LOOP
for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop
dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);
end loop;
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
ELSE
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
END IF;
END;
/

set serveroutput on

set feedback off

set linesize 300

declare

blockcount number;

sqlreport varchar(3000);

sqloutput varchar(5000);

v_rec varchar(3000);

cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID

from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET

from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN

FROM gv$session where username is not null and username=v and sid=b order by inst_id;

begin

DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');

DBMS_OUTPUT.put_line('-----------------------------------------------');

select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,

(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

IF blockcount > 0 THEN

DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');

DBMS_OUTPUT.put_line('-------------------------------------------------------');

for v_rec in blk_curs LOOP

dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');

end loop;

DBMS_OUTPUT.put_line('-');

DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');

DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');

for v_rec2 in distval LOOP

for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop

dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);

end loop;

DBMS_OUTPUT.put_line('-');

ELSE

DBMS_OUTPUT.put_line('-');

DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');

DBMS_OUTPUT.put_line('-');

END IF;

END;

Output:

Checking for blocking sessions on this database
------------------------------------------------
Found blocking sessions -> Fetching report for the same
--------------------------------------------------------
VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)
-
-
Further details on blocking sessions -> includes kill script of blocking session
--------------------------------------------------------------------------------
alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Checking for blocking sessions on this database

------------------------------------------------

Found blocking sessions -> Fetching report for the same

--------------------------------------------------------

VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)

Further details on blocking sessions -> includes kill script of blocking session

--------------------------------------------------------------------------------

alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Thank you for giving your valuable time to read the above information. Follow us on Website … Read More

Oracle: Comparison between Python and Shell Script for sending email for the set of SQL commands

4 years ago by Rakesh SahuOracle DB Admin

Dear Readers, In this article, we will see the following Comparison between Python and Shell Script for sending email for the set of SQL commands. Python and Shell Script for sending email for the set of SQL commands: Shell Script:

#!/bin/bash
#Script to perform testing
## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'
export ORACLE_SID='prod'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexperts@gmail.com'
export LOG='/tmp/test_mig.log'

#Function step1
function step1
{
echo " " >> $LOG
echo "Started Step 1" >> $LOG
echo " " >> $LOG
echo "Test email " >> $LOG
sqlplus vinod/vinod <<START >>$LOG
prompt updating records in EMP table
select * from emp where deptno=20;
prompt update emp set sal=sal+500 WHERE deptno=20;
update emp set sal=sal+500 WHERE deptno=20;
prompt select * from emp where deptno=20;
select * from emp;
START
echo " " >> $LOG
echo "----- END OF Step1 ------" >> $LOG
}

#Function to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/bin/bash

#Script to perform testing

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'

export ORACLE_SID='prod'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexperts@gmail.com'

export LOG='/tmp/test_mig.log'

#Function step1

function step1

{

echo " " >> $LOG

echo "Started Step 1" >> $LOG

echo " " >> $LOG

echo "Test email " >> $LOG

sqlplus vinod/vinod <<START >>$LOG

prompt updating records in EMP table

select * from emp where deptno=20;

prompt update emp set sal=sal+500 WHERE deptno=20;

update emp set sal=sal+500 WHERE deptno=20;

prompt select * from emp where deptno=20;

select * from emp;

START

echo " " >> $LOG

echo "----- END OF Step1 ------" >> $LOG

}

#Function to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Output:

updating records in EMP table
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 4800 20
7566 JONES MANAGER 7839 02-APR-81 6975 20
7788 SCOTT ANALYST 7566 19-APR-87 7000 20
7876 ADAMS CLERK 7788 23-MAY-87 5100 20
7902 FORD ANALYST 7566 03-DEC-81 7000 20

SQL> update emp set sal=sal+500 WHERE deptno=20
SQL>
5 rows updated.

SQL> select * from emp where deptno=20
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5300 20
7566 JONES MANAGER 7839 02-APR-81 7475 20
7788 SCOTT ANALYST 7566 19-APR-87 7500 20
7876 ADAMS CLERK 7788 23-MAY-87 5600 20
7902 FORD ANALYST 7566 03-DEC-81 7500 20

updating records in EMP table

SQL>

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 4800 20

7566 JONES MANAGER 7839 02-APR-81 6975 20

7788 SCOTT ANALYST 7566 19-APR-87 7000 20

7876 ADAMS CLERK 7788 23-MAY-87 5100 20

7902 FORD ANALYST 7566 03-DEC-81 7000 20

SQL> update emp set sal=sal+500 WHERE deptno=20

SQL>

5 rows updated.

SQL> select * from emp where deptno=20

SQL>

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 5300 20

7566 JONES MANAGER 7839 02-APR-81 7475 20

7788 SCOTT ANALYST 7566 19-APR-87 7500 20

7876 ADAMS CLERK 7788 23-MAY-87 5600 20

7902 FORD ANALYST 7566 03-DEC-81 7500 20

Python Script:… Read More

ORACLE: PL/SQL script to rebuild Table(s) and it’s corresponding indexes

4 years ago by Vinod SiramOracle DB Admin

ORACLE:PL/SQL script to rebuild Table(s) and it’s corresponding indexes. Note : Please implement in lower environments before proceeding to Production. Pre requisites : Please download and check below file for pre-requisites. Table creation scripts The below script will be used for 1.Rebuild table(s) 2.Rebuild… Read More

Tools for Administering the Oracle Database

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will discuss Tools for Administering the Oracle Database Please Fill below Google Form (If we missed any Products, Tools, and Utilities that you use in achieving your goals as a Cassandra Administrator.) 👇🏻👇🏻👇🏻👇🏻👇🏻 https://forms.gle/oracledba Monitoring Tools: OEM Nagios EM… Read More

Protected: Shell Script to get EMAIL alerts for ABENDED PROCESSES in Oracle GoldenGate

5 years ago by Vinod SiramOracle DB Admin

There is no excerpt because this is a protected post.

Protected: Automating drop partitions for set of Partition tables(Retention based on Selected Dates)

5 years ago by Binnary Ajay KumarOracle DB Admin

There is no excerpt because this is a protected post.

Generate DDL for Tables using PLSQL Package.

6 years ago by Binnary Ajay KumarOracle DB Admin

Generate DDL for Tables. In this article , we will see Generating DDL for tables in various methods. Generating DDL is much easier using Plsql Package Plsql Package : DBMS_METADATA.GET_DDL Method 1 : The below script will help you to take DDL only for a… Read More

ORACLE: GET NOTIFIED FOR TOP SQL’s ORDERED BY ELAPSED TIME

6 years ago by Vinod SiramOracle DB Admin

TOP SQL’s In this article,we will get notified for TOP SQL’s ORDERED BY ELAPSED TIME Step 1 : Create temporary tables to store TOP SQL’s

SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS
(
  SQL_ID            VARCHAR2(20 CHAR),
  LASTMODIFIEDDATE  DATE                        DEFAULT SYSDATE               NOT NULL
);

SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS

(

SQL_ID VARCHAR2(20 CHAR),

LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL

);

Another table to store ALL SQL’s.

SYS>>CREATE TABLE dbmon.ALL_ELAPSEDTIME_SQLIDS
(
  SQL_ID            VARCHAR2(20 CHAR),
  LASTMODIFIEDDATE  DATE                        DEFAULT SYSDATE               NOT NULL
);

SYS>>CREATE TABLE dbmon.ALL_ELAPSEDTIME_SQLIDS

(

SQL_ID VARCHAR2(20 CHAR),

LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL

);

Step 2 : Create the below scripts to capture TOP SQL’s in tables… Read More