Handling PL/SQL Errors

Good article to understand error management with PL/SQL

http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html

http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html

How to call External Procedure from Oracle Database

create a file which has name "shell.c"

content of shell.c file
------------------------------------
#include
#include
#include

void sh(char *);

void sh( char *cmd )
{
int num;

num = system(cmd);
}
------------------------------------

--create object file
[oracle@host01 lib]$  gcc -fPIC -g -c -Wall shell.c

--create shared library file
[oracle@host01 lib]$  gcc -shared -o shell.so shell.o


modify parameter in $ORACLE_HOME/hs/admin/extproc.ora file

SET EXTPROC_DLLS=/home/oracle/lib/shell.so

--create lib and procedure for test

CREATE OR REPLACE LIBRARY SHELL_LIB
 IS '/home/oracle/lib/shell.so'
/

CREATE OR REPLACE PROCEDURE "SHELL" (cmd IN char)
as external
name "sh"
library shell_lib
language C
parameters (cmd string);
/


SQL> exec shell('/bin/pwd > /home/oracle/lib/aa.txt');


output file looks like

[oracle@host01 lib]$ more aa.txt
/u01/oracle/11.2.0.3/dbs

ORA-28575: unable to open RPC connection to external procedure agent

RMAN ORA-01008: not all variables bound

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 10 10:33:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /


DBGSQL:     TARGET> select count(*) into :dbstate from v$parameter where lower(name) = '_dummy_instance' and upper(value) = 'TRUE'
DBGSQL:        sqlcode = 1008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01008: not all variables bound




workaround:

SQL>alter system flush shared_pool;

Oracle database trigger order

Oracle Database fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

Execution steps of trigger.
http://docs.oracle.com/cd/B28359_01/server.111/b28318/triggers.htm#CNCPT418


 DBA_TRIGGER_ORDERING description
http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2107.htm#REFRN20581

sample:

create or replace trigger trigger_01
before insert on test
for each row
follows trigger_02
....


create or replace trigger trigger_02
before insert on test
for each row

sqlplus ORA-01031: insufficient privileges

You want to connect intance to start.(oracle 11g)

but there is an error.

like

[oracle@oratest01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 23 02:00:05 2009

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

1-Check groups in linux.(if dba group doesn't exist firstly create it)

more /etc/group
.
....

oinstall:x:500:
dba:x:501:oracle


2-Check your oracle user groups

more /etc/passwd
.
....
oracle:x:500:500::/home/oracle:/bin/bash



[root@oratest01 ~]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

RMAN-20032: checkpoint change# too low

If you use catalog for backup


RMAN> resync database;
RMAN-20032: checkpoint change# too low

If database returned from cold backup resync can't be succesful.

solution

connect target /
connect catalog user@catdb

unregister database;
register database;

Veritas Volume Manager

Useful command samples for Veritas Volume Manager.

http://www.hyborian.demon.co.uk/notes/vx_cli.html

ORA-22858: invalid alteration of datatype (convert varchar2 to clob)

When you convert a field from varchar2 to clob
raise an error ORA-22858: invalid alteration of datatype

SQL> create table vty_ra1 (a varchar2(10));



SQL> alter table vty_ra1 modify a clob;



ORA-22858: invalid alteration of datatype

workaround:

alter table vty_ra1 modify a long;




alter table vty_ra1 modify a clob;

RMAN-00569: ERROR MESSAGE STACK FOLLOWS

When you want to backup your db with using catalog database.

If raise an error like follow

INF - RMAN-00571: ===========================================================
INF - RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
INF - RMAN-00571: ===========================================================
INF - Recovery Manager complete.
INF - End of Recovery Manager output.
INF - End Oracle Recovery Manager.

Cause

Catalog db's tns entry must be in tnsnames.ora.

Check your $ORACLE_HOME/network/admin/tnsnames.ora file.

How to open large file more than 2GB

in C program you can add line.

#define _FILE_OFFSET_BITS 64

Uninstall ORACLE HOME manually

You can use to deinstall ORACLE_HOME from inventory with following method.(oracle database or grid home)

export ORACLE_HOME=

# detach 
$ORACLE_HOME/oui/bin/runInstaller -detachHome -silent -local ORACLE_HOME=$ORACLE_HOME

# confirm:
$ORACLE_HOME/OPatch/opatch lsinventory -all   

# remove directory
rm -rf $ORACLE_HOME

MUST_BE_SAME_TIMEZONE_FILE_VERSION

When you upgrade database to 11g r2

when running catupgrd.sql

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
  2     FROM sys.props$
  3     WHERE
  4       (
  5        ((SELECT TO_NUMBER(value$) from sys.props$
  6           WHERE name = 'DST_PRIMARY_TT_VERSION') !=
  7         (SELECT tz_version from registry$database))
  8        AND
  9        (((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
10           '9.2.') OR
11         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
12           '10.1') OR
13         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
14           '10.2') OR
15         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
16           '11.1'))
17       );
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
                 *
ERROR at line 1:
ORA-01722: invalid number


control following steps


SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        4


If version of timezone_file is different than TZ_VERSION
run following update.

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;


try to run catupgrd.sql

ORA-14265: data type or length of a table subpartitioning column may not be changed

When you want to modify partition table' s key columns.

sample

1 - create partitioned table with subpartition.


SQL> CREATE TABLE vty_log
    (tarih        DATE,
    program       VARCHAR2(6),
    pcismi        VARCHAR2(12),
    mesaj         VARCHAR2(2000),
    boyut         NUMBER(10,0))
  PARTITION BY RANGE (TARIH)
  SUBPARTITION BY HASH (program)
  (
  PARTITION vty_log_201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  LOGGING
  (
  SUBPARTITION vty_log_201201_P1,
  SUBPARTITION vty_log_201201_P2,
  SUBPARTITION vty_log_201201_P3,
  SUBPARTITION vty_log_201201_P4
  )
  );

2- try to modify subpartition column

SQL> alter table vty_log modify program varchar2(10);

*

ERROR at line 1:

ORA-14265: data type or length of a table subpartitioning column may not be changed 

you have to drop/create table to achive this problem.

You can create non-partition table for each partition which has same name with partition

and exchange partition 

or

If have enoguh downtime you can export/import with orjinal table. 

or

you can create dummy table desired structure and you can insert/select/rename.

Oracle 11g Cross platform Active Standby

You can use different supported platform for standby database with using 11g active standby features.

please click here

Oracle JDBC Connection Test

Save source  file: Conn.java

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       } 
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     } 
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   } 
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

ps: Change host, port, SID, user and password to your value in connection url

("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

Compile java

javac -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn.java

Run program

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn



PS : for compile and run in windows os, change columns(:) to semicolons(;)  like
.;$ORACLE_HOME/jdbc/lib/ojdbc5.jar;$ORACLE_HOME/jlib/orai18n.jar

also change jar names in their directories the correct ones.

simple java tricks

http://www.oracle.com/technetwork/java/compile-136656.html

Oracle SNAPSHOT STANDBY Database

For test purpose or others you can open read/write mode your standby database.



--to make read/write

alter system set db_recovery_file_dest_size=100G;
alter system set db_flashback_retention_target=7200; ---5 days
alter system set db_recovery_file_dest='/u01/flashback';

shutdown immediate

startup mount

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

shutdown
startup mount
alter database open;

select open_mode,database_role from v$database;



--to return physical standby

shutdown immediate
startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

shutdown
startup mount
select open_mode,database_role from v$database;

--start recovery.
alter database recover managed standby database disconnect from session;



http://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm#SBYDB00708

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams056.htm#REFRN10233

ORA-02303: cannot drop or replace a type with type or table dependents

You can't replace type sources if it has dependent object(s).
for example another type is produced from this type.

If you want to replace new source you must drop dependent objects before.
and create again that after replaced main type source.

ORA-02303: cannot drop or replace a type with type or table dependents

for example

drop TYPE appuser.table_logtable;

CREATE OR REPLACE 
TYPE bankdb.type_hpl_heslog AS OBJECT
        (col1                                           DATE,
         col2                                           VARCHAR2(14)         
)
/

CREATE OR REPLACE 
TYPE appuser.table_logtable AS TABLE OF type_logtable;

Also you can find dependent object with below sql

select * from dba_dependencies
where name = 'TYPE_LOGTABLE' and owner='APPUSER';

Temporary Tablespace Usage

v$sort_usage dynamic performance views deprecated in 9.2

 http://docs.oracle.com/cd/B19306_01/server.102/b14238/changes.htm#i639263

can use V$TEMPSEG_USAGE to obtain temp segment usage by session.

col machine format a20
col osuser format a20

select s.sid,s.username, s.osuser, s.machine, sum(u.extents) from V$TEMPSEG_USAGE u, v$session s
where s.saddr=u.session_addr
group by sid,s.username, s.osuser, s.machine
order by 5 desc;


       SID USERNAME                       OSUSER               MACHINE              SUM(U.EXTENTS)
---------- ------------------------------ -------------------- -------------------- --------------
      1718 APPUSER                        orauser2             node1                          7561
      2170 RAMAZAN                        rozturk              DOMAIN\PCRAMAZAN                120
      3358 OTHERUSER                      SYSTEM               node3                            11

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

You have enough undo tablespace but you can't use free area althoug it is free.
or
there is no active transaction in your database but undo tablespace is full?


--firstly select free space by this select

SQL> select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS';

SUM(BYTES)
----------
1080098816

--select extent total size by grouping status

SQL> SELECT DISTINCT STATUS, SUM (BYTES), COUNT (*)
  FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;

STATUS    SUM(BYTES)         COUNT(*)                               
--------- ------------------ ---------------- 
ACTIVE              18481152               17 
EXPIRED             14417920              220 
UNEXPIRED         2064187392             8695 

SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
undo_management                      string                           AUTO
undo_retention                       integer                          3600
undo_tablespace                      string                           UNDOTBS

you can set "_undo_autotune" and undo_retention parameters  to trigger clearing unexpired extents which no one in use.

ALTER SYSTEM SET "_undo_autotune"=FALSE;
ALTER SYSTEM SET undo_retention=3600;

Kill inactive sessions

If you want to kill all inactive session in database which has been wait 10 minutes.
You can use below statements.

SQL> select count(*) from v$session;

  COUNT(*)
----------
      503

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' cmd
from v$session where last_call_et>600 and status='INACTIVE';

CMD
----------------------------------------------------------------------
alter system kill session '3803,3' immediate;
alter system kill session '3806,5' immediate;
alter system kill session '3811,3' immediate;
alter system kill session '3813,10' immediate;
alter system kill session '3814,20' immediate;
alter system kill session '3816,14' immediate;
alter system kill session '3823,135' immediate;
alter system kill session '3824,25' immediate;
.....