-- MigrateMyDatabase (migratemydatabase.eu): structure of one Oracle schema, for the free migration scan.
-- READ-ONLY. One SELECT on the data dictionary (ALL_* views): no table data, no code text, nothing is created.
-- Code (views, procedures, packages, triggers, types) is only read to count lines and to find feature keywords
-- such as MERGE or CONNECT BY; only those keyword names are in the result, never the code.
--
-- Oracle 12c or newer: 12.1, 12.2, 18c, 19c, 21c, 23ai / 26ai, Autonomous Database, Amazon RDS
-- (tested on 18c, 21c and 26ai). Oracle 11g is not supported: the statement fails there.
-- Version-dependent columns (identity 12.1+, collation 12.2+, full version 18c+) are only read when present.
-- View and materialized view text is analysed up to its first 32 KB.
--
-- Which schema: by default the schema you are connected to. To scan another schema, put its name (as stored,
-- usually upper case) between the quotes on the line marked "<<< schema" below. Your user then needs to see
-- that schema's objects (SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE, or grants on its objects).
--
-- How to run
-- SQL*Plus or SQLcl: sqlplus user/password@//host:1521/service @mmdb-oracle.sql
-- The result is written to mmdb-result.txt in the current folder (and shown on screen).
-- SQL Developer: open this file and press F5 (Run Script). The result appears in the Script Output panel
-- and is written to mmdb-result.txt. Copy all of it, or upload the file.
-- Other tools (Toad, DBeaver, DataGrip, APEX SQL Workshop, ...): run only the SELECT statement, from the
-- line "WITH" to the semicolon at the end. Fetch all rows, select all rows of the result, copy, paste.
-- The SET and SPOOL lines are SQL*Plus commands that some tools do not understand: leave them out there.
--
-- Permissions: a normal user can scan its own schema (no DBA rights). Some database-level details (archive log
-- mode, redo log size, container type, sizes of another schema) need SELECT ANY DICTIONARY or
-- SELECT_CATALOG_ROLE; without it those fields stay empty.
--
-- What is collected: database version, edition, character sets; the schema; tables (row counts from optimizer
-- statistics, sizes from segments), columns (types, defaults), indexes, keys, check constraints; for views,
-- materialized views, procedures, functions, packages, triggers and types the number of lines and keywords only.
-- ---------- SQL*Plus / SQLcl / SQL Developer settings (leave these out in other tools) ----------
SET DEFINE OFF
SET SQLBLANKLINES ON
SET PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON TRIMOUT ON FEEDBACK OFF HEADING OFF VERIFY OFF ECHO OFF TAB OFF
SPOOL mmdb-result.txt
-- ---------- the statement ----------
WITH
p AS (
SELECT /*+ MATERIALIZE */ CAST(par.o AS VARCHAR2(128)) AS o, CAST(REPLACE(par.o, '''', '''''') AS VARCHAR2(256)) AS q,
CAST(SYS_CONTEXT('USERENV', 'SESSION_USER') AS VARCHAR2(128)) AS u, CHR(1) AS s
FROM (SELECT NVL(TRIM(''), SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) AS o FROM dual) par -- <<< schema
),
v AS (
SELECT /*+ MATERIALIZE */ TO_NUMBER(REGEXP_SUBSTR(version, '[0-9]+', 1, 1)) * 10 + TO_NUMBER(REGEXP_SUBSTR(version, '[0-9]+', 1, 2)) AS vn,
version AS ver, product
FROM product_component_version WHERE product LIKE 'Oracle%Database%' AND ROWNUM = 1
),
pr AS (
SELECT /*+ MATERIALIZE */ CASE WHEN EXISTS (SELECT 1 FROM session_privs WHERE privilege = 'SELECT ANY DICTIONARY')
OR EXISTS (SELECT 1 FROM session_roles WHERE role IN ('SELECT_CATALOG_ROLE', 'DBA')) THEN 1 ELSE 0 END AS dict,
CASE WHEN EXISTS (SELECT 1 FROM all_objects WHERE owner = 'SYS' AND object_name = 'V_$VERSION') THEN 1 ELSE 0 END AS ver_ok,
CASE WHEN EXISTS (SELECT 1 FROM all_objects WHERE owner = 'SYS' AND object_name = 'V_$DATABASE') THEN 1 ELSE 0 END AS db_ok,
CASE WHEN EXISTS (SELECT 1 FROM all_objects WHERE owner = 'SYS' AND object_name = 'V_$LOG') THEN 1 ELSE 0 END AS log_ok,
CASE WHEN EXISTS (SELECT 1 FROM all_objects WHERE owner = 'SYS' AND object_name = 'DBA_SEGMENTS') THEN 1 ELSE 0 END AS seg_ok,
CASE WHEN EXISTS (SELECT 1 FROM all_objects WHERE owner = 'SYS' AND object_name = 'DBA_CONTEXT') THEN 1 ELSE 0 END AS ctx_ok
FROM dual
),
-- the dynamic queries, built once. The MATERIALIZE hints in this statement work around Oracle 18c optimizer errors
-- (do not add them to CTEs that call DBMS_XMLGEN: 18c then loses values)
qs AS (
SELECT /*+ MATERIALIZE */
CAST('SELECT '
|| CASE WHEN pr.dict = 1 OR pr.ver_ok = 1 THEN '(SELECT banner FROM v$version WHERE banner LIKE ''Oracle%'' AND ROWNUM = 1)' ELSE 'NULL' END || ' BANNER'
|| CASE WHEN v.vn >= 180 THEN ', (SELECT version_full FROM product_component_version WHERE product LIKE ''Oracle%Database%'' AND ROWNUM = 1) VFULL' END
|| CASE WHEN v.vn >= 121 THEN ', SYS_CONTEXT(''USERENV'', ''CON_NAME'') CON' END
|| CASE WHEN v.vn >= 121 THEN ', (SELECT COUNT(*) FROM all_users WHERE oracle_maintained = ''N'') NUSERS' END
|| CASE WHEN v.vn >= 122 THEN ', (SELECT default_collation FROM all_users WHERE username = ''' || p.q || ''') COLL' END
|| CASE WHEN pr.dict = 1 OR pr.db_ok = 1 THEN ', (SELECT log_mode FROM v$database) LOGMODE' END
|| CASE WHEN (pr.dict = 1 OR pr.db_ok = 1) AND v.vn >= 121 THEN ', (SELECT cdb FROM v$database) CDB' END
|| CASE WHEN pr.dict = 1 OR pr.log_ok = 1 THEN ', (SELECT TO_CHAR(SUM(bytes) / 1048576, ''FM99999999990.0'') FROM v$log) LOGMB' END
|| ' FROM dual' AS VARCHAR2(4000)) AS q_dbx,
CAST(CASE WHEN v.vn >= 121
THEN 'SELECT username N FROM all_users WHERE oracle_maintained = ''Y''' ELSE 'SELECT ''SYS'' N FROM dual' END AS VARCHAR2(4000)) AS q_ou,
CAST(
CASE WHEN p.o <> p.u AND (pr.dict = 1 OR pr.seg_ok = 1)
THEN 'SELECT segment_name N, segment_type T, bytes B FROM dba_segments WHERE owner = ''' || p.q || ''''
ELSE 'SELECT 1 N FROM dual WHERE 1 = 0' END AS VARCHAR2(4000)) AS q_seg,
CAST(CASE WHEN v.vn >= 121 THEN 'SELECT table_name T, column_name C'
|| ', identity_column I' || CASE WHEN v.vn >= 122 THEN ', collation CO' END
|| ' FROM all_tab_cols WHERE owner = ''' || p.q || ''' AND hidden_column = ''NO'' AND (identity_column = ''YES'''
|| CASE WHEN v.vn >= 122 THEN ' OR collation <> ''USING_NLS_COMP''' END || ')'
ELSE 'SELECT 1 T FROM dual WHERE 1 = 0' END AS VARCHAR2(4000)) AS q_cx,
CAST(CASE WHEN v.vn >= 121 THEN 'SELECT table_name T, constraint_name N,'
|| ' SUBSTR(search_condition_vc, 1, 400) D, status ST, validated VA FROM all_constraints WHERE owner = ''' || p.q || ''''
|| ' AND constraint_type = ''C'' AND NOT REGEXP_LIKE(search_condition_vc, ''^\s*"[^"]+" IS NOT NULL\s*$'')'
ELSE 'SELECT 1 T FROM dual WHERE 1 = 0' END AS VARCHAR2(4000)) AS q_ck,
CAST(
CASE WHEN pr.dict = 1 OR pr.ctx_ok = 1 THEN 'SELECT COUNT(*) C FROM dba_context WHERE schema = ''' || p.q || ''''
ELSE 'SELECT 1 C FROM dual WHERE 1 = 0' END AS VARCHAR2(4000)) AS q_ctx,
CAST(
CASE WHEN p.o = p.u THEN 'SELECT COUNT(*) C FROM user_flashback_archive_tables'
WHEN pr.dict = 1 THEN 'SELECT COUNT(*) C FROM dba_flashback_archive_tables WHERE owner_name = ''' || p.q || ''''
ELSE 'SELECT 1 C FROM dual WHERE 1 = 0' END AS VARCHAR2(4000)) AS q_fba
FROM p, v, pr
),
-- database-level values that depend on version or privileges, read through DBMS_XMLGEN (dynamic SQL in a query)
dbx AS (
SELECT x.*
FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_dbx)
COLUMNS banner VARCHAR2(512) PATH 'BANNER', vfull VARCHAR2(64) PATH 'VFULL', con VARCHAR2(128) PATH 'CON',
nusers VARCHAR2(16) PATH 'NUSERS', coll VARCHAR2(128) PATH 'COLL', logmode VARCHAR2(32) PATH 'LOGMODE',
cdb VARCHAR2(8) PATH 'CDB', logmb VARCHAR2(32) PATH 'LOGMB') x
),
-- Oracle-maintained users (not migrated, not cross-schema references)
ou AS (
SELECT x.n FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_ou)
COLUMNS n VARCHAR2(128) PATH 'N') x
UNION
SELECT column_value FROM TABLE(sys.odcivarchar2list('SYS', 'SYSTEM', 'PUBLIC', 'XDB', 'MDSYS', 'CTXSYS', 'ORDSYS',
'ORDPLUGINS', 'ORDDATA', 'OLAPSYS', 'WMSYS', 'LBACSYS', 'DVSYS', 'EXFSYS', 'OUTLN', 'DBSNMP', 'APPQOSSYS',
'ANONYMOUS', 'SI_INFORMTN_SCHEMA', 'MDDATA', 'ORACLE_OCM', 'XS$NULL', 'FLOWS_FILES', 'AUDSYS', 'GSMADMIN_INTERNAL',
'OJVMSYS', 'DBSFWUSER', 'REMOTE_SCHEDULER_AGENT', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'DIP', 'RDSADMIN'))
),
-- segments of the schema: USER_SEGMENTS for the own schema, DBA_SEGMENTS (if readable) for another one
seg AS (
SELECT s.segment_name, s.segment_type, s.bytes FROM user_segments s, p WHERE p.o = p.u
UNION ALL
SELECT x.n, x.t, TO_NUMBER(x.b) FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_seg)
COLUMNS n VARCHAR2(128) PATH 'N', t VARCHAR2(32) PATH 'T', b VARCHAR2(32) PATH 'B') x
),
segok AS (SELECT CASE WHEN p.o = p.u OR pr.dict = 1 OR pr.seg_ok = 1 THEN 1 ELSE 0 END AS ok FROM p, pr),
tb AS (
SELECT /*+ MATERIALIZE */ t.table_name, t.num_rows, t.partitioned, t.iot_type, t.temporary, t.nested,
CASE WHEN EXISTS (SELECT 1 FROM all_external_tables e WHERE e.owner = t.owner AND e.table_name = t.table_name) THEN 1 ELSE 0 END AS ext
FROM all_tables t, p
WHERE t.owner = p.o AND t.dropped = 'NO' AND t.secondary = 'N'
AND NVL(t.iot_type, 'X') NOT IN ('IOT_OVERFLOW', 'IOT_MAPPING')
AND t.table_name NOT LIKE 'MLOG$\_%' ESCAPE '\' AND t.table_name NOT LIKE 'RUPD$\_%' ESCAPE '\'
AND NOT EXISTS (SELECT 1 FROM all_mviews m WHERE m.owner = t.owner AND m.container_name = t.table_name)
),
objmap AS (
SELECT table_name AS seg, table_name AS t FROM tb
UNION ALL SELECT i.index_name, i.table_name FROM all_indexes i, p WHERE i.owner = p.o AND i.table_owner = p.o
UNION ALL SELECT l.segment_name, l.table_name FROM all_lobs l, p WHERE l.owner = p.o
),
tsize AS (SELECT m.t, SUM(s.bytes) AS bytes FROM seg s JOIN objmap m ON m.seg = s.segment_name GROUP BY m.t),
-- LONG columns (DATA_DEFAULT, SEARCH_CONDITION, view text ...) are read one value at a time with DBMS_XMLGEN.GETXML,
-- which returns the LONG as text inside ... (not XML-escaped, so it is cut out of the text, not parsed)
-- identity (12.1+) and collation (12.2+) columns, read through DBMS_XMLGEN so that older versions do not fail
cx AS (
SELECT x.t, x.c, x.i, x.co
FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_cx)
COLUMNS t VARCHAR2(128) PATH 'T', c VARCHAR2(128) PATH 'C', i VARCHAR2(3) PATH 'I', co VARCHAR2(128) PATH 'CO') x
),
dfx AS (
SELECT c.table_name AS t, c.column_name AS c, DBMS_XMLGEN.GETXML('SELECT data_default L FROM all_tab_cols WHERE owner = '''
|| p.q || ''' AND table_name = ''' || REPLACE(c.table_name, '''', '''''') || ''' AND column_name = '''
|| REPLACE(c.column_name, '''', '''''') || '''') AS x
FROM all_tab_cols c, p
WHERE c.owner = p.o AND c.hidden_column = 'NO' AND c.default_length > 0 AND c.virtual_column = 'NO'
AND c.table_name IN (SELECT table_name FROM tb)
),
df AS (SELECT t, c, DBMS_LOB.SUBSTR(CASE WHEN DBMS_LOB.INSTR(x, '') > 0 THEN SUBSTR(x, DBMS_LOB.INSTR(x, '') + 3,
REGEXP_INSTR(x, '\s*\s*\s*$') - DBMS_LOB.INSTR(x, '') - 3) END, 400, 1) AS d FROM dfx),
cols AS (
SELECT /*+ MATERIALIZE */ c.table_name, c.column_name, c.column_id, c.data_type, c.data_type_owner, c.data_length, c.char_length, c.char_used,
c.data_precision, c.data_scale, c.nullable, c.virtual_column
FROM all_tab_cols c, p WHERE c.owner = p.o AND c.hidden_column = 'NO'
AND c.table_name IN (SELECT table_name FROM tb)
),
-- index expressions (LONG): only to tell a DESC column from a real expression, the expression is not output
iex AS (
SELECT ic.index_owner AS io, ic.index_name AS n, ic.column_position AS cp,
DBMS_XMLGEN.GETXML('SELECT column_expression L FROM all_ind_expressions WHERE index_owner = '''
|| REPLACE(ic.index_owner, '''', '''''') || ''' AND index_name = ''' || REPLACE(ic.index_name, '''', '''''')
|| ''' AND column_position = ' || ic.column_position) AS x
FROM all_ind_columns ic, p
WHERE ic.table_owner = p.o AND ic.column_name LIKE 'SYS\_NC%$' ESCAPE '\'
),
ie AS (SELECT io, n, cp, DBMS_LOB.SUBSTR(CASE WHEN DBMS_LOB.INSTR(x, '') > 0 THEN SUBSTR(x, DBMS_LOB.INSTR(x, '') + 3,
REGEXP_INSTR(x, '\s*\s*\s*$') - DBMS_LOB.INSTR(x, '') - 3) END, 200, 1) AS e FROM iex),
ix AS (
SELECT /*+ MATERIALIZE */ i.owner, i.index_name, i.table_name, i.index_type, i.uniqueness, i.partitioned, i.ityp_owner, i.ityp_name
FROM all_indexes i, p WHERE i.table_owner = p.o AND i.index_type <> 'LOB' AND i.table_name IN (SELECT table_name FROM tb)
),
ixc AS (
SELECT ic.index_owner, ic.index_name,
LISTAGG(CASE WHEN ie.e IS NULL THEN ic.column_name
WHEN REGEXP_LIKE(ie.e, '^"[^"]+"$') THEN SUBSTR(ie.e, 2, LENGTH(ie.e) - 2)
ELSE '(expression)' END
|| CASE WHEN ic.descend = 'DESC' THEN ' DESC' END, ',') WITHIN GROUP (ORDER BY ic.column_position) AS cols,
MAX(CASE WHEN ie.e IS NOT NULL AND NOT REGEXP_LIKE(ie.e, '^"[^"]+"$') THEN 1 ELSE 0 END) AS expr
FROM all_ind_columns ic, p, ie
WHERE ic.table_owner = p.o AND ie.io (+) = ic.index_owner AND ie.n (+) = ic.index_name AND ie.cp (+) = ic.column_position
GROUP BY ic.index_owner, ic.index_name
),
con AS (
SELECT /*+ MATERIALIZE */ c.owner, c.constraint_name, c.constraint_type, c.table_name, c.index_owner, c.index_name, c.r_owner, c.r_constraint_name,
c.delete_rule, c.status, c.validated, c.deferrable
FROM all_constraints c, p WHERE c.owner = p.o AND c.constraint_type IN ('P', 'U', 'R')
),
ccol AS (
SELECT /*+ MATERIALIZE */ cc.owner, cc.constraint_name, LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS cols
FROM all_cons_columns cc
WHERE (cc.owner, cc.constraint_name) IN (SELECT owner, constraint_name FROM con WHERE constraint_type = 'R'
UNION SELECT r_owner, r_constraint_name FROM con WHERE constraint_type = 'R')
GROUP BY cc.owner, cc.constraint_name
),
-- check constraints: SEARCH_CONDITION_VC on 12.1+, the LONG SEARCH_CONDITION one by one on 11.2. NOT NULL ones are left out.
ck AS (
SELECT x.t, x.n, x.d, x.st, x.va
FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_ck)
COLUMNS t VARCHAR2(128) PATH 'T', n VARCHAR2(128) PATH 'N', d VARCHAR2(1600) PATH 'D', st VARCHAR2(8) PATH 'ST', va VARCHAR2(16) PATH 'VA') x
UNION ALL
SELECT t, n, DBMS_LOB.SUBSTR(CASE WHEN DBMS_LOB.INSTR(x, '') > 0 THEN SUBSTR(x, DBMS_LOB.INSTR(x, '') + 3,
REGEXP_INSTR(x, '\s*\s*\s*$') - DBMS_LOB.INSTR(x, '') - 3) END, 400, 1), st, va
FROM (SELECT /*+ NO_MERGE */ c.table_name AS t, c.constraint_name AS n, c.status AS st, c.validated AS va,
DBMS_XMLGEN.GETXML('SELECT search_condition L FROM all_constraints WHERE owner = ''' || p.q
|| ''' AND constraint_name = ''' || REPLACE(c.constraint_name, '''', '''''') || '''') AS x
FROM all_constraints c, p, v WHERE v.vn < 121 AND c.owner = p.o AND c.constraint_type = 'C')
),
-- code objects
ob AS (
SELECT /*+ MATERIALIZE */ CASE o.object_type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE o.object_type END AS fam,
o.object_name AS name,
MAX(CASE WHEN o.status <> 'VALID' AND o.object_type <> 'MATERIALIZED VIEW' THEN 1 ELSE 0 END) AS inv
FROM all_objects o, p
WHERE o.owner = p.o AND o.object_name NOT LIKE 'BIN$%'
AND o.object_type IN ('VIEW', 'MATERIALIZED VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
GROUP BY CASE o.object_type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE o.object_type END, o.object_name
HAVING CASE o.object_type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE o.object_type END <> 'TYPE'
OR MAX(CASE WHEN o.object_type = 'TYPE BODY' THEN 1 ELSE 0 END) = 1
),
-- view and materialized view text (LONG, the first 32 KB) one by one, analysed in chunks of 1000 characters
vtx AS (
SELECT 'VIEW' AS fam, w.view_name AS n, DBMS_XMLGEN.GETXML('SELECT text L FROM all_views WHERE owner = '''
|| p.q || ''' AND view_name = ''' || REPLACE(w.view_name, '''', '''''') || '''') AS x
FROM all_views w, p WHERE w.owner = p.o
UNION ALL
SELECT 'MATERIALIZED VIEW', m.mview_name, DBMS_XMLGEN.GETXML('SELECT query L FROM all_mviews WHERE owner = '''
|| p.q || ''' AND mview_name = ''' || REPLACE(m.mview_name, '''', '''''') || '''')
FROM all_mviews m, p WHERE m.owner = p.o
),
vt AS (SELECT fam, n, CASE WHEN DBMS_LOB.INSTR(x, '') > 0 THEN SUBSTR(x, DBMS_LOB.INSTR(x, '') + 3,
REGEXP_INSTR(x, '\s*\s*\s*$') - DBMS_LOB.INSTR(x, '') - 3) END AS t FROM vtx),
gen AS (SELECT LEVEL AS k FROM dual CONNECT BY LEVEL <= (SELECT NVL(MAX(CEIL(DBMS_LOB.GETLENGTH(t) / 1000)), 1) FROM vt)),
sl AS (
SELECT CASE s.type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE s.type END AS fam,
s.name, s.line, UPPER(s.text) AS u
FROM all_source s, p
WHERE s.owner = p.o AND s.type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
UNION ALL
SELECT vt.fam, vt.n, gen.k, UPPER(DBMS_LOB.SUBSTR(vt.t, 1000, (gen.k - 1) * 1000 + 1))
FROM vt JOIN gen ON gen.k <= CEIL(DBMS_LOB.GETLENGTH(vt.t) / 1000)
),
vl AS (SELECT fam, n, LENGTH(t) - LENGTH(REPLACE(t, CHR(10))) + 1 AS lines FROM vt),
fl AS (
SELECT fam, name, COUNT(*) AS lines,
MAX(CASE WHEN line = 1 AND fam NOT IN ('VIEW', 'MATERIALIZED VIEW')
AND REGEXP_LIKE(u, '^\s*(PACKAGE|PACKAGE BODY|TYPE|TYPE BODY|PROCEDURE|FUNCTION|TRIGGER)\s+\S+\s+WRAPPED') THEN 1 ELSE 0 END) AS f_wrapped,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])CURSOR\s+[A-Z"]') AND u NOT LIKE '%REF CURSOR%' THEN 1 ELSE 0 END) AS f_cursor,
MAX(CASE WHEN u LIKE '%BULK COLLECT%' OR REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])FORALL\s') THEN 1 ELSE 0 END) AS f_bulk,
MAX(CASE WHEN u LIKE '%EXECUTE IMMEDIATE%' OR u LIKE '%DBMS\_SQL.%' ESCAPE '\' THEN 1 ELSE 0 END) AS f_dynsql,
MAX(CASE WHEN u LIKE '%AUTONOMOUS\_TRANSACTION%' ESCAPE '\' THEN 1 ELSE 0 END) AS f_autonomous,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|\s)EXCEPTION(\s|$)') THEN 1 ELSE 0 END) AS f_exception,
MAX(CASE WHEN u LIKE '%RAISE\_APPLICATION\_ERROR%' ESCAPE '\' THEN 1 ELSE 0 END) AS f_raise,
MAX(CASE WHEN REGEXP_LIKE(u, 'CONNECT\s+BY(\s|$)') THEN 1 ELSE 0 END) AS f_connectby,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])ROWNUM([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_rownum,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#.])DECODE\s*\(') THEN 1 ELSE 0 END) AS f_decode,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#.])NVL2?\s*\(') THEN 1 ELSE 0 END) AS f_nvl,
MAX(CASE WHEN REGEXP_LIKE(u, '\(\s*\+\s*\)') THEN 1 ELSE 0 END) AS f_outerplus,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])MERGE\s+(/\*.*\*/\s*)?INTO(\s|$)') THEN 1 ELSE 0 END) AS f_merge,
MAX(CASE WHEN REGEXP_LIKE(u, '^[^'']*[A-Z0-9_$#"]@"?[A-Z]') THEN 1 ELSE 0 END) AS f_dblink,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])(SYSDATE|SYSTIMESTAMP)([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_sysdate,
MAX(CASE WHEN REGEXP_LIKE(u, '\.(NEXTVAL|CURRVAL)([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_sequence,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])PIPELINED([^A-Z0-9_$#]|$)|PIPE\s+ROW') THEN 1 ELSE 0 END) AS f_pipelined,
MAX(CASE WHEN REGEXP_LIKE(u, '(AS|IS)\s+OBJECT(\s|\(|$)|(^|\s)UNDER\s|MEMBER\s+(FUNCTION|PROCEDURE)|CONSTRUCTOR\s+FUNCTION') THEN 1 ELSE 0 END) AS f_objtypes,
MAX(CASE WHEN REGEXP_LIKE(u, '(AS|IS)\s+(TABLE\s+OF|VARRAY|VARYING\s+ARRAY)(\s|\(|$)') THEN 1 ELSE 0 END) AS f_collections,
MAX(CASE WHEN u LIKE '%REF CURSOR%' OR u LIKE '%SYS\_REFCURSOR%' ESCAPE '\'
OR REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])OPEN\s+\S+\s+FOR(\s|$)') THEN 1 ELSE 0 END) AS f_refcursor,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])(XMLTYPE|XMLELEMENT|XMLAGG|XMLFOREST|XMLTABLE|XMLQUERY|XMLSERIALIZE|XMLPARSE|XMLSEQUENCE|EXTRACTVALUE|DBMS_XML)')
THEN 1 ELSE 0 END) AS f_xml,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])(JSON_[A-Z_]+|APEX_JSON)|IS\s+(NOT\s+)?JSON([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_json,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])((ALL|DBA|USER|CDB)_(TABLES|TAB_COLUMNS|TAB_COLS|OBJECTS|SOURCE|VIEWS|INDEXES|IND_COLUMNS|'
|| 'CONSTRAINTS|CONS_COLUMNS|USERS|SEQUENCES|SYNONYMS|PROCEDURES|ARGUMENTS|DEPENDENCIES|TRIGGERS|JOBS|SCHEDULER_JOBS|SEGMENTS|'
|| 'TAB_PRIVS|ROLE_PRIVS|SYS_PRIVS|ERRORS|TYPES|LOBS|TAB_PARTITIONS|PART_TABLES|MVIEWS|DB_LINKS|DIRECTORIES|TAB_COMMENTS|'
|| 'COL_COMMENTS|DATA_FILES|TABLESPACES)|G?V\$[A-Z_]+)([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_catalog,
MAX(CASE WHEN u LIKE '%/*+%' OR u LIKE '%--+%' THEN 1 ELSE 0 END) AS f_hints,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])(COMMIT|ROLLBACK|SAVEPOINT)([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_transaction,
MAX(CASE WHEN REGEXP_LIKE(u, '(^|[^A-Z0-9_$#])U?ROWID([^A-Z0-9_$#]|$)') THEN 1 ELSE 0 END) AS f_rowid
FROM sl GROUP BY fam, name
),
-- DBMS_ and UTL_ packages used (first two per line)
pk AS (
SELECT fam, name, LISTAGG(pkg, ',') WITHIN GROUP (ORDER BY pkg) AS pkgs
FROM (SELECT DISTINCT sl.fam, sl.name, REGEXP_SUBSTR(sl.u, '(^|[^A-Z0-9_$#])((DBMS|UTL)_[A-Z0-9_]+)', 1, n.k, NULL, 2) AS pkg
FROM sl, (SELECT 1 AS k FROM dual UNION ALL SELECT 2 FROM dual) n
WHERE sl.u LIKE '%DBMS\_%' ESCAPE '\' OR sl.u LIKE '%UTL\_%' ESCAPE '\')
WHERE pkg IS NOT NULL GROUP BY fam, name
),
-- dependencies: user-defined object / collection types used, database links used
dp AS (
SELECT /*+ MATERIALIZE */ CASE d.type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE d.type END AS fam, d.name,
MAX(CASE WHEN t.typecode = 'OBJECT' THEN 1 ELSE 0 END) AS obj,
MAX(CASE WHEN t.typecode = 'COLLECTION' THEN 1 ELSE 0 END) AS coll,
MAX(CASE WHEN d.referenced_link_name IS NOT NULL THEN 1 ELSE 0 END) AS dblink
FROM all_dependencies d, p, all_types t
WHERE d.owner = p.o AND t.owner (+) = d.referenced_owner AND t.type_name (+) = d.referenced_name
AND (d.referenced_link_name IS NOT NULL OR (d.referenced_type = 'TYPE' AND d.referenced_owner <> 'SYS'))
GROUP BY CASE d.type WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE d.type END, d.name
),
fn AS (
SELECT /*+ MATERIALIZE */ pc.object_name, MAX(pc.pipelined) AS pipelined, MAX(pc.aggregate) AS aggregate
FROM all_procedures pc, p WHERE pc.owner = p.o AND pc.object_type = 'FUNCTION' GROUP BY pc.object_name
),
tr AS (SELECT /*+ MATERIALIZE */ t.trigger_name, t.table_name, t.base_object_type, t.trigger_type FROM all_triggers t, p WHERE t.owner = p.o),
-- simple counts for the feature lines
cnt AS (
SELECT 'sequence' AS k, COUNT(*) AS c, NULL AS e FROM all_sequences x, p WHERE x.sequence_owner = p.o
AND x.sequence_name NOT LIKE 'ISEQ$$\_%' ESCAPE '\'
UNION ALL SELECT 'synonym', COUNT(*), TO_CHAR(SUM(CASE WHEN x.db_link IS NOT NULL OR x.table_owner <> p.o THEN 1 ELSE 0 END))
FROM all_synonyms x, p WHERE x.owner = p.o
UNION ALL SELECT 'public_synonym', COUNT(*), NULL FROM all_synonyms x, p WHERE x.owner = 'PUBLIC' AND x.table_owner = p.o
UNION ALL SELECT 'db_link', NVL(SUM(CASE WHEN x.owner = p.o THEN 1 END), 0), TO_CHAR(NVL(SUM(CASE WHEN x.owner = 'PUBLIC' THEN 1 END), 0))
FROM all_db_links x, p WHERE x.owner IN (p.o, 'PUBLIC')
UNION ALL SELECT 'materialized_view', COUNT(*), NULL FROM all_mviews x, p WHERE x.owner = p.o
UNION ALL SELECT 'materialized_view_log', COUNT(*), NULL FROM all_mview_logs x, p WHERE x.log_owner = p.o
UNION ALL SELECT 'object_type', COUNT(*), NULL FROM all_types x, p WHERE x.owner = p.o AND x.typecode = 'OBJECT'
AND x.type_name NOT LIKE 'SYS\_PLSQL\_%' ESCAPE '\' AND x.type_name NOT LIKE 'SYSTP%'
UNION ALL SELECT 'collection_type', COUNT(*), NULL FROM all_types x, p WHERE x.owner = p.o AND x.typecode = 'COLLECTION'
AND x.type_name NOT LIKE 'SYS\_PLSQL\_%' ESCAPE '\' AND x.type_name NOT LIKE 'SYSTP%'
UNION ALL SELECT 'nested_table', COUNT(*), NULL FROM all_nested_tables x, p WHERE x.owner = p.o
UNION ALL SELECT 'object_table', COUNT(*), NULL FROM all_object_tables x, p WHERE x.owner = p.o AND x.dropped = 'NO'
UNION ALL SELECT 'partitioned_table', COUNT(*), NULL FROM tb WHERE partitioned = 'YES'
UNION ALL SELECT 'iot', COUNT(*), NULL FROM tb WHERE iot_type = 'IOT'
UNION ALL SELECT 'external_table', COUNT(*), NULL FROM tb WHERE ext = 1
UNION ALL SELECT 'global_temporary_table', COUNT(*), NULL FROM tb WHERE temporary = 'Y'
UNION ALL SELECT 'lob_column', COUNT(*), NULL FROM cols WHERE data_type IN ('CLOB', 'NCLOB', 'BLOB', 'BFILE')
UNION ALL SELECT 'long_column', COUNT(*), NULL FROM cols WHERE data_type IN ('LONG', 'LONG RAW')
UNION ALL SELECT 'xmltype_column', COUNT(*), NULL FROM cols WHERE data_type = 'XMLTYPE'
UNION ALL SELECT 'spatial_column', COUNT(*), NULL FROM cols WHERE data_type = 'SDO_GEOMETRY'
UNION ALL SELECT 'bitmap_index', COUNT(*), NULL FROM ix WHERE index_type LIKE 'BITMAP%' OR index_type LIKE '%BITMAP'
UNION ALL SELECT 'function_based_index', COUNT(*), NULL FROM ixc WHERE expr = 1
UNION ALL SELECT 'domain_index', COUNT(*), NULL FROM ix WHERE index_type = 'DOMAIN'
UNION ALL SELECT 'vpd_policy', COUNT(*), NULL FROM all_policies x, p WHERE x.object_owner = p.o
UNION ALL SELECT 'scheduler_job', COUNT(*), NULL FROM all_scheduler_jobs x, p WHERE x.owner = p.o
UNION ALL SELECT 'dbms_job', COUNT(*), NULL FROM all_jobs x, p WHERE x.schema_user = p.o
UNION ALL SELECT 'queue', COUNT(*), NULL FROM all_queues x, p WHERE x.owner = p.o AND x.queue_type = 'NORMAL_QUEUE'
UNION ALL SELECT 'java_class', COUNT(*), NULL FROM all_objects x, p WHERE x.owner = p.o AND x.object_type = 'JAVA CLASS'
UNION ALL SELECT 'directory', COUNT(*), NULL FROM all_directories
UNION ALL SELECT 'editioning', COUNT(*), NULL FROM all_views x, p WHERE x.owner = p.o AND x.editioning_view = 'Y'
UNION ALL SELECT 'schema_trigger', COUNT(*), NULL FROM tr WHERE base_object_type IN ('SCHEMA', 'DATABASE')
UNION ALL SELECT 'invalid_object', COUNT(*), NULL FROM all_objects x, p WHERE x.owner = p.o AND x.status <> 'VALID'
AND x.object_type <> 'MATERIALIZED VIEW' AND x.object_name NOT LIKE 'BIN$%'
UNION ALL SELECT 'context', NVL(MAX(TO_NUMBER(x.c)), -1), NULL FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_ctx)
COLUMNS c VARCHAR2(16) PATH 'C') x
UNION ALL SELECT 'flashback_archive', NVL(MAX(TO_NUMBER(x.c)), -1), NULL FROM qs, XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE(qs.q_fba)
COLUMNS c VARCHAR2(16) PATH 'C') x
),
-- ---------- the lines: kind order, sort keys, fields separated by CHR(1) ----------
body AS (
SELECT 0 AS ko, ' ' AS s1, 0 AS s2,
'meta' || p.s || 'oracle' || p.s || '1' || p.s || TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYY-MM-DD"T"HH24:MI:SS') AS txt
FROM p
UNION ALL
SELECT 1, ' ', 0,
'db' || p.s || SYS_CONTEXT('USERENV', 'DB_NAME') || p.s || SUBSTR(NVL(dbx.banner, v.product), 1, 400) || p.s || NVL(dbx.vfull, v.ver)
|| p.s || v.ver || p.s
|| CASE WHEN NVL(dbx.banner, v.product) LIKE '%Enterprise Edition%' THEN 'EE'
WHEN NVL(dbx.banner, v.product) LIKE '%Standard Edition 2%' THEN 'SE2'
WHEN NVL(dbx.banner, v.product) LIKE '%Standard Edition One%' THEN 'SE1'
WHEN NVL(dbx.banner, v.product) LIKE '%Standard Edition%' THEN 'SE'
WHEN NVL(dbx.banner, v.product) LIKE '%Express Edition%' THEN 'XE'
WHEN NVL(dbx.banner, v.product) LIKE '%Personal%' THEN 'PE'
WHEN NVL(dbx.banner, v.product) LIKE '% Free%' THEN 'Free' END
|| p.s || '' || p.s || '' || p.s || dbx.coll
|| p.s || (SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET')
|| p.s || CASE WHEN segok.ok = 1 THEN TO_CHAR(NVL((SELECT SUM(bytes) FROM seg), 0) / 1048576, 'FM99999999990.0') END
|| p.s || dbx.logmb || p.s || dbx.logmode
|| p.s || CASE WHEN p.o = p.u THEN
(SELECT CASE WHEN COUNT(*) > 0 THEN '1' ELSE '0' END FROM user_tablespaces t WHERE t.encrypted = 'YES'
AND t.tablespace_name IN (SELECT default_tablespace FROM user_users UNION SELECT tablespace_name FROM user_segments)) END
|| p.s || dbx.nusers
|| p.s || (SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET')
|| p.s || dbx.con || p.s || CASE dbx.cdb WHEN 'YES' THEN '1' WHEN 'NO' THEN '0' END
FROM p, v, dbx, segok
UNION ALL
SELECT 2, ' ', 0, 'schema' || p.s || p.o || p.s
|| TO_CHAR((SELECT COUNT(*) FROM all_objects o WHERE o.owner = p.o AND o.object_name NOT LIKE 'BIN$%'))
FROM p
UNION ALL
SELECT 3, t.table_name, 0,
'table' || p.s || p.o || p.s || t.table_name || p.s || TO_CHAR(t.num_rows)
|| p.s || CASE WHEN segok.ok = 1 THEN TO_CHAR(NVL(ts.bytes, 0) / 1048576, 'FM99999999990.0') END
|| p.s || CASE WHEN EXISTS (SELECT 1 FROM con c WHERE c.table_name = t.table_name AND c.constraint_type = 'P') THEN '1' ELSE '0' END
|| p.s || p.s || p.s
|| p.s || CASE WHEN t.partitioned = 'YES' THEN '1' ELSE '0' END
|| p.s || CASE WHEN t.iot_type = 'IOT' THEN 'IOT' WHEN t.ext = 1 THEN 'external' WHEN t.temporary = 'Y' THEN 'temporary'
WHEN t.nested = 'YES' THEN 'nested' END
FROM tb t CROSS JOIN p CROSS JOIN segok LEFT JOIN tsize ts ON ts.t = t.table_name
UNION ALL
SELECT 4, c.table_name, c.column_id,
'col' || p.s || p.o || p.s || c.table_name || p.s || c.column_name || p.s || TO_CHAR(c.column_id)
|| p.s || LOWER(c.data_type)
|| p.s || CASE WHEN c.data_type_owner IS NOT NULL THEN
(SELECT LOWER(MAX(ty.typecode)) FROM all_types ty WHERE ty.owner = c.data_type_owner AND ty.type_name = c.data_type) END
|| p.s || TO_CHAR(CASE WHEN c.data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN c.char_length ELSE c.data_length END)
|| p.s || TO_CHAR(c.data_precision) || p.s || TO_CHAR(c.data_scale)
|| p.s || CASE WHEN c.nullable = 'Y' THEN '1' ELSE '0' END
|| p.s || CASE WHEN cx.i = 'YES' THEN '1' ELSE '0' END
|| p.s || CASE WHEN c.virtual_column = 'YES' AND c.data_type_owner IS NULL THEN '1' ELSE '0' END
|| p.s || CASE WHEN cx.i = 'YES' THEN NULL
ELSE SUBSTR(RTRIM(LTRIM(df.d, ' ' || CHR(9) || CHR(10) || CHR(13)), ' ' || CHR(9) || CHR(10) || CHR(13)), 1, 200) END
|| p.s || CASE WHEN cx.co <> 'USING_NLS_COMP' THEN cx.co END
|| p.s || CASE WHEN c.data_type IN ('CLOB', 'NCLOB', 'BLOB', 'BFILE', 'LONG', 'LONG RAW') THEN '0000001' ELSE '0000000' END
|| p.s || 'U'
|| p.s || CASE WHEN c.data_type IN ('CHAR', 'VARCHAR2') THEN c.char_used END
FROM cols c CROSS JOIN p LEFT JOIN cx ON cx.t = c.table_name AND cx.c = c.column_name
LEFT JOIN df ON df.t = c.table_name AND df.c = c.column_name
UNION ALL
SELECT 5, i.table_name || '.' || i.index_name, 0,
'index' || p.s || p.o || p.s || i.table_name || p.s || i.index_name || p.s || i.index_type
|| p.s || CASE WHEN i.uniqueness = 'UNIQUE' THEN '1' ELSE '0' END
|| p.s || CASE WHEN EXISTS (SELECT 1 FROM con c WHERE c.constraint_type = 'P' AND c.index_name = i.index_name AND c.index_owner = i.owner) THEN '1' ELSE '0' END
|| p.s || CASE WHEN EXISTS (SELECT 1 FROM con c WHERE c.constraint_type = 'U' AND c.index_name = i.index_name AND c.index_owner = i.owner) THEN '1' ELSE '0' END
|| p.s || '0' || p.s || SUBSTR(ixc.cols, 1, 2000) || p.s || '0'
|| p.s || NVL(TO_CHAR(ixc.expr), '0')
|| p.s || CASE WHEN i.partitioned = 'YES' THEN '1' ELSE '0' END
|| p.s || CASE WHEN i.ityp_name IS NOT NULL THEN i.ityp_owner || '.' || i.ityp_name END
FROM ix i CROSS JOIN p LEFT JOIN ixc ON ixc.index_owner = i.owner AND ixc.index_name = i.index_name
UNION ALL
SELECT 6, f.table_name || '.' || f.constraint_name, 0,
'fk' || p.s || p.o || p.s || f.table_name || p.s || f.constraint_name || p.s || r.owner || p.s || r.table_name
|| p.s || f.delete_rule || p.s || 'NO ACTION'
|| p.s || CASE WHEN f.status = 'DISABLED' THEN '1' ELSE '0' END
|| p.s || CASE WHEN f.validated = 'NOT VALIDATED' THEN '1' ELSE '0' END
|| p.s || SUBSTR(fc.cols, 1, 1500) || p.s || SUBSTR(rc.cols, 1, 1500)
|| p.s || CASE WHEN f.deferrable = 'DEFERRABLE' THEN '1' ELSE '0' END
FROM con f CROSS JOIN p
JOIN all_constraints r ON r.owner = f.r_owner AND r.constraint_name = f.r_constraint_name
LEFT JOIN ccol fc ON fc.owner = f.owner AND fc.constraint_name = f.constraint_name
LEFT JOIN ccol rc ON rc.owner = f.r_owner AND rc.constraint_name = f.r_constraint_name
WHERE f.constraint_type = 'R' AND f.table_name IN (SELECT table_name FROM tb)
UNION ALL
SELECT 7, k.t || '.' || k.n, 0,
'check' || p.s || p.o || p.s || k.t || p.s || k.n || p.s || k.d
|| p.s || CASE WHEN k.st = 'DISABLED' THEN '1' ELSE '0' END
|| p.s || CASE WHEN k.va = 'NOT VALIDATED' THEN '1' ELSE '0' END
FROM ck k CROSS JOIN p
WHERE k.t IN (SELECT table_name FROM tb) AND NOT REGEXP_LIKE(NVL(k.d, 'x'), '^\s*"[^"]+" IS NOT NULL\s*$')
UNION ALL
SELECT 8, ob.fam || '.' || ob.name, 0,
'module' || p.s || p.o || p.s || ob.name
|| p.s || CASE ob.fam WHEN 'VIEW' THEN 'V' WHEN 'MATERIALIZED VIEW' THEN 'MV' WHEN 'PROCEDURE' THEN 'P' WHEN 'TRIGGER' THEN 'TR'
WHEN 'PACKAGE' THEN 'PKG' WHEN 'TYPE' THEN 'TYP'
WHEN 'FUNCTION' THEN CASE WHEN fn.aggregate = 'YES' THEN 'AGG' WHEN fn.pipelined = 'YES' THEN 'TF' ELSE 'FN' END END
|| p.s || TO_CHAR(CASE WHEN ob.fam IN ('VIEW', 'MATERIALIZED VIEW') THEN vl.lines ELSE fl.lines END)
|| p.s || CASE WHEN tr.base_object_type IN ('TABLE', 'VIEW') THEN tr.table_name END
|| p.s || CASE WHEN ob.fam = 'TRIGGER' THEN CASE WHEN tr.trigger_type = 'INSTEAD OF' THEN '1' ELSE '0' END END
|| p.s || SUBSTR(
CASE WHEN ob.fam IN ('VIEW', 'MATERIALIZED VIEW') AND vl.lines IS NULL THEN 'NOTEXT,' END
|| CASE WHEN fl.f_wrapped = 1 THEN 'WRAPPED,' END
|| CASE WHEN fl.f_cursor = 1 THEN 'CURSOR,' END
|| CASE WHEN fl.f_bulk = 1 THEN 'BULK,' END
|| CASE WHEN fl.f_dynsql = 1 THEN 'DYNSQL,' END
|| CASE WHEN fl.f_autonomous = 1 THEN 'AUTONOMOUS,' END
|| CASE WHEN fl.f_exception = 1 THEN 'EXCEPTION,' END
|| CASE WHEN fl.f_raise = 1 THEN 'RAISE_APP_ERROR,' END
|| CASE WHEN fl.f_connectby = 1 THEN 'CONNECT_BY,' END
|| CASE WHEN fl.f_rownum = 1 THEN 'ROWNUM,' END
|| CASE WHEN fl.f_decode = 1 THEN 'DECODE,' END
|| CASE WHEN fl.f_nvl = 1 THEN 'NVL,' END
|| CASE WHEN fl.f_outerplus = 1 THEN 'OUTER_PLUS,' END
|| CASE WHEN fl.f_merge = 1 THEN 'MERGE,' END
|| CASE WHEN fl.f_dblink = 1 OR dp.dblink = 1 THEN 'DBLINK,' END
|| CASE WHEN fl.f_sysdate = 1 THEN 'SYSDATE,' END
|| CASE WHEN fl.f_sequence = 1 THEN 'SEQUENCE,' END
|| CASE WHEN fl.f_pipelined = 1 OR fn.pipelined = 'YES' THEN 'PIPELINED,' END
|| CASE WHEN fl.f_objtypes = 1 OR dp.obj = 1 THEN 'OBJECT_TYPES,' END
|| CASE WHEN fl.f_collections = 1 OR dp.coll = 1 THEN 'COLLECTIONS,' END
|| CASE WHEN fl.f_refcursor = 1 THEN 'REF_CURSOR,' END
|| CASE WHEN fl.f_xml = 1 THEN 'XML,' END
|| CASE WHEN fl.f_json = 1 THEN 'JSON,' END
|| CASE WHEN fl.f_catalog = 1 THEN 'CATALOG,' END
|| CASE WHEN fl.f_hints = 1 THEN 'HINTS,' END
|| CASE WHEN fl.f_transaction = 1 THEN 'TRANSACTION,' END
|| CASE WHEN fl.f_rowid = 1 THEN 'ROWID,' END
|| CASE WHEN pk.pkgs IS NOT NULL THEN pk.pkgs || ',' END, 1, 2000)
|| p.s || TO_CHAR(ob.inv)
FROM ob CROSS JOIN p
LEFT JOIN fl ON fl.fam = ob.fam AND fl.name = ob.name
LEFT JOIN vl ON vl.fam = ob.fam AND vl.n = ob.name
LEFT JOIN pk ON pk.fam = ob.fam AND pk.name = ob.name
LEFT JOIN dp ON dp.fam = ob.fam AND dp.name = ob.name
LEFT JOIN fn ON ob.fam = 'FUNCTION' AND fn.object_name = ob.name
LEFT JOIN tr ON ob.fam = 'TRIGGER' AND tr.trigger_name = ob.name
UNION ALL
SELECT DISTINCT 9, d.name || '.' || d.referenced_owner || '.' || d.referenced_name || '@' || d.referenced_link_name, 0,
'xref' || p.s || p.o || p.s || d.name || p.s || d.referenced_link_name || p.s || '' || p.s || d.referenced_owner || p.s || d.referenced_name
FROM all_dependencies d, p
WHERE d.owner = p.o AND d.type <> 'SYNONYM' AND (d.referenced_link_name IS NOT NULL
OR (d.referenced_owner <> p.o AND d.referenced_owner NOT IN (SELECT n FROM ou)))
UNION ALL
SELECT 10, cnt.k, 0, 'feature' || p.s || cnt.k || p.s || TO_CHAR(cnt.c) || p.s || cnt.e FROM cnt, p
)
-- every line once, plus the end line (number of lines before it) after the last one
SELECT CASE WHEN g.e = 1 THEN 'MMDB1|end|' || TO_CHAR(b.n)
ELSE 'MMDB1|' || REPLACE(REPLACE(REPLACE(REPLACE(b.txt, '|', '/'), CHR(13), ' '), CHR(10), ' '), CHR(1), '|') END AS mmdb
FROM (SELECT ko, s1, s2, txt, COUNT(*) OVER () AS n FROM body) b,
(SELECT 0 AS e FROM dual UNION ALL SELECT 1 FROM dual) g
WHERE g.e = 0 OR b.ko = 0
ORDER BY g.e, b.ko, b.s1, b.s2;
SPOOL OFF