admin scripts

10g-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/active_session_waits.sql-- Author : DR Timothy S Hall-- Description : Displays information on the current wait states for all active database sessions.-- Requirements : Access to the V$ views.-- Call Syntax : @active_session_waits-- Last Modified: 21/12/2004-- -----------------------------------------------------------------------------------SET LINESIZE 250SET PAGESIZE 1000

COLUMN username FORMAT A15COLUMN osuser FORMAT A15COLUMN sid FORMAT 99999COLUMN serial# FORMAT 9999999COLUMN wait_class FORMAT A15COLUMN state FORMAT A19COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username, a.osuser, a.sid, a.serial#, d.spid AS process_id, a.wait_class, a.seconds_in_wait, a.state, a.blocking_session, a.blocking_session_status, a.module, TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_timeFROM v$session a, v$process dWHERE a.paddr = d.addrAND a.status = 'ACTIVE'ORDER BY 1,2;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/db_usage_hwm.sql-- Author : DR Timothy S Hall-- Description : Displays high water mark statistics.-- Requirements : Access to the DBA views.-- Call Syntax : @db_usage_hwm-- Last Modified: 26-NOV-2004-- -----------------------------------------------------------------------------------

COLUMN name FORMAT A40COLUMN highwater FORMAT 999999999999COLUMN last_value FORMAT 999999999999SET PAGESIZE 24

SELECT hwm1.name, hwm1.highwater, hwm1.last_valueFROM dba_high_water_mark_statistics hwm1WHERE hwm1.version = (SELECT MAX(hwm2.version) FROM dba_high_water_mark_statistics hwm2 WHERE hwm2.name = hwm1.name)ORDER BY hwm1.name;

COLUMN FORMAT DEFAULT

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/dynamic_memory.sql-- Author : DR Timothy S Hall-- Description : Displays the values of the dynamically memory pools.-- Requirements : Access to the V$ views.-- Call Syntax : @dynamic_memory-- Last Modified: 08-NOV-2004-- -----------------------------------------------------------------------------------

COLUMN name FORMAT A40COLUMN value FORMAT A40

SELECT name, valueFROM v$parameterWHERE SUBSTR(name, 1, 1) = '_'ORDER BY name;


-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/feature_usage.sql-- Author : DR Timothy S Hall-- Description : Displays feature usage statistics.-- Requirements : Access to the DBA views.-- Call Syntax : @feature_usage-- Last Modified: 26-NOV-2004

-- -----------------------------------------------------------------------------------

COLUMN name FORMAT A50COLUMN detected_usages FORMAT 999999999999

SELECT u1.name, u1.detected_usagesFROM dba_feature_usage_statistics u1WHERE u1.version = (SELECT MAX(u2.version) FROM dba_feature_usage_statistics u2 WHERE u2.name = u1.name)ORDER BY u1.name;


-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql-- Author : DR Timothy S Hall-- Description : Generates AWR reports for all snapsots between the specified start and end point.-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.-- Call Syntax : Create the directory with the appropriate path.-- Adjust the start and end snapshots as required.-- @generate_multiple_awr_reports.sql-- Last Modified: 02/08/2007-- -----------------------------------------------------------------------------------CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/';

DECLARE -- Adjust before use. l_snap_start NUMBER := 1; l_snap_end NUMBER := 10; l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR'; l_last_snap NUMBER := NULL; l_dbid v$database.dbid%TYPE; l_instance_number v$instance.instance_number%TYPE; l_file UTL_FILE.file_type; l_file_name VARCHAR(50);

BEGIN SELECT dbid INTO l_dbid FROM v$database;

SELECT instance_number INTO l_instance_number

FROM v$instance; FOR cur_snap IN (SELECT snap_id FROM dba_hist_snapshot WHERE instance_number = l_instance_number AND snap_id BETWEEN l_snap_start AND l_snap_end ORDER BY snap_id) LOOP IF l_last_snap IS NOT NULL THEN l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767); FOR cur_rep IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id))) LOOP UTL_FILE.put_line(l_file, cur_rep.output); END LOOP; UTL_FILE.fclose(l_file); END IF; l_last_snap := cur_snap.snap_id; END LOOP; EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END;/

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/job_classes.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about job classes.-- Requirements : Access to the DBA views.-- Call Syntax : @job_classes-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 200

COLUMN service FORMAT A20COLUMN comments FORMAT A40

SELECT job_class_name, resource_consumer_group, service, logging_level,

log_history, commentsFROM dba_scheduler_job_classesORDER BY job_class_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/job_programs.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about job programs.-- Requirements : Access to the DBA views.-- Call Syntax : @job_programs-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 250

COLUMN owner FORMAT A20COLUMN program_name FORMAT A30COLUMN program_action FORMAT A50COLUMN comments FORMAT A40

SELECT owner, program_name, program_type, program_action, number_of_arguments, enabled, commentsFROM dba_scheduler_programsORDER BY owner, program_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/job_schedules.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about job schedules.-- Requirements : Access to the DBA views.-- Call Syntax : @job_schedules-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 250

COLUMN owner FORMAT A20COLUMN schedule_name FORMAT A30COLUMN start_date FORMAT A35COLUMN repeat_interval FORMAT A50COLUMN end_date FORMAT A35

COLUMN comments FORMAT A40

SELECT owner, schedule_name, start_date, repeat_interval, end_date, commentsFROM dba_scheduler_schedulesORDER BY owner, schedule_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/jobs.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler job information.-- Requirements : Access to the DBA views.-- Call Syntax : @jobs-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 200

COLUMN owner FORMAT A20COLUMN next_run_date FORMAT A35

SELECT owner, job_name, enabled, job_class, next_run_dateFROM dba_scheduler_jobsORDER BY owner, job_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/jobs_running.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information for running jobs.-- Requirements : Access to the DBA views.-- Call Syntax : @jobs_running-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 200

COLUMN owner FORMAT A20

SELECT owner,

job_name, running_instance, elapsed_timeFROM dba_scheduler_running_jobsORDER BY owner, job_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/lock_tree.sql-- Author : DR Timothy S Hall-- Description : Displays information on all database sessions with the username-- column displayed as a heirarchy if locks are present.-- Requirements : Access to the V$ views.-- Call Syntax : @lock_tree-- Last Modified: 18-MAY-2005-- -----------------------------------------------------------------------------------SET LINESIZE 500SET PAGESIZE 1000

COLUMN username FORMAT A15COLUMN machine FORMAT A25COLUMN logon_time FORMAT A20

SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username, s.osuser, s.sid, s.serial#, s.lockwait, s.status, s.module, s.machine, s.program, TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_timeFROM v$session sCONNECT BY PRIOR s.sid = s.blocking_sessionSTART WITH s.blocking_session IS NULL;

SET PAGESIZE 14

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/segment_advisor.sql

-- Author : DR Timothy S Hall-- Description : Displays segment advice for the specified segment.-- Requirements : Access to the DBMS_ADVISOR package.-- Call Syntax : Object-type = "tablespace":-- @segment_advisor.sql tablespace (tablespace-name) null-- Object-type = "table" or "index":-- @segment_advisor.sql (object-type) (object-owner) (object-name)-- Last Modified: 08-APR-2005-- -----------------------------------------------------------------------------------SET SERVEROUTPUT ON SIZE 1000000SET LINESIZE 200SET VERIFY OFF

DECLARE l_object_id NUMBER; l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK'; l_object_type VARCHAR2(32767) := UPPER('&1'); l_attr1 VARCHAR2(32767) := UPPER('&2'); l_attr2 VARCHAR2(32767) := UPPER('&3');BEGIN IF l_attr2 = 'NULL' THEN l_attr2 := NULL; END IF;

DBMS_ADVISOR.create_task ( advisor_name => 'Segment Advisor', task_name => l_task_name);

DBMS_ADVISOR.create_object ( task_name => l_task_name, object_type => l_object_type, attr1 => l_attr1, attr2 => l_attr2, attr3 => NULL, attr4 => 'null', attr5 => NULL, object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter ( task_name => l_task_name, parameter => 'RECOMMEND_ALL', value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => l_task_name);

FOR cur_rec IN (SELECT f.impact, o.type, o.attr1, o.attr2, f.message, f.more_info

FROM dba_advisor_findings f JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name WHERE f.task_name = l_task_name ORDER BY f.impact DESC) LOOP DBMS_OUTPUT.put_line('..'); DBMS_OUTPUT.put_line('Type : ' || cur_rec.type); DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1); DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2); DBMS_OUTPUT.put_line('Message : ' || cur_rec.message); DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info); END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace); DBMS_ADVISOR.delete_task(task_name => l_task_name);END;/

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/services.sql-- Author : DR Timothy S Hall-- Description : Displays information about database services.-- Requirements : Access to the DBA views.-- Call Syntax : @services-- Last Modified: 05/11/2004-- -----------------------------------------------------------------------------------SET LINESIZE 200COLUMN name FORMAT A30COLUMN network_name FORMAT A50

SELECT name, network_nameFROM dba_servicesORDER BY name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/session_waits.sql-- Author : DR Timothy S Hall

-- Description : Displays information on all database session waits.-- Requirements : Access to the V$ views.-- Call Syntax : @session_waits-- Last Modified: 11/03/2005-- -----------------------------------------------------------------------------------SET LINESIZE 200SET PAGESIZE 1000

COLUMN username FORMAT A20COLUMN event FORMAT A30COLUMN wait_class FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.stateFROM v$session_wait sw, v$session sWHERE s.sid = sw.sidORDER BY sw.seconds_in_wait DESC;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/10g/sga_buffers.sql-- Author : DR Timothy S Hall-- Description : Displays the status of buffers in the SGA.-- Requirements : Access to the v$ and DBA views.-- Call Syntax : @sga_buffers-- Last Modified: 27/07/2005-- -----------------------------------------------------------------------------------SET LINESIZE 200COLUMN object_name FORMAT A30

SELECT t.name AS tablespace_name, o.object_name, SUM(DECODE(bh.status, 'free', 1, 0)) AS free, SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur, SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur, SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr, SUM(DECODE(bh.status, 'read', 1, 0)) AS read, SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec, SUM(DECODE(bh.status, 'irec', 1, 0)) AS irecFROM v$bh bh JOIN dba_objects o ON o.object_id = bh.objd JOIN v$tablespace t ON t.ts# = bh.ts#

GROUP BY t.name, o.object_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/monitoring/window_groups.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about window groups.-- Requirements : Access to the DBA views.-- Call Syntax : @window_groups-- Last Modified: 05/11/2004-- -----------------------------------------------------------------------------------SET LINESIZE 250


SELECT window_group_name, enabled, number_of_windows, commentsFROM dba_scheduler_window_groupsORDER BY window_group_name;

SELECT window_group_name, window_nameFROM dba_scheduler_wingroup_membersORDER BY window_group_name, window_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/monitoring/windows.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about windows.-- Requirements : Access to the DBA views.-- Call Syntax : @windows-- Last Modified: 05/11/2004-- -----------------------------------------------------------------------------------SET LINESIZE 250


SELECT window_name,

resource_plan, enabled, active, commentsFROM dba_scheduler_windowsORDER BY window_name;

11g

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/11g/identify_trace_file.sql-- Author : DR Timothy S Hall-- Description : Displays the name of the trace file associated with the current session.-- Requirements : Access to the V$ views.-- Call Syntax : @identify_trace_file-- Last Modified: 23/08/2008-- -----------------------------------------------------------------------------------SET LINESIZE 100COLUMN value FORMAT A60

SELECT valueFROM v$diag_infoWHERE name = 'Default Trace File';

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/11g/job_credentials.sql-- Author : DR Timothy S Hall-- Description : Displays scheduler information about job credentials.-- Requirements : Access to the DBA views.-- Call Syntax : @job_credentials-- Last Modified: 23/08/2008-- -----------------------------------------------------------------------------------COLUMN credential_name FORMAT A25COLUMN username FORMAT A20COLUMN windows_domain FORMAT A20

SELECT credential_name, username, windows_domainFROM dba_scheduler_credentialsORDER BY credential_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/11g/memory_target_advice.sql-- Author : DR Timothy S Hall-- Description : Provides information to help tune the MEMORY_TARGET parameter.-- Requirements : Access to the DBA views.-- Call Syntax : @memory_target_advice-- Last Modified: 23/08/2008-- -----------------------------------------------------------------------------------SELECT *FROM v$memory_target_adviceORDER BY memory_size;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/11g/temp_free_space.sql-- Author : DR Timothy S Hall-- Description : Displays information about temporary tablespace usage.-- Requirements : Access to the DBA views.-- Call Syntax : @temp_free_space-- Last Modified: 23/08/2008-- -----------------------------------------------------------------------------------SELECT *FROM dba_temp_free_space;

constraints...

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/disable_chk.sql-- Author : DR Timothy S Hall-- Description : Disables all check constraints for a specified table, or all tables.-- Call Syntax : @disable_chk (table-name or all) (schema-name)-- Last Modified: 28/01/2001

-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'FROM all_constraints aWHERE a.constraint_type = 'C'AND a.owner = UPPER('&2');AND a.table_name = DECODE(UPPER('&1'),'ALL',a.table_name,UPPER('&1'));

SPOOL OFF

-- Comment out following line to prevent immediate [email protected]

SET PAGESIZE 14SET FEEDBACK ONSET VERIFY ON

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/disable_fk.sql-- Author : DR Timothy S Hall-- Description : Disables all Foreign Keys belonging to the specified table, or all tables.-- Call Syntax : @disable_fk (table-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'FROM all_constraints aWHERE a.constraint_type = 'R'AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))AND a.owner = Upper('&2');

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/disable_pk.sql-- Author : DR Timothy S Hall-- Description : Disables the Primary Key for the specified table, or all tables.-- Call Syntax : @disable_pk (table-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE PRIMARY KEY;'FROM all_constraints aWHERE a.constraint_type = 'P'AND a.owner = Upper('&2')AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/disable_ref_fk.sql-- Author : DR Timothy S Hall-- Description : Disables all Foreign Keys referencing a specified table, or all tables.-- Call Syntax : @disable_ref_fk (table-name) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFF

SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";' enable_constraintsFROM all_constraints aWHERE a.owner = Upper('&2')AND a.constraint_type = 'R'AND a.r_constraint_name IN (SELECT a1.constraint_name FROM all_constraints a1 WHERE a1.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1')) AND a1.owner = Upper('&2'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/enable_chk.sql-- Author : DR Timothy S Hall-- Description : Enables all check constraints for a specified table, or all tables.-- Call Syntax : @enable_chk (table-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'FROM all_constraints aWHERE a.constraint_type = 'C'AND a.owner = Upper('&2');AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,UPPER('&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/enable_fk.sql-- Author : DR Timothy S Hall-- Description : Enables all Foreign Keys belonging to the specified table, or all tables.-- Call Syntax : @enable_fk (table-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'FROM all_constraints aWHERE a.constraint_type = 'R'AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))AND a.owner = Upper('&2');

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/enable_pk.sql-- Author : DR Timothy S Hall-- Description : Enables the Primary Key for the specified table, or all tables.-- Call Syntax : @disable_pk (table-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE PRIMARY KEY;'FROM all_constraints aWHERE a.constraint_type = 'P'AND a.owner = Upper('&2')AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/constraints/enable_ref_fk.sql-- Author : DR Timothy S Hall-- Description : Enables all Foreign Keys referencing a specified table, or all tables.-- Call Syntax : @enable_ref_fk (table-name) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'FROM all_constraints aWHERE a.owner = Upper('&2')AND a.constraint_type = 'R'AND a.r_constraint_name IN (SELECT a1.constraint_name FROM all_constraints a1 WHERE a1.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1')) AND a1.owner = Upper('&2'));

SPOOL OFF

-- Comment out following line to prevent immediate run

@temp.sql


miscellaneous...

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/analyze_all.sql-- Author : DR Timothy S Hall-- Description : Outdated script to analyze all tables for the specified schema.-- Comment : Use DBMS_UTILITY.ANALYZE_SCHEMA or DBMS_STATS.GATHER_SCHEMA_STATS if your server allows it.-- Call Syntax : @ananlyze_all (schema-name)-- Last Modified: 26/02/2002-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ANALYZE TABLE "' || table_name || '" COMPUTE STATISTICS;'FROM all_tablesWHERE owner = Upper('&1')ORDER BY 1;

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/column_comments.sql-- Author : DR Timothy S Hall-- Description : Displays comments associate with specific tables.-- Requirements : Access to the DBA views.-- Call Syntax : @column_comments (schema) (table-name)-- Last Modified: 15/07/2000

-- -----------------------------------------------------------------------------------SET VERIFY OFFSET PAGESIZE 100COLUMN column_name FORMAT A20COLUMN comments FORMAT A50

SELECT column_name, commentsFROM dba_col_commentsWHERE owner = UPPER('&1')AND table_name = UPPER('&2')ORDER BY column_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/comments.sql-- Author : DR Timothy S Hall-- Description : Displays all comments for the specified table and its columns.-- Call Syntax : @comments (table-name) (schema-name)-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------PROMPTSET VERIFY OFFSET FEEDBACK OFFSET LINESIZE 255SET PAGESIZE 1000

SELECT a.table_name "Table", a.table_type "Type", Substr(a.comments,1,200) "Comments"FROM all_tab_comments aWHERE a.table_name = Upper('&1')AND a.owner = Upper('&2');

SELECT a.column_name "Column", Substr(a.comments,1,200) "Comments"FROM all_col_comments aWHERE a.table_name = Upper('&1')AND a.owner = Upper('&2');

SET VERIFY ONSET FEEDBACK ONSET PAGESIZE 14PROMPT

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compare_schemas.sql-- Author : Ottar Sorland (Oracle UK)-- Description : See file.-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------/* This Code entry comes from Ottar Sorland, a consultant for Oracle UK in Bracknell, United Kingdom.

This script compares the object definitions in the current schema to that of a remote schema.The remote schema is defined using a database link.THE SCRIPT COMPARES THE FOLLOWING:- Existence of tables- Existence of columns- Column definitions- Existence of indexes- Index definitions (column usage)- Existence of constraints- Constraint definitions (table, type and reference)- Constraint column usage (for unique, primary key and foreign keys)- Check constraint definitions- Existence of triggers- Definition of triggers- Existence of procedure/packages/functions- Definition of procedures/packages/functions(Ie. the script does not do a complete check, it does not for examplecheck any grants, synonyms, clusters or storage definitions).The script drops and creates a few temporary objects prefixed withthe first 3 letter combination (AAA - ZZZ) that does not conflict with any existing objects.If you find ways of improving this script or have any comments and/orproblems, please send a mail to the author.This script has been tested on Oracle 7.3.*/undef prexundef prefxundef aundef thisuserundef bundef REMOTESCHEMAundef REMOTEPASSW undef connstring undef cundef todaysdatevariable prefx varchar2(3)declarei number ;j number ;k number ;cnt number;begin

for i in ascii('A') .. ascii('Z') loop for j in ascii('A') .. ascii('Z') loop for k in ascii('A') .. ascii('Z') loop select count(*) into cnt from user_objects where object_name like chr(i)||chr(j)||chr(k)||'%'; if cnt = 0 then :prefx := chr(i)||chr(j)||chr(k); return; end if; end loop; end loop; end loop;end;/column a new_val prexset verify offset linesize 132set feedback offselect :prefx a from dual;column b new_val thisuserselect user b from dual;column c new_val todaysdateselect to_char(sysdate,'DD-MON-YYYY HH24:MI') c from dual;accept REMOTESCHEMA char prompt 'Enter remote username:'accept REMOTEPASSW char prompt 'Enter remote password:' hideaccept connstring char prompt 'Enter remote connectstring:'spool dbdiff.txtPROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT SCHEMA DEFINITION DIFFERENCES &todaysdate PROMPT PROMPT this schema: &thisuser PROMPT remote schema: &remoteschema.@&connstringPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPTPROMPTcreate database link &prex.lnk connect to &REMOTESCHEMA identified by &REMOTEPASSW using '&CONNSTRING';PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT TABLES MISSING IN THIS SCHEMA:PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~create table &prex.common_tables asselect table_name from user_TAbles@&prex.lnkintersectselect table_name from user_tables;select table_name from user_TAbles@&prex.lnkminusselect table_name from &prex.common_tables;PROMPTPROMPT

PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT TABLES MISSING IN REMOTE SCHEMA:PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name from user_TAbles where table_name not like '&prex.%'minusselect table_name from user_tables@&prex.lnk;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT COLUMNS MISSING IN THIS SCHEMA FOR COMMON TABLESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name,column_name from user_tab_columns@&prex.lnkwhere table_name in(select table_name from &prex.common_tables)minusselect table_name,column_name from user_tab_columns where table_name in(select table_name from &prex.common_tables);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT COLUMNS MISSING IN REMOTE SCHEMA FOR COMMON TABLESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name,column_name from user_tab_columnswhere table_name in(select table_name from &prex.common_tables)minusselect table_name,column_name from user_tab_columns@&prex.lnk where table_name in(select table_name from &prex.common_tables);

create table &prex.diff_cols1( TABLE_NAME VARCHAR2(30),COLUMN_NAME VARCHAR2(30),DATA_TYPE VARCHAR2(9),DATA_LENGTH NUMBER,DATA_PRECISION NUMBER,DATA_SCALE NUMBER,NULLABLE VARCHAR2(1),COLUMN_ID NUMBER,DEFAULT_LENGTH NUMBER,DATA_DEFAULT varchar2(2000));

create table &prex.diff_cols2( TABLE_NAME VARCHAR2(30),COLUMN_NAME VARCHAR2(30),DATA_TYPE VARCHAR2(9),DATA_LENGTH NUMBER,DATA_PRECISION NUMBER,DATA_SCALE NUMBER,NULLABLE VARCHAR2(1),COLUMN_ID NUMBER,

DEFAULT_LENGTH NUMBER,DATA_DEFAULT varchar2(2000));

declarecursor c1 isselectl.TABLE_NAME , l.COLUMN_NAME, l.DATA_TYPE , l.DATA_LENGTH, l.DATA_PRECISION , l.DATA_SCALE , l.NULLABLE, l.COLUMN_ID , l.DEFAULT_LENGTH , l.DATA_DEFAULT

from user_tab_columns l,&prex.common_tables cwhere c.table_name=l.table_name ;TYPE rec is record (TABLE_NAME VARCHAR2(30),COLUMN_NAME VARCHAR2(30),DATA_TYPE VARCHAR2(9),DATA_LENGTH NUMBER,DATA_PRECISION NUMBER,DATA_SCALE NUMBER,NULLABLE VARCHAR2(1),COLUMN_ID NUMBER,DEFAULT_LENGTH NUMBER,DATA_DEFAULT varchar2(2000)

);c rec;beginopen c1;loop

fetch c1 into c; exit when c1%NOTFOUND; insert into &prex.diff_cols1 values (c.table_name,c.column_name,c.data_type,c.data_length, c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, c.DEFAULT_LENGTH, c.DATA_DEFAULT);end loop;end;/declarecursor c1 isselectl.TABLE_NAME , l.COLUMN_NAME, l.DATA_TYPE , l.DATA_LENGTH, l.DATA_PRECISION , l.DATA_SCALE , l.NULLABLE, l.COLUMN_ID , l.DEFAULT_LENGTH , l.DATA_DEFAULT

from user_tab_columns@&prex.lnk l,&prex.common_tables cwhere c.table_name=l.table_name ;

TYPE rec is record (TABLE_NAME VARCHAR2(30),COLUMN_NAME VARCHAR2(30),DATA_TYPE VARCHAR2(9),DATA_LENGTH NUMBER,DATA_PRECISION NUMBER,DATA_SCALE NUMBER,NULLABLE VARCHAR2(1),COLUMN_ID NUMBER,DEFAULT_LENGTH NUMBER,DATA_DEFAULT varchar2(2000)

);c rec;beginopen c1;loop

fetch c1 into c; exit when c1%NOTFOUND; insert into &prex.diff_cols2 values (c.table_name,c.column_name,c.data_type,c.data_length, c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, c.DEFAULT_LENGTH, c.DATA_DEFAULT);end loop;end;/column table_name format a20column column_name format a20column param format a15column local_value format a20column remote_value format a20set arraysize 1set maxdata 32000PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT DIFFERENCE IN COLUMN-DEFSPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select l.table_name,l.column_name,'DATA_DEFAULT' param ,l.DATA_DEFAULT local_value, r.DATA_DEFAULT remote_valuefrom &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.DATA_DEFAULT != r.DATA_DEFAULTunionselect l.table_name,l.column_name,'DATA_TYPE',l.data_type,r.data_type from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.data_type != r.data_typeunionselect l.table_name,l.column_name,'DATA_LENGTH',to_char(l.data_length),to_char(r.data_length) from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.data_length != r.data_lengthunionselect l.table_name,l.column_name,'DATA_PRECISION',

to_char(l.DATA_PRECISION),to_char(r.DATA_PRECISION) from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.DATA_PRECISION != r.DATA_PRECISIONunionselect l.table_name,l.column_name,'DATA_SCALE',to_char(l.DATA_SCALE),to_char(r.DATA_SCALE) from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.DATA_SCALE != r.DATA_SCALEunionselect l.table_name,l.column_name,'NULLABLE',l.NULLABLE,r.NULLABLE from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.NULLABLE != r.NULLABLEunionselect l.table_name,l.column_name,'COLUMN_ID',to_char(l.COLUMN_ID),to_char(r.COLUMN_ID) from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.COLUMN_ID != r.COLUMN_IDunionselect l.table_name,l.column_name,'DEFAULT_LENGTH',to_char(l.DEFAULT_LENGTH),to_char(r.DEFAULT_LENGTH) from &prex.diff_cols1 l, &prex.diff_cols2 r where l.table_name=r.table_name and l.column_name=r.column_name and l.DEFAULT_LENGTH != r.DEFAULT_LENGTHorder by 1,2/ create table &prex.common_indexes asselect table_name, index_name from user_indexes@&prex.lnkwhere table_name in (select table_name from &prex.common_tables)intersectselect table_name, INdex_name from user_indexeswhere table_name in (select table_name from &prex.common_tables);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT INDEXES MISSING IN THIS SCHEMA FOR COMMON TABLESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name, index_name from user_indexes@&prex.lnkwhere table_name in (select table_name from &prex.common_tables)minusselect table_name, index_name from &prex.common_indexes;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT INDEXES MISSING IN REMOTE SCHEMA FOR COMMON TABLESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select table_name, index_name from user_indexeswhere table_name in (select table_name from &prex.common_tables)minusselect table_name, index_name from &prex.common_indexes;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT COMMON INDEXES WITH DIFFERENT UNIQUENESSPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select a.table_name, a.index_name, a.uniqueness local, b.uniqueness remotefrom user_indexes a, user_indexes@&prex.lnk bwhere a.index_name = b.index_nameand a.uniqueness != b.uniquenessand (a.table_name, a.index_name) in(select table_name, index_name from &prex.common_indexes);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT INDEX COLUMNS MISSING IN THIS SCHEMA FOR COMMON INDEXESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select index_name, column_name from user_ind_columns@&prex.lnkwhere (table_name,index_name) in (select table_name,index_name from &prex.common_indexes)minusselect index_name, column_name from user_ind_columnswhere (table_name,index_name) in (select table_name,index_name from &prex.common_indexes);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT INDEX COLUMNS MISSING IN REMOTE SCHEMA FOR COMMON INDEXESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select index_name, column_name from user_ind_columnswhere (table_name,index_name) in (select table_name,index_name from &prex.common_indexes)minusselect index_name, column_name from user_ind_columns@&prex.lnkwhere (table_name,index_name) in (select table_name,index_name from &prex.common_indexes);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT INDEX COLUMNS POSITIONED DIFFERENTLY FOR COMMON INDEXESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select a.index_name, a.column_name, a.column_position local, b.column_position remote from user_ind_columns a,

user_ind_columns@&prex.lnk bwhere (a.table_name,a.index_name) in (select table_name,index_name from &prex.common_indexes) and b.index_name = a.index_nameand b.table_name = a.table_nameand a.column_name = b.column_nameand a.column_position != b.column_position;

PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT CONSTRAINTS MISSING IN THIS SCHEMA FOR COMMON TABLESPROMPT (WORKS ONLY FOR CONSTRAINT WITH NON SYSTEM GENERATED NAMES)PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name,constraint_name from user_constraints@&prex.lnkwhere constraint_name not like 'SYS%' and table_name in(select table_name from &prex.common_tables)minusselect table_name,constraint_name from user_constraints where constraint_name not like 'SYS%' and table_name in(select table_name from &prex.common_tables);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT CONSTRAINTS MISSING IN REMOTE SCHEMA FOR COMMON TABLESPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select table_name,constraint_name from user_constraintswhere constraint_name not like 'SYS%' and table_name in(select table_name from &prex.common_tables)minusselect table_name,constraint_name from user_constraints@&prex.lnk where constraint_name not like 'SYS%' and table_name in(select table_name from &prex.common_tables);PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT COMMON CONSTRAINTS, TYPE MISMATCHPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select a.constraint_name,a.constraint_type local_type, b.constraint_type remote_typefrom user_constraints a, user_constraints@&prex.lnk b wherea.table_name = b.table_name anda.constraint_name=b.constraint_name and a.constraint_type !=b.constraint_type;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT COMMON CONSTRAINTS, TABLE MISMATCHPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select a.constraint_name,a.table_name,b.table_name fromuser_constraints a, user_constraints@&prex.lnk b,(select z.constraint_name from (select constraint_name, table_name from useR_constraints unionselect constraint_name, table_name from user_constraints@&prex.lnk) zgroup by constraint_name having count(*) >1) qwhere a.constraint_name = q.constraint_name and b.constraint_name=q.constraint_nameand a.table_name != b.table_name;create table &prex.comcons asselect constraint_name, constraint_type, table_name from useR_constraints intersect select constraint_name, constraint_type, table_name from user_constraints@&prex.lnk;delete from &prex.comcons where constraint_name in (select constraint_name from &prex.comcons group by constraint_name having count(*) > 1);delete from &prex.comcons where constraint_name like 'SYS%';PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT DIFFERENCES IN COLUMN USAGE FOR CONSTRAINT DEFS PROMPT (Unique key, Primary Key, Foreign key)PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~declarecursor c1 isselect a.constraint_name,a.position,a.column_name,b.constraint_type from user_cons_columns a, &prex.comcons bwhere a.constraint_name=b.constraint_nameunionselect a.constraint_name,a.position,a.column_name,b.constraint_type from user_cons_columns@&prex.lnk a, &prex.comcons bwhere a.constraint_name=b.constraint_nameminus(select a.constraint_name,a.position,a.column_name,b.constraint_type from user_cons_columns a, &prex.comcons b where a.constraint_name=b.constraint_nameintersectselect a.constraint_name,a.position,a.column_name,b.constraint_type from user_cons_columns@&prex.lnk a, &prex.comcons b where a.constraint_name=b.constraint_name);i binary_integer;beginfor c in c1 loop dbms_output.put_line('COLUMN USAGE DIFFERENCE FOR '||c.constraint_type|| ' CONSTRAINT '||c.constraint_name); dbms_output.put_line('. Local columns:'); i:=1; for c2 in (select column_name col from user_cons_columns where constraint_name=c.constraint_name order by position) loop

dbms_output.put_line('. '||c2.col); end loop; i:=1; dbms_output.put_line('. Remote columns:'); for c3 in (select column_name col from user_cons_columns@&prex.lnk where constraint_name=c.constraint_name ) loop dbms_output.put_line('. '||c3.col); end loop;end loop;end;/PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT DIFFERENCES IN CHECK CONSTRAINT DEFS PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set serveroutput ondeclare cursor c1 is select constraint_name,constraint_type,table_name from &prex.comcons where constraint_type='C';cons varchar2(50);tab1 varchar2(50);tab2 varchar2(50);search1 varchar2(32000);search2 varchar2(32000);begindbms_output.enable(100000);for c in c1 loop select search_condition into search1 from user_constraints where constraint_name=c.constraint_name; select search_condition into search2 from user_constraints@&prex.lnk where constraint_name=c.constraint_name; if search1 != search2 then dbms_output.put_line('Check constraint '||c.constraint_name|| ' defined differently!'); dbms_output.put_line('. Local definition:'); dbms_output.put_line('. '||search1); dbms_output.put_line('. Remote definition:'); dbms_output.put_line('. '||search2); end if;end loop;end;/PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT TRIGGERS MISSING IN REMOTE SCHEMAPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select trigger_name from user_Triggers minus select trigger_name from user_Triggers@&prex.lnk;

PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT TRIGGERS MISSING IN THIS SCHEMAPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select trigger_name from user_Triggers@&prex.lnk minus select trigger_name from user_Triggers@&prex.lnk;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT TRIGGER DEFINITION DIFFERENCES ON COMMON TRIGGERSPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set serveroutput ondeclarecursor c1 is select TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME,REFERENCING_NAMES,rtrim(WHEN_CLAUSE,' '),STATUS, rtrim(replace(description,'"&thisuser".',null),' ') DESCRIPTION,TRIGGER_BODY from user_Triggers;nam1 varchar2(30);type1 varchar2(16);event1 varchar2(26);table1 varchar2(30);ref1 varchar2(87);when1 varchar2(2000);status1 varchar2(8);desc1 varchar2(2000);body1 varchar2(32000);type2 varchar2(16);event2 varchar2(26);table2 varchar2(30);ref2 varchar2(87);when2 varchar2(2000);status2 varchar2(8);desc2 varchar2(2000);body2 varchar2(32000);pr_head boolean;begindbms_output.enable(100000);open c1;loopfetch c1 into nam1,type1,event1,table1,ref1,when1,status1,desc1,body1;exit when c1%notfound;begin

select TRIGGER_TYPE,TRIGGERING_EVENT, TABLE_NAME,REFERENCING_NAMES,rtrim(WHEN_CLAUSE,' '),STATUS, rtrim(replace(description,upper('"&remoteschema".'),null),' ') DESCRIPTION, TRIGGER_BODY into type2,event2,table2,ref2,when2,status2,desc2,body2 from user_Triggers@&prex.lnk where trigger_name=nam1;

pr_head := FALSE; if table1 != table2 then dbms_output.put_line('T R I G G E R : '||nam1); dbms_output.put_line('-------------------------------------------------'|| '-----------------------'); pr_head := TRUE; dbms_output.put_line(' '); dbms_output.put_line('DEFINED ON DIFFERENT TABLES!'); dbms_output.put_line('. This table_name : '||table1); dbms_output.put_line('. Remote table_name: '||table2); end if; if event1 != event2 then if not pr_head then dbms_output.put_line('T R I G G E R : '||nam1); dbms_output.put_line('-------------------------------------------------'|| '-----------------------'); pr_head := TRUE; end if; dbms_output.put_line(' '); dbms_output.put_line('DEFINED FOR DIFFERENT EVENTS!'); dbms_output.put_line('. This event: '||event1); dbms_output.put_line('. Remote event: '||event2); end if; if type1 != type2 then if not pr_head then dbms_output.put_line('T R I G G E R : '||nam1); dbms_output.put_line('-------------------------------------------------'|| '-----------------------'); pr_head := TRUE; end if; dbms_output.put_line(' '); dbms_output.put_line('DIFFERENT TYPES!'); dbms_output.put_line('. This type: '||type1); dbms_output.put_line('. Remote: '||type2); end if; if ref1 != ref2 then if not pr_head then dbms_output.put_line('T R I G G E R : '||nam1); dbms_output.put_line('-------------------------------------------------'|| '-----------------------'); pr_head := TRUE; end if; dbms_output.put_line(' '); dbms_output.put_line('DIFFERENT REFERENCES!'); dbms_output.put_line('. This ref: '||ref1); dbms_output.put_line('. Remote: '||ref2); end if; if when1 != when2 then dbms_output.put_line(' '); if not pr_head then dbms_output.put_line('T R I G G E R : '||nam1); dbms_output.put_line('-------------------------------------------------'||

'-----------------------'); pr_head := TRUE; end if; dbms_output.put_line('DIFFERENT WHEN CLAUSES!');

dbms_output.put_line('. Local when_clause:'); dbms_output.put_line(when1); dbms_output.put_line('. Remote when_clause: '); dbms_output.put_line(when2); end if; if status1 != status2 then dbms_output.put_line(' '); dbms_output.put_line('DIFFERENT STATUS!'); dbms_output.put_line('. Local status: '||status1); dbms_output.put_line('. Remote status: '||status2); end if;if replace(desc1,chr(10),'') != replace(desc2,chr(10),'') then

dbms_output.put_line(' '); dbms_output.put_line('DIFFERENT DESCRIPTIONS!'); dbms_output.put_line('Local definition: '); dbms_output.put_line(desc1); dbms_output.put_line('Remote definition: '); dbms_output.put_line(desc2); end if; if body1 != body2 then dbms_output.put_line(' '); dbms_output.put_line('THE PL/SQL BLOCKS ARE DIFFERENT! '); dbms_output.put_line(' '); end if; exception when NO_DATA_FOUND then null; when others then raise_application_error(-20010,SQLERRM);end;

end loop;end;/PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT MISSING PROCEDURES/PACKAGES/FUNCTIONS IN REMOTE SCHEMAPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select distinct name,type from user_source minus select distinct name,type from user_source@&prex.lnk;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT MISSING PROCEDURES/PACKAGES/FUNCTIONS IN LOCAL SCHEMAPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select distinct name,type from user_source@&prex.lnk minus select distinct name,type from user_source;create table &prex.comcod asselect distinct name,type from user_source intersect select distinct name,type from user_source@&prex.lnk;PROMPTPROMPT

PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT PROCEDURES/PACKAGES/FUNCTIONS WITH DIFFERENT DEFINITIONSPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select distinct q.name Object_name,q.type Object_type from (select a.name,a.type,a.line,a.text from user_source a, &prex.comcod b where a.name=b.name union select a.name,a.type,a.line,a.text from user_source@&prex.lnk a, &prex.comcod b

where a.name=b.name minus(select a.name,a.type,a.line,a.text from user_source a, &prex.comcod b where a.name=b.name intersect

select a.name,a.type,a.line,a.text from user_source@&prex.lnk a, &prex.comcod b where a.name=b.name )) q;

PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT VIEWS MISSING IN THIS SCHEMA:PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~create table &prex.common_views asselect view_name from user_views@&prex.lnkintersectselect view_name from user_views;select view_name from user_views@&prex.lnkminusselect view_name from &prex.common_views;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT VIEWS MISSING IN REMOTE SCHEMA:PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select view_name from user_viewsminusselect view_name from user_views@&prex.lnk;PROMPTPROMPTPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROMPT VIEWS WITH DIFFERENCES IN THE DEFINITIONPROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~declaredef1 varchar2(32000);def2 varchar2(32000);len1 number;len2 number;i number;

cursor c1 is select view_name from &prex.common_views;begindbms_output.enable(100000);for c in c1 loop select text,text_length into def1,len1 from user_Views where view_name=c.view_name; select text,text_length into def2,len2 from user_Views@&prex.lnk where view_name=c.view_name; i := 1; def1:=replace(def1,' ',''); def2:=replace(def2,' ',''); if def1 != def2 or length(def1) != length(def2) then dbms_output.put_line(lpad('-',35+length(c.view_name),'-')); dbms_output.put_line('| '||c.view_name || ' |'); dbms_output.put_line(lpad('-',35+length(c.view_name),'-')); dbms_output.put_line('Local text_length: ' || to_char(len1)); dbms_output.put_line('Remote text_length): ' || to_char(len2)); dbms_output.put_line(' '); i := 1; while i <= length(def1) loop if substr(def1,i,240) != substr(def2,i,240) then dbms_output.put_line('Difference at offset ' || to_char(i)); dbms_output.put_line(' local: ' || substr(def1,i,240)); dbms_output.put_line(' remote: ' || substr(def2,i,240)); end if; i := i + 240; end loop; end if; if length(def2) > length(def1) then dbms_output.put_line('Remote longer than Local. Next 255 bytes: '); dbms_output.put_line(substr(def2,length(def1),255)); end if;end loop;end;/drop database link &prex.lnk;drop table &prex.comcod;drop table &prex.diff_cols1;drop table &prex.diff_cols2;drop table &prex.common_tables;drop table &prex.common_indexes;drop table &prex.common_views;drop table &prex.ind;drop table &prex.ind1;drop table &prex.ind2;drop table &prex.comcons;spool offset verify onset feedback onundef prex

undef prefxundef aundef thisuserundef bundef REMOTESCHEMAundef REMOTEPASSW undef connstring undef cundef todaysdate

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid objects for specified schema, or all schema.-- Requirements : Requires all other "Compile_All" scripts.-- Call Syntax : @compile_all (schema-name or all)-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------@Compile_All_Specs &&1@Compile_All_Bodies &&1@Compile_All_Procs &&1@Compile_All_Funcs &&1@Compile_All_Views &&1

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_bodies.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid package bodies for specified schema, or all schema.-- Call Syntax : @compile_all_bodies (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'FROM all_objects aWHERE a.object_type = 'PACKAGE BODY'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_funcs.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid functions for specified schema, or all schema.-- Call Syntax : @compile_all_funcs (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE a.object_type = 'FUNCTION'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_procs.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid procedures for specified schema, or all schema.-- Call Syntax : @compile_all_procs (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE a.object_type = 'PROCEDURE'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_specs.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid package specifications for specified schema, or all schema.-- Call Syntax : @compile_all_specs (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE a.object_type = 'PACKAGE'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_trigs.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid triggers for specified schema, or all schema.-- Call Syntax : @compile_all_trigs (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE a.object_type = 'TRIGGER'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/compile_all_views.sql-- Author : DR Timothy S Hall-- Description : Compiles all invalid views for specified schema, or all schema.-- Call Syntax : @compile_all_views (schema-name or all)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE a.object_type = 'VIEW'AND a.status = 'INVALID'AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF



CREATE OR REPLACE PACKAGE conversion_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/conversion_api-- Author : DR Timothy S Hall-- Description : Provides some base conversion functions.-- Ammedments :-- When Who What-- =========== ======== =================================================-- 10-SEP-2003 Tim Hall Initial Creation-- --------------------------------------------------------------------------

FUNCTION to_base(p_dec IN NUMBER, p_base IN NUMBER) RETURN VARCHAR2;

FUNCTION to_dec (p_str IN VARCHAR2, p_from_base IN NUMBER DEFAULT 16) RETURN NUMBER;

FUNCTION to_hex(p_dec IN NUMBER) RETURN VARCHAR2;

FUNCTION to_bin(p_dec IN NUMBER) RETURN VARCHAR2;

FUNCTION to_oct(p_dec IN NUMBER) RETURN VARCHAR2;

END conversion_api;/SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY conversion_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/conversion_api

-- Author : DR Timothy S Hall-- Description : Provides some base conversion functions.-- Ammedments :-- When Who What-- =========== ======== =================================================-- 10-SEP-2003 Tim Hall Initial Creation-- --------------------------------------------------------------------------

-- ----------------------------------------------------------------------------FUNCTION to_base(p_dec IN NUMBER, p_base IN NUMBER) RETURN VARCHAR2 IS-- ----------------------------------------------------------------------------

l_str VARCHAR2(255) DEFAULT NULL;l_num NUMBER DEFAULT p_dec;l_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';

BEGINIF (TRUNC(p_dec) <> p_dec OR p_dec < 0) THEN

RAISE PROGRAM_ERROR;END IF;LOOP

l_str := SUBSTR(l_hex, MOD(l_num,p_base)+1, 1) || l_str;l_num := TRUNC(l_num/p_base);EXIT WHEN (l_num = 0);

END LOOP;RETURN l_str;

END to_base;-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------FUNCTION to_dec (p_str IN VARCHAR2, p_from_base IN NUMBER DEFAULT 16) RETURN NUMBER IS-- ----------------------------------------------------------------------------

l_num NUMBER DEFAULT 0;l_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';

BEGINFOR i IN 1 .. LENGTH(p_str) LOOP

l_num := l_num * p_from_base + INSTR(l_hex,UPPER(SUBSTR(p_str,i,1)))-1;

END LOOP;RETURN l_num;

END to_dec;-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------

FUNCTION to_hex(p_dec IN NUMBER) RETURN VARCHAR2 IS-- ----------------------------------------------------------------------------BEGIN

RETURN to_base(p_dec, 16);END to_hex;-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------FUNCTION to_bin(p_dec IN NUMBER) RETURN VARCHAR2 IS-- ----------------------------------------------------------------------------BEGIN

RETURN to_base(p_dec, 2);END to_bin;-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------FUNCTION to_oct(p_dec IN NUMBER) RETURN VARCHAR2 IS-- ----------------------------------------------------------------------------BEGIN

RETURN to_base(p_dec, 8);END to_oct;-- ----------------------------------------------------------------------------

END conversion_api;/SHOW ERRORS

CREATE OR REPLACE PACKAGE csv AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/cvs.sql-- Author : DR Timothy S Hall-- Description : Basic CSV API. For usage notes see:-- http://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php---- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';--

-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');---- Requirements : UTL_FILE, DBMS_SQL-- Ammedments :-- When Who What-- =========== ======== =================================================-- 14-MAY-2005 Tim Hall Initial Creation-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_query IN VARCHAR2);END csv;/SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/cvs.sql-- Author : DR Timothy S Hall-- Description : Basic CSV API. For usage notes see:-- http://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php---- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';---- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');---- Requirements : UTL_FILE, DBMS_SQL-- Ammedments :-- When Who What-- =========== ======== =================================================-- 14-MAY-2005 Tim Hall Initial Creation-- --------------------------------------------------------------------------

g_sep VARCHAR2(5) := ',';

PROCEDURE generate (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_query IN VARCHAR2) AS l_cursor PLS_INTEGER; l_rows PLS_INTEGER; l_col_cnt PLS_INTEGER; l_desc_tab DBMS_SQL.desc_tab; l_buffer VARCHAR2(32767);

l_file UTL_FILE.file_type;

BEGIN l_cursor := DBMS_SQL.open_cursor; DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 ); END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

-- Output the column names. FOR i IN 1 .. l_col_cnt LOOP IF i > 1 THEN UTL_FILE.put(l_file, g_sep); END IF; UTL_FILE.put(l_file, l_desc_tab(i).col_name); END LOOP; UTL_FILE.new_line(l_file);

-- Output the data. LOOP EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

FOR i IN 1 .. l_col_cnt LOOP IF i > 1 THEN UTL_FILE.put(l_file, g_sep); END IF;

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer); UTL_FILE.put(l_file, l_buffer); END LOOP; UTL_FILE.new_line(l_file); END LOOP;

UTL_FILE.fclose(l_file);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; IF DBMS_SQL.is_open(l_cursor) THEN DBMS_SQL.close_cursor(l_cursor); END IF; RAISE;END generate;

END csv;/SHOW ERRORS

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/dict_comments.sql-- Author : DR Timothy S Hall-- Description : Displays comments associate with specific tables.-- Requirements : Access to the DBA views.-- Call Syntax : @dict_comments (table-name or partial match)-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------PROMPTSET VERIFY OFFSET FEEDBACK OFFSET LINESIZE 255SET PAGESIZE 1000

SELECT a.table_name "Table", Substr(a.comments,1,200) "Comments"FROM dictionary aWHERE a.table_name LIKE Upper('%&1%');

SET VERIFY ONSET FEEDBACK ONSET PAGESIZE 14PROMPT

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/drop_all.sql-- Author : DR Timothy S Hall-- Description : Drops all objects within the current schema.-- Call Syntax : @drop_all-- Last Modified: 20/01/2006-- Notes : Loops a maximum of 5 times, allowing for failed drops due to dependencies.-- Quits outer loop if no drops were atempted.-- -----------------------------------------------------------------------------------SET SERVEROUTPUT ONDECLARE l_count NUMBER; l_cascade VARCHAR2(20);BEGIN << dependency_failure_loop >> FOR i IN 1 .. 5 LOOP EXIT dependency_failure_loop WHEN l_count = 0; l_count := 0; FOR cur_rec IN (SELECT object_name, object_type FROM user_objects) LOOP BEGIN l_count := l_count + 1; l_cascade := NULL;

IF cur_rec.object_type = 'TABLE' THEN l_cascade := ' CASCADE CONSTRAINTS'; END IF; EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' || l_cascade; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; -- Comment out the following line if you are pre-10g, or want to preserve the recyclebin contents. EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; DBMS_OUTPUT.put_line('Pass: ' || i || ' Drops: ' || l_count); END LOOP;END;/

CREATE OR REPLACE PACKAGE BODY err AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/err.pkb-- Author : DR Timothy S Hall-- Description : A simple mechanism for logging error information to a table.-- Requirements : err.pks, dsp.pks, dsp.pkb and schema definied in err.pks-- Ammedments :-- When Who What-- =========== ======== =================================================-- 17-JUL-2003 Tim Hall Initial Creation-- --------------------------------------------------------------------------

-- Package Variables g_logs_on BOOLEAN := TRUE; g_date_format VARCHAR2(50) := 'DD-MON-YYYY HH24:MI:SS';

-- Exposed Methods

-- -------------------------------------------------------------------------- PROCEDURE reset_defaults IS -- -------------------------------------------------------------------------- BEGIN g_logs_on := TRUE; g_date_format := 'DD-MON-YYYY HH24:MI:SS'; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE logs_on IS -- -------------------------------------------------------------------------- BEGIN g_logs_on := TRUE; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE logs_off IS -- -------------------------------------------------------------------------- BEGIN g_logs_on := FALSE; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS') IS -- -------------------------------------------------------------------------- BEGIN g_date_format := p_date_format; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2, p_error_level IN NUMBER DEFAULT 5, p_error_user IN VARCHAR2 DEFAULT USER) IS -- -------------------------------------------------------------------------- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN IF g_logs_on THEN INSERT INTO error_logs (id, prefix, data, error_level, created_date, created_by) VALUES (error_logs_seq.NEXTVAL,

p_prefix, p_data, p_error_level, SYSDATE, p_error_user);

COMMIT; END IF; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE line (p_data IN VARCHAR2, p_error_level IN NUMBER DEFAULT 5, p_error_user IN VARCHAR2 DEFAULT USER) IS -- -------------------------------------------------------------------------- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN line (p_prefix => NULL, p_data => p_data, p_error_level => p_error_level, p_error_user => p_error_user); END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE display (p_error_level IN NUMBER DEFAULT NULL, p_error_user IN VARCHAR2 DEFAULT NULL, p_from_date IN DATE DEFAULT NULL, p_to_date IN DATE DEFAULT NUll) IS -- -------------------------------------------------------------------------- CURSOR c_errors IS SELECT * FROM error_logs WHERE error_level = NVL(p_error_level, error_level) AND created_by = NVL(p_error_user, created_by) AND created_date >= NVL(p_from_date, created_date) AND created_date <= NVL(p_to_date, created_date) ORDER BY id; BEGIN FOR cur_rec IN c_errors LOOP dsp.line(cur_rec.prefix, cur_rec.data); END LOOP; END; -- --------------------------------------------------------------------------

END err;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE dsp AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/dsp.pks-- Author : DR Timothy S Hall-- Description : An extension of the DBMS_OUTPUT package.-- Requirements : http://www.oracle-base.com/dba/miscellaneous/dsp.pkb-- Ammedments :-- When Who What-- =========== ======== =================================================-- 08-JAN-2002 Tim Hall Initial Creation-- 04-APR-2005 Tim Hall Store last call. Add get_last_prefix and-- get_last_data to allow retrieval.-- Switch from date to timestamp for greater accuracy.-- --------------------------------------------------------------------------

PROCEDURE reset_defaults;

PROCEDURE show_output_on; PROCEDURE show_output_off;

PROCEDURE show_date_on; PROCEDURE show_date_off;

PROCEDURE line_wrap_on; PROCEDURE line_wrap_off;

PROCEDURE set_max_width (p_width IN NUMBER);

PROCEDURE set_date_format (p_date_format IN VARCHAR2);

PROCEDURE file_output_on (p_file_dir IN VARCHAR2 DEFAULT NULL, p_file_name IN VARCHAR2 DEFAULT NULL);

PROCEDURE file_output_off;

FUNCTION get_last_prefix RETURN VARCHAR2;

FUNCTION get_last_data RETURN VARCHAR2;

PROCEDURE line (p_data IN VARCHAR2); PROCEDURE line (p_data IN NUMBER); PROCEDURE line (p_data IN BOOLEAN); PROCEDURE line (p_data IN DATE, p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF');

PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2); PROCEDURE line (p_prefix IN VARCHAR2, p_data IN NUMBER); PROCEDURE line (p_prefix IN VARCHAR2, p_data IN BOOLEAN); PROCEDURE line (p_prefix IN VARCHAR2, p_data IN DATE, p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF');

END dsp;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY dsp AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/dsp.pkb-- Author : DR Timothy S Hall-- Description : An extension of the DBMS_OUTPUT package.-- Requirements : http://www.oracle-base.com/dba/miscellaneous/dsp.pks-- Ammedments :-- When Who What-- =========== ======== =================================================-- 08-JAN-2002 Tim Hall Initial Creation.-- 04-APR-2005 Tim Hall Store last call. Add get_last_prefix and-- get_last_data to allow retrieval.-- Switch from date to timestamp for greater accuracy.-- --------------------------------------------------------------------------

-- Package Variablesg_show_output BOOLEAN := FALSE;g_show_date BOOLEAN := FALSE;g_line_wrap BOOLEAN := TRUE;g_max_width NUMBER(10) := 255;g_date_format VARCHAR2(32767) := 'DD-MON-YYYY HH24:MI:SS.FF';g_file_dir VARCHAR2(32767) := NULL;g_file_name VARCHAR2(32767) := NULL;g_last_prefix VARCHAR2(32767) := NULL;g_last_data VARCHAR2(32767) := NULL;

-- Hidden MethodsPROCEDURE display (p_prefix IN VARCHAR2, p_data IN VARCHAR2);PROCEDURE wrap_line (p_data IN VARCHAR2);PROCEDURE output (p_data IN VARCHAR2);

-- Exposed Methods

-- --------------------------------------------------------------------------PROCEDURE reset_defaults IS-- --------------------------------------------------------------------------BEGIN g_show_output := FALSE; g_show_date := FALSE; g_line_wrap := TRUE; g_max_width := 255; g_date_format := 'DD-MON-YYYY HH24:MI:SS.FF';END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE show_output_on IS-- --------------------------------------------------------------------------BEGIN g_show_output := TRUE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE show_output_off IS-- --------------------------------------------------------------------------BEGIN g_show_output := FALSE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE show_date_on IS-- --------------------------------------------------------------------------BEGIN g_show_date := TRUE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE show_date_off IS

-- --------------------------------------------------------------------------BEGIN g_show_date := FALSE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line_wrap_on IS-- --------------------------------------------------------------------------BEGIN g_line_wrap := TRUE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line_wrap_off IS-- --------------------------------------------------------------------------BEGIN g_line_wrap := FALSE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE set_max_width (p_width IN NUMBER) IS-- --------------------------------------------------------------------------BEGIN g_max_width := p_width;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE set_date_format (p_date_format IN VARCHAR2) IS-- --------------------------------------------------------------------------BEGIN g_date_format := p_date_format;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE file_output_on (p_file_dir IN VARCHAR2 DEFAULT NULL, p_file_name IN VARCHAR2 DEFAULT NULL) IS-- --------------------------------------------------------------------------BEGIN g_file_dir := p_file_dir; g_file_name := p_file_name;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE file_output_off IS-- --------------------------------------------------------------------------BEGIN g_file_dir := NULL; g_file_name := NULL;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_last_prefix RETURN VARCHAR2 IS-- --------------------------------------------------------------------------BEGIN RETURN g_last_prefix;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_last_data RETURN VARCHAR2 IS-- --------------------------------------------------------------------------BEGIN RETURN g_last_data;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_data IN VARCHAR2) IS

-- --------------------------------------------------------------------------BEGIN display (NULL, p_data);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_data IN NUMBER) IS-- --------------------------------------------------------------------------BEGIN display (NULL, p_data);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_data IN BOOLEAN) IS-- --------------------------------------------------------------------------BEGIN line (NULL, p_data);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_data IN DATE, p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF') IS-- --------------------------------------------------------------------------BEGIN line (NULL, p_data, p_format);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2) IS-- --------------------------------------------------------------------------BEGIN display (p_prefix, p_data);END;

-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_prefix IN VARCHAR2, p_data IN NUMBER) IS-- --------------------------------------------------------------------------BEGIN display (p_prefix, TO_CHAR(p_data));END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_prefix IN VARCHAR2, p_data IN BOOLEAN) IS-- --------------------------------------------------------------------------BEGIN IF p_data THEN display (p_prefix, 'TRUE'); ELSE display (p_prefix, 'FALSE'); END IF;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE line (p_prefix IN VARCHAR2, p_data IN DATE, p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF') IS-- --------------------------------------------------------------------------BEGIN display (p_prefix, TO_CHAR(p_data, p_format));END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE display (p_prefix IN VARCHAR2, p_data IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_data VARCHAR2(32767) := p_data;BEGIN

g_last_prefix := p_prefix; g_last_data := p_data; IF g_show_output THEN IF l_data IS NULL THEN l_data := '<NULL>'; END IF;

IF p_prefix IS NOT NULL THEN l_data := p_prefix || ' : ' || l_data; END IF;

IF g_show_date THEN l_data := TO_CHAR(SYSTIMESTAMP, g_date_format) || ' : ' || l_data; END IF;

IF Length(l_data) > g_max_width THEN IF g_line_wrap THEN wrap_line (l_data); ELSE l_data := SUBSTR(l_data, 1, g_max_width); output (l_data); END IF; ELSE output (l_data); END IF; END IF;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE wrap_line (p_data IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_data VARCHAR2(32767) := p_data;BEGIN LOOP display (NULL, SUBSTR(l_data, 1, g_max_width)); l_data := SUBSTR(l_data, g_max_width + 1); EXIT WHEN l_data IS NULL; END LOOP;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE output (p_data IN VARCHAR2) IS-- --------------------------------------------------------------------------BEGIN IF g_file_dir IS NULL OR g_file_name IS NULL THEN DBMS_OUTPUT.put_line(p_data);

ELSE DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen (g_file_dir, g_file_name, 'A'); UTL_FILE.put_line(l_file, p_data); UTL_FILE.fclose (l_file); EXCEPTION WHEN OTHERS THEN UTL_FILE.fclose (l_file); END; END IF;END;-- --------------------------------------------------------------------------

END dsp;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE err AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/err.pks-- Author : DR Timothy S Hall-- Description : A simple mechanism for logging error information to a table.-- Requirements : err.pkb, dsp.pks, dsp.pkb and:---- CREATE TABLE error_logs (-- id NUMBER(10) NOT NULL,-- prefix VARCHAR2(50),-- data VARCHAR2(2000) NOT NULL,-- error_level NUMBER(2) NOT NULL,-- created_date DATE NOT NULL,-- created_by VARCHAR2(50) NOT NULL);---- ALTER TABLE error_logs ADD (CONSTRAINT error_logs_pk PRIMARY KEY (id));---- CREATE SEQUENCE error_logs_seq;---- Ammedments :-- When Who What-- =========== ======== =================================================-- 17-JUL-2003 Tim Hall Initial Creation-- --------------------------------------------------------------------------


PROCEDURE logs_on;

PROCEDURE logs_off;

PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS');

PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2, p_error_level IN NUMBER DEFAULT 5, p_error_user IN VARCHAR2 DEFAULT USER);

PROCEDURE line (p_data IN VARCHAR2, p_error_level IN NUMBER DEFAULT 5, p_error_user IN VARCHAR2 DEFAULT USER);

PROCEDURE display (p_error_level IN NUMBER DEFAULT NULL, p_error_user IN VARCHAR2 DEFAULT NULL, p_from_date IN DATE DEFAULT NULL, p_to_date IN DATE DEFAULT NUll);END err;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY ftp AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/ftp.pkb-- Author : DR Timothy S Hall-- Description : Basic FTP API. For usage notes see:-- http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php-- Requirements : http://www.oracle-base.com/dba/miscellaneous/ftp.pks-- Ammedments :-- When Who What-- =========== ======== =================================================-- 14-AUG-2003 Tim Hall Initial Creation-- 10-MAR-2004 Tim Hall Add convert_crlf procedure.-- Incorporate CRLF conversion functionality into-- put_local_ascii_data and put_remote_ascii_data-- functions.-- Make get_passive function visible.-- Added get_direct and put_direct procedures.-- 23-DEC-2004 Tim Hall The get_reply procedure was altered to deal with-- banners starting with 4 white spaces. This fix is-- a small variation on the resolution provided by-- Gary Mason who spotted the bug.-- 10-NOV-2005 Tim Hall Addition of get_reply after doing a transfer to

-- pickup the 226 Transfer complete message. This-- allows gets and puts with a single connection.-- Issue spotted by Trevor Woolnough.-- 03-OCT-2006 Tim Hall Add list, rename, delete, mkdir, rmdir procedures.-- 12-JAN-2007 Tim Hall A final call to get_reply was added to the get_remote%-- procedures to allow multiple transfers per connection.-- 15-Jan-2008 Tim Hall login: Include timeout parameter (suggested by Dmitry Bogomolov).-- 21-Jan-2008 Tim Hall put_%: "l_pos < l_clob_len" to "l_pos <= l_clob_len" to prevent-- potential loss of one character for single-byte files or files-- sized 1 byte bigger than a number divisible by the buffer size-- (spotted by Michael Surikov).-- 23-Jan-2008 Tim Hall send_command: Possible solution for ORA-29260 errors included,-- but commented out (suggested by Kevin Phillips).-- 12-Feb-2008 Tim Hall put_local_binary_data and put_direct: Open file with "wb" for-- binary writes (spotted by Dwayne Hoban). -- 03-Mar-2008 Tim Hall list: get_reply call and close of passive connection added-- (suggested by Julian, Bavaria).-- 12-Jun-2008 Tim Hall A final call to get_reply was added to the put_remote%-- procedures, but commented out. If uncommented, it may cause the-- operation to hang, but it has been reported (morgul) to allow-- multiple transfers per connection.-- get_reply: Moved to pakage specification.-- 24-Jun-2008 Tim Hall get_remote% and put_remote%: Exception handler added to close the passive-- connection and reraise the error (suggested by Mark Reichman).-- 22-Apr-2009 Tim Hall get_remote_ascii_data: Remove unnecessary logout (suggested by John Duncan).-- get_reply and list: Handle 400 messages as well as 500 messages (suggested by John Duncan).-- logout: Added a call to UTL_TCP.close_connection, so not necessary to close-- any connections manually (suggested by Victor Munoz).-- get_local_*_data: Check for zero length files to prevent exception (suggested by Daniel)-- nlst: Added to return list of file names only (suggested by Julian and John Duncan)-- --------------------------------------------------------------------------

g_reply t_string_table := t_string_table();g_binary BOOLEAN := TRUE;g_debug BOOLEAN := TRUE;g_convert_crlf BOOLEAN := TRUE;

PROCEDURE debug (p_text IN VARCHAR2); -- --------------------------------------------------------------------------FUNCTION login (p_host IN VARCHAR2, p_port IN VARCHAR2, p_user IN VARCHAR2, p_pass IN VARCHAR2, p_timeout IN NUMBER := NULL) RETURN UTL_TCP.connection IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN g_reply.delete; l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout => p_timeout); get_reply (l_conn); send_command(l_conn, 'USER ' || p_user); send_command(l_conn, 'PASS ' || p_pass); RETURN l_conn;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_passive (p_conn IN OUT NOCOPY UTL_TCP.connection) RETURN UTL_TCP.connection IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_reply VARCHAR2(32767); l_host VARCHAR(100); l_port1 NUMBER(10); l_port2 NUMBER(10);BEGIN send_command(p_conn, 'PASV'); l_reply := g_reply(g_reply.last); l_reply := REPLACE(SUBSTR(l_reply, INSTR(l_reply, '(') + 1, (INSTR(l_reply, ')')) - (INSTR(l_reply, '('))-1), ',', '.'); l_host := SUBSTR(l_reply, 1, INSTR(l_reply, '.', 1, 4)-1);

l_port1 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 4)+1, (INSTR(l_reply, '.', 1, 5)-1) - (INSTR(l_reply, '.', 1, 4)))); l_port2 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 5)+1)); l_conn := utl_tcp.open_connection(l_host, 256 * l_port1 + l_port2);

return l_conn;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE logout(p_conn IN OUT NOCOPY UTL_TCP.connection, p_reply IN BOOLEAN := TRUE) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'QUIT', p_reply); UTL_TCP.close_connection(p_conn);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE send_command (p_conn IN OUT NOCOPY UTL_TCP.connection, p_command IN VARCHAR2, p_reply IN BOOLEAN := TRUE) IS-- -------------------------------------------------------------------------- l_result PLS_INTEGER;BEGIN l_result := UTL_TCP.write_line(p_conn, p_command); -- If you get ORA-29260 after the PASV call, replace the above line with the following line. -- l_result := UTL_TCP.write_text(p_conn, p_command || utl_tcp.crlf, length(p_command || utl_tcp.crlf)); IF p_reply THEN get_reply(p_conn); END IF;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE get_reply (p_conn IN OUT NOCOPY UTL_TCP.connection) IS-- -------------------------------------------------------------------------- l_reply_code VARCHAR2(3) := NULL;BEGIN LOOP g_reply.extend; g_reply(g_reply.last) := UTL_TCP.get_line(p_conn, TRUE); debug(g_reply(g_reply.last));

IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(g_reply(g_reply.last), 1, 3); END IF; IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN RAISE_APPLICATION_ERROR(-20000, g_reply(g_reply.last)); ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP;EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_local_ascii_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN CLOB IS-- -------------------------------------------------------------------------- l_bfile BFILE; l_data CLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_bfile := BFILENAME(p_dir, p_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

IF DBMS_LOB.getlength(l_bfile) > 0 THEN DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile)); END IF;

DBMS_LOB.fileclose(l_bfile);

RETURN l_data;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_local_binary_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN BLOB IS-- --------------------------------------------------------------------------

l_bfile BFILE; l_data BLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_bfile := BFILENAME(p_dir, p_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); IF DBMS_LOB.getlength(l_bfile) > 0 THEN DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile)); END IF; DBMS_LOB.fileclose(l_bfile);

RETURN l_data;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN CLOB IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_amount PLS_INTEGER; l_buffer VARCHAR2(32767); l_data CLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call);

l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_file, TRUE); --logout(l_conn, FALSE); BEGIN LOOP l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767); DBMS_LOB.writeappend(l_data, l_amount, l_buffer); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_TCP.close_connection(l_conn); get_reply(p_conn);

RETURN l_data;

EXCEPTION WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------FUNCTION get_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN BLOB IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_amount PLS_INTEGER; l_buffer RAW(32767); l_data BLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call);

l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_file, TRUE); BEGIN LOOP l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767); DBMS_LOB.writeappend(l_data, l_amount, l_buffer); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_TCP.close_connection(l_conn); get_reply(p_conn);

RETURN l_data;


-- --------------------------------------------------------------------------PROCEDURE put_local_ascii_data (p_data IN CLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_out_file UTL_FILE.file_type; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_clob_len INTEGER;BEGIN l_clob_len := DBMS_LOB.getlength(p_data);

l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767); WHILE l_pos <= l_clob_len LOOP DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF;

UTL_FILE.put(l_out_file, l_buffer); UTL_FILE.fflush(l_out_file); l_pos := l_pos + l_amount; END LOOP; UTL_FILE.fclose(l_out_file);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE put_local_binary_data (p_data IN BLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_out_file UTL_FILE.file_type; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER;BEGIN l_blob_len := DBMS_LOB.getlength(p_data);

l_out_file := UTL_FILE.fopen(p_dir, p_file, 'wb', 32767); WHILE l_pos <= l_blob_len LOOP DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_out_file, l_buffer, TRUE); UTL_FILE.fflush(l_out_file); l_pos := l_pos + l_amount; END LOOP; UTL_FILE.fclose(l_out_file);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE put_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN CLOB) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_result PLS_INTEGER; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_clob_len INTEGER;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_file, TRUE); l_clob_len := DBMS_LOB.getlength(p_data);

WHILE l_pos <= l_clob_len LOOP DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF; l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer)); UTL_TCP.flush(l_conn); l_pos := l_pos + l_amount; END LOOP;

UTL_TCP.close_connection(l_conn); -- The following line allows some people to make multiple calls from one connection. -- It causes the operation to hang for me, hence it is commented out by default.

-- get_reply(p_conn);


-- --------------------------------------------------------------------------PROCEDURE put_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN BLOB) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_result PLS_INTEGER; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_file, TRUE); l_blob_len := DBMS_LOB.getlength(p_data);

WHILE l_pos <= l_blob_len LOOP DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer); l_result := UTL_TCP.write_raw(l_conn, l_buffer, l_amount); UTL_TCP.flush(l_conn); l_pos := l_pos + l_amount; END LOOP;

UTL_TCP.close_connection(l_conn); -- The following line allows some people to make multiple calls from one connection. -- It causes the operation to hang for me, hence it is commented out by default. -- get_reply(p_conn);


-- --------------------------------------------------------------------------PROCEDURE get (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2, p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2) AS-- --------------------------------------------------------------------------BEGIN IF g_binary THEN put_local_binary_data(p_data => get_remote_binary_data (p_conn, p_from_file), p_dir => p_to_dir, p_file => p_to_file); ELSE put_local_ascii_data(p_data => get_remote_ascii_data (p_conn, p_from_file), p_dir => p_to_dir, p_file => p_to_file); END IF; END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE put (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2, p_to_file IN VARCHAR2) AS-- --------------------------------------------------------------------------BEGIN IF g_binary THEN put_remote_binary_data(p_conn => p_conn, p_file => p_to_file, p_data => get_local_binary_data(p_from_dir, p_from_file)); ELSE put_remote_ascii_data(p_conn => p_conn, p_file => p_to_file, p_data => get_local_ascii_data(p_from_dir, p_from_file)); END IF; get_reply(p_conn);END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE get_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2,

p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_out_file UTL_FILE.file_type; l_amount PLS_INTEGER; l_buffer VARCHAR2(32767); l_raw_buffer RAW(32767);BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_from_file, TRUE); IF g_binary THEN l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'wb', 32767); ELSE l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'w', 32767); END IF; BEGIN LOOP IF g_binary THEN l_amount := UTL_TCP.read_raw (l_conn, l_raw_buffer, 32767); UTL_FILE.put_raw(l_out_file, l_raw_buffer, TRUE); ELSE l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF; UTL_FILE.put(l_out_file, l_buffer); END IF; UTL_FILE.fflush(l_out_file); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_FILE.fclose(l_out_file); UTL_TCP.close_connection(l_conn);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE put_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2,

p_to_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_bfile BFILE; l_result PLS_INTEGER; l_amount PLS_INTEGER := 32767; l_raw_buffer RAW(32767); l_len NUMBER; l_pos NUMBER := 1; ex_ascii EXCEPTION;BEGIN IF NOT g_binary THEN RAISE ex_ascii; END IF;

l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_to_file, TRUE);

l_bfile := BFILENAME(p_from_dir, p_from_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); l_len := DBMS_LOB.getlength(l_bfile);

WHILE l_pos <= l_len LOOP DBMS_LOB.READ (l_bfile, l_amount, l_pos, l_raw_buffer); debug(l_amount); l_result := UTL_TCP.write_raw(l_conn, l_raw_buffer, l_amount); l_pos := l_pos + l_amount; END LOOP; DBMS_LOB.fileclose(l_bfile); UTL_TCP.close_connection(l_conn);EXCEPTION WHEN ex_ascii THEN RAISE_APPLICATION_ERROR(-20000, 'PUT_DIRECT not available in ASCII mode.'); WHEN OTHERS THEN IF DBMS_LOB.fileisopen(l_bfile) = 1 THEN DBMS_LOB.fileclose(l_bfile); END IF; RAISE;

END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE help (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'HELP', TRUE);END;

-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE ascii (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'TYPE A', TRUE); g_binary := FALSE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE binary (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'TYPE I', TRUE); g_binary := TRUE;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE list (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_list t_string_table := t_string_table(); l_reply_code VARCHAR2(3) := NULL;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'LIST ' || p_dir, TRUE);

BEGIN LOOP l_list.extend; l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE); debug(l_list(l_list.last)); IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(l_list(l_list.last), 1, 3); END IF; IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));

ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; END; l_list.delete(l_list.last); p_list := l_list; utl_tcp.close_connection(l_conn); get_reply (p_conn);END; -- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE nlst (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_list t_string_table := t_string_table(); l_reply_code VARCHAR2(3) := NULL;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'NLST ' || p_dir, TRUE);

BEGIN LOOP l_list.extend; l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE); debug(l_list(l_list.last)); IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(l_list(l_list.last), 1, 3); END IF; IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last)); ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; END; l_list.delete(l_list.last); p_list := l_list;

utl_tcp.close_connection(l_conn); get_reply (p_conn);END; -- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE rename (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from IN VARCHAR2, p_to IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RNFR ' || p_from, TRUE); send_command(p_conn, 'RNTO ' || p_to, TRUE); logout(l_conn, FALSE);END rename; -- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE delete (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'DELE ' || p_file, TRUE); logout(l_conn, FALSE);END delete; -- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE mkdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'MKD ' || p_dir, TRUE); logout(l_conn, FALSE);END mkdir;

-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE rmdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RMD ' || p_dir, TRUE); logout(l_conn, FALSE);END rmdir; -- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE convert_crlf (p_status IN BOOLEAN) AS-- --------------------------------------------------------------------------BEGIN g_convert_crlf := p_status;END;-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------PROCEDURE debug (p_text IN VARCHAR2) IS-- --------------------------------------------------------------------------BEGIN IF g_debug THEN DBMS_OUTPUT.put_line(SUBSTR(p_text, 1, 255)); END IF;END;-- --------------------------------------------------------------------------

END ftp;/SHOW ERRORS

CREATE OR REPLACE PACKAGE ftp AS-- --------------------------------------------------------------------------

-- Name : http://www.oracle-base.com/dba/miscellaneous/ftp.pks-- Author : DR Timothy S Hall-- Description : Basic FTP API. For usage notes see:-- http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php-- Requirements : UTL_TCP-- Ammedments :-- When Who What-- =========== ======== =================================================-- 14-AUG-2003 Tim Hall Initial Creation-- 10-MAR-2004 Tim Hall Add convert_crlf procedure.-- Make get_passive function visible.-- Added get_direct and put_direct procedures.-- 03-OCT-2006 Tim Hall Add list, rename, delete, mkdir, rmdir procedures.-- 15-Jan-2008 Tim Hall login: Include timeout parameter (suggestedby Dmitry Bogomolov).-- 12-Jun-2008 Tim Hall get_reply: Moved to pakage specification.-- 22-Apr-2009 Tim Hall nlst: Added to return list of file names only (suggested by Julian and John Duncan)-- --------------------------------------------------------------------------

TYPE t_string_table IS TABLE OF VARCHAR2(32767);

FUNCTION login (p_host IN VARCHAR2, p_port IN VARCHAR2, p_user IN VARCHAR2, p_pass IN VARCHAR2, p_timeout IN NUMBER := NULL) RETURN UTL_TCP.connection;

FUNCTION get_passive (p_conn IN OUT NOCOPY UTL_TCP.connection) RETURN UTL_TCP.connection;

PROCEDURE logout (p_conn IN OUT NOCOPY UTL_TCP.connection, p_reply IN BOOLEAN := TRUE);

PROCEDURE send_command (p_conn IN OUT NOCOPY UTL_TCP.connection, p_command IN VARCHAR2, p_reply IN BOOLEAN := TRUE);

PROCEDURE get_reply (p_conn IN OUT NOCOPY UTL_TCP.connection);

FUNCTION get_local_ascii_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN CLOB;

FUNCTION get_local_binary_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN BLOB;

FUNCTION get_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN CLOB;

FUNCTION get_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN BLOB;

PROCEDURE put_local_ascii_data (p_data IN CLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2);

PROCEDURE put_local_binary_data (p_data IN BLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2);

PROCEDURE put_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN CLOB);

PROCEDURE put_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN BLOB);

PROCEDURE get (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2, p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2);

PROCEDURE put (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2, p_to_file IN VARCHAR2);

PROCEDURE get_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2, p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2);

PROCEDURE put_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2, p_to_file IN VARCHAR2);

PROCEDURE help (p_conn IN OUT NOCOPY UTL_TCP.connection);

PROCEDURE ascii (p_conn IN OUT NOCOPY UTL_TCP.connection);

PROCEDURE binary (p_conn IN OUT NOCOPY UTL_TCP.connection);

PROCEDURE list (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table);

PROCEDURE nlst (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table);

PROCEDURE rename (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from IN VARCHAR2, p_to IN VARCHAR2);

PROCEDURE delete (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2);

PROCEDURE mkdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2);

PROCEDURE rmdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2);

PROCEDURE convert_crlf (p_status IN BOOLEAN);

END ftp;/SHOW ERRORS

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/login.sql-- Author : DR Timothy S Hall-- Description : Resets the SQL*Plus prompt when a new connection is made.-- Call Syntax : @login-- Last Modified: 04/03/2004-- -----------------------------------------------------------------------------------SET FEEDBACK OFFSET TERMOUT OFF

COLUMN X NEW_VALUE YSELECT LOWER(USER || '@' || instance_name) X FROM v$instance;SET SQLPROMPT '&Y> '

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

SET TERMOUT ONSET FEEDBACK ONSET LINESIZE 100

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/get_pivot.sql-- Author : DR Timothy S Hall

-- Description : Creates a function to produce a virtual pivot table with the specific values.-- Requirements : CREATE TYPE, CREATE PROCEDURE-- Call Syntax : @get_pivot.sql-- Last Modified: 13/08/2003-- -----------------------------------------------------------------------------------

CREATE OR REPLACE TYPE t_pivot AS TABLE OF NUMBER;/

CREATE OR REPLACE FUNCTION get_pivot(p_max IN NUMBER, p_step IN NUMBER DEFAULT 1) RETURN t_pivot AS l_pivot t_pivot := t_pivot();BEGIN FOR i IN 0 .. TRUNC(p_max/p_step) LOOP l_pivot.extend; l_pivot(l_pivot.last) := 1 + (i * p_step); END LOOP; RETURN l_pivot;END;/SHOW ERRORS

SELECT column_valueFROM TABLE(get_pivot(17,2));

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/gen_health.sql-- Author : DR Timothy S Hall-- Description : Miscellaneous queries to check the general health of the system.-- Call Syntax : @gen_health-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------SELECT file_id, tablespace_name, file_name, status FROM sys.dba_data_files;

SELECT file#, name, status, enabled FROM v$datafile;

SELECT * FROM v$backup;

SELECT * FROM v$recovery_status;

SELECT * FROM v$recover_file;

SELECT * FROM v$recovery_file_status;

SELECT * FROM v$recovery_log;

SELECT username, command, status, module FROM v$session;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/proc_defs.sql-- Author : DR Timothy S Hall-- Description : Lists the parameters for the specified package and procedure.-- Call Syntax : @proc_defs (package-name) (procedure-name or all)-- Last Modified: 24/09/2003-- -----------------------------------------------------------------------------------COLUMN "Object Name" FORMAT A30COLUMN ol FORMAT A2COLUMN sq FORMAT 99COLUMN "Argument Name" FORMAT A32COLUMN "Type" FORMAT A15COLUMN "Size" FORMAT A6BREAK ON ol SKIP 2SET PAGESIZE 0SET LINESIZE 200SET TRIMOUT ONSET TRIMSPOOL ONSET VERIFY OFF

SELECT object_name AS "Object Name", overload AS ol, sequence AS sq, RPAD(' ', data_level*2, ' ') || argument_name AS "Argument Name", data_type AS "Type", (CASE WHEN data_type IN ('VARCHAR2','CHAR') THEN TO_CHAR(data_length) WHEN data_scale IS NULL OR data_scale = 0 THEN TO_CHAR(data_precision) ELSE TO_CHAR(data_precision) || ',' || TO_CHAR(data_scale) END) "Size",

in_out AS "In/Out", default_valueFROM user_argumentsWHERE package_name = UPPER('&1')AND object_name = DECODE(UPPER('&2'), 'ALL', object_name, UPPER('&2'))ORDER BY object_name, overload, sequence;

SET PAGESIZE 14SET LINESIZE 80

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/rebuild_index.sql-- Author : DR Timothy S Hall-- Description : Rebuilds the specified index, or all indexes.-- Call Syntax : @rebuild_index (index-name or all) (schema-name)-- Last Modified: 28/01/2001-- -----------------------------------------------------------------------------------SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER INDEX ' || a.index_name || ' REBUILD;'FROM all_indexes aWHERE index_name = DECODE(Upper('&1'),'ALL',a.index_name,Upper('&1'))AND table_owner = Upper('&2')ORDER BY 1/

SPOOL OFF



CREATE OR REPLACE PACKAGE soap_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/soap_api-- Author : DR Timothy S Hall-- Description : SOAP related functions for consuming web services.-- Ammedments :-- When Who What-- =========== ======== =================================================

-- 04-OCT-2003 Tim Hall Initial Creation-- 23-FEB-2006 Tim Hall Parameterized the "soap" envelope tags.-- --------------------------------------------------------------------------

TYPE t_request IS RECORD ( method VARCHAR2(256), namespace VARCHAR2(256), body VARCHAR2(32767), envelope_tag VARCHAR2(30));

TYPE t_response IS RECORD( doc XMLTYPE, envelope_tag VARCHAR2(30));

FUNCTION new_request(p_method IN VARCHAR2, p_namespace IN VARCHAR2, p_envelope_tag IN VARCHAR2 DEFAULT 'SOAP-ENV') RETURN t_request;

PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request, p_name IN VARCHAR2, p_type IN VARCHAR2, p_value IN VARCHAR2);

FUNCTION invoke(p_request IN OUT NOCOPY t_request, p_url IN VARCHAR2, p_action IN VARCHAR2) RETURN t_response;

FUNCTION get_return_value(p_response IN OUT NOCOPY t_response, p_name IN VARCHAR2, p_namespace IN VARCHAR2) RETURN VARCHAR2;

END soap_api;/SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY soap_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/soap_api-- Author : DR Timothy S Hall-- Description : SOAP related functions for consuming web services.-- Ammedments :-- When Who What-- =========== ======== =================================================-- 04-OCT-2003 Tim Hall Initial Creation-- 23-FEB-2006 Tim Hall Parameterized the "soap" envelope tags.

-- --------------------------------------------------------------------------

-- ---------------------------------------------------------------------FUNCTION new_request(p_method IN VARCHAR2, p_namespace IN VARCHAR2, p_envelope_tag IN VARCHAR2 DEFAULT 'SOAP-ENV') RETURN t_request AS-- --------------------------------------------------------------------- l_request t_request;BEGIN l_request.method := p_method; l_request.namespace := p_namespace; l_request.envelope_tag := p_envelope_tag; RETURN l_request;END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request, p_name IN VARCHAR2, p_type IN VARCHAR2, p_value IN VARCHAR2) AS-- ---------------------------------------------------------------------BEGIN p_request.body := p_request.body||'<'||p_name||' xsi:type="'||p_type||'">'||p_value||'</'||p_name||'>';END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------PROCEDURE generate_envelope(p_request IN OUT NOCOPY t_request,

p_env IN OUT NOCOPY VARCHAR2) AS-- ---------------------------------------------------------------------BEGIN p_env := '<'||p_request.envelope_tag||':Envelope xmlns:'||p_request.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/" ' || 'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema">' || '<'||p_request.envelope_tag||':Body>' ||

'<'||p_request.method||' '||p_request.namespace||' '||p_request.envelope_tag||':encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">' || p_request.body || '</'||p_request.method||'>' || '</'||p_request.envelope_tag||':Body>' || '</'||p_request.envelope_tag||':Envelope>';END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------PROCEDURE show_envelope(p_env IN VARCHAR2) AS-- --------------------------------------------------------------------- i PLS_INTEGER; l_len PLS_INTEGER;BEGIN i := 1; l_len := LENGTH(p_env); WHILE (i <= l_len) LOOP DBMS_OUTPUT.put_line(SUBSTR(p_env, i, 60)); i := i + 60; END LOOP;END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------PROCEDURE check_fault(p_response IN OUT NOCOPY t_response) AS-- --------------------------------------------------------------------- l_fault_node XMLTYPE; l_fault_code VARCHAR2(256); l_fault_string VARCHAR2(32767);BEGIN l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault', 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/'); IF (l_fault_node IS NOT NULL) THEN l_fault_code := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()', 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval(); l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()',

'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval(); RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string); END IF;END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------FUNCTION invoke(p_request IN OUT NOCOPY t_request, p_url IN VARCHAR2, p_action IN VARCHAR2) RETURN t_response AS-- --------------------------------------------------------------------- l_envelope VARCHAR2(32767); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response t_response;BEGIN generate_envelope(p_request, l_envelope); show_envelope(l_envelope); l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1'); UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml'); UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope)); UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action); UTL_HTTP.write_text(l_http_request, l_envelope); l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.read_text(l_http_response, l_envelope); UTL_HTTP.end_response(l_http_response); l_response.doc := XMLTYPE.createxml(l_envelope); l_response.envelope_tag := p_request.envelope_tag; l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()', 'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"'); -- show_envelope(l_response.doc.getstringval()); check_fault(l_response); RETURN l_response;END;-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------FUNCTION get_return_value(p_response IN OUT NOCOPY t_response, p_name IN VARCHAR2,

p_namespace IN VARCHAR2) RETURN VARCHAR2 AS-- ---------------------------------------------------------------------BEGIN RETURN p_response.doc.extract('//'||p_name||'/child::text()',p_namespace).getstringval();END;-- ---------------------------------------------------------------------

END soap_api;/SHOW ERRORS

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/switch_schema.sql-- Author : DR Timothy S Hall-- Description : Allows developers to switch synonyms between schemas where a single instance-- : contains multiple discrete schemas.-- Requirements : Must be loaded into privileged user such as SYS.-- Usage : Create the package in a user that has the appropriate privileges to perform the actions (SYS)-- : Amend the list of schemas in the "reset_grants" FOR LOOP as necessary.-- : Call SWITCH_SCHEMA.RESET_GRANTS once to grant privileges to the developer role.-- : Assign the developer role to all developers.-- : Tell developers to use EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYMS ('SCHEMA-NAME'); to switch-- : there synonyms between schemas.-- Call Syntax : EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYMS ('SCHEMA-NAME');-- Last Modified: 02/06/2003-- -----------------------------------------------------------------------------------CREATE OR REPLACE PACKAGE switch_schema AS

PROCEDURE reset_grants;PROCEDURE reset_schema_synonyms (p_schema IN VARCHAR2);

END;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY switch_schema AS

PROCEDURE reset_grants ISBEGIN

FOR cur_obj IN (SELECT owner, object_name, object_type FROM all_objects WHERE owner IN ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') AND object_type IN ('TABLE','VIEW','SEQUENCE', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TYPE')) LOOP CASE WHEN cur_obj.object_type IN ('TABLE','VIEW') THEN EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || cur_obj.owner || '."' || cur_obj.object_name || '" TO developer'; WHEN cur_obj.object_type IN ('SEQUENCE') THEN EXECUTE IMMEDIATE 'GRANT SELECT ON ' || cur_obj.owner || '."' || cur_obj.object_name || '" TO developer'; WHEN cur_obj.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TYPE') THEN EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || cur_obj.owner || '."' || cur_obj.object_name || '" TO developer'; END CASE; END LOOP;END;

PROCEDURE reset_schema_synonyms (p_schema IN VARCHAR2) IS v_user VARCHAR2(30) := USER;BEGIN -- Drop all existing synonyms FOR cur_obj IN (SELECT synonym_name FROM all_synonyms WHERE owner = v_user) LOOP EXECUTE IMMEDIATE 'DROP SYNONYM ' || v_user || '."' || cur_obj.synonym_name || '"'; END LOOP;

-- Create new synonyms FOR cur_obj IN (SELECT object_name, object_type FROM all_objects WHERE owner = p_schema AND object_type IN ('TABLE','VIEW','SEQUENCE'))

LOOP EXECUTE IMMEDIATE 'CREATE SYNONYM ' || v_user || '."' || cur_obj.object_name || '" FOR ' || p_schema || '."' || cur_obj.object_name || '"'; END LOOP;END;

END;/

SHOW ERRORS

CREATE PUBLIC SYNONYM switch_schema FOR switch_schema;GRANT EXECUTE ON switch_schema TO PUBLIC;

CREATE ROLE developer;

CREATE OR REPLACE PACKAGE string_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/string_api.sql-- Author : DR Timothy S Hall-- Description : A package to hold string utilities.-- Requirements : -- Ammedments :-- When Who What-- =========== ======== =================================================-- 02-DEC-2004 Tim Hall Initial Creation-- --------------------------------------------------------------------------

-- Public typesTYPE t_split_array IS TABLE OF VARCHAR2(32767);

FUNCTION split_text (p_text IN CLOB, p_delimeter IN VARCHAR2 DEFAULT ',') RETURN t_split_array;

END string_api;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY string_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/string_api.sql-- Author : DR Timothy S Hall-- Description : A package to hold string utilities.-- Requirements : -- Ammedments :-- When Who What-- =========== ======== =================================================-- 02-DEC-2004 Tim Hall Initial Creation-- --------------------------------------------------------------------------

-- ----------------------------------------------------------------------------FUNCTION split_text (p_text IN CLOB, p_delimeter IN VARCHAR2 DEFAULT ',') RETURN t_split_array IS-- ---------------------------------------------------------------------------- l_array t_split_array := t_split_array(); l_text CLOB := p_text; l_idx NUMBER;BEGIN

l_array.delete;

IF l_text IS NULL THEN RAISE_APPLICATION_ERROR(-20000, 'P_TEXT parameter cannot be NULL'); END IF;

WHILE l_text IS NOT NULL LOOP l_idx := INSTR(l_text, p_delimeter); l_array.extend; IF l_idx > 0 THEN l_array(l_array.last) := SUBSTR(l_text, 1, l_idx - 1); l_text := SUBSTR(l_text, l_idx + 1); ELSE l_array(l_array.last) := l_text; l_text := NULL; END IF; END LOOP; RETURN l_array;END split_text;-- ----------------------------------------------------------------------------

END string_api;/

SHOW ERRORS

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/string_agg.sql-- Author : DR Timothy S Hall (based on an a method suggested by Tom Kyte).-- http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562-- Description : Aggregate function to concatenate strings.-- Call Syntax : Incorporate into queries as follows:-- COLUMN employees FORMAT A50-- -- SELECT deptno, string_agg(ename) AS employees-- FROM emp-- GROUP BY deptno;-- -- DEPTNO EMPLOYEES-- ---------- ---------------------------------------------------- 10 CLARK,KING,MILLER-- 20 SMITH,FORD,ADAMS,SCOTT,JONES-- 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD-- -- Last Modified: 20-APR-2005-- -----------------------------------------------------------------------------------CREATE OR REPLACE TYPE t_string_agg AS OBJECT( g_string VARCHAR2(32767),

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER);/SHOW ERRORS

CREATE OR REPLACE TYPE BODY t_string_agg IS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER IS BEGIN sctx := t_string_agg(NULL); RETURN ODCIConst.Success; END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN SELF.g_string := self.g_string || ',' || value; RETURN ODCIConst.Success; END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN returnValue := RTRIM(LTRIM(SELF.g_string, ','), ','); RETURN ODCIConst.Success; END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER IS BEGIN SELF.g_string := SELF.g_string || ',' || ctx2.g_string; RETURN ODCIConst.Success; END;END;/

SHOW ERRORS

CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)RETURN VARCHAR2PARALLEL_ENABLE AGGREGATE USING t_string_agg;/SHOW ERRORS

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/table_comments.sql-- Author : DR Timothy S Hall-- Description : Displays comments associate with specific tables.-- Requirements : Access to the DBA views.-- Call Syntax : @table_comments (schema or all) (table-name or partial match)-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------SET VERIFY OFFCOLUMN table_name FORMAT A30COLUMN comments FORMAT A40

SELECT table_name, commentsFROM dba_tab_commentsWHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))AND table_name LIKE UPPER('%&2%')ORDER BY table_name;

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/table_defs.sql-- Author : DR Timothy S Hall-- Description : Lists the column definitions for the specified table.-- Call Syntax : @table_defs (tablee-name or all)-- Last Modified: 24/09/2003-- -----------------------------------------------------------------------------------COLUMN column_id FORMAT 99COLUMN data_type FORMAT A10COLUMN nullable FORMAT A8COLUMN size FORMAT A6BREAK ON table_name SKIP 2SET PAGESIZE 0SET LINESIZE 200SET TRIMOUT ONSET TRIMSPOOL ON

SET VERIFY OFF

SELECT table_name, column_id, column_name, data_type, (CASE WHEN data_type IN ('VARCHAR2','CHAR') THEN TO_CHAR(data_length) WHEN data_scale IS NULL OR data_scale = 0 THEN TO_CHAR(data_precision) ELSE TO_CHAR(data_precision) || ',' || TO_CHAR(data_scale) END) "SIZE", DECODE(nullable, 'Y', '', 'NOT NULL') nullableFROM user_tab_columnsWHERE table_name = DECODE(UPPER('&1'), 'ALL', table_name, UPPER('&1'))ORDER BY table_name, column_id;

SET PAGESIZE 14SET LINESIZE 80

-- ------------------------------------------------------------------------------------- File Name : http://www.oracle-base.com/dba/miscellaneous/table_differences.sql-- Author : DR Timothy S Hall-- Description : Checks column differences between a specified table or ALL tables.-- : The comparison is done both ways so datatype/size mismatches will-- : be listed twice per column.-- : Log into the first schema-owner. Make sure a DB Link is set up to-- : the second schema owner. Use this DB Link in the definition of -- : the c_table2 cursor and amend v_owner1 and v_owner2 accordingly-- : to make output messages sensible.-- : The result is spooled to the Tab_Diffs.txt file in the working directory.-- Call Syntax : @table_differences (table-name or all)-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------SET SERVEROUTPUT ONSET LINESIZE 500SET VERIFY OFFSET FEEDBACK OFFPROMPT

SPOOL Tab_Diffs.txt

DECLARE

CURSOR c_tables IS

SELECT a.table_name FROM user_tables a WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1')); CURSOR c_table1 (p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS SELECT a.column_name, a.data_type, a.data_length, a.data_precision, a.data_scale, a.nullable FROM user_tab_columns a WHERE a.table_name = p_table_name AND a.column_name = NVL(p_column_name,a.column_name);

CURSOR c_table2 (p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS SELECT a.column_name, a.data_type, a.data_length, a.data_precision, a.data_scale, a.nullable FROM user_tab_columns@pdds a WHERE a.table_name = p_table_name AND a.column_name = NVL(p_column_name,a.column_name);

v_owner1 VARCHAR2(10) := 'DDDS2'; v_owner2 VARCHAR2(10) := 'PDDS'; v_data c_table1%ROWTYPE; v_work BOOLEAN := FALSE; BEGIN

Dbms_Output.Disable; Dbms_Output.Enable(1000000); FOR cur_tab IN c_tables LOOP v_work := FALSE; FOR cur_rec IN c_table1 (cur_tab.table_name, NULL) LOOP v_work := TRUE; OPEN c_table2 (cur_tab.table_name, cur_rec.column_name); FETCH c_table2 INTO v_data; IF c_table2%NOTFOUND THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : Present in ' || v_owner1 || ' but not in ' || v_owner2); ELSE IF cur_rec.data_type != v_data.data_type THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_TYPE differs between ' || v_owner1 || ' and ' || v_owner2); END IF;

IF cur_rec.data_length != v_data.data_length THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_LENGTH differs between ' || v_owner1 || ' and ' || v_owner2); END IF; IF cur_rec.data_precision != v_data.data_precision THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_PRECISION differs between ' || v_owner1 || ' and ' || v_owner2); END IF; IF cur_rec.data_scale != v_data.data_scale THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_SCALE differs between ' || v_owner1 || ' and ' || v_owner2); END IF; IF cur_rec.nullable != v_data.nullable THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : NULLABLE differs between ' || v_owner1 || ' and ' || v_owner2); END IF; END IF; CLOSE c_table2; END LOOP; FOR cur_rec IN c_table2 (cur_tab.table_name, NULL) LOOP v_work := TRUE; OPEN c_table1 (cur_tab.table_name, cur_rec.column_name); FETCH c_table1 INTO v_data; IF c_table1%NOTFOUND THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : Present in ' || v_owner2 || ' but not in ' || v_owner1); ELSE IF cur_rec.data_type != v_data.data_type THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_TYPE differs between ' || v_owner2 || ' and ' || v_owner1); END IF; IF cur_rec.data_length != v_data.data_length THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_LENGTH differs between ' || v_owner2 || ' and ' || v_owner1); END IF; IF cur_rec.data_precision != v_data.data_precision THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_PRECISION differs between ' || v_owner2 || ' and ' || v_owner1); END IF; IF cur_rec.data_scale != v_data.data_scale THEN Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_SCALE differs between ' || v_owner2 || ' and ' || v_owner1); END IF; IF cur_rec.nullable != v_data.nullable THEN

Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : NULLABLE differs between ' || v_owner2 || ' and ' || v_owner1); END IF; END IF; CLOSE c_table1; END LOOP; IF v_work = FALSE THEN Dbms_Output.Put_Line(cur_tab.table_name || ' does not exist!'); END IF; END LOOP;END;/

SPOOL OFF

PROMPTSET FEEDBACK ON

CREATE OR REPLACE PACKAGE trc AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/trc.pks-- Author : DR Timothy S Hall-- Description : A simple mechanism for tracing information to a table.-- Requirements : trc.pkb, dsp.pks, dsp.pkb and:---- CREATE TABLE trace_data (-- id NUMBER(10) NOT NULL,-- prefix VARCHAR2(50),-- data VARCHAR2(2000) NOT NULL,-- trc_level NUMBER(2) NOT NULL,-- created_date DATE NOT NULL,-- created_by VARCHAR2(50) NOT NULL);-- -- ALTER TABLE trace_data ADD (CONSTRAINT trc_pk PRIMARY KEY (id));---- CREATE SEQUENCE trc_seq;---- Ammedments :-- When Who What-- =========== ======== =================================================-- 08-JAN-2002 Tim Hall Initial Creation-- --------------------------------------------------------------------------


PROCEDURE trace_on; PROCEDURE trace_off;

PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS'); PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2, p_trc_level IN NUMBER DEFAULT 5, p_trc_user IN VARCHAR2 DEFAULT USER);

PROCEDURE display (p_trc_level IN NUMBER DEFAULT NULL, p_trc_user IN VARCHAR2 DEFAULT NULL, p_from_date IN DATE DEFAULT NULL, p_to_date IN DATE DEFAULT NUll);END trc;/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY trc AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/trc.pkb-- Author : DR Timothy S Hall-- Description : A simple mechanism for tracing information to a table.-- Requirements : trc.pks, dsp.pks, dsp.pkb and schema definied in trc.pks-- Ammedments :-- When Who What-- =========== ======== =================================================-- 08-JAN-2002 Tim Hall Initial Creation-- --------------------------------------------------------------------------

-- Package Variables g_trace_on BOOLEAN := FALSE; g_date_format VARCHAR2(50) := 'DD-MON-YYYY HH24:MI:SS';

-- Exposed Methods

-- -------------------------------------------------------------------------- PROCEDURE reset_defaults IS -- -------------------------------------------------------------------------- BEGIN g_trace_on := FALSE; g_date_format := 'DD-MON-YYYY HH24:MI:SS'; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE trace_on IS -- -------------------------------------------------------------------------- BEGIN g_trace_on := TRUE; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE trace_off IS -- -------------------------------------------------------------------------- BEGIN g_trace_on := FALSE; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS') IS -- -------------------------------------------------------------------------- BEGIN g_date_format := p_date_format; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE line (p_prefix IN VARCHAR2, p_data IN VARCHAR2, p_trc_level IN NUMBER DEFAULT 5, p_trc_user IN VARCHAR2 DEFAULT USER) IS -- -------------------------------------------------------------------------- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN IF g_trace_on THEN INSERT INTO trace_data (id, prefix, data, trc_level, created_date, created_by) VALUES (trc_seq.nextval,

p_prefix, p_data, p_trc_level, Sysdate, p_trc_user); COMMIT; END IF; END; -- --------------------------------------------------------------------------

-- -------------------------------------------------------------------------- PROCEDURE display (p_trc_level IN NUMBER DEFAULT NULL, p_trc_user IN VARCHAR2 DEFAULT NULL, p_from_date IN DATE DEFAULT NULL, p_to_date IN DATE DEFAULT NUll) IS -- -------------------------------------------------------------------------- CURSOR c_trace IS SELECT * FROM trace_data WHERE trc_level = NVL(p_trc_level, trc_level) AND created_by = NVL(p_trc_user, created_by) AND created_date >= NVL(p_from_date, created_date) AND created_date <= NVL(p_to_date, created_date) ORDER BY id; BEGIN FOR cur_rec IN c_trace LOOP dsp.line(cur_rec.prefix, cur_rec.data); END LOOP; END; -- -------------------------------------------------------------------------- END trc;/

SHOW ERRORS

admin scripts

Documents