Query to get long executed operation/sql

select 'OSUSER : 's.osuser a ,'OSPROC : 's.process a ,'ORAUSER : 'sl.username a ,'TARGET : 'sl.target a ,'SOFAR : 'sl.sofar a ,'TOTALWRK: 'sl.totalwork a ,'START : 'to_char(sl.start_time,'YY-MON-DD HH24:MI:SS') a ,'LASTUPD : 'to_char(sl.last_update_time,'YY-MON-DD HH24:MI:SS') a ,'ELAPSED : 'round(sl.elapsed_seconds/60,2) a ,'TMREMAIN: 'round(sl.time_remaining/60,2) a ,'CONTEXT : 'sl.context a ,'MESSAGE : 'sl.message afrom v$session_longops sl ,v$session swhere sl.target not like 'SYS%'and sl.sid = s.sidand sl.serial# = s.serial#order by start_time asc

Dropping a function index make packages using thre related table invalid

But if just drop a normal index will not invalidate packages related.

Query to find who is locking my object

set lines 120 hea on trunc on pages 50
col sid for 9999
col serial# for 9999999
col osuser for a10
col owner for a10
col module for a10
col username for a10
col type for a12
select sid
, serial#
, osuser
, username
, b.owner
, b.type
, module
, action act
from v$session v
,dba_ddl_locks b
where b.session_id = v.sid
and b.name =upper('&object_name')

Funny thing when use a package global variable through select

update ( select /*+ ALL_ROWS */ t.discography_intf_id, t.intf_status_id, t.err_mesg from da_discography_intf t, artist t1 where t.primary_artist_id = t1.artist_id and t.primary_artist_name <> t1.display_name ) set intf_status_id = (select da_discography_utl.get_intf_status_id( da_discography_constants.gc_intf_status_load_err) from dual), err_mesg = (select da_discography_constants.gc_intf_status_wrong_art_msg from dual);

This query causes ora-06502 error. My guess is when you use select from dual to get a constant value, it loses the sense of the length of the value, so will give a numeric error.
So don't select a function with a global constant.

Lag function can make query to have a default order by

select album_id, client_id, lag(client_id) over (partition by album_id order by submit_ts asc) lag_client_idfrom stat.submit_factwhere album_id = 440556

In this query, I did not put a 'order by' at the end of the query. But because I use lag function, it will make the whole query order by submit_ts. It makes sense.