Creating calender rotation in plsql for x number of people...

Below is an example to create a rotation for 4 people in 3 step process. Make changes as needed.

1) 

create table test (START_DATE DATE,WEEK_DAY varchar2(20),W_D_N varchar2(10) default null,WEEK_NUMBER number,NAME varchar2(20) default null);

create table test1 ( ID number, NAME varchar2(10) default null);

insert into test1 values (1,'jack');

insert into test1 values (2,'mike');

insert into test1 values (3,'jim');

insert into test1 values (4,'bob');

commit;

2) ---load calender now with appropriate dates and validate randomly:

insert into test (START_DATE,WEEK_DAY,W_D_N,WEEK_NUMBER,NAME)

       SELECT (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) start_date,to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')Week_day,

     ( CASE 

    WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'MON%'   THEN   7      

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'TUES%'  THEN   1

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'WED%'   THEN   2

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'THUR%'  THEN   3

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'FRID%'  THEN   4 

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'SAT%'   THEN   5

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'SUN%'   THEN   6

     ELSE

          null

  END ) W_D_N,

        to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1), 'WW') week_number,null

  FROM DUAL CONNECT BY (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) <= to_date('2018-12-31','YYYY-MM-DD');

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

3) ---- have a rotation set from the above... change the value "x < 4" based on the rotation..

set serveroutput on

declare 

TYPE at_most_twelve_t IS VARRAY (100) OF VARCHAR2 (100);

l_months   at_most_twelve_t;

type rotat_name is VARRAY (100) OF VARCHAR2 (100);

rotat_cur rotat_name;

--temp varchar2(10);

x NUMBER := 0;

y NUMBER := 0;

z NUMBER := 0;

p NUMBER := 0;

Begin

SELECT distinct week_number  BULK COLLECT INTO l_months FROM test where name is null order by week_number asc;

  select name BULK COLLECT into rotat_cur from test1 order by id asc; 

  for i in 1 .. l_months.count

  LOOP

      -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || l_months(i));

       ---- set the x value for if loop based on number of people in rotation.

          if  x < 4 then

          x := x + 1;

          else 

           x := 1;

          end if;

             

         -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || x);

          for k in 1 .. rotat_cur.count 

             loop

                  if    k = x then  

             for j in (select w_d_n from test where week_number=l_months(i))

                 loop 

                 update test set Name=rotat_cur(k) where week_number=l_months(i);

                 commit;

                 end loop;

                  else

                    continue;

                    end if;

            end loop;

  END LOOP;

END;

commit;

===========================================

Hope this helps....

ASM disk to OS device to SCSI Id's (Linux)

Use below scripts to get the needed mapping between the ASM raw disks to OS devices to SCSI ID's.
use below script if your Unix admin is using dev rules for mapping....

run below one as oracle user ... if needed this to run as grid owner then change the logic below..

1) 1st script:-
disk_info.sql

{

col NAME for a30
col PATH for a30
spool asm_info.log
select NAME,PATH,OS_MB from V$ASM_DISK order by NAME asc;
spool off

}

2) 2nd script which is *.sh

ASM_disk_SCSI_shell.sh

{

COMMAND=$0

PGM=${COMMAND##*/}

host_name=`hostname | cut -d"." -f1`

# Remove nohup.out files if any from previous execution.

if [ -f nohup.out ]; then

   rm nohup.out

fi

#

## Choose directory/Path or hit enter to choose default path(currect directory).

echo "Enter the unix directory path to create the reports.Press Enter to create in current directory:"

read dir

if [ "$dir" = "" ]

then

repdir=$PWD

else

repdir=$dir

cp $PWD/*.sql $repdir

fi

##

### Remove the old log files.

cd $repdir

rm $repdir/*.log

rm $repdir/*.html

###

#### Chech to find which database/instance is up and running based on crontab entries.

   for db in `cat /etc/oratab| egrep -v '(^#|\+|\-)'|cut -f1 -d: -s|egrep -v 'OMS'|egrep -v 'AGENT'`

       do

         echo "$db";

         db_check=`pgrep -al pmon|grep $db |cut -f3,4 -d_`

            if [ -n "$db_check" ]

              then

              db_sid=$db_check

              # echo "$db_sid"

                echo "$db"

                dbhome=`cat /etc/oratab|egrep $db|egrep -v '(^#|\+)'|cut -f2 -d: -s`

                echo "$dbhome"

                uname -a

                break

                else

                echo "db_check is empty";

            fi

      done

####

### /// Setting environment variables.

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/xterm:/sbin:/opt/freeware/bin:/bin:.

export ORACLE_SID=$db_check

export ORACLE_HOME=$dbhome

PATH=$ORACLE_HOME/bin:$PATH

LIBPATH=$ORACLE_HOME/lib:$LIBPATH

### ///

#### Login into database to get the Disk info.

sqlplus / as sysdba << EOF

set heading off

set feedback off

set echo off

set pagesize 0

@$repdir/disk_info.sql

exit

EOF

sort -b -V -k 2 $repdir/asm_info.log >>$repdir/asm_info_cut.log_1

mv $repdir/asm_info_cut.log_1 $repdir/asm_info.log

cat asm_info.log| awk '{print $2}' >> $repdir/asm_info_cut.log

cat asm_info_cut.log | sort -V >asm_info_cut_1.log

mv asm_info_cut_1.log asm_info_cut.log

cat asm_info_cut.log|while read disk

do

echo "`cat /etc/udev/rules.d/60-raw.rules | grep -v '^#' | grep -w $disk | awk '{print $2}' | grep -Po '".*?"' | grep -oP '"\K[^"]+' `" >> $repdir/asm_disk_lun_info.log

echo "` raw -qa | grep -w $disk | awk '{print $1,$5,$7}' | sed 's/,//;s/://g' | awk '{ print $1"\t"$2":"$3}' `" >>asm_disk_lun_info_m.log

done

cat $repdir/asm_disk_lun_info.log |while read rawd

do

echo "$rawd  `lsscsi | grep $rawd''  | tr '[' '\t' | tr ']' '\t' | awk '{print $1}'` " >> $repdir/asm_raw-physical.log

done

paste $repdir/asm_info.log $repdir/asm_disk_lun_info.log $repdir/asm_raw-physical.log| awk  '

BEGIN {

format = "%-20s %-18s %-19s %-18s  %-19s %s\n"

printf format,  "ASM_groups", "ASM_RAW_DISK",  "OS_DISK_SIZE", "UNIX_MAP_Disk", "LINUX_id",  "LINUX_SCSI_id"

printf format,  "----------", "-------------", "------------", "-------------", "--------", "-------------"}

{for(i=1;i<=NF;i++)

printf("%-20s%c", $i, (i==NF) ? ORS : "")}' >> $repdir/asm_info_final.log;

exit;

}

Hope this helps...

tablespace script for 11g

select a.TABLESPACE_NAME,round((a.max_size/1024/1024/1024),3) MAX_SIZE_GB,
  ROUND(a.TOTSIZE/1024/1024/1024,3) as ALLOC_SPACE_GB ,
  ROUND((b.used)/1024/1024/1024,3) as Used_SPACE_GB ,
  ROUND(((b.used)/1024/1024/1024)/(a.TOTSIZE/1024/1024/1024)*100,3) as USED_PERCENT_ALLOC,
  ROUND(((b.used)/1024/1024/1024)/(a.max_size/1024/1024/1024)*100,3) as USED_PERCENT_MAXSIZE,
  ROUND((((a.totsize)/1024/1024/1024)- ((b.used)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
  ROUND((((a.TOTSIZE  - (B.USED)) / a.TOTSIZE) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,
  (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
FROM
  (SELECT tablespace_name, SUM(bytes) totsize,sum(maxbytes) max_size
  FROM dba_data_files@'DB_STRING'
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,SUM(bytes) used
  FROM dba_segments@'DB_STRING'
  GROUP BY tablespace_name
  ) b
where a.TABLESPACE_NAME=B.TABLESPACE_NAME

union

select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,
(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,
 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,
  (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
from
dba_temp_files@'DB_STRING'  a,dba_tablespace_usage_metrics@'DB_STRING' b,dba_tablespaces@'DB_STRING' c
where a.tablespace_name=b.tablespace_name
and b.tablespace_name=c.tablespace_name
and a.tablespace_name  in (''TEMP'')
group by a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),
(round(((b.used_space*c.block_size)/1024/1024/1024),3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),
ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,
ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0)

union

select a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,
(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,
 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3) as  FREE_SPACE_PERCENT_ALLOC,
 (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
from
dba_data_files@'DB_STRING'  a,dba_tablespace_usage_metrics@'DB_STRING' b,dba_tablespaces@'DB_STRING' c
where a.tablespace_name=b.tablespace_name
and b.tablespace_name=c.tablespace_name
and a.tablespace_name not like (''TEMP'')
and a.tablespace_name not in (select distinct tablespace_name from dba_segments@'DB_STRING')
group by a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),
(round(((b.used_space*c.block_size)/1024/1024/1024),3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),
ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,
ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3);


hope this helps...

script to reset sequence to 1 or reset sequence to greater than the current value

Use below script to reset value to 1 or less that the current value.

1)

Easy way to reset the sequence to a desired value quickly.

alter sequence NAME_ID_SEQ restart start with  1;

 alter sequence NAME_ID_SEQ increment by 1; 

2) Script:

create or replace procedure set_seq_to( seq_name in varchar2,

New_val in number )

 as

 l_curr number;

 begin

 execute immediate 'select ' || seq_name || '.nextval from dual' INTO l_curr;

 execute immediate 'alter sequence ' || seq_name || ' increment by ' || (New_val-l_curr-1) || ' minvalue 0';

 execute immediate 'select ' || seq_name || '.nextval from dual' INTO l_curr;

 execute immediate 'alter sequence ' || seq_name ||  ' increment by 1 ';

dbms_output.put_line ( 'Sequence ' || seq_name || ' is now at ' || New_val );

 end;

 /

Exec set_seq_to(‘seq_name’,reset-to-value);

'select ' seq_name.nextval from dual  -- run couple of times…. To make lastnumber greater than minvalue…

'alter sequence seq_name increment by 1  minvalue 1;

2)

greater than current value...
if you want to increase sequence to a value greater than the current value by 2414 then do below steps.

alter sequence NAME_ID_SEQ  increment by 2415;

alter sequence NAME_SEQ  increment by 1;

hope this helps..

OS Semaphores issue with multiple errors

When we have multiple DB with high processes for each database we will see this below semaphores issue when they are set to default numbers.

ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpcreates

edit /etc/sysctl.conf file from defaults.

kernel.sem = 250 32000 100 128 

change that to 

kernel.sem = 250 32000 100 256

you can change them on run time using below command

/sbin/sysctl -p 

on Linux check these...
ipcs -s | awk '{print $5}' |awk '{ sum+=$1} END {print sum}'
ipcs | wc -l ---- this number should not be more than "max number of arrays in ipcs -ls"  ... if you see higher number then try to increase the number.

Ref: Doc ID 949468.1 Doc applies to 12c, too. 

Hope this helps .....

check crs/has are enabled or not

how to check in multi environments.... whether crs option is enabled or not.
you can create a metric extension in OEM to check for all hosts....

#!/bin/sh

OSVER=`uname`
case $OSVER in
  "AIX")
asm_sid=`cat /etc/oratab| egrep -v '(^#)'|cut -f1 -d: -s |grep +`
asm_home=`cat /etc/oratab|grep ${asm_sid} |cut -f2 -d: -s`
export ORACLE_SID=${asm_sid}
export ORACLE_HOME=${asm_home}
echo $ORACLE_HOME
echo $ORACLE_SID
export ORACLE_BASE=/temp/app/oracle
export PATH=$ORACLE_HOME/bin:$PATH

test_val=`crsctl config has|grep -i enabled`  ----- replace if you have sudo only access
if [ -n "$test_val" ]; then
   x=1;
   z=crs-parm-enabled;
   else
   x=0;
   z=crs-parm-disabled;
fi

   break ;;
  "Linux")

asm_sid=`cat /etc/oratab| egrep -v '(^#)'|cut -f1 -d: -s |grep +`
asm_home=`cat /etc/oratab|grep ${asm_sid} |cut -f2 -d: -s`
export ORACLE_SID=${asm_sid}
export ORACLE_HOME=${asm_home}
echo $ORACLE_HOME
echo $ORACLE_SID
export ORACLE_BASE=/temp/app
export PATH=$ORACLE_HOME/bin:$PATH

nr_status=`ps -ef| grep lck |wc -l`
if [[ $nr_status -eq 1 ]]
 then
   test_val=`crsctl config has|grep -i enabled`
    if [ -n "$test_val" ]; then
      x=1;
      z=crs-parm-enabled;
    else
      x=0;
      z=crs-parm-disabled;
   fi
 else
    test_val=`sudo $ORACLE_HOME/bin/crsctl config crs|grep -i enabled`     ----------- replace if you have sudo only access
    if [ -n "$test_val" ]; then
      x=1;
      z=crs-parm-enabled;
    else
      x=0;
      z=crs-parm-disabled;
    fi
fi
break ;;
esac

echo $x;
echo $z;
echo "em_result=$z|$x";


home this helps...

tablespace sizing script for oracle 12c..

Consider only select statement inside the PLSQL...


create table PROD_DB_SIZE_DETAILS (INSTANCENAME                  varchar2(40),

                                   TABLESPACE_NAME               varchar2(40) ,

                                   MAX_SIZE_GB                   number(10,3) ,

                                   ALLOC_SPACE_GB                number(10,3) ,

                                   USED_SPACE_GB                 number(10,3) ,

                                   USED_PERCENT_ALLOC            number(10,3) ,

                                   USED_PERCENT_MAXSIZE          number(10,3) ,

                                   FREE_SPACE_TO_ALLOC_GB        number(10,3) ,

                                   FREE_SPACE_PERCENT_ALLOC      number(10,3) ,

                                   CON_ID                        number(10) ,

                                   RUN_DATE                      DATE

                                   );

                               

drop table PROD_DB_SIZE_DETAILS;

delete from PROD_DB_SIZE_DETAILS;

commit;

select * from PROD_DB_SIZE_DETAILS;





==============================================================

DECLARE

tab_count  number(10,2);

sql_stmt VARCHAR2(10000);

begin



sql_stmt := 'insert into PROD_DB_SIZE_DETAILS select INSTANCENAME,tablespace_name,MAX_SIZE_GB,ALLOC_SPACE_GB,USED_SPACE_GB,USED_PERCENT_ALLOC,USED_PERCENT_MAXSIZE,FREE_SPACE_TO_ALLOC_GB,FREE_SPACE_PERCENT_ALLOC,CON_ID,TRUNC(SYSDATE) from

(select a.TABLESPACE_NAME,round((a.max_size/1024/1024/1024),3) MAX_SIZE_GB,

  ROUND(a.TOTSIZE/1024/1024/1024,3) as ALLOC_SPACE_GB ,

  ROUND((b.used)/1024/1024/1024,3) as Used_SPACE_GB ,

  ROUND(((b.used)/1024/1024/1024)/(a.TOTSIZE/1024/1024/1024)*100,3) as USED_PERCENT_ALLOC,

  ROUND(((b.used)/1024/1024/1024)/(a.max_size/1024/1024/1024)*100,3) as USED_PERCENT_MAXSIZE,

  ROUND((((a.totsize)/1024/1024/1024)- ((b.used)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

  ROUND((((a.TOTSIZE  - (B.USED)) / a.TOTSIZE) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

  case when  a.con_id=1  then (select name from v$database@DB_STRING)

  when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

FROM

  (SELECT tablespace_name,con_id,    SUM(bytes) totsize,sum(maxbytes) max_size

  FROM cdb_data_files@DB_STRING

  GROUP BY tablespace_name,con_id

  ) a,

  (SELECT tablespace_name,    SUM(bytes) used,con_id

  FROM cdb_segments@DB_STRING

  GROUP BY tablespace_name,con_id

  ) b

where a.TABLESPACE_NAME=B.TABLESPACE_NAME

and a.con_id=b.con_id



union



select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,

(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,

 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

 when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_temp_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name  in (''TEMP'')

group by a.con_id,a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),

(round(((b.used_space*c.block_size)/1024/1024/1024),3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),

ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,

ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0)



union



select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,

(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,

 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_data_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name not like (''TEMP'')

and a.tablespace_name not in (select distinct tablespace_name from cdb_segments@DB_STRING)

group by a.con_id,a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),

(round(((b.used_space*c.block_size)/1024/1024/1024),3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),

ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,

ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3))';



execute immediate  sql_stmt;

commit;

end;



=================================================================



Hope this helps....

How to re-create UNDO tablespace in Rac databases.

First, check what undo files in the tablespaces..

1)

set linesize 200;

col FILE_NAME for a80;

col TABLESPACE_NAME for a20;

select FILE_NAME,TABLESPACE_NAME,status,online_status from dba_data_files where tablespace_name like 'UNDO%';

check size of the tablespace...

 SELECT tablespace_name,

        SUM ( ( (bytes / 1024) / 1024) / 1024) size_gb,

        COUNT (file_id) no_of_datafiles

 FROM dba_data_files

 WHERE tablespace_name LIKE 'UNDOTBS1'

 GROUP BY tablespace_name;

2)

see what UNDO tablespace is currently used... 

show parameter UNDO_TABLESPACE;

second, check what segments are still tried to the UNDO...

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN ( 

  SELECT segment_name

  FROM dba_segments 

  WHERE tablespace_name = 'UNDOTBS1'

);

If no rows found.... then..

alter system set undo_tablespace=UNDOTBS2 scope=both SID='RAC_1'

drop tablespace UNDOTBS1 including contents and datafiles;

3) re-create undo tablespace again....

create undo tablespace UNDOTBS1 datafile '+DATA' size 10G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

     alter tablespace UNDOTBS1 add datafile '+DATIND' size 5G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

follow the same for the other undo too...

Hope this Helps...

Upate a column based on the results of multiple tables in oracle

below is the generic select..

select b.abc,c.abc,c.abc,p.abc ,w.abc from table_A a,((c.abc*abc)*p.abc) as "normal"
table_A b, table_A c,table_A p, table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and rownum <10;

to update a table column based on mutiple tables result... use Merge Statement:


merge into table_A s
using
(select ((c.abc*abc)*p.abc)  as normal,w.abc  from
table_A f, table_A b,table_A p,table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and ((c.abc*abc)*p.abc) <> w.abc and rownum <10) z
on (s.abc=z.abc)
when matched then update set s.abc=z.abc;

Patch Inventory is missing in Alert log while DB is bounced.

In some cases, when a particular database is started the patch inventory is not loaded in the alert log and  says  “No patches have been applied” in the alert log.

To make the inventory to be loaded in alert log. Run the below statement in the database (and expect output as “OK”  and re-start the database again).

Note:- Don’t run ./datapatch from now…

select dbms_sqlpatch.verify_queryable_inventory from dual;

Hope this Helps...

creating baselines from AWR for consistant performance and how to move them to another environments

1)
Here we are creating a BASELINE profiles from previous runs using AWR reports... if we think the execution plan in one database is working fine and want to have the same consistant execution plan for sql.

login as system user

1)

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/

2)

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>4176, end_snap=>4179,basic_filter=>'sql_id = ''0cadrfdx9t0z1e''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'BASIC_PROC_NAME', populate_cursor=>cur);
  CLOSE cur;
END;
/



 SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'BASIC_PROC_NAME')
             );
           


3)

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    basic_filter=>'plan_hash_value = ''4972313474'''
    );
END;
/

Make sure you see the row….

SELECT * FROM dba_sql_plan_baselines ;

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;

4) To backup the sql baseline to other environment... then continue.

exec DBMS_SPM.CREATE_STGTAB_BASELINE('SQL_PROF_BASELINE','SCHEMA_NAME');

select table_name,owner from dba_tables where table_name='SQL_PROF_BASELINE';

SELECT * FROM dba_sql_plan_baselines;
no rows initially

5)

var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME', sql_handle => 'SQL_300901f0272d8837', plan_name => 'SQL_PLAN_30281y0mkv21r64b2d935' );
end;
/


6)

export ....

7)

Purge the existing which is not good....

exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');

8)
import.... as system user

unpack...

7)

 var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME');
end;
 /

SELECT * FROM dba_sql_plan_baselines;

***************************** END **********************************



 DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_300901f0272d8837'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/


Hope this Helps....

Performance: database slowness/blocking when Oracle Auto Task scheduled window jobs are running.

Disable below Oracle scheduled Auto jobs when you see slowness in the Application in one of the environments. 

If you have your own Gather Statistics Job  running in your environment and you do not want these advisor jobs..

These jobs usually runs in the night in weekdays and has a bigger window in the weekend.

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);



Hope this Helps...

How to check whether table is getting used or not in oracle

To find whether object that is getting used or not please do these little steps to judge...

1) using dba_dependiencies..

select
   owner,
   type,
   name,
   referenced_owner,
   referenced_type,
   referenced_name
from
   dba_dependencies
where
referenced_owner like 'ABC_%'
and referenced_type ='TABLE'
and referenced_name in (
'ABC_123');

2) check any DDL Operations where done.

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP FROM dba_TAB_MODIFICATIONS
WHERE TABLE_NAME='TMP_UCL_USER';


3) find any sql's were run from awr snapshots... add columns / make changes as you need.

select
   p.object_name  ,p.sql_id,
   to_char(sn.end_interval_time,'DD-MM')   ,
   count(1)                           
from
  dba_hist_sql_plan   p,
  dba_hist_sqlstat    s,
  dba_hist_snapshot  sn
where
  p.object_owner <> 'SYS' 
and
  p.sql_id = s.sql_id
and
  s.snap_id = sn.snap_id
  and
  sn.end_interval_time between to_date ('20-12-2017 10:00','DD-MM-YYYY HH24:MI')  and to_date ('01-01-2018 19:30','DD-MM-YYYY HH24:MI') and
 --s.executions_delta > 1000
p.object_name in (
 and
s.module not in ('emagent_SQL_oracle_database','emagent_AQMetrics','Oracle Enterprise Manager.Metric Engine')
group by p.object_name, to_char(sn.end_interval_time,'DD-MM'),p.sql_id
order by   2,1;


hope this helps......

how to get pluggable databases info from container database in oracle 12C using a common user.

This post helps you to query all pluggable databases info using common user in container database.. To do that we need to create a common user in the container database and provide neccesary grants.
As you all know we can create a common user with out "C##" in the container database by using "_" parameter..

alter session set "_ORACLE_SCRIPT"=true;

provide neccessary grants... connect/select_catalog_role/select any dictionary/ alter session/create session ... if neccesary tablespace limits too...

once the user is created you still need to run below command to read the pluggable databases information...

alter user COMMON_USER_CHECK set container_data=all;

Using this user and CDB* views now you can get all the info from container.  in one place..

Hope this helps..

Script to get Lun ids for Linux in VM for ASM disks.

Below information will be handy when you want to know the lun id'd for the raw devices you are using in the ASM environment ..this will eliminate the need for you to wait for Unix admins to give you the lun info. ....

> there are 2 scripts... one is .sql and another one is .sh(main shell)..

1)

 make a script to ger ASM info of the raw devices...

set heading off
set feedback off
set echo off
set pagesize 0
col NAME for a30
col PATH for a30
spool asm_info.log
select NAME,PATH,OS_MB from V$ASM_DISK order by NAME asc;

2)

then make a main shell.... and give execute permissions...

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

ORACLE_SID=+ASM

ORACLE_HOME=/usr/app/oracle/db_1

PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID

export ORACLE_HOME

export PATH

echo $ORACLE_SID

echo $ORACLE_HOME

cd /usr/job/scripts/lun_info

rm -f /usr/job/scripts/lun_info/asm_info.log

rm -f /usr/job/scripts/lun_info/asm_info_cut.log

rm -f /usr/job/scripts/lun_info/asm_info_final.log

rm -f /usr/job/scripts/lun_info/asm_disk_lun_info.log

rm -f /usr/job/scripts/lun_info/asm_raw-physical.log

sqlplus / as sysdba << EOF

set heading off

set feedback off

set echo off

set pagesize 0

@disk_info.sql

exit

EOF

#cat asm_info.log|cut -c7- > asm_info_cut.log

cat asm_info.log| awk '{print $2}' > asm_info_cut.log

cat asm_info_cut.log|while read disk

do

echo "`cat /etc/udev/rules.d/60-raw.rules | grep -v '^#' | grep $disk | awk '{print $2}' | grep -Po '".*?"' | grep -oP '"\K[^"]+' `" >> asm_disk_lun_info.log

done

cat asm_disk_lun_info.log |while read rawd

do

echo "$rawd  `lsscsi | grep $rawd'' | awk '{print $1}'` " >> asm_raw-physical.log

done

paste asm_info.log asm_disk_lun_info.log asm_raw-physical.log| awk  '

BEGIN {

format = "%-20s %-18s %-19s %-18s  %-19s %s\n"

printf format,  "ASM_groups", "ASM_RAW_DISK",  "OS_DISK_SIZE", "UNIX_MAP_Disk", "LINUX_id",  "LINUX_SCSI_id"

printf format,  "----------", "-------------", "------------", "-------------", "--------", "-------------"}

{for(i=1;i<=NF;i++)

printf("%-20s%c", $i, (i==NF) ? ORS : "")}' >> asm_info_final.log;

exit;

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

look for asm_info_final.log...... for this column....

LINUX_SCSI_id  --- validate with your storage admins...

Hope this helps......................................