Category: Oracle

. $ENV_FILE echo “Started Analyze Schema of $SCHEMA_NAME at `date +%D-%T`” $ORACLE_HOME/bin/sqlplus -s DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE, granularity=> ‘ALL’ ); EOF echo “Ended at `date +%D-%T`” – Script End Also, configure cron for execution on every sunday 11.00 PM as following. 00 11 * * 0 /export/home/oracle/oraprocs/analyze_schema.ksh /oracle/app/oracle/oraprocs/ora_SID.env CIF > /export/home/oracle/oraprocs/logs/analyze_schema_CIF.log 2> /export/home/oracle/oraprocs/logs/analyze_schema_CIF.err

select job_name, state from dba_scheduler_jobs SQL> execute DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’); PL/SQL procedure successfully completed. SQL> execute DBMS_SCHEDULER.DISABLE(‘PURGE_LOG’); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL>

After getting output from sid_wise_sql.sql, you can use TOP unix command to see whether those unix processes are consuming some CPU or Memory?? This will be useful to detelrmine whether a session is doing somehing or just hanging..

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” »

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…” »

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;

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 )…

Read More “sid_wise_sql.sql” »

********************************************* ***** (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…

Read More “All About Trace Fils” »

(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” »