Something like this might suffice.
1) parse the clob into rows
2) scan each row and extract the table name
SQL> create global temporary table gtt ( each_line varchar2(200));
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 c clob :=
3 'SQL_ID g4u07qbc9aks8
4 --------------------
5 SELECT master_cells.CELL_ID FROM GSA.PUBLICATION_CELLS pcells INNER
6 JOIN GSA.PUBLICATION_SUBSECTIONS subsections ON
7 pcells.PUBLICATION_SUBSEC_SYSKEY = subsections.PUBLICATION_SUBSEC_SYSKEY
8 INNER JOIN GSA.PUBLICATION_SECTIONS sections ON
9 subsections.PUBLICATION_SECTION_SYSKEY =
10 sections.PUBLICATION_SECTION_SYSKEY INNER JOIN GSA.PUBLICATIONS pubs ON
11 sections.PUBLICATION_SYSKEY = pubs.PUBLICATION_SYSKEY INNER JOIN
12 GSA.PUBLICATION_STR pub_str ON pubs.PUBLICATION_SYSKEY =
13 pub_str.PUBLICATION_SYSKEY INNER JOIN GSA.MASTER_SECTIONS
14 master_sections ON sections.MASTER_SECTION_SYSKEY =
15 master_sections.MASTER_SECTION_SYSKEY INNER JOIN GSA.MASTER_SUBSECTIONS
16 master_subs ON master_sections.MASTER_SECTION_SYSKEY =
17 master_subs.MASTER_SECTION_SYSKEY INNER JOIN GSA.MASTER_CELLS
18 master_cells ON master_subs.MASTER_SUBSEC_SYSKEY =
19 master_cells.MASTER_SUBSEC_SYSKEY AND pcells.MASTER_CELL_SYSKEY =
20 master_cells.MASTER_CELL_SYSKEY WHERE pubs.PUBLICATION_SYSKEY = :1 AND
21 pubs.PUBLICATION_SYSKEY IS NOT NULL AND master_cells.CELL_ID IS NOT
22 NULL AND pub_str.LANG_SYSKEY = 1 ORDER BY master_cells.CELL_ID
23
24 Plan hash value: 2263598373
25
26 ----------------------------------------------------------------------------------------------------------------------
27 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
28 ----------------------------------------------------------------------------------------------------------------------
29 | 0 | SELECT STATEMENT | | | | 1147 (100)| |
30 | 1 | SORT ORDER BY | | 1 | 79 | 1147 (3)| 00:00:14 |
31 | 2 | NESTED LOOPS | | 1 | 79 | 1146 (2)| 00:00:14 |
32 | 3 | NESTED LOOPS | | 7666 | 79 | 1146 (2)| 00:00:14 |
33 | 4 | HASH JOIN | | 7666 | 464K| 376 (1)| 00:00:05 |
34 | 5 | NESTED LOOPS | | 851 | 43401 | 287 (0)| 00:00:04 |
35 | 6 | NESTED LOOPS | | 880 | 43401 | 287 (0)| 00:00:04 |
36 | 7 | NESTED LOOPS | | 44 | 1716 | 28 (0)| 00:00:01 |
37 | 8 | NESTED LOOPS | | 11 | 297 | 7 (0)| 00:00:01 |
38 | 9 | INDEX UNIQUE SCAN | PUBLICATION_STR_PK | 1 | 9 | 1 (0)| 00:00:01 |
39 | 10 | TABLE ACCESS BY INDEX ROWID| PUBLICATION_SECTIONS | 11 | 198 | 6 (0)| 00:00:01 |
40 | 11 | INDEX RANGE SCAN | PUBLICATION_SECTIONS_INDEX1 | 11 | | 2 (0)| 00:00:01 |
41 | 12 | TABLE ACCESS BY INDEX ROWID | PUBLICATION_SUBSECTIONS | 4 | 48 | 2 (0)| 00:00:01 |
42 | 13 | INDEX RANGE SCAN | PUBLICATION_SUBSECTIONS_INDEX1 | 4 | | 0 (0)| |
43 | 14 | INDEX RANGE SCAN | PUBLICATION_CELLS_INDEX1 | 20 | | 0 (0)| |
44 | 15 | TABLE ACCESS BY INDEX ROWID | PUBLICATION_CELLS | 19 | 228 | 6 (0)| 00:00:01 |
45 | 16 | VIEW | index$_join$_012 | 12212 | 131K| 89 (2)| 00:00:02 |
46 | 17 | HASH JOIN | | | | | |
47 | 18 | INDEX FAST FULL SCAN | MASTER_SUBSECTIONS_INDEX1 | 12212 | 131K| 49 (0)| 00:00:01 |
48 | 19 | INDEX FAST FULL SCAN | MASTER_SUBSECTIONS_UK1 | 12212 | 131K| 62 (2)| 00:00:01 |
49 | 20 | BITMAP CONVERSION TO ROWIDS | | | | | |
50 | 21 | BITMAP AND | | | | | |
51 | 22 | BITMAP CONVERSION FROM ROWIDS | | | | | |
52 | 23 | INDEX RANGE SCAN | MASTER_CELL_PK | 1 | | 0 (0)| |
53 | 24 | BITMAP CONVERSION FROM ROWIDS | | | | | |
54 | 25 | INDEX RANGE SCAN | MASTER_CELLS_INDEX1 | 1 | | 0 (0)| |
55 | 26 | TABLE ACCESS BY INDEX ROWID | MASTER_CELLS | 1 | 17 | 1146 (2)| 00:00:14 |
56 -------------------------------------------------------------------------------------------------------------------';
57
58
59 l_big_chunk varchar2(32767);
60 l_leftover varchar2(200);
61 l_offset pls_integer;
62 l_len pls_integer;
63 delim varchar2(1) := chr(10);
64
65 type t_rows is table of varchar2(200) index by pls_integer;
66 l_rows t_rows;
67
68 l_delim pls_integer;
69 begin
70 l_offset := 1;
71
72 loop
73 l_big_chunk := dbms_lob.substr(c,32700,l_offset);
74 l_len := length(l_big_chunk);
75 l_offset := l_offset + l_len;
76 l_big_chunk := l_leftover || l_big_chunk;
77
78 loop
79 l_delim := instr(l_big_chunk,delim);
80 --dbms_output.put_line('l_delim='||l_delim);
81 exit when l_delim = 0 or l_big_chunk is null;
82 l_rows(l_rows.count+1) := substr(l_big_chunk,1,l_delim-1);
83 l_big_chunk := substr(l_big_chunk,l_delim+1);
84 end loop;
85 l_leftover := l_big_chunk;
86
87 exit when l_len < 32700;
88 end loop;
89
90 forall i in 1 .. l_rows.count
91 insert into gtt values ( l_rows(i) ) ;
92
93 end;
94 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select substr(each_line, instr(each_line,'|',1,3) +2 , instr(each_line,'|',1,4) - instr(each_line,'|',1,3) -2 )
2 from gtt
3 where each_line like '%TABLE ACCESS%'
4 /
SUBSTR(EACH_LINE,INSTR(EACH_LINE,'|',1,3)+2,INSTR(EACH_LINE,'|',1,4)-INSTR(EACH_LINE,'|',1,3)-2)
----------------------------------------------------------------------------------------------------------------------------------
PUBLICATION_SECTIONS
PUBLICATION_SUBSECTIONS
PUBLICATION_CELLS
MASTER_CELLS
4 rows selected.
SQL>
SQL>
but its probably worth looking at DBA_HIST_SQL_PLAN as a preliminary activity