shivenracdba

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', 'OLA...

"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.

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

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>';

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

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;

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...

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     ...

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; exp...

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 ...

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 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; ========================================...

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...

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. ...

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 ...

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...

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".

create or replace procedure sp_process_move_data as lv_tablespace varchar2(100) := '<tablespace_name>'; lv_objects_owner varchar2(100) := '<schema_name>'; lv_sql varchar2(100); type lr_user_segments is record ( segment_name user_segments.segment_name%type, segment_type user_segments.segment_type%type, segsize number, partition_number number); 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_partitions lt_user_tab_partitions; la_user_indexes lt_user_indexes; la_user_ind_partitions lt_user_ind_partitions; la_user_segments lt_user_segments; procedure log_msg(pv_msg in varc...

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; /

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

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 per...