Report historical wait times per event for a specific Table

I always try and name my constraint and index names
with the same prefix as my table name and then suffix it with
a few characters to indicate what type of constraint or index it is.

For Example;

For a table such as TRADE_DEAL;

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer
,GBP_Amnt     number          not null
,USD_Amnt     number);

The primary key would be named as follows;

alter table trade_deal 
add constraint TRADE_DEAL_PK primary key (deal_id);

A check constraint might be named as follows;

alter table trade_deal add constraint
TRADE_DEAL_CK_RISK check (trade_risk in (1,2,3,4,5,6,7,8,9));

The main reason for naming my objects using the above convention
is that it simplifies running queries to asses performance
stats on a particular set of related objects.

For example, the AWR query below will output the time waited
per wait event for a set of related table and indexes.

First set the start and end snapshot ID as variables before
attempting to run the query below. Use this script if you want;
http://bluefrog-oracle.blogspot.com/2011/11/set-start-and-end-snapshot-id-for-awr.html

variable p_Schema varchar2(30);
exec :p_Schema := sys_context('USERENV','CURRENT_SCHEMA');

col Object_Name format a32
col Object_Type format a10
col Event_Name  format a40
select
  /*+ all_rows */
   ds.Instance        as Instance_Number
  ,ao.Object_Name     as Object_Name
  ,ao.Object_Type     as Object_Type
  ,ds.Event           as Event_Name
  ,sum(ds.Cnt)        as Event_Wait_Cnt
  ,sum(Time_Waited)   as Time_Waited
from
  (
   select    /*+ all_rows */
             au.Username
            ,e.Name          as Event
            ,count(*)        as Cnt
            ,sum(Wait_Time)  as Time_Waited
            ,da.Current_Obj# as Object_ID
            ,Instance_Number as Instance
   from
             dba_hist_active_sess_history da
            ,v$Event_Name e
            ,all_users   au
   where     da.Event_ID = e.Event_ID
   and       da.User_ID  = au.User_ID
   and       da.Snap_ID  between :p_Start_Snap_ID and :p_End_Snap_ID
   and       au.Username = :p_Schema
   group by  au.Username
            ,e.Name
            ,da.Current_Obj#
            ,da.Instance_Number
   order by 3 desc
  )                     ds
  ,dba_Objects          ao
where
          ao.Object_ID          = ds.Object_ID
and       ao.Object_name        like  '&Object%'
and       ao.Object_Type        in ('TABLE','INDEX')
-- Exclude SQL Client type of waits
and       ds.Event              not like 'SQL*Net%'
group by  ao.Object_Name
         ,ao.Object_Type
         ,ds.Event
         ,ds.Instance
order by 5 desc, 4
/

Combining FORALL with BULK COLLECT

First create a few test tables along with some sample data;

create or replace type varchar2_ntt as table of varchar2(4000);
/

create or replace type number_ntt as table of number;
/

create type ty_Deal as object (Deal_ID number
                              ,Deal_Type varchar2(10)
                              ,Currency varchar2(10)
                              ,Convert_Rate   number
                              ,Converted_Amnt number);
/

create type tb_Deal as table of ty_Deal;
/

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer check (Trade_Risk in (1,2,3,4,5,6,7,8,9))
,GBP_Amnt     number          not null
,USD_Amnt     number);

create table FX_Rate
(FX_Rate_ID   number        not null
,FX_Rate_Date timestamp     not null
,Deal_Type    varchar2(10)  not null
,FX_From_Cy   varchar2(3)   not null
,FX_To_Cy     varchar2(3)   not null
,FX_Rate      number        not null
);

insert all
 into Trade_Deal values(1,'SPOT',   1,current_timestamp,1,10,null)
 into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)
  into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)
  into Trade_Deal values(4,'SPOT',   1,current_timestamp,1,22,null)
select 1
from dual
/

insert all
 into FX_Rate values (1,current_timestamp,'SPOT','GBP','USD',1.5757)
 into FX_Rate values (2,current_timestamp,'SPOT','USD','GBP',0.6336)
select 1
from dual

commit;


The goal of the procedure is to update the USD_AMNT colunm
on the TRADE_DEAL table based on the latest foreign exchange
rate available in the FX_RATE table for the given DEAL_TYPE.

The output will be a collection of updated Deals, which could
then be passed further onto a separate PL/SQL procedure or Java
class as a parameter.

The benefit of using BULK COLLECT with
a FORALL is that other PL/SQL procedures or Java classes do not have
to query the database again to identify which deals were updated since
they're all in the output collection that was populated as a result
of the FORALL UPDATE. 
The output collection could be passed on as a parameter.

Create a procedure to illustrate the  point;

create or replace procedure Update_To_Currency
  (
   p_Deal_Type  in Trade_Deal.Deal_Type%type
  ,p_To_Cy      in FX_Rate.FX_To_Cy%type
  ,p_From_Cy    in FX_Rate.FX_From_Cy%type
  ,p_Latest     in timestamp
  ,p_tb_Deal    out tb_Deal
  ) as

  tb_Spot         tb_Deal := tb_Deal(); -- input  collection
  tb_Spot_Updated tb_Deal := tb_Deal(); -- output collection

  l_To_Cy         FX_Rate.FX_To_Cy%type     := p_To_Cy;
  l_From_Cy       FX_Rate.FX_To_Cy%type     := p_From_Cy;
  l_Deal_Type     Trade_Deal.Deal_Type%type := p_Deal_Type;
  l_Latest        timestamp                 := p_Latest;

begin

  select  ty_Deal(td.Deal_ID,td.Deal_Type,l_To_Cy,fr.FX_Rate,0)
  bulk    collect into tb_Spot
  from    Trade_Deal td
  inner join FX_Rate fr on td.Deal_Type     = fr.Deal_Type
                        and fr.FX_From_Cy   = l_From_Cy
                        and fr.FX_Rate_Date <= l_Latest
  where   td.Deal_Type = l_Deal_Type;

  -- The following FORALL is combined with a BULK COLLECT
  forall i in 1..tb_Spot.count
     update Trade_Deal td
     set    td.USD_Amnt      =  td.GBP_Amnt*tb_Spot(i).Convert_Rate
     where  td.Deal_Type     =  tb_Spot(i).Deal_Type
     and    td.Deal_ID       =  tb_Spot(i).Deal_ID
     returning ty_Deal(td.Deal_ID
                      ,td.Deal_Type
                      ,l_To_Cy
                      ,tb_Spot(i).Convert_Rate
                      ,td.USD_Amnt)
     bulk collect into tb_Spot_Updated;

  p_tb_Deal := tb_Spot_Updated;
end Update_To_Currency;
/

Now for a test;

SQL> set serveroutput on
SQL> declare
  2    tb_Deals_Updated tb_Deal := tb_Deal();
  3  begin
  4
  5    Update_To_Currency ('SPOT'
  6                       ,'USD'
  7                       ,'GBP'
  8                       ,current_timestamp
  9                       ,tb_Deals_Updated);
 10
 11    -- Verify that the Collection contains only updated Deals
 12
 13    for i in 1..tb_Deals_Updated.count loop
 14      dbms_output.put_line(tb_Deals_Updated(i).Deal_ID
 15                 ||' '|| tb_Deals_Updated(i).Deal_Type
 16                 ||' '|| tb_Deals_Updated(i).Currency
 17                 ||' '|| tb_Deals_Updated(i).Convert_Rate
 18                 ||' '|| tb_Deals_Updated(i).Converted_Amnt);
 19    end loop;
 20
 21    --call other procs that take tb_Deal as a parameter type
 22    -- and pass tb_Deals_Updated as a parameter
 23
 24  end;
 25  /
1 SPOT USD 1.5757 15.757
4 SPOT USD 1.5757 34.6654

PL/SQL procedure successfully completed.

Finally, verify the TRADE_DEAL table was updated as well.

SQL> select deal_id,deal_type,usd_amnt
  2  from Trade_Deal
  3  where Deal_type = 'SPOT';

   DEAL_ID DEAL_TYPE    USD_AMNT
---------- ---------- ----------
         1 SPOT           15.757
         4 SPOT          34.6654

Identify SQL in blocking and waiting sessions

col Event format a25
col DML_BLOCKING format a45
col DML_In_Waiting format a45
set linesize 400
set pagesize 3000

-- LABEL: STATEMENT A
select  distinct
        a.sid                     as Waiting_SID
       ,d.sql_text                as DML_In_Waiting
       ,o.Owner                   as Object_Owner
       ,o.Object_Name             as Locked_Object
       ,a.Blocking_Session        as Blocking_SID
       ,c.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$active_session_history  b
       ,v$sql                     c
       ,v$sql                     d
       ,all_objects               o
where
        a.event                   = 'enq: TX - row lock contention'
and     a.sql_id                  = d.sql_id
and     a.blocking_session        = b.session_id
and     c.sql_id                  = b.sql_id
and     a.Row_Wait_Obj#           = o.Object_ID
and     b.Current_Obj#            = a.Row_Wait_Obj#
and     b.Current_File#           = a.Row_Wait_File#
and     b.Current_Block#          = a.Row_Wait_Block#

-- LABEL: STATEMENT B
select  distinct
        a.sid                     as Waiting_SID
       ,a.event                   as Event
       ,c.sql_text                as DML_Blocking
       ,b.sid                     as Blocking_SID
       ,b.event                   as Event
       ,b.sql_id                  as Blocking_SQL_ID
       ,b.prev_sql_id             as Blocking_Prev_SQL_ID
       ,d.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$session                 b
       ,v$sql                     c
       ,v$sql                     d
where
        a.event                   = 'enq: TX - row lock contention'
and     a.blocking_session        = b.sid
and     c.sql_id                  = a.sql_id
and     d.sql_id                  = nvl(b.sql_id,b.prev_sql_id);

The first statement is generally more accurate than the second, since
the session holding the lock may have issued several DML
statements since issuing the lock, so the DML that is output
as holding the lock may not be related to the blocking DML.

Occasionally you may find that the SQL_ID for the
session holding the lock is NULL, reason being that since issuing the
lock some PL/SQL code may have executed or a COMMIT is being performed
or some other ongoing latch activity is busy, in which case there
is no SQL_ID to identify the session with.

In such cases, join to NVL(B.SQL_ID,B.PREV_SQL_ID).


Sample output after simulating a blocking and waiting session;

In session 1, issue a full table lock;

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i', '1');
insert into test_lock values ('ii','2');
commit;
select * from test_lock for update;

In session 2, issue an update on one of the rows in table TEST_LOCK

update test_lock set a='i' where bb='1';

The above UPDATE statement will simply hang. Do not issue a rollback
in session1.

In Session 1, run the first SQL statement, labelled as "STATEMENT A" above.

Sample output;

WAITING_SID DML_IN_WAITING                          OBJECT_OWNER LOCKED_OBJECT                  BLOCKING_SID DML_BLOCKING
----------- --------------------------------------- ------------ ------------------------------ ------------ ----------------------------------
       2081 update test_lock set a='i' where bb='1' USER1        TEST_LOCK                               382 select * from test_lock for update

Identify blocking session for an UPDATE

Simulate a blocking session first:

Open 2 session windows, either in SQL Plus or any other cilent tool
you may be using.
In session 1 create a table and insert some data

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i','1');
insert into test_lock values ('ii', '2');
select * from test_lock;
commit;

Before starting the simulation, obtain the unique session ID's
for both sessions by using the following statement;

select distinct sid from v$mystat;

Obtain an exclusive lock in session 1 as follows;

select * from test_lock for update;



Now, in session 2, run these queries to identify blocking sessions;

update test_lock set a='i' where bb='1';

This statement will simply hang, since session 1 holds an exclusive lock on all rows within the table. Do not issue a rollback or commit;

Now query V$LOCK in session 1 using the following 3 statements;

set pagesize 3000
set linesize 1000
select * from v$lock;

select l1.sid, ' IS BLOCKING ', l2.sid, l1.lmode, l2.request
from   v$lock l1, v$lock l2
where   l1.block    = 1
and     l2.request  > 0
and     l1.id1      =l2.id1
and    l1.id2       =l2.id2;

select    s1.username
       || '@'
       || s1.machine
       || ' ( SID='
       || s1.sid
       || ' )  is blocking '
       || s2.username
       || '@'
       || s2.machine
       || ' ( SID='
       || s2.sid
       || ' ) ' AS blocking_status
from  v$lock    l1
     ,v$session s1
     ,v$lock    l2
     ,v$session s2
where s1.sid    = l1.sid
and   s2.sid    = l2.sid
and   l1.BLOCK  = 1
and   l2.request> 0
and   l1.id1    = l2.id1
and   l2.id2    = l2.id2;

Finally, use the next few statements to identify the object ID and
blocked row information.
Replace the session id in the where clause with the
waiting session id, session 2.

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session
where sid=&sid;


Replace the SID in the where clause with that of session 2
to obtain the Object name and ROWID

select do.object_name
      ,row_wait_obj#
      ,row_wait_file#
      ,row_wait_block#
      ,row_wait_row#
      ,dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#
                                   ,ROW_WAIT_FILE#
                                   ,ROW_WAIT_BLOCK#
                                   ,ROW_WAIT_ROW# )
from   v$session       s
      ,dba_objects     do
where  sid             = &sid -- this is the SID from session2
and    s.ROW_WAIT_OBJ# = do.OBJECT_ID;



Replace the ROWID from the previous result set to obtain the
actual row data

select *
from test_lock
where rowid='AAAVnHAAQAAAp0tAAA';

Identify most scanned tables

Identify the top 20 most scanned tables.

set linesize 400
set pagesize 3000
col Owner format a15
col Object_Name format a30

select /*+ all_rows */
       *
from
    (select Inst_ID
           ,owner
           ,object_name
           ,value
     from   gv$segment_statistics
     where  statistic_name ='logical  reads'
     and    object_type='TABLE'
     order by 3 desc)
where rownum < 21
/

Generate XML elements from a SQL result set

with t1 as
  (
    select '699' col1 from dual union
    select '996'      from dual
  )
--
select  
    xmlagg (
            xmlelement ("result", xmlforest (col1 "col1"))
           )
from   t1

BULK collect XML into an OBJECT table

create or replace type ty_Rep  as object
(rep_name varchar2(40)
,prg_name varchar2(40)
,app_name varchar2(40));
/
create or replace type tb_Rep as table of ty_Rep;
/


set serveroutput on
declare

l_xml xmltype:= xmltype
('<soap:Envelope
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
  <fetchReportDataResponse xmlns="http://SomeLocation/">
   <fetchReportDataResult>
    <feed xmlns="urn:DA.gtt.BBN.SomeLocation.Services">
     <Table xmlns="">
      <Report>Item</Report>
      <Program>PItem</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>App Chunk</Report>
      <Program>AC</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>Customer</Report>
      <Program>Cust</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Delivery</Report>
      <Program>Delv</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Process</Report>
      <Program>Pr</Program>
      <Application>London</Application>
     </Table>
    </feed>
   </fetchReportDataResult>
  </fetchReportDataResponse>
 </soap:Body>
</soap:Envelope>');


  l_Rep tb_Rep := tb_Rep();

begin

  select ty_Rep(x.Report, x.Program, x.Application)
  bulk collect  into  l_Rep
  from (select l_XML as m from dual) d
       ,xmltable ('//Table' passing d.m
         columns
          Report varchar2(40) path 'Report'
         ,Program varchar2(40) path 'Program'       
         ,Application varchar2(40) path 'Application') as x;

  dbms_output.put_line(l_Rep.count);
 
  for r in 1..l_Rep.count loop
    dbms_output.put_line('Report: ' || l_Rep(r).rep_name);
    dbms_output.put_line('Program: ' || l_Rep(r).prg_name);
    dbms_output.put_line('Application: ' || l_Rep(r).app_name);
  end loop;

end;

/

Extract data from single XML node

set serveroutput on

declare
l_XML xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>
                          <customertran>
                            <tran>856</tran>
                          </customertran>');
l_Tran varchar2(10);
begin
  select x.tran
  into l_Tran
  from   (select l_XML as m from dual) d
        ,xmltable ('//customertran' passing d.m
                   columns tran varchar2(40) path 'tran') as x;
  dbms_output.put_line(l_Tran);
end;

/

Identify hot blocks using AWR

First set the start and end snapshot ID as variables. 

Use this script if you want; http://bluefrog-oracle.blogspot.com/2011/11/set-start-and-end-snapshot-id-for-awr.html

To get a general idea of where the majority of WAITS's occur,
run the following SQL statement to view the counts of wait classes
in descending order;
---------------------------------------------------------

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
where    d.nap_id between :p_Start_Snap_ID and :p_End_Snap_ID
group by d.wait_class_id
        ,d.wait_class
order by 3 desc;


Next, List a breakdown of Events per Wait class identified
in the previous result set;
---------------------------------------------------------
select   d.wait_class_id                as Wait_Class_id
        ,d.wait_class                   as Wait_Class_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID                     = e.Event_ID
group by d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4 desc;


Now attempt to identify which users are responsible for the
waits (broken down per event type).

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         = e.Event_ID
and      d.User_id          = u.User_ID
group by u.Username
        ,d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


You may want to exclude WAITS's for SYS and focus only on the
application specific schemas, in which case, add
the additional predicate "u.Username != 'SYS'"

Also, you would probably want to exclude SQL*Net related WAIT's,
therefore add "e.Name not like 'SQL*Net%'" as a predicate.

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


To drill down on hot blocks, the WAIT class to target would be;
"User I/O".
Therefore add an additional predicate;
      "d.Wait_Class       like  'User I/O'".

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 1, 4 desc;


To drill down on which Objects the hot blocks occur in,
join to the all_Objects dictionary view.

Remove the Event Name from the grouping and select list
since we know longer want to focus on individual reasons
for the general "User I/O" (of which there are several).

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,a.Object_Name                  as Object_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,all_users                      u
        ,all_objects                    a
        ,v$Event_Name                   e
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
and      d.Current_Obj#     =     a.Object_ID
and      a.Object_Type      =     'TABLE'
group by u.Username
        ,d.wait_class
        ,a.Object_Name
order by 4 desc,  2, 3;


And finally, to identify the most read ROWs relative to a Top-N
number passed in as a parameter.

select User_Name
      ,Object_Name
      ,Hot_Row_ID
      ,Cnt
from
  (
  select   u.Username                     as User_Name
          ,a.Object_Name                  as Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#) as Hot_Row_ID
          ,count(*)                       as Cnt
  from     dba_hist_active_sess_history   d
          ,all_users                      u
          ,all_objects                    a
          ,v$Event_Name                   e
  where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
  and      d.Event_ID         =     e.Event_ID
  and      d.User_id          =     u.User_ID
  and      u.Username         !=    'SYS'
  and      e.Name not         like  'SQL*Net%'
  and      d.Wait_Class       like  'User I/O'
  and      d.Current_Obj#     =     a.Object_ID
  and      a.Object_Type      =     'TABLE'
  group by u.Username
          ,a.Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#)
  order by 4 desc,  2
  )
where rownum < &top_n;

Set Start and End Snapshot ID for AWR scripts

variable p_Start_Snap_ID  number
variable p_End_Snap_ID    number

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi'
/
alter session set nls_timestamp_format = 'dd/mm/yyyy hh24:mi'
/

col Begin_interval_time format a20
col End_interval_time   format a20

set linesize 400
set pagesize 4000

select Snap_ID, Instance_Number, begin_interval_time, end_interval_time
from (
      select snap_id
            ,Instance_Number
            ,begin_interval_time
            ,end_interval_time
      from   dba_hist_snapshot
      order by snap_id desc
     )
where rownum < &&Rownum_Limit
/

prompt Get Start and End snapshot ID
begin

  -- One must use an anonymous block when setting a Bind Variable
  select min(Snap_ID)
        ,max (Snap_ID)
  into  :p_Start_Snap_ID
       ,:p_End_Snap_ID
  from
  (select snap_id
         ,Instance_Number
         ,begin_interval_time
         ,end_interval_time
   from dba_hist_snapshot
   order by snap_id desc)
  where rownum < &&Rownum_Limit;

end;
/

Split PL/SQL VARCHAR2 variable into chunks of 4000 each

The procedure below splits a varchar2 variable, declared
in PL/SQL, into several 4000 varchar2 chunks, which can then
be inserted into a table, since the maximum length of a
varchar2 column is 4000 characters whereas the maximum
length of a PL/SQL varchar2 variable is 32767 characters.

First create a test table;
create table xdq (str varchar2(4000), chunk_id number);

Procedure to split the string into 4000 character chunks;
create or replace procedure xdq_Ins (p_Str in varchar2) as

STR_EMPTY   exception;
l_Clob      clob        := to_clob(p_Str);  -- convert to a clob
l_Len       pls_integer := dbms_lob.getlength(l_Clob); --str length
l_Chunk_Cnt pls_integer := ceil(l_Len/4000); -- number of chunks

begin
  if (l_Len < 1) then
    raise STR_EMPTY;
  end if;

  insert into xdq
   (str
   ,Chunk_ID)
   (
    select dbms_lob.substr(l_Clob, 4000, ((level-1) * 4000) + 1)
          ,level
    from   dual
    connect by level <= l_Chunk_Cnt
   );
  
  exception
    when STR_EMPTY then
      raise_application_error('No string to insert', -20088);
end xdq_Ins;
/


Now the test

SQL> begin
  2    xdq_Ins(rpad('x',5001,'x'));
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select length(str) str_len, chunk_id
  2  from xdq;

   STR_LEN   CHUNK_ID
---------- ----------
      4000          1
      1001          2