On which object is my query stuck (spending) time? Using v$session_wait and dba_extents

How to find the object name where our query is spending time?
In case the wait event in v$session_wait is "file sequential read", we can use the join below, to identify the index_name:



SQL> select segment_name,segment_type,owner,tablespace_name from
    dba_extents,v$session_wait
    where file_id=p1
   and p2 between block_id and block_id + blocks -1
   and sid=4933;

SEGMENT_NAME                                                                      SEGMENT_TYPE       OWNER                          TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
TABLE_TEST_4IX                                                               INDEX PARTITION    MY_USER                        APL_LARGE_IX

invoker_rights_clause to the rescue

The issue: we are trying to call a stored procedure defined in a different user, using a synonym, to truncate a table in our account. This is failing, since by default the procedure is ran using the "definer rights".

The solution: define the procedure/package to run using "invoker rights", as below:

CREATE PACKAGE     "TRUNC_TAB" AUTHID CURRENT_USER AS
    PROCEDURE TRUNCATE_TABLE (i_table_name in varchar2);
    PROCEDURE TRUNCATE_PARTITION (i_table_name in varchar2,i_partition_name  in varchar2);
END TRUNC_TAB ;
/