Parrallel execution (external link)

Understanding Parallel Execution - parts 1 and 2

LONG columns and scripts

There are many ways to read a LONG column -  in general one have to use the PL/SQL code. The summary of those ways is provided here (http://www.oracle-developer.net/display.php?id=430). 
I focused only on one of those methods i.e. the one leveraging the XML processing features of the Oracle RDBMS. The main reason here is the convenience while trying to produce a kind of administration script (which a basic task is usually to generate another script, which in turn makes the final effect). Here an example - the script, which produces the script, which may recreate constraints from a chosen schema:

set pages 0 lin 300 trimspool on trimout on
define CONDITIONS='OWNER=''VPD_BPSS'''

-- check
SELECT 'alter table '
  || xs.owner
  ||'.'
  || xs.table_name
  ||' add constraint '
  || xs.constraint_name
  ||' '
  || ' check ('
  || xs.search_condition
  ||')'
  ||
  CASE xs.status
    WHEN 'ENABLED'
    THEN ' enable '
    WHEN 'DISABLED '
    THEN ' disable '
  END
  || ';' SQL1
FROM XMLTABLE('/ROWSET/ROW' PASSING
  (SELECT dbms_xmlgen.getxmltype(
    q'[SELECT * FROM dba_constraints c WHERE c.constraint_type in ('C') and &&CO
NDITIONS]' )
  FROM dual
  ) COLUMNS
  owner VARCHAR2(32) PATH 'OWNER',
  constraint_name VARCHAR2(32) path 'CONSTRAINT_NAME',
  constraint_type VARCHAR2(1) path 'CONSTRAINT_TYPE',
  table_name VARCHAR2(30) path 'TABLE_NAME',
  search_condition VARCHAR2(4000) path 'SEARCH_CONDITION',
  status VARCHAR2(30) path 'STATUS' ) xs
UNION ALL
-- pk and uniq
SELECT 'alter table '
  ||c.owner
  ||'.'
  ||c.table_name
  ||' add constraint '
  ||c.constraint_name
  ||
  CASE c.constraint_type
    WHEN 'P'
    THEN ' primary key'
    WHEN 'U'
    THEN ' unique'
  END
  || ' ('
  ||
  (SELECT LISTAGG(i.column_name, ',') WITHIN GROUP (
  ORDER BY i.position)
  FROM dba_cons_columns i
  WHERE c.owner         = i.owner
  AND c.constraint_name = i.constraint_name
  )
  ||') using index '
  ||c.index_owner
  ||'.'
  ||c.index_name
  ||' '
  || c.deferrable
  ||' '
  || c.deferred
  ||' '
  ||';' DDL1
FROM DBA_CONSTRAINTS C
WHERE c.constraint_type IN ('P','U')
AND
  &&CONDITIONS
UNION ALL
-- fk
SELECT 'alter table '
  ||c.owner
  ||'.'
  ||c.table_name
  ||' add constraint '
  ||c.constraint_name
  ||' foreign key('
  ||
  (SELECT LISTAGG(f.column_name, ',') WITHIN GROUP (
  ORDER BY f.position)
  FROM dba_cons_columns f
  WHERE c.owner        =f.owner
  AND c.constraint_name=f.constraint_name
  )
  ||') '
  ||' references '
  ||
  (SELECT p.owner
    ||'.'
    ||p.table_name
    ||'('
    || LISTAGG(p.column_name, ',') WITHIN GROUP (
  ORDER BY p.position)
    || ')'
  FROM dba_cons_columns p
  WHERE c.r_OWNER        =p.owner
  AND c.r_constraint_name = p.constraint_name
  GROUP BY p.owner,
    p.table_name
  )
  ||' '
  ||
  CASE
    WHEN c.delete_rule IS NOT NULL
    AND c.delete_rule!  ='NO ACTION'
    THEN 'ON DELETE '
      || c.delete_rule
      ||' '
  END
  || c.deferrable
  ||' '
  || c.deferred
  ||' '
  ||
  CASE status
    WHEN 'ENABLED'
    THEN 'ENABLE novalidate '
    WHEN 'DISABLED '
    THEN 'DISABLE '
  END
  ||';' ddl1
FROM DBA_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE IN ('R')
AND
  &&CONDITIONS ;