spool cur_sql set lines 132 set pages 300 col USR format a14 col PAR heading “Parsing ID” format 9999999 col ELA heading “ELP time(Sec)” format 9999999.0 col HASH heading “Hash Value” col USR heading “Parsing User” col T heading “SQL Text having Elapsed time > 1 sec” break on HASH on USR on ELA skip…
df -k | grep -i “/EMCdb”|awk ‘{tot += $2; used += $3; avail += $4;} END {print “Total”,tot/1024/1024,”Used “,used/1024/1024,”Avail “avail/102 4/1024, “Avail % ” avail*100/tot, “Used % ” used*100/tot}’ df -k | grep -i “/testdb”|awk ‘{tot += $2; used += $3; avail += $4;} END {print “Total”,tot/1024/1024,”Used “,used/1024/1024,”Avail “avail/10 24/1024, “Avail % ” avail*100/tot, “Used…
. $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
Follwing is the script for manually gathering Stats using DBMS_STATS. Execution will be like ./analyze_schema.ksh /oracle/app/oracle/oraprocs/ora_SID.env CIF – Script Start #!/usr/bin/ksh ########## Configuration ############# ENV_FILE=$1 SCHEMA_NAME=$2
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>
find . -type d -exec du -sk {} ;
– Check Alert.log of standby server.
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…
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…
