Posts

Showing posts from January, 2013

list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'

Search package name wise :-
select object_name, object_type , status
     from dba_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' )
     and object_name in ('DBMS_JAVA' ,'UTL_DBWS')  ;

OR  generic type search :-


select object_name, object_type , status
     from user_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' );



SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE Owner IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       '…

Latch Free wait event

"Latch Free" wait event in this database, to solve this wait event  re-size these parameters using below query.

alter system set open_cursors=600 scope=spfile;
alter system set session_cached_cursors=250 scope=spfile;
alter system set cursor_sharing='FORCE' scope=spfile;

and increase the SGA and PGA accordingly .

We need to bounce DB for these parameters to implement.

last_analyzed Oracle tables

select table_name, last_analyzed, num_rows
from user_tables
where last_analyzed < sysdate - 1
order by 3;


select trunc(last_analyzed) "LAST_ANALYZED", count(*), decode(db_unique_name,null,name,db_unique_name) DBNAME, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "RUNTIME" from dba_tables, v$database where owner='<username>' and last_analyzed >= sysdate-20 group by trunc(last_analyzed), decode(db_unique_name,null,name,db_unique_name) order by trunc(last_analyzed);

finding last dml on tables in Oracle

There is a way to find the last dml time :-

Alter Table Tabname Monitoring;

Once this is there, Select * from User_Tab_Modifications;

If you want all tables to be Checked, Run the script like this
Spool Monitor.sql
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
Spool Off;
@Monitor.sql


select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>;

 select * from all_tab_modifications where table_name = '<table_name>';

If Oracle Queue is invalid in Oracle Streams

Use this:

conn / as sysdba
exec dbms_aqadm_syscalls.kwqa_3gl_validatequeue('<user_name>','RM_QUEUE_ID11');
commit;
connect <user_name>/&PASSWORD
exec DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => '<queue_name>', force => true);

Oracle Job Backup script

select 'declare lv_job number(10); begin ' ||
       'sys.dbms_job.submit(job => lv_job, what => ''' || what ||
       ''', next_date => (sysdate + 1000), interval => ''' || interval ||
       '''); commit; end;'
  from user_jobs;

job stuck in RAC , Process and locks in Oracle RAC

1.Check for blocking sessions in DB.
2.Check for wait events in the system at time of job execution.
3.Execution plan of sql queries.
4.Stats/Fragmentation in the tables.

 select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=75 order by time_waited desc;

 select count(*),event from v$active_session_history where session_id=158 group by event order by count(*) desc;


select PROGRAM,sid,status,last_call_et/(60*60) ,sql_id from gv$session where last_call_et/(60*60) > 3 and status='ACTIVE' AND PROGRAM LIKE '%J00%';


select * from dba_hist_sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$active_session_history where session_id = 72 order by sample_id desc;
select * from dba_tab_statistics  where owner like '<user_name>'AND table_name like '<table_name>';
select * from gv$system_event where wait_class !='Idle' order by time_waited desc ;

stick the job to the specific instance in Oracle RAC

First method ( and easy one too ) :- note:-  must perform this activity on each instance one by one. ......stop jobs which is assigning to the specific instance.....
$ export ORACLE_SID=SID
connect database using job owner ....
$ sqlplus username/password
list of jobs and its instance.... 
sql> select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ----------       2510          0       2951          0       2992          0       3091          0       3111          0       1570          0       1567          0       1568          0       1569          0       1571          0       1572          0
stick the job to the specific instance.....
Syntax DBMS_JOB.INSTANCE (    job        IN BINARY_INTEGER,    instance   IN BINARY_INTEGER,    force      IN BOOLEAN DEFAULT FALSE);
sql>execute dbms_job.instance(job=>1571, instance=>2,force=>true);
verify from the instance number........
sql>select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ---------- …

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

Installing DBMS_JAVA package in Oracle following  files need to be run:- 
sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';
@?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; @?/rdbms/admin/catexf.sql;
and verify your java installation in Oracle Database 
sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; sql> select dbms_java.longname(object_name), status, object_type from all_objects where       object_type = 'JAVA CLASS';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
calling UTL_DBWS web services through Oracle Database :- 
set the follwing parameters in .bash_profile of oracle user  export JAVA_HOME=$ORACLE_HOME/jdk ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/bin:$PATH; export PATH CLASSPA…

Oracle DB Health Scripts 2 - I/O,cpu_time,user_time,wait_time,PGA_memory

SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select
    …

memory leak

Image
Recently i found a interesting situation where a LQA server has only 3 GB of RAM and  5 GB of swap . I assigned the 20GB of memory to the Oracle and re-start it  and without any problem it started smoothly . I tried to found out the memory leakage on this server but in vain , and the most interesting part is when all the users log on this server , this worked fine without any problem .Again i re-assigned the memory to it  according to the RAM and requirement . :)

Find out invalid objects in Oracle Database and Generate scripts to compile

Find out invalid objects in Oracle and compile them :-

select owner,count(*),object_type from dba_objects where status='INVALID' group by owner,object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by owner;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :-


select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
===========================================================


query …

get ddl of dblinks ,tablespace,user creation, role granted

Get ddl of database links in Oracle Database :-

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

================================================================== Get ddl of tablespace creation in Oracle Database :- 
 select 'create tablespace ' || df.tablespace_name || chr(10)  || ' datafile ''' || df.file_name || ''' size ' || df.bytes   || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '   || maxbytes)   || chr(10)   || 'default storage ( initial ' || initial_extent   || decode (next_extent, null, null, '…

ORA-01476 divisor is equal to zero , DBMS_STATS.GATHER_TABLE_STATS

exec dbms_stats.gather_schema_stats('<username>',CASCADE => TRUE);

to gather index stats :-

SQL>  BEGIN
         dbms_stats.gather_schema_stats(
         ownname=>'<username>',
          METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
          CASCADE=>TRUE,
           ESTIMATE_PERCENT=>100);
           END;
     /

 exec dbms_stats.gather_schema_stats('<username>',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

===================================================================
Login as schema user and follow below steps one by one :-



step 1:-
  exec DBMS_STATS.SET_TABLE_PREFS('<username>','<tablename>','INCREMENTAL','TRUE');

Step 2:-
Check if the above command has set the preferences correcctly.

  select dbms_stats.get_prefs('INCREMENTAL', tabname=>'<tablename>') from dual;

step3:-

exec dbms_stats.gather_table_stats(ownnam…

finding fragmentation at table level and tablespace level in Oracle and steps to remove them

Use this query :-


select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
    round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
    round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
    from all_tables  WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Or This one :- It will collect the data which are having mor…

Oracle : force query to use hints

alter session set tracefile_identifier='RC_CBO';
alter session set events '10053 trace name context forever, level 1';
Following these two commands, run either the problem query or an explain plan for the problem query. Look for the tracefile in the user_dump_dest directory; it can be readily identified by its tracefile_identifier (in this case,RC_CBO). Browse it, attempt to understand as much of it as you can, hint your query, and start the tracing process over again. It can be very helpful to have both tracefiles (before and after hinting.


To get a trace of your own SQL session, the following statements should be included preceding the SQL under analysis:
alter session set tracefile_identifier='RC_PROF';
alter session set sql_trace=TRUE;
To also see wait events (recommended!) use this alternative :-
alter session set tracefile_identifier='RC_PROF';
alter session set events '10046 trace name context forever, level 8';
Look for files with the t…

exclude parameter in Oracle EXP Datapump

expdp dumpfile=<file_name>.dmp exclude=TABLE:\"IN \(\'<table_name1>\',\'<table_name2>\',\'<table_name3>\'\)\"   schemas=<user_name>


Note :- If original Export used INDEXES=n, then Data Pump uses the EXCLUDE=INDEX parameter. If original Export used INDEXES=y, then Data Pump uses the INCLUDE=INDEX parameter".

Oracle Custom Procedure for defragmentation Partition tables and their associated index and move non partitioned table

create or replace procedure sp_process_move_data as
lv_tablespacevarchar2(100) := '<tablespace_name>';
lv_objects_ownervarchar2(100) := '<schema_name>';
lv_sqlvarchar2(100);

type lr_user_segments is record (segment_nameuser_segments.segment_name%type,
segment_typeuser_segments.segment_type%type,
segsizenumber,
partition_numbernumber);

type lt_user_segments is table of lr_user_segments index by binary_integer;
type lt_user_tab_partitions is table of user_tab_partitions%rowtype index by binary_integer;
type lt_user_indexes is table of user_indexes%rowtype index by binary_integer;
type lt_user_ind_partitions is table of user_ind_partitions%rowtype index by binary_integer;

la_user_tab_partitionslt_user_tab_partitions;
la_user_indexeslt_user_indexes;
la_user_ind_partitionslt_user_ind_partitions;
la_user_segmentslt_user_segments;

procedure log_msg(pv_msg in varchar2) is
lv_ind number;
begin
execute immediate 'select nvl(max(sr_no),0)+1 from process_log'
into lv_ind;

execute …

modify statistics gather in Oracle

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


create or replace procedure user_stats
AS
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
      obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
  filter_lst.extend(1);
filter_lst(1).ownname:='<schema_name>';
filter_lst(1).objname:='<object_name>';

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'<schema_name>',objlist=>obj_lst, obj_filter_list=>filter_lst);
END;
end user_stats;
/


declare
  my_job number;
begin
  dbms_job.submit(job => my_job,
    what => 'user_stats;',
    next_date => trunc(sysdate)+1,
    interval => 'trunc(sysdate)+1');
end;
/

how to use dbverify tool in Oracle

select tablespace_name, segment_name, TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK
from sys.sys_user_segs
where tablespace_name='USERS' and SEGMENT_NAME like 'JUNK%';

dbv file=/ora_data/oracle/data/common_data.dbf   LOGFILE=common_data.log   feedback=200

Oracle Audit and how to clear off Oracle Audit

select user_name, audit_option, success, failure
        from sys.dba_stmt_audit_opts;

select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,
userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff
from sys.aud$ where userid=<USER_NAME>';

select username, priv_used, ses_actions from dba_audit_object;

select action, action_name, username from dba_audit_trail ;

select * from stmt_audit_option_map;


clear Audit :-


begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval    => 7*24 );
end;
/

Next, to perform the actual purge, you have to execute a packaged procedure dbms_audit_mgmt.clean_audit_trail().

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type =>  dbms_audit_mgmt.audit_trail_all
);
end;
/

Beware: this performs a DELETE operation so there will be a lot of redo and undo. When the initial purge is complete, you may want to set up an automated …