Concatenate 2 CLOB in oracle

SQL
====
Select  CONCAT(clob1, clob2) from table

PL/SQL
====
declare
  clob1 clob;
  clob2 clob;
begin
  clob1 := 'Clob1 ';
  clob2 := '+ Clob2';
  dbms_output.put_line(CONCAT(clob1, clob2));
end;

How to give Debug Grants to a schema

GRANT DEBUG CONNECT SESSION TO <schema>

Find List of Referenced dependencies for a package or procedure or view

select referenced_owner,referenced_name from all_dependencies where  lower(name)='package Name'

Error : timeout occurred while waiting to lock object

I am getting error timeout occurred while waiting to lock object when i Compile a package. I want to remove the lock.

SELECT * FROM v$access  where object='package_name'

Take that SID and pass it to the below query and take sid and serial#

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and s.sid=?

Kill Session

Use the below  query to kill the corresponding session

ALTER SYSTEM KILL SESSION 'sid,serial#'

Bulk Collect Example

Declare
  Type emprec IS RECORD(
    empno Number,
    name  varchar2(40));
  TYPE emptab IS TABLE OF emprec;
  cursor empcur IS
    select * from emp1;
  emptable emptab;

BEGIN
  OPEN empcur;
  FETCH empcur BULK COLLECT
    INTO emptable;

  if (nvl(emptable.LAST 0) > 0) then
    FOR i in emptable.FIRST .. emptable.LAST LOOP
      BEGIN
           INSERT INTO EMP1(empno,name) values (emptable(i).empno,emptable(i).name);
      END;
    END LOOP;
  END IF;

  COMMIT;
 END;