to find queries ran in last one month accessing a particular object in oracle database

select tab.*,t.sql_text from (select * from dba_hist_sqltext where sql_text like '%MSTR.VW_MS_DSTR_DATAMART%')t,
(select trunc(b.begin_interval_time) DateRun,a.PARSING_SCHEMA_NAME,
a.Sql_id,a.plan_hash_value PLANHASH,sum(a.executions_delta) executions,max(px_servers_execs_delta) PXUsed,
sum(a.disk_reads_delta) disk_reads,sum(a.buffer_gets_delta) buffer_gets,sum(a.rows_processed_delta) rows_proced,
sum(a.elapsed_time_delta)/(1000000*60) ELAPSEDTIME_mIN,
sum(a.elapsed_time_delta)/(1000000*60*decode(sum(a.executions_delta),0,1,sum(a.executions_delta))) Per_EXEC_MINS
from DBA_HIST_SQLstat a, dba_hist_snapshot b
where a.snap_id = b.snap_id and trunc(b.begin_interval_time)> trunc(sysdate-30)
group by trunc(b.begin_interval_time),a.PARSING_SCHEMA_NAME,
a.Sql_id,a.plan_hash_value order by trunc(b.begin_interval_time) desc) tab
where tab.sql_id=t.sql_id;

Comments

Post a Comment

Popular posts from this blog

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

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_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...
Read more

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