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...