SQL Capture

 

Query 0: CAPTURE_INFO (environment header)

sql

/* Q0 CAPTURE_INFO - read-only */
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM') AS capture_ts,
       d.name                          AS db_name,
       d.db_unique_name,
       TO_CHAR(d.dbid)                 AS dbid,
       d.cdb,
       SYS_CONTEXT('USERENV','CON_NAME')             AS connected_container,
       SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') AS session_edition,
       d.platform_name, d.database_role, d.open_mode,
       TO_CHAR(i.inst_id)              AS inst_id,
       i.instance_name, i.host_name, i.version_full,
       TO_CHAR(i.startup_time,'YYYY-MM-DD HH24:MI:SS') AS instance_startup,
       (SELECT value FROM nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET')         AS db_charset,
       (SELECT x.value FROM gv$parameter x
         WHERE x.inst_id = i.inst_id AND x.name = 'optimizer_features_enable') AS optimizer_features_enable,
       (SELECT x.value FROM gv$parameter x
         WHERE x.inst_id = i.inst_id AND x.name = 'cursor_sharing')            AS cursor_sharing
FROM   gv$instance i
CROSS JOIN v$database d
ORDER  BY i.inst_id;

Query 1: SQL_INVENTORY (one row per child cursor per RAC instance)

Every identifier is wrapped in TO_CHAR, so it is exported as text. Metric columns stay numeric so you can calculate with them, and each metric column name ends with its unit.

sql

/* Q1 SQL_INVENTORY - read-only */
WITH p AS (SELECT 32000 AS chunk_chars FROM dual)
SELECT /* EBS_MIG_SQL_INVENTORY */
       TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM')  AS capture_ts,
       d.name                                         AS db_name,
       d.db_unique_name,
       TO_CHAR(d.dbid)                                AS dbid,
       NVL(c.name, SYS_CONTEXT('USERENV','CON_NAME')) AS pdb_name,
       TO_CHAR(s.con_id)                              AS con_id,
       TO_CHAR(s.inst_id)                             AS inst_id,
       i.instance_name,
       i.host_name,
       /* identifiers - all TEXT */
       s.sql_id,
       TO_CHAR(s.hash_value)                          AS hash_value,
       TO_CHAR(s.old_hash_value)                      AS old_hash_value,
       TO_CHAR(s.plan_hash_value)                     AS plan_hash_value,
       TO_CHAR(s.full_plan_hash_value)                AS full_plan_hash_value,
       TO_CHAR(s.child_number)                        AS child_number,
       TO_CHAR(s.exact_matching_signature)            AS exact_matching_signature,
       TO_CHAR(s.force_matching_signature)            AS force_matching_signature,
       TO_CHAR(s.optimizer_env_hash_value)            AS optimizer_env_hash_value,
       /* context */
       s.parsing_schema_name, s.module, s.action, s.service,
       s.sql_profile, s.sql_plan_baseline, s.sql_patch,
       s.is_bind_sensitive, s.is_bind_aware, s.is_shareable, s.object_status,
       s.optimizer_mode, s.optimizer_cost,
       s.first_load_time, s.last_load_time,
       TO_CHAR(s.last_active_time,'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
       /* cumulative counters since this child cursor was loaded */
       s.executions             AS executions_cnt,
       s.px_servers_executions  AS px_servers_executions_cnt,
       s.parse_calls            AS parse_calls_cnt,
       s.loads                  AS loads_cnt,
       s.invalidations          AS invalidations_cnt,
       s.fetches                AS fetches_cnt,
       s.rows_processed         AS rows_processed_cnt,
       s.buffer_gets            AS buffer_gets_blocks,
       s.disk_reads             AS disk_reads_blocks,
       s.cpu_time               AS cpu_time_us,
       s.elapsed_time           AS elapsed_time_us,
       s.user_io_wait_time      AS user_io_wait_us,
       s.cluster_wait_time      AS cluster_wait_us,
       s.concurrency_wait_time  AS concurrency_wait_us,
       s.application_wait_time  AS application_wait_us,
       s.sharable_mem           AS sharable_mem_bytes,
       /* derived */
       ROUND(s.elapsed_time / 1e6, 3)                          AS elapsed_time_sec,
       ROUND(s.cpu_time     / 1e6, 3)                          AS cpu_time_sec,
       ROUND(s.elapsed_time / NULLIF(s.executions,0) / 1e3, 3) AS elapsed_ms_per_exec,
       ROUND(s.cpu_time     / NULLIF(s.executions,0) / 1e3, 3) AS cpu_ms_per_exec,
       ROUND(s.buffer_gets  / NULLIF(s.executions,0), 1)       AS buffer_gets_per_exec,
       ROUND(s.disk_reads   / NULLIF(s.executions,0), 1)       AS disk_reads_per_exec,
       ROUND(s.rows_processed / NULLIF(s.executions,0), 1)     AS rows_per_exec,
       /* pointer to SQL_TEXT sheet */
       DBMS_LOB.GETLENGTH(s.sql_fulltext)                      AS sql_text_length_chars,
       GREATEST(1, CEIL(NVL(DBMS_LOB.GETLENGTH(s.sql_fulltext),0) / p.chunk_chars)) AS sql_text_chunks,
       REPLACE(REPLACE(SUBSTR(s.sql_text,1,200),CHR(10),' '),CHR(13),' ')         AS sql_text_preview
FROM   gv$sql s
JOIN   gv$instance i       ON i.inst_id = s.inst_id
LEFT JOIN gv$containers c  ON c.inst_id = s.inst_id AND c.con_id = s.con_id
CROSS JOIN v$database d
CROSS JOIN p
ORDER  BY s.elapsed_time DESC;

Query 2: SQL_TEXT (full text in ordered chunks, no truncation)

sql

/* Q2 SQL_TEXT - read-only; one row per <=32,000-char chunk, one SQL_ID stored once */
WITH p AS (SELECT 32000 AS chunk_chars FROM dual),
     n AS (SELECT LEVEL AS chunk_no FROM dual CONNECT BY LEVEL <= 2048),
     t AS (SELECT con_id, sql_id, sql_fulltext,
                  NVL(DBMS_LOB.GETLENGTH(sql_fulltext),0) AS total_len,
                  CASE WHEN DBMS_LOB.INSTR(sql_fulltext, CHR(13)) > 0
                       THEN 'Y' ELSE 'N' END              AS contains_cr
           FROM (SELECT s.con_id, s.sql_id, s.sql_fulltext,
                        ROW_NUMBER() OVER (PARTITION BY s.con_id, s.sql_id
                                           ORDER BY s.inst_id, s.child_number) AS rn
                 FROM gv$sql s)
           WHERE rn = 1)
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM') AS capture_ts,
       TO_CHAR(t.con_id)                                   AS con_id,
       t.sql_id,
       n.chunk_no,
       GREATEST(1, CEIL(t.total_len / p.chunk_chars))      AS chunk_count,
       t.total_len                                         AS total_length_chars,
       GREATEST(0, LEAST(p.chunk_chars,
                t.total_len - (n.chunk_no-1)*p.chunk_chars)) AS chunk_length_chars,
       t.contains_cr,
       SUBSTR(t.sql_fulltext, (n.chunk_no-1)*p.chunk_chars + 1, p.chunk_chars) AS chunk_text
FROM   t
CROSS JOIN p
JOIN   n ON n.chunk_no <= GREATEST(1, CEIL(t.total_len / p.chunk_chars))
ORDER  BY t.con_id, t.sql_id, n.chunk_no;

The 32,000-character chunk size stays safely under Excel’s hard limit of 32,767 characters per cell. To rebuild a statement, concatenate its chunks in CHUNK_NO order. CHUNK_LENGTH_CHARS lets you prove that nothing was cut off.