pvmehta.com

Roles and Stored Procs II

31-Jan-2006 Admin No Comments

Problem Resolution For roles and Stored Procedures. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:961430030094 http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html As per http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:961430030094 with tom kyte, he has mentioned that we cannot compile this unless we give direct object privs to Owner of procedure, as at compile time AUTHID DEFINER and AUTHID CURRENT_USERS behave same. They both compile as per the definers privs. We can resolve…

Read More “Roles and Stored Procs II” »

Oracle, SQL scripts

Identical Dblink Issue…

30-Jan-2006 Admin No Comments

After discussion with The Oracle Support following is the conclusion. This appears to be the same problem as discussed in the following note and bug report: – Note:215123.1 “Procedures Get Invalidated When Database Link Names are Identical” – Bug:2485372 “OBJECTS SHARED SAME REMOTE OBJECT NAME INVALIDATES EACH OTHER” The conclusion is that this is working…

Read More “Identical Dblink Issue…” »

Oracle, SQL scripts

How to check current redo log progress redo_progress.sql

30-Jan-2006 Admin No Comments

select le.leseq “Current log sequence No”, 100*cp.cpodr_bno/le.lesiz “Percent Full”, cp.cpodr_bno “Current Block No”, le.lesiz “Size of Log in Blocks” from x$kcccp cp, x$kccle le where LE.leseq =CP.cpodr_seq and bitand(le.leflg,24)=8;

Oracle, SQL scripts

sid_wise_sql.sql

30-Jan-2006 Admin No Comments

accept u_name prompt ‘Enter User Name (Enter NONE if want SID specific query): ‘ accept s_id prompt ‘Enter SID (Enter 0 for Userspecific Query): ‘ select a.sid, b.sql_text, a.sql_id, c.spid, b.executions, b.disk_reads, b.elapsed_time, b.cpu_time, b.buffer_gets from v$session a, v$sqlarea b, v$process c where a.sql_id = b.sql_id(+) and (a.username = upper(‘&u_name’) or a.sid = &s_id )…

All About Trace Fils

26-Jan-2006 Admin No Comments

********************************************* ***** (1) TOOLS TO ANALYZE TRACE FILES. ***** ********************************************* Mainly 2 tools to analyze trace files. (A) tkprof (B) trcsess (from oracle 10g). This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module…

Korn Shell Arithmatic

23-Jan-2006 Admin No Comments

Method 1 ======== One more way of adding two numbers. Which one to use depends on which shell it is. d=400 e=52 f=`expr $d + $e` print $f Method 2 ======== #!/usr/bin/ksh let a=$1 let b=$2 let c=a+b print $c

Linux/Unix, shell

10g oem configuration

19-Jan-2006 Admin No Comments

(1) Check whether your database is registered with Enterprise Manager or not, using following command: posdbprod21:oracle> emctl status dbconsole OC4J Configuration issue. /oracle/app/oracle/product/10.1/oc4j/j2ee/OC4J_DBConsole_posdbprod21_CIF not found. posdbprod21:oracle> This means database is not registered with OEM. (2) Remember DBCA and other java interfaces use /vat/opt/oracle/oratab file to find the number of oracle instances instaled on that server….

Read More “10g oem configuration” »

Oracle, SQL scripts