SPO sqldx.log SET DEF ON; SET DEF ^ TERM OFF ECHO ON VER OFF SERVEROUT ON SIZE UNL; REM REM $Header: 1366133.1 sqldx.sql 12.1.04 2013/11/11 carlos.sierra mauro.pagano $ REM REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved. REM REM AUTHOR REM carlos.sierra@oracle.com REM mauro.pagano@oracle.com REM REM SCRIPT REM sqldx.sql SQL Dynamic eXtract REM REM DESCRIPTION REM Produces a set of reports with information about one SQL REM statement. REM REM This script does not install any objects in the database. REM It does not perform any DDL commands. REM It can be used in Dataguard or any read-only database. REM REM PRE-REQUISITES REM 1. Execute as SYS or user with DBA role or user with access REM to data dictionary views. REM REM PARAMETERS REM 1. Oracle Pack license (Tuning or Diagnostics) T|D REM 2. Output Type (HTML or CSV or Both) H|C|B REM 3. SQL_ID of interest. REM REM EXECUTION REM 1. Start SQL*Plus connecting as SYS or user with DBA role or REM user with access to data dictionary views. REM 2. Execute script sqldx.sql passing values for parameters. REM REM EXAMPLE REM # sqlplus / as sysdba REM SQL> START [path]sqldx.sql [T|D] [H|C|B] [SQL_ID] REM SQL> START sqldx.sql T B 51x6yr9ym5hdc REM REM NOTES REM 1. For possible errors see sqldx.log. REM 2. If site has both Tuning and Diagnostics licenses then REM specified T (Oracle Tuning pack includes Oracle Diagnostics) REM DEF script = 'sqldx'; DEF module = 'SQLDX'; DEF mos_doc = '1366133.1'; DEF doc_ver = '12.1.04'; DEF doc_date = '2013/11/11'; DEF max_rows_threshold = 10000; /**************************************************************************************************/ SET TERM ON ECHO OFF; PRO PRO Parameter 1: PRO Oracle Pack License (Tuning or Diagnostics) [T|D] (required) PRO DEF input_license = '^1'; PRO SET TERM OFF; COL olicense NEW_V olicense FOR A1; SELECT 'license: ' x, NVL(UPPER(SUBSTR(TRIM('^^input_license.'), 1, 1)), 'N') olicense FROM DUAL; VAR olicense CHAR(1); EXEC :olicense := '^^olicense.'; SET TERM ON; WHENEVER SQLERROR EXIT SQL.SQLCODE; BEGIN IF '^^olicense.' IS NULL OR '^^olicense.' NOT IN ('T', 'D', 'N') THEN RAISE_APPLICATION_ERROR(-20100, 'Oracle Pack License (Tuning or Diagnostics) must be specified as "T" or "D".'); END IF; END; / WHENEVER SQLERROR CONTINUE; PRO PRO Parameter 2: PRO Output Type (HTML or CSV or Both) [H|C|B] (required) PRO DEF input_output_type = '^2'; PRO SET TERM OFF; COL output_type NEW_V output_type FOR A1; SELECT 'output_type: ' x, NVL(UPPER(SUBSTR(TRIM('^^input_output_type.'), 1, 1)), 'B') output_type FROM DUAL; VAR output_type CHAR(1); EXEC :output_type := UPPER(SUBSTR('^^output_type.', 1, 1)); SET TERM ON; WHENEVER SQLERROR EXIT SQL.SQLCODE; BEGIN IF '^^output_type.' IS NULL OR '^^output_type.' NOT IN ('H', 'C', 'B', 'N') THEN RAISE_APPLICATION_ERROR(-20100, 'Output Type (HTML or CSV or Both) must be specified as "H" or "C" or "B".'); END IF; END; / WHENEVER SQLERROR CONTINUE; PRO PRO Parameter 3: PRO SQL_ID of the SQL to be analyzed (required) PRO DEF input_sql_id = '^3'; PRO PRO Values passed: PRO License: "^^input_license." PRO Output : "^^input_output_type." PRO SQL_ID : "^^input_sql_id." PRO /**************************************************************************************************/ SET TERM OFF; -- get current time COL current_time NEW_V current_time FOR A15; SELECT 'current_time: ' x, TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') current_time FROM DUAL; -- get file names prefix if executed from sqlt or sqlhc SELECT 'client_info: ' x, SYS_CONTEXT('USERENV', 'CLIENT_INFO') client_info FROM DUAL; COL prefix NEW_V prefix FOR A256; -- sqldx.sql can be executed stand alone or called from sqlt or sqlhc SELECT 'prefix: ' x, CASE WHEN SUBSTR(SYS_CONTEXT('USERENV', 'CLIENT_INFO'), 1, 4) IN ('sqlt', 'sqlh') THEN SYS_CONTEXT('USERENV', 'CLIENT_INFO') ELSE '^^script._^^current_time.' END prefix FROM DUAL; -- get dblink if executed from sqltxtrsby VAR module_name VARCHAR2(256); VAR action_name VARCHAR2(256); EXEC DBMS_APPLICATION_INFO.READ_MODULE(module_name => :module_name, action_name => :action_name); COL my_dblink NEW_V my_dblink FOR A256; SELECT 'my_dblink: ' x, CASE WHEN :module_name = 'sqltxtrsby' AND SUBSTR(:action_name, 1, 1) = '@' THEN :action_name ELSE NULL END my_dblink FROM DUAL; -- reset module, action and client info EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => NULL); -- get dbid COL dbid NEW_V dbid; SELECT 'dbid: ' x, dbid FROM v$database^^my_dblink.; COL sql_id NEW_V sql_id FOR A13; SELECT 'sql_id mem: ' x, sql_id FROM gv$sqlarea^^my_dblink. WHERE sql_id = TRIM('^^input_sql_id.') UNION SELECT 'sql_id awr: ' x, sql_id FROM dba_hist_sqltext^^my_dblink. WHERE :olicense IN ('T', 'D') AND dbid = ^^dbid. AND sql_id = TRIM('^^input_sql_id.'); SELECT 'sql_id: ' x, NVL('^^sql_id.', TRIM('^^input_sql_id.')) sql_id FROM DUAL; VAR sql_id VARCHAR2(13); EXEC :sql_id := '^^sql_id.'; SET TERM ON; WHENEVER SQLERROR EXIT SQL.SQLCODE; BEGIN IF '^^sql_id.' IS NULL THEN IF :olicense IN ('T', 'D') THEN RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory nor in AWR.'); ELSE RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory.'); END IF; END IF; END; / WHENEVER SQLERROR CONTINUE; SET TERM OFF; -- get file names prefix by sql_id COL prefix1 NEW_V prefix1 FOR A256; SELECT 'prefix1: ' x, '^^prefix._^^sql_id.' prefix1 FROM DUAL; -- get file names prefix by global COL prefix4 NEW_V prefix4 FOR A256; SELECT 'prefix4: ' x, '^^prefix._global' prefix4 FROM DUAL; -- get file names prefix by table COL prefix5 NEW_V prefix5 FOR A256; SELECT 'prefix5: ' x, '^^prefix._table' prefix5 FROM DUAL; -- set module, action and client info EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^module. ^^doc_ver.', action_name => '^^script..sql'); EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => '^^module.'); /**************************************************************************************************/ /* ------------------------- * * get sql_text * * ------------------------- */ SET TERM ON; PRO PRO ### ... getting SQL text ... PRO SET TERM OFF; VAR sql_text CLOB; EXEC :sql_text := NULL; -- get sql_text from memory DECLARE l_sql_text VARCHAR2(32767); BEGIN -- 10g see bug 5017909 DBMS_OUTPUT.PUT_LINE('getting sql_text from memory'); FOR i IN (SELECT DISTINCT piece, sql_text FROM gv$sqltext_with_newlines^^my_dblink. WHERE sql_id = '^^sql_id.' ORDER BY 1, 2) LOOP IF :sql_text IS NULL THEN DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); END IF; l_sql_text := REPLACE(i.sql_text, CHR(00), ' '); DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text); END LOOP; IF :sql_text IS NOT NULL THEN DBMS_LOB.CLOSE(:sql_text); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM); :sql_text := NULL; END; / -- get sql_text from awr BEGIN IF :olicense IN ('T', 'D') AND (:sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0) THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from awr'); SELECT REPLACE(sql_text, CHR(00), ' ') INTO :sql_text FROM dba_hist_sqltext^^my_dblink. WHERE :olicense IN ('T', 'D') AND dbid = ^^dbid. AND sql_id = '^^sql_id.' AND sql_text IS NOT NULL AND ROWNUM = 1; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM); :sql_text := NULL; END; / SELECT 'sql_text: ' x, :sql_text FROM DUAL; /* ------------------------- * * get signature * * ------------------------- */ SET TERM ON; PRO PRO ### ... getting signature ... PRO SET TERM OFF; -- signature (force=false) VAR signature NUMBER; BEGIN IF :olicense = 'T' AND :sql_text IS NOT NULL THEN :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text, FALSE); ELSE :signature := -1; END IF; END; / COL signature NEW_V signature FOR A20; SELECT 'signature: ' x, TO_CHAR(:signature) signature FROM DUAL; COL prefix2 NEW_V prefix2 FOR A256; SELECT 'prefix2: ' x, '^^prefix._^^signature._exact' prefix2 FROM DUAL; -- signature (force=true) VAR signaturef NUMBER; BEGIN IF :olicense = 'T' AND :sql_text IS NOT NULL THEN :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text, TRUE); ELSE :signaturef := -1; END IF; END; / COL signaturef NEW_V signaturef FOR A20; SELECT 'signaturef: ' x, TO_CHAR(:signaturef) signaturef FROM DUAL; COL prefix3 NEW_V prefix3 FOR A256; SELECT 'prefix3: ' x, '^^prefix._^^signaturef._force' prefix3 FROM DUAL; /* ------------------------- * * get tables * * ------------------------- */ SET TERM ON; PRO PRO ### ... getting tables ... PRO SET TERM OFF; VAR tables_list CLOB; EXEC :tables_list := NULL; -- get list of tables from execution plan -- format (('owner', 'table_name'), (), ()...) DECLARE l_pair VARCHAR2(32767); BEGIN DBMS_LOB.CREATETEMPORARY(:tables_list, TRUE, DBMS_LOB.SESSION); FOR i IN (WITH object AS ( SELECT /*+ MATERIALIZE */ object_owner owner, object_name name FROM gv$sql_plan^^my_dblink. WHERE inst_id IN (SELECT inst_id FROM gv$instance) AND sql_id = '^^sql_id.' AND object_owner IS NOT NULL AND object_name IS NOT NULL UNION SELECT object_owner owner, object_name name FROM dba_hist_sql_plan^^my_dblink. WHERE :olicense IN ('T', 'D') AND dbid = ^^dbid. AND sql_id = '^^sql_id.' AND object_owner IS NOT NULL AND object_name IS NOT NULL ) SELECT 'TABLE', t.owner, t.table_name FROM dba_tab_statistics^^my_dblink. t, -- include fixed objects object o WHERE t.owner = o.owner AND t.table_name = o.name UNION SELECT 'TABLE', i.table_owner, i.table_name FROM dba_indexes^^my_dblink. i, object o WHERE i.owner = o.owner AND i.index_name = o.name) LOOP IF l_pair IS NULL THEN DBMS_LOB.WRITEAPPEND(:tables_list, 1, '('); ELSE DBMS_LOB.WRITEAPPEND(:tables_list, 1, ','); END IF; l_pair := '('''''||i.owner||''''','''''||i.table_name||''''')'; DBMS_LOB.WRITEAPPEND(:tables_list, LENGTH(l_pair), l_pair); END LOOP; IF l_pair IS NULL THEN l_pair := '(''''DUMMY'''',''''DUMMY'''')'; DBMS_LOB.WRITEAPPEND(:tables_list, LENGTH(l_pair), l_pair); ELSE DBMS_LOB.WRITEAPPEND(:tables_list, 1, ')'); END IF; END; / SET LONG 2000000 LONGC 2000; SELECT 'tables_list: ' x, :tables_list FROM DUAL; SET LIN 32767; COL tables_list NEW_V tables_list FOR A32767; SELECT :tables_list tables_list FROM DUAL; /**************************************************************************************************/ -- produce driver script SET TERM OFF ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NUM 20 NEWP NONE PAGES 0 LONG 2000000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF SERVEROUT ON SIZE UNL; SET TERM ON; PRO PRO ### ... generating dynamic script, please wait ... PRO SET TERM OFF; SPO ^^prefix1._driver.sql; PRO REM $Header: ^^mos_doc. ^^prefix1._driver.sql ^^doc_ver. ^^doc_date. carlos.sierra $ PRO REM created by ^^script..sql SET DEF ON; DEF subst_var = '^'; PRO SET DEF ON;; PRO SET DEF ^ TERM OFF ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NUM 20 NEWP NONE PAGES 0 LONG 2000000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF SERVEROUT ON SIZE UNL;; SET DEF ^; PRO ALTER SESSION SET nls_numeric_characters = ".,";; PRO ALTER SESSION SET nls_date_format = 'YYYY-MM-DD/HH24:MI:SS';; PRO ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD/HH24:MI:SS.FF';; PRO ALTER SESSION SET nls_timestamp_tz_format = 'YYYY-MM-DD/HH24:MI:SS.FF TZH:TZM';; PRO ALTER SESSION SET nls_sort = 'BINARY';; PRO ALTER SESSION SET nls_comp = 'BINARY';; PRO CL BRE COL;; PRO -- YYYY-MM-DD/HH24:MI:SS PRO COL time_stamp1 NEW_V time_stamp1 FOR A20;; PRO /*********************************************************************************/ DECLARE l_cnt INTEGER; l_tbls_h INTEGER; l_tbls_c INTEGER; l_table_name VARCHAR2(32767); l_file_name VARCHAR2(32767); l_columns_list VARCHAR2(32767); PROCEDURE put_line (p_line IN VARCHAR2) IS l_pos INTEGER := 1; BEGIN WHILE l_pos < LENGTH(p_line) LOOP DBMS_OUTPUT.PUT_LINE(SUBSTR(p_line, l_pos, LEAST(2000, LENGTH(p_line) - l_pos + 1))); l_pos := l_pos + 2000; END LOOP; END put_line; PROCEDURE put_header ( p_prefix IN VARCHAR2, p_id IN VARCHAR2 ) IS BEGIN put_line('PRO '); put_line('PRO '); put_line('PRO '); put_line('PRO '); put_line('PRO'); put_line('PRO '); put_line('PRO '||p_prefix||'_'||l_file_name||'.html'); put_line('PRO'); put_line('PRO '); put_line('PRO'); put_line('PRO '); put_line('PRO '); put_line('PRO

^^mos_doc. ^^module. ^^doc_ver. '||p_id||' '||l_table_name||'

'); put_line('PRO'); END put_header; PROCEDURE put_footer IS BEGIN put_line('PRO'); put_line('SELECT '''' FROM dual;'); put_line('PRO
'); put_line('PRO ^^mos_doc. ^^module. ^^doc_ver. ^^subst_var.^^subst_var.time_stamp1.'); put_line('PRO '); put_line('PRO '); END put_footer; FUNCTION get_columns_list(p_table_name IN VARCHAR2) RETURN VARCHAR2 IS l2_count INTEGER := 0; l2_columns_list VARCHAR2(32767) := NULL; BEGIN IF '^^my_dblink.' IS NULL THEN RETURN '*'; -- all columns ELSE -- exclude LOBs for sqltxtrsby FOR i IN (SELECT column_name FROM dba_tab_columns^^my_dblink. WHERE owner = 'SYS' AND table_name = p_table_name AND data_type NOT IN ('CLOB', 'LONG', 'BLOB') -- CLOB causes "ORA-64202: remote temporary or abstract LOB locator is encountered" on sqltxtrsby ORDER BY column_id) LOOP l2_count := l2_count + 1; IF l2_count = 1 THEN l2_columns_list := i.column_name; ELSE l2_columns_list := l2_columns_list||', '||i.column_name; END IF; END LOOP; RETURN l2_columns_list; END IF; END get_columns_list; PROCEDURE describe_table(p_table_name IN VARCHAR2) IS l_tab_comments VARCHAR2(32767); BEGIN BEGIN SELECT comments INTO l_tab_comments FROM dba_tab_comments WHERE owner = 'SYS' AND table_name = p_table_name AND table_type = 'VIEW'; put_line('PRO '||l_tab_comments); EXCEPTION WHEN OTHERS THEN NULL; END; put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" ENTMAP OFF SPOOL OFF;'); put_line('SELECT col.column_id "#",'); put_line(' ''
''||col.column_name||''
'' "Name",'); put_line(' ''
''||DECODE(nullable, ''N'', ''NOT NULL'')||''
'' "Null?",'); put_line(' ''
''||col.data_type||(CASE WHEN data_type LIKE ''%CHAR%'' THEN ''(''||data_length||'')'' END)||''
'' "Type",'); put_line(' ''
''||REPLACE(REPLACE(REPLACE(com.comments, ''>'', CHR(38)||''GT;''), ''<'', CHR(38)||''LT;''), CHR(10), ''
'')||''
'' "Comments"'); put_line(' FROM dba_tab_columns col,'); put_line(' dba_col_comments com'); put_line(' WHERE col.owner = ''SYS'''); put_line(' AND col.table_name = '''||p_table_name||''''); put_line(' AND com.owner(+) = col.owner'); put_line(' AND com.table_name(+) = col.table_name'); put_line(' AND com.column_name(+) = col.column_name'); put_line(' ORDER BY col.column_id;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); END describe_table; BEGIN IF :olicense IN ('D', 'T') AND :output_type IN ('H', 'C', 'B') THEN -- by sql_id BEGIN put_line('SET TERM ON;'); put_line('PRO ###'); put_line('PRO ### by sql_id'); put_line('PRO ###'); put_line('SET TERM OFF;'); l_tbls_h := 0; l_tbls_c := 0; FOR i IN (SELECT table_name, column_name FROM dba_tab_columns^^my_dblink. WHERE column_name = 'SQL_ID' AND owner = 'SYS' AND data_type = 'VARCHAR2' AND data_length = 13 AND (table_name LIKE 'WR%' OR table_name LIKE 'DBA%' OR table_name LIKE 'SQL%' OR table_name LIKE 'GV!_%' ESCAPE '!' /* GV_$ */) AND table_name NOT LIKE 'SQLT%' AND table_name NOT LIKE '%LOGSTDBY%' ORDER BY table_name) LOOP l_table_name := REPLACE(i.table_name, 'GV_$', 'GV$'); l_file_name := REPLACE(l_table_name, '$', 's'); l_cnt := NULL; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^script..sql', action_name => 'SQL_ID '||i.table_name); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(i.table_name||' '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ''^^sql_id.''' INTO l_cnt; DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); EXCEPTION WHEN OTHERS THEN put_line('-- skip: '||l_table_name||' by sql_id. reason: '||SQLERRM); END; IF l_cnt > 0 THEN l_columns_list := get_columns_list(i.table_name); put_line('SELECT TO_CHAR(SYSDATE, ''YYYY-MM-DD/HH24:MI:SS'') time_stamp1 FROM DUAL;'); put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => ''^^script..sql'', action_name => ''SQL_ID '||i.table_name||''');'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('''||i.table_name||' ^^subst_var.^^subst_var.time_stamp1.'');'); put_line('-- select: '||l_table_name||' by sql_id. count(*): '||l_cnt); put_line('SET TERM ON;'); put_line('PRO ^^subst_var.^^subst_var.time_stamp1. '||l_table_name||';'); put_line('SET TERM OFF;'); -- sql_id html IF :output_type IN ('H', 'B') THEN l_tbls_h := l_tbls_h + 1; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); IF l_table_name IN ('GV$SQLAREA', 'DBA_HIST_SQLTEXT') THEN put_line('CL BRE;'); ELSE put_line('BRE ON sql_text ON sql_fulltext;'); END IF; put_line('SPO ^^prefix1._'||l_file_name||'.html;'); put_header('^^prefix1.', '^^sql_id.'); describe_table(i.table_name); put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" SPOOL OFF;'); put_line('SELECT ROWNUM "#", v.* FROM (SELECT /*+ NO_MERGE */ '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ''^^sql_id.'' AND ROWNUM <= ^^max_rows_threshold.) v;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); put_footer; put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; -- sql_id csv IF :output_type IN ('C', 'B') THEN l_tbls_c := l_tbls_c + 1; IF l_table_name IN ('GV$SQLAREA', 'DBA_HIST_SQLTEXT') THEN put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('BRE ON sql_text ON sql_fulltext;'); ELSE put_line('COL sql_text NOPRI;'); put_line('COL sql_fulltext NOPRI;'); put_line('CL BRE;'); END IF; put_line('SPO ^^prefix1._'||l_file_name||'.csv;'); put_line('SET HEA ON PAGES 50000 LIN 32767 LONGC 4000 COLSEP '','';'); put_line('SELECT '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ''^^sql_id.'' AND ROWNUM <= ^^max_rows_threshold.;'); put_line('SET HEA OFF PAGES 0 LIN 2000 LONGC 2000 COLSEP '' '';'); put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL);'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);'); put_line('/*********************************************************************************/'); ELSIF l_cnt = 0 THEN put_line('-- skip: '||l_table_name||' by sql_id. reason: COUNT(*) = 0'); END IF; END LOOP; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('CL BRE;'); put_line('/*********************************************************************************/'); IF l_tbls_h > 0 THEN put_line('HOS zip -m ^^prefix1._html ^^prefix1._*.html'); put_line('HOS unzip -l ^^prefix1._html'); END IF; IF l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix1._csv ^^prefix1._*.csv'); put_line('HOS unzip -l ^^prefix1._csv'); END IF; IF l_tbls_h + l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix. ^^prefix1._*.zip'); put_line('HOS unzip -l ^^prefix.'); put_line('/*********************************************************************************/'); END IF; END; -- by exact signature BEGIN put_line('SET TERM ON;'); put_line('PRO ###'); put_line('PRO ### by exact signature'); put_line('PRO ###'); put_line('SET TERM OFF;'); l_tbls_h := 0; l_tbls_c := 0; FOR i IN (SELECT c1.table_name, c1.column_name FROM dba_tab_columns^^my_dblink. c1 WHERE :olicense = 'T' AND :signature > 0 AND c1.column_name IN ('SIGNATURE', 'EXACT_MATCHING_SIGNATURE') AND c1.owner = 'SYS' AND c1.data_type = 'NUMBER' AND (c1.table_name LIKE 'WR%' OR c1.table_name LIKE 'DBA%' OR c1.table_name LIKE 'SQL%' OR c1.table_name LIKE 'GV!_%' ESCAPE '!' /* GV_$ */) AND c1.table_name NOT LIKE 'SQLT%' AND c1.table_name NOT LIKE '%LOGSTDBY%' AND NOT EXISTS ( SELECT NULL FROM dba_tab_columns^^my_dblink. c2 WHERE c2.owner = c1.owner AND c2.table_name = c1.table_name AND c2.column_name = 'SQL_ID' AND c2.data_type = 'VARCHAR2' AND c2.data_length = 13 ) ORDER BY c1.table_name) LOOP l_table_name := REPLACE(i.table_name, 'GV_$', 'GV$'); l_file_name := REPLACE(l_table_name, '$', 's'); l_cnt := NULL; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^script..sql', action_name => 'EXACT_MATCHING_SIGNATURE '||i.table_name); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(i.table_name||' '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signature.' INTO l_cnt; DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); EXCEPTION WHEN OTHERS THEN put_line('-- skip: '||l_table_name||' by exact signature. reason: '||SQLERRM); END; IF l_cnt > 0 THEN l_columns_list := get_columns_list(i.table_name); put_line('SELECT TO_CHAR(SYSDATE, ''YYYY-MM-DD/HH24:MI:SS'') time_stamp1 FROM DUAL;'); put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => ''^^script..sql'', action_name => ''EXACT_MATCHING_SIGNATURE '||i.table_name||''');'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('''||i.table_name||' ^^subst_var.^^subst_var.time_stamp1.'');'); put_line('-- select: '||l_table_name||' by exact signature. count(*): '||l_cnt); put_line('SET TERM ON;'); put_line('PRO ^^subst_var.^^subst_var.time_stamp1. '||l_table_name||';'); put_line('SET TERM OFF;'); -- exact signature html IF :output_type IN ('H', 'B') THEN l_tbls_h := l_tbls_h + 1; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); IF l_table_name IN ('SQL$TEXT', 'DBA_SQL_PROFILES') THEN put_line('CL BRE;'); ELSE put_line('BRE ON sql_text ON sql_fulltext;'); END IF; put_line('SPO ^^prefix2._'||l_file_name||'.html;'); put_header('^^prefix2.', '^^signature.'); describe_table(i.table_name); put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" SPOOL OFF;'); put_line('SELECT ROWNUM "#", v.* FROM (SELECT /*+ NO_MERGE */ '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signature. AND ROWNUM <= ^^max_rows_threshold.) v;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); put_footer; put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; -- exact signature csv IF :output_type IN ('C', 'B') THEN l_tbls_c := l_tbls_c + 1; IF l_table_name IN ('SQL$TEXT', 'DBA_SQL_PROFILES') THEN put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('BRE ON sql_text ON sql_fulltext;'); ELSE put_line('COL sql_text NOPRI;'); put_line('COL sql_fulltext NOPRI;'); put_line('CL BRE;'); END IF; put_line('SPO ^^prefix2._'||l_file_name||'.csv;'); put_line('SET HEA ON PAGES 50000 LIN 32767 LONGC 4000 COLSEP '','';'); put_line('SELECT '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signature. AND ROWNUM <= ^^max_rows_threshold.;'); put_line('SET HEA OFF PAGES 0 LIN 2000 LONGC 2000 COLSEP '' '';'); put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL);'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);'); put_line('/*********************************************************************************/'); ELSIF l_cnt = 0 THEN put_line('-- skip: '||l_table_name||' by exact signature. reason: COUNT(*) = 0'); END IF; END LOOP; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('CL BRE;'); put_line('/*********************************************************************************/'); IF l_tbls_h > 0 THEN put_line('HOS zip -m ^^prefix2._html ^^prefix2._*.html'); put_line('HOS unzip -l ^^prefix2._html'); END IF; IF l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix2._csv ^^prefix2._*.csv'); put_line('HOS unzip -l ^^prefix2._csv'); END IF; IF l_tbls_h + l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix. ^^prefix2._*.zip'); put_line('HOS unzip -l ^^prefix.'); put_line('/*********************************************************************************/'); END IF; END; -- by force signature (only if different than exact signature) BEGIN put_line('SET TERM ON;'); put_line('PRO ###'); put_line('PRO ### by force signature'); put_line('PRO ###'); put_line('SET TERM OFF;'); l_tbls_h := 0; l_tbls_c := 0; FOR i IN (SELECT c1.table_name, c1.column_name FROM dba_tab_columns^^my_dblink. c1 WHERE :olicense = 'T' AND :signaturef > 0 AND :signaturef <> :signature AND c1.column_name IN ('SIGNATURE', 'FORCE_MATCHING_SIGNATURE') AND c1.owner = 'SYS' AND c1.data_type = 'NUMBER' AND (c1.table_name LIKE 'WR%' OR c1.table_name LIKE 'DBA%' OR c1.table_name LIKE 'SQL%' OR c1.table_name LIKE 'GV!_%' ESCAPE '!' /* GV_$ */) AND c1.table_name NOT LIKE 'SQLT%' AND c1.table_name NOT LIKE '%LOGSTDBY%' ORDER BY c1.table_name) LOOP l_table_name := REPLACE(i.table_name, 'GV_$', 'GV$'); l_file_name := REPLACE(l_table_name, '$', 's'); l_cnt := NULL; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^script..sql', action_name => 'FORCE_MATCHING_SIGNATURE '||i.table_name); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(i.table_name||' '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signaturef.' INTO l_cnt; DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); EXCEPTION WHEN OTHERS THEN put_line('-- skip: '||l_table_name||' by force signature. reason: '||SQLERRM); END; IF l_cnt > 0 THEN l_columns_list := get_columns_list(i.table_name); put_line('SELECT TO_CHAR(SYSDATE, ''YYYY-MM-DD/HH24:MI:SS'') time_stamp1 FROM DUAL;'); put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => ''^^script..sql'', action_name => ''FORCE_MATCHING_SIGNATURE '||i.table_name||''');'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('''||i.table_name||' ^^subst_var.^^subst_var.time_stamp1.'');'); put_line('-- select: '||l_table_name||' by force signature. count(*): '||l_cnt); put_line('SET TERM ON;'); put_line('PRO ^^subst_var.^^subst_var.time_stamp1. '||l_table_name||';'); put_line('SET TERM OFF;'); -- force signature html IF :output_type IN ('H', 'B') THEN l_tbls_h := l_tbls_h + 1; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); IF l_table_name IN ('SQL$TEXT', 'DBA_SQL_PROFILES') THEN put_line('CL BRE;'); ELSE put_line('BRE ON sql_text ON sql_fulltext;'); END IF; put_line('SPO ^^prefix3._'||l_file_name||'.html;'); put_header('^^prefix3.', '^^signaturef.'); describe_table(i.table_name); put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" SPOOL OFF;'); put_line('SELECT ROWNUM "#", v.* FROM (SELECT /*+ NO_MERGE */ '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signaturef. AND ROWNUM <= ^^max_rows_threshold.) v;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); put_footer; put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; -- force signature csv IF :output_type IN ('C', 'B') THEN l_tbls_c := l_tbls_c + 1; IF l_table_name IN ('SQL$TEXT', 'DBA_SQL_PROFILES') THEN put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('BRE ON sql_text ON sql_fulltext;'); ELSE put_line('COL sql_text NOPRI;'); put_line('COL sql_fulltext NOPRI;'); put_line('CL BRE;'); END IF; put_line('SPO ^^prefix3._'||l_file_name||'.csv;'); put_line('SET HEA ON PAGES 50000 LIN 32767 LONGC 4000 COLSEP '','';'); put_line('SELECT '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE '||i.column_name||' = ^^signaturef. AND ROWNUM <= ^^max_rows_threshold.;'); put_line('SET HEA OFF PAGES 0 LIN 2000 LONGC 2000 COLSEP '' '';'); put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL);'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);'); put_line('/*********************************************************************************/'); ELSIF l_cnt = 0 THEN put_line('-- skip: '||l_table_name||' by force signature. reason: COUNT(*) = 0'); END IF; END LOOP; put_line('COL sql_text PRI;'); put_line('COL sql_fulltext PRI;'); put_line('CL BRE;'); put_line('/*********************************************************************************/'); IF l_tbls_h > 0 THEN put_line('HOS zip -m ^^prefix3._html ^^prefix3._*.html'); put_line('HOS unzip -l ^^prefix3._html'); END IF; IF l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix3._csv ^^prefix3._*.csv'); put_line('HOS unzip -l ^^prefix3._csv'); END IF; IF l_tbls_h + l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix. ^^prefix3._*.zip'); put_line('HOS unzip -l ^^prefix.'); put_line('/*********************************************************************************/'); END IF; END; -- by table BEGIN put_line('SET TERM ON;'); put_line('PRO ###'); put_line('PRO ### by table'); put_line('PRO ###'); put_line('SET TERM OFF;'); FOR i IN (SELECT c1.owner, c1.table_name FROM dba_tab_columns^^my_dblink. c1 WHERE c1.column_name = 'TABLE_NAME' AND c1.owner = 'SYS' AND c1.table_name NOT LIKE '%LOGSTDBY%' AND SUBSTR(c1.table_name, 1, 3) IN ('COL', 'DBA', 'ROL', 'TAB') AND EXISTS (SELECT null FROM dba_tab_cols^^my_dblink. c2 WHERE c2.owner = c1.owner AND c2.table_name = c1.table_name AND c2.column_name IN ('TABLE_OWNER', 'OWNER', 'OWNER_NAME', 'SCHEMA_NAME', 'USER_NAME', 'USERNAME')) ORDER BY c1.table_name) LOOP FOR j IN (SELECT column_name FROM dba_tab_columns^^my_dblink. WHERE owner = i.owner AND table_name = i.table_name AND column_name IN ('TABLE_OWNER', 'OWNER', 'OWNER_NAME', 'SCHEMA_NAME', 'USER_NAME', 'USERNAME') ORDER BY CASE column_name WHEN 'TABLE_OWNER' THEN 1 WHEN 'OWNER' THEN 2 WHEN 'OWNER_NAME' THEN 3 WHEN 'SCHEMA_NAME' THEN 4 WHEN 'USER_NAME' THEN 5 WHEN 'USERNAME' THEN 6 ELSE 7 END) LOOP l_table_name := REPLACE(i.table_name, 'GV_$', 'GV$'); l_file_name := REPLACE(l_table_name, '$', 's'); l_cnt := NULL; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^script..sql', action_name => 'TABLE '||i.table_name); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(i.table_name||' '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||l_table_name||'^^my_dblink. WHERE ('||j.column_name||', table_name) IN ^^tables_list.' INTO l_cnt; DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); EXCEPTION WHEN OTHERS THEN put_line('-- skip: '||l_table_name||' by table. reason: '||SQLERRM); END; IF l_cnt > 0 THEN l_columns_list := get_columns_list(i.table_name); put_line('SELECT TO_CHAR(SYSDATE, ''YYYY-MM-DD/HH24:MI:SS'') time_stamp1 FROM DUAL;'); put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => ''^^script..sql'', action_name => ''TABLE '||i.table_name||''');'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('''||i.table_name||' ^^subst_var.^^subst_var.time_stamp1.'');'); put_line('-- select: '||l_table_name||' by table. count(*): '||l_cnt); put_line('SET TERM ON;'); put_line('PRO ^^subst_var.^^subst_var.time_stamp1. '||l_table_name||';'); put_line('SET TERM OFF;'); -- table html IF :output_type IN ('H', 'B') THEN l_tbls_h := l_tbls_h + 1; put_line('SPO ^^prefix5._'||l_file_name||'.html;'); put_header('^^prefix5.', NULL); describe_table(i.table_name); put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" SPOOL OFF;'); put_line('SELECT ROWNUM "#", v.* FROM (SELECT /*+ NO_MERGE */ '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE ('||j.column_name||', table_name) IN ^^tables_list. AND ROWNUM <= ^^max_rows_threshold.) v;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); put_footer; put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; -- table csv IF :output_type IN ('C', 'B') THEN l_tbls_c := l_tbls_c + 1; put_line('SPO ^^prefix5._'||l_file_name||'.csv;'); put_line('SET HEA ON PAGES 50000 LIN 32767 LONGC 4000 COLSEP '','';'); put_line('SELECT '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE ('||j.column_name||', table_name) IN ^^tables_list. AND ROWNUM <= ^^max_rows_threshold.;'); put_line('SET HEA OFF PAGES 0 LIN 2000 LONGC 2000 COLSEP '' '';'); put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL);'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);'); put_line('/*********************************************************************************/'); ELSIF l_cnt = 0 THEN put_line('-- skip: '||l_table_name||' by table. reason: COUNT(*) = 0'); END IF; EXIT; -- take 1st column only END LOOP; END LOOP; put_line('/*********************************************************************************/'); IF l_tbls_h > 0 THEN put_line('HOS zip -m ^^prefix5._html ^^prefix5._*.html'); put_line('HOS unzip -l ^^prefix5._html'); END IF; IF l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix5._csv ^^prefix5._*.csv'); put_line('HOS unzip -l ^^prefix5._csv'); END IF; IF l_tbls_h + l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix. ^^prefix5._*.zip'); put_line('HOS unzip -l ^^prefix.'); put_line('/*********************************************************************************/'); END IF; END; -- by global BEGIN put_line('SET TERM ON;'); put_line('PRO ###'); put_line('PRO ### by global'); put_line('PRO ###'); put_line('SET TERM OFF;'); l_tbls_h := 0; l_tbls_c := 0; FOR i IN (SELECT DISTINCT table_name FROM dba_tab_cols^^my_dblink. WHERE owner = 'SYS' AND (table_name LIKE 'WR%' OR table_name LIKE 'DBA%' OR table_name LIKE 'SQL%' OR table_name LIKE 'GV!_%' ESCAPE '!' /* GV_$ */) AND table_name NOT LIKE 'SQLT%' AND table_name NOT LIKE '%LOGSTDBY%' --AND table_name IN ('DBA_HIST_SNAPSHOT', 'DBA_OBJECTS', 'GV_$PARAMETER2') AND table_name IN ('DBA_HIST_SNAPSHOT', 'GV_$PARAMETER2') ORDER BY table_name) LOOP l_table_name := REPLACE(i.table_name, 'GV_$', 'GV$'); l_file_name := REPLACE(l_table_name, '$', 's'); l_cnt := NULL; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^script..sql', action_name => 'GLOBAL '||i.table_name); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(i.table_name||' '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||l_table_name||'^^my_dblink.' INTO l_cnt; DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); EXCEPTION WHEN OTHERS THEN put_line('-- skip: '||l_table_name||' by global. reason: '||SQLERRM); END; IF l_cnt > 0 THEN l_columns_list := get_columns_list(i.table_name); put_line('SELECT TO_CHAR(SYSDATE, ''YYYY-MM-DD/HH24:MI:SS'') time_stamp1 FROM DUAL;'); put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => ''^^script..sql'', action_name => ''GLOBAL'');'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('''||i.table_name||' ^^subst_var.^^subst_var.time_stamp1.'');'); put_line('-- select: '||l_table_name||' by global. count(*): '||l_cnt); put_line('SET TERM ON;'); put_line('PRO ^^subst_var.^^subst_var.time_stamp1. '||l_table_name||';'); put_line('SET TERM OFF;'); -- global html IF :output_type IN ('H', 'B') THEN l_tbls_h := l_tbls_h + 1; put_line('SPO ^^prefix4._'||l_file_name||'.html;'); put_header('^^prefix4.', NULL); describe_table(i.table_name); put_line('SET HEA ON PAGES 25 MARK HTML ON TABLE "" SPOOL OFF;'); put_line('SELECT ROWNUM "#", v.* FROM (SELECT /*+ NO_MERGE */ '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE ROWNUM <= ^^max_rows_threshold.) v;'); put_line('SET HEA OFF PAGES 0 MARK HTML OFF;'); put_footer; put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; -- global csv IF :output_type IN ('C', 'B') THEN l_tbls_c := l_tbls_c + 1; put_line('SPO ^^prefix4._'||l_file_name||'.csv;'); put_line('SET HEA ON PAGES 50000 LIN 32767 LONGC 4000 COLSEP '','';'); put_line('SELECT '||l_columns_list||' FROM '||l_table_name||'^^my_dblink. WHERE ROWNUM <= ^^max_rows_threshold.;'); put_line('SET HEA OFF PAGES 0 LIN 2000 LONGC 2000 COLSEP '' '';'); put_line('SPO OFF;'); ELSE put_line('-- output_type: '||:output_type); END IF; put_line('EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL);'); put_line('EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);'); put_line('/*********************************************************************************/'); ELSIF l_cnt = 0 THEN put_line('-- skip: '||l_table_name||' by global. reason: COUNT(*) = 0'); END IF; END LOOP; put_line('/*********************************************************************************/'); IF l_tbls_h > 0 THEN put_line('HOS zip -m ^^prefix4._html ^^prefix4._*.html'); put_line('HOS unzip -l ^^prefix4._html'); END IF; IF l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix4._csv ^^prefix4._*.csv'); put_line('HOS unzip -l ^^prefix4._csv'); END IF; IF l_tbls_h + l_tbls_c > 0 THEN put_line('HOS zip -m ^^prefix. ^^prefix4._*.zip'); put_line('HOS unzip -l ^^prefix.'); put_line('/*********************************************************************************/'); END IF; END; ELSE put_line('-- license: '||:olicense); put_line('-- output_type: '||:output_type); END IF; END; / PRO SET TERM ON ECHO OFF FEED 6 VER ON SHOW OFF HEA ON LIN 80 NUM 10 NEWP 1 PAGES 14 LONG 80 LONGC 80 SQLC MIX TAB ON TRIMS OFF TI OFF TIMI OFF ARRAY 15 NUMF "" SQLP SQL> SUF sql BLO . RECSEP WR APPI OFF SERVEROUT OFF AUTOT OFF;; PRO PRO PRO PRO ^^prefix._*.zip files have been created. PRO SET DEF ON;; SPO OFF; /**************************************************************************************************/ -- end EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => NULL, action_name => NULL); EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => NULL); SET TERM ON ECHO OFF FEED 6 VER ON SHOW OFF HEA ON LIN 80 NUM 10 NEWP 1 PAGES 14 LONG 80 LONGC 80 SQLC MIX TAB ON TRIMS OFF TI OFF TIMI OFF ARRAY 15 NUMF "" SQLP SQL> SUF sql BLO . RECSEP WR APPI OFF SERVEROUT OFF AUTOT OFF; PRO PRO ^^prefix1._driver.sql file has been created. PRO @^^prefix1._driver.sql SET DEF ON; SET DEF ^; HOS zip -m ^^prefix1._log ^^prefix1._*.sql sqldx.log HOS unzip -l ^^prefix1._log HOS zip -m ^^prefix. ^^prefix1._log.zip PRO PRO ^^module. files have been created. PRO HOS unzip -l ^^prefix. CL COL; SET DEF ON; UNDEF 1 2 3 script module mos_doc doc_ver doc_date dbid input_sql_id input_license input_output_type current_time prefix prefix1 prefix2 prefix3 sql_id signature signaturef olicense output_type signature signaturef my_dblink;