--
-- Copyright [2024 - 2026] Confluent Inc.
--
-- Confluent Oracle XStream CDC connector setup script (version 1.0)
--
-- Purpose:
--   Interactive SQL*Plus script that generates a SQL script to set up Oracle XStream
--   components for Oracle XStream CDC connector.
--
--   The interactive script must be run by a user with the Database Administrator (DBA) role.
--   This is a read-only script and does not make any changes to the database.
--   The script uses an external file (default: tables-list.txt) to define which tables to capture.
--   a. If capturing the entire database, this file is not required.
--   b. The file must be located in the Oracle directory defined by SCRIPT_DIR.
--      If this mapping does not exist, you must create it before running the script.
--   c. The file must contain a single line of comma-separated, fully qualified table names (e.g., HR.EMPLOYEES,SALES.ORDERS).
--   This script generates a secondary SQL execution script. The DBA must review this secondary script for accuracy before running it.
--
-- Compatibility: Oracle Database version 19c and later.
--
-- ----------------------------
-- SQL*Plus session formatting
-- ----------------------------
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET TERMOUT ON
SET DEFINE ON

-- Exit codes for OS and SQL errors (fail fast if anything breaks)
WHENEVER OSERROR EXIT 9
WHENEVER SQLERROR EXIT SQL.SQLCODE

prompt =================================
prompt Generate XStream Setup SQL script
prompt =================================

-- Output file where the generated setup script will be spooled
accept p_outfile char prompt 'Output file name [default: xstream_setup_script.sql]: ' default 'xstream_setup_script.sql'
prompt

SET TERMOUT OFF

-- -------------------------------------------
-- Detect DB properties (used in generated SQL)
-- -------------------------------------------
column v_log_mode new_value v_log_mode noprint
SELECT log_mode AS v_log_mode FROM v$database;

column v_is_cdb new_value v_is_cdb noprint
SELECT cdb AS v_is_cdb FROM v$database;

column v_is_rac new_value v_is_rac noprint
SELECT CASE WHEN UPPER(value) = 'TRUE' THEN 'YES' ELSE 'NO' END AS v_is_rac
FROM v$parameter
WHERE name = 'cluster_database';

column v_db_unique_name new_value v_db_unique_name noprint
SELECT value AS v_db_unique_name
FROM v$parameter
WHERE name = 'db_unique_name';

-- Start writing the generated setup script to the chosen output file
SPOOL &&P_OUTFILE

SELECT '-- ===========================================================================' FROM dual;
SELECT '-- Copyright [2024 - 2026] Confluent Inc.' FROM dual;
SELECT '--' FROM dual;
SELECT '-- XStream Outbound Local Setup Script' FROM dual;
SELECT '-- Generated on: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT '-- ===========================================================================' FROM dual;
SELECT '-- This script is solely intended to facilitate the automated ' FROM dual;
SELECT '-- installation and configuration of Oracle XStream components.' FROM dual;
SELECT '--' FROM dual;
SELECT '-- This script MUST be reviewed by a Database Administrator (DBA) for ' FROM dual;
SELECT '-- accuracy and environmental compatibility prior to execution.' FROM dual;
SELECT '--' FROM dual;
SELECT '-- WARNING: There is NO in-built automated rollback mechanism for the ' FROM dual;
SELECT '-- changes performed by this script. If this script exits prematurely ' FROM dual;
SELECT '-- due to error, or completes successfully, any changes made to the ' FROM dual;
SELECT '-- database remain persistent.' FROM dual;
SELECT '--' FROM dual;
SELECT '-- IMPORTANT: Must be executed as a user with SYSDBA privileges.' FROM dual;
SELECT '-- ===========================================================================' FROM dual;
SELECT 'set echo on' FROM dual;
SELECT 'set verify off' FROM dual;
SELECT 'set define on' FROM dual;
SELECT 'set sqlblanklines on' FROM dual;
SELECT 'whenever oserror exit 9' FROM dual;
SELECT 'whenever sqlerror exit sql.sqlcode' FROM dual;
SELECT '' FROM dual;

SELECT '-- ====================================================' FROM dual;
SELECT '-- Output formatting (for readable verification output)' FROM dual;
SELECT '-- ====================================================' FROM dual;
SELECT 'set heading on' FROM dual;
SELECT 'set feedback on' FROM dual;
SELECT 'set pagesize 200' FROM dual;
SELECT 'set linesize 200' FROM dual;
SELECT 'set tab off' FROM dual;
SELECT 'set trimout on' FROM dual;
SELECT 'set trimspool on' FROM dual;
SELECT '' FROM dual;
SELECT 'column name format a45' FROM dual;
SELECT 'column type format a12' FROM dual;
SELECT 'column value format a120' FROM dual;
SELECT '' FROM dual;
SELECT 'column log_mode format a20' FROM dual;
SELECT 'column supplemental_log_data_min format a25' FROM dual;
SELECT 'column supplemental_log_data_all format a25' FROM dual;
SELECT '' FROM dual;
SELECT 'column owner format a30' FROM dual;
SELECT 'column table_name format a30' FROM dual;
SELECT 'column log_group_name format a30' FROM dual;
SELECT 'column log_group_type format a22' FROM dual;
SELECT 'column always format a10' FROM dual;
SELECT 'column generated format a10' FROM dual;

SELECT '' FROM dual;

SELECT '-- Detected at generation time: CDB=&&v_is_cdb, RAC=&&v_is_rac' FROM dual;
SELECT '' FROM dual;

SELECT '-- =====================================' FROM dual;
SELECT '-- Step 1: Enable Oracle XStream support' FROM dual;
SELECT '-- =====================================' FROM dual;

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT 'alter system set enable_goldengate_replication = true scope=both;' FROM dual;
SELECT '' FROM dual;

SELECT '-- Verify' FROM dual;
SELECT 'show parameter enable_goldengate_replication;' FROM dual;
SELECT '' FROM dual;

SELECT '-- =================================' FROM dual;
SELECT '-- Step 2: Configure ARCHIVELOG Mode' FROM dual;
SELECT '-- =================================' FROM dual;
SELECT 'SELECT log_mode FROM v$database;' FROM dual;
SELECT '' FROM dual;
SELECT '-- Detected log_mode at generation time: &&v_log_mode' FROM dual;
SELECT '' FROM dual;

SELECT '-- ARCHIVELOG is already enabled. No additional commands generated.' FROM dual
 WHERE '&&v_log_mode' = 'ARCHIVELOG';

SELECT '-- IMPORTANT GUIDELINES:' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG';
SELECT '-- 1) Enabling ARCHIVELOG requires a restart and must be done in a maintenance window.' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG';
SELECT '-- 2) Run shutdown/startup commands from a LOCAL SYSDBA session on the database host' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG';
SELECT '--    (i.e., a server-side SYSDBA connection). Avoid running shutdown/startup via a listener/service connect string,' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG';
SELECT '--    because the session may disconnect and service registration may temporarily disappear while the DB is down.' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG';

SELECT '-- 3) The commands below are generated as comments. Review carefully, then UNCOMMENT to execute.' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '--' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '-- shutdown immediate;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '-- startup mount;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '-- alter database archivelog;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '-- alter database open;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';
SELECT '-- SELECT log_mode FROM v$database;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' <> 'YES';

SELECT '-- 3) RAC detected: use srvctl to restart the database for ARCHIVELOG changes before running the generated script. The commands below are generated as comments for reference. Review and adjust for your environment.' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   srvctl stop database -d &&v_db_unique_name' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   srvctl start database -d &&v_db_unique_name -o mount' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   -- connect as SYSDBA and run:' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   alter database archivelog;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   srvctl start database -d &&v_db_unique_name' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   -- verify:' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';
SELECT '--   SELECT log_mode FROM v$database;' FROM dual
 WHERE '&&v_log_mode' <> 'ARCHIVELOG' AND '&&v_is_rac' = 'YES';

SELECT '' FROM dual;
SELECT '-- ======================================' FROM dual;
SELECT '-- Step 3: Configure Supplemental Logging' FROM dual;
SELECT '-- ======================================' FROM dual;
SELECT '' FROM dual;

SPOOL OFF
SET TERMOUT ON

prompt Detected database type:
prompt   CDB = &&v_is_cdb
prompt   RAC = &&v_is_rac
prompt

-- List PDBs (terminal only) without DBMS_OUTPUT
SELECT 'Available PDBs:' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT listagg(name, chr(10)) within GROUP (ORDER BY name)
FROM v$pdbs
WHERE name <> 'PDB$SEED'
  AND '&&v_is_cdb' = 'YES';

column v_src_prompt new_value v_src_prompt noprint
SELECT CASE
         WHEN '&&v_is_cdb' = 'YES'
           THEN 'Source container (PDB name FROM the list or empty for CDB$ROOT): '
         ELSE 'Source container [No-op for non-CDB] (Press Enter to skip): '
       END AS v_src_prompt
FROM dual;

accept p_src_container char prompt '&&v_src_prompt'
prompt

SET TERMOUT OFF

column v_src_container new_value v_src_container noprint
SELECT CASE
         WHEN '&&v_is_cdb' = 'YES' AND NVL(LENGTH(TRIM('&&p_src_container')), 0) = 0 THEN 'CDB$ROOT'
         WHEN '&&v_is_cdb' = 'YES' THEN UPPER(TRIM('&&p_src_container'))
         ELSE 'N/A'
       END AS v_src_container
FROM dual;

BEGIN
   IF '&&v_is_cdb' = 'YES' THEN
     IF UPPER('&&v_src_container') <> 'CDB$ROOT' THEN
      DECLARE
        l_cnt number := 0;
      BEGIN
        SELECT COUNT(*)
          INTO l_cnt
          FROM v$pdbs
         WHERE UPPER(name) = UPPER('&&v_src_container')
           AND name <> 'PDB$SEED';

         IF l_cnt = 0 THEN
           RAISE_APPLICATION_ERROR(-20006, 'Invalid PDB name: '||'&&v_src_container'||'.');
        END IF;
      END;
    END IF;
  END IF;
END;
/

SET TERMOUT ON

prompt What do you want to capture?
prompt   DB     - All tables (DB-level supplemental logging, broad snapshot grants, outbound captures all tables)
prompt   TABLES - Only selected tables (table-level supplemental logging, per-table snapshot grants, outbound captures only provided tables)
prompt

accept p_supp_scope char prompt 'Enter DB or TABLES [default: TABLES]: ' default 'TABLES'
prompt

SET TERMOUT OFF

column v_supp_scope new_value v_supp_scope noprint
SELECT UPPER(TRIM('&&p_supp_scope')) AS v_supp_scope FROM dual;
BEGIN
  IF '&&v_supp_scope' NOT IN ('DB', 'TABLES') THEN
     RAISE_APPLICATION_ERROR(
      -20001,
      'Invalid capture scope: '||'&&v_supp_scope'||'. Allowed values: DB or TABLES.'
    );
  END IF;
END;
/

SET TERMOUT ON

accept p_tables_file char prompt 'Tables list input file [default: tables-list.txt] (No-op for DB scope, Press Enter): ' default 'tables-list.txt'


-- ------------------------------------------------------------
-- Validate FQN format (schema.table,...)
-- ------------------------------------------------------------
DECLARE
  l_file   utl_file.file_type;
  l_list   varchar2(32767);

  PROCEDURE assert_fqn_list(p_list varchar2, p_label varchar2, p_err number) IS
    l_token varchar2(4000);
    l_idx   pls_integer := 1;
  BEGIN
    IF NVL(LENGTH(TRIM(p_list)), 0) = 0 THEN
       RAISE_APPLICATION_ERROR(p_err, p_label || ' cannot be empty.');
    END IF;

    IF instr(p_list, ' ') > 0 THEN
       RAISE_APPLICATION_ERROR(p_err, p_label || ' must not contain spaces.');
    END IF;

    LOOP
      l_token := trim(regexp_substr(p_list, '[^,]+', 1, l_idx));
      EXIT WHEN l_token IS NULL;

       IF instr(l_token, '.') = 0 THEN
         RAISE_APPLICATION_ERROR(
          p_err,
          p_label || ' must contain fully qualified schema.table values.'
        );
      END IF;

      l_idx := l_idx + 1;
    END LOOP;
  END;
BEGIN
  IF UPPER('&&v_supp_scope') = 'TABLES' THEN

    BEGIN
      l_file := utl_file.fopen('SCRIPT_DIR','&&p_tables_file','r');
    EXCEPTION
      WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(
          -20010,
          'Cannot open tables list file &&p_tables_file in DIRECTORY SCRIPT_DIR'
        );
    END;

    BEGIN
      utl_file.get_line(l_file, l_list);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RAISE_APPLICATION_ERROR(
          -20011,
          'Tables list file &&p_tables_file is empty'
        );
    END;

    utl_file.fclose(l_file);

    assert_fqn_list(l_list, 'Tables list', -20003);

  END IF;
END;
/

SET TERMOUT OFF
SPOOL &&P_OUTFILE APPEND

SELECT '-- Selected source container: &&v_src_container' FROM dual;
SELECT '-- Selected scope (supplemental logging, snapshot grants, outbound capture): &&v_supp_scope' FROM dual;
SELECT '' FROM dual;

SELECT '-- Step 3A: Enable minimal supplemental logging (pre-requisite)' FROM dual;

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT 'alter database add supplemental log data;' FROM dual;
SELECT '' FROM dual;

SELECT '-- Verify minimal supplemental logging' FROM dual;
SELECT 'SELECT supplemental_log_data_min FROM v$database;' FROM dual;
SELECT '' FROM dual;

SELECT '-- Step 3B: Enable supplemental logging' FROM dual;

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_supp_scope' = 'DB' AND '&&v_is_cdb' = 'YES';
SELECT 'alter database add supplemental log data (all) columns;' FROM dual
 WHERE '&&v_supp_scope' = 'DB';

SELECT '-- Verify database-level supplemental logging flags (DB scope)' FROM dual
 WHERE '&&v_supp_scope' = 'DB';
SELECT 'SELECT supplemental_log_data_min, supplemental_log_data_all FROM v$database;' FROM dual
 WHERE '&&v_supp_scope' = 'DB';

SELECT 'alter session set container = &&v_src_container;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES' AND '&&v_is_cdb' = 'YES';

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
  l_file        utl_file.file_type;
  l_list        varchar2(32767);
BEGIN
   IF '&&v_supp_scope' = 'TABLES' THEN

    -- read table list
    l_file := utl_file.fopen('SCRIPT_DIR','&&p_tables_file','r');
    utl_file.get_line(l_file, l_list);
    utl_file.fclose(l_file);

    -- loop tables
    FOR r IN (
      SELECT TRIM(regexp_substr(l_list,'[^,]+',1,level)) tbl
      FROM dual
      CONNECT BY regexp_substr(l_list,'[^,]+',1,level) IS NOT NULL
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(
        'alter table '||r.tbl||
        ' add supplemental log data (all) columns;'
      );
    END LOOP;
  END IF;
END;
/

SET SERVEROUTPUT OFF

SELECT '' FROM dual;

SELECT '-- Verify database-level supplemental logging flags (TABLES scope)' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT 'SELECT supplemental_log_data_min, supplemental_log_data_all FROM v$database;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '' FROM dual;

SELECT '-- Verify ALL COLUMN LOGGING log groups in ALL_LOG_GROUPS (TABLES scope)' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';

SELECT 'SELECT owner, table_name, log_group_name, log_group_type, always, generated '||
       'FROM all_log_groups '||
       'WHERE log_group_type = ''ALL COLUMN LOGGING'';'
FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '' FROM dual;

SELECT '-- ===========================' FROM dual;
SELECT '-- Step 4: Setup XStream Users' FROM dual;
SELECT '-- ===========================' FROM dual;
SELECT '' FROM dual;

SPOOL OFF

column v_cap_user_default new_value v_cap_user_default noprint
SELECT CASE WHEN '&&v_is_cdb' = 'YES' THEN 'c##cfltadmin' ELSE 'cfltadmin' END AS v_cap_user_default FROM dual;

column v_con_user_default new_value v_con_user_default noprint
SELECT CASE WHEN '&&v_is_cdb' = 'YES' THEN 'c##cfltuser' ELSE 'cfltuser' END AS v_con_user_default FROM dual;

column v_cap_user_prompt new_value v_cap_user_prompt noprint
SELECT CASE
         WHEN '&&v_is_cdb' = 'YES'
           THEN 'XStream capture common user name [default: &&v_cap_user_default]: '
         ELSE 'XStream capture user name [default: &&v_cap_user_default]: '
       END AS v_cap_user_prompt
FROM dual;

column v_con_user_prompt new_value v_con_user_prompt noprint
SELECT CASE
         WHEN '&&v_is_cdb' = 'YES'
           THEN 'XStream connect common user name [default: &&v_con_user_default]: '
         ELSE 'XStream connect user name [default: &&v_con_user_default]: '
       END AS v_con_user_prompt
FROM dual;

SET TERMOUT ON

prompt
prompt ======================================================================
prompt XStream Users and Tablespaces
prompt ======================================================================
prompt
prompt This generator requires the names of the Capture and Connect users
prompt and their default tablespaces, even if they already exist.
prompt
prompt You will now be asked whether this script should create these users
prompt and tablespaces.
prompt
prompt   Y = Generate DDL to create the users and tablespaces.
prompt   N = Do not generate CREATE statements. Existing objects will be used.
prompt
prompt ======================================================================
prompt Provide the details for the following users:
prompt   1) Capture user  – This user manages the XStream components and
prompt                      captures changes from the redo log.
prompt   2) Connect user  – This user connects to the XStream outbound server
prompt                      to receive changes as logical change records (LCRs).
prompt ======================================================================
prompt
prompt


accept p_create_cap char prompt 'Create CAPTURE user + tablespace? [Y/N] (default: Y): ' default 'Y'

prompt

accept p_create_con char prompt 'Create CONNECT user + tablespace? [Y/N] (default: Y): ' default 'Y'

prompt

SET TERMOUT OFF

column v_create_cap new_value v_create_cap noprint
SELECT CASE UPPER(TRIM('&&p_create_cap'))
         WHEN 'N' THEN 'NO'
         ELSE 'YES'
       END v_create_cap
FROM dual;

column v_create_con new_value v_create_con noprint
SELECT CASE UPPER(TRIM('&&p_create_con'))
         WHEN 'N' THEN 'NO'
         ELSE 'YES'
       END v_create_con
FROM dual;

SET TERMOUT ON

accept p_cap_user char prompt '&&v_cap_user_prompt' default '&&v_cap_user_default'
prompt

SET TERMOUT OFF

column v_cap_pass_prompt new_value v_cap_pass_prompt noprint
SELECT 'Password for '||'&&p_cap_user'||' [default: password]: ' AS v_cap_pass_prompt FROM dual;

SET TERMOUT ON

accept p_cap_pass char prompt '&&v_cap_pass_prompt' default 'password'
prompt

accept p_cap_tbs_name char prompt 'Tablespace name for capture user [default: xstream_adm_tbs]: ' default 'xstream_adm_tbs'
prompt

accept p_con_user char prompt '&&v_con_user_prompt' default '&&v_con_user_default'
prompt

SET TERMOUT OFF

column v_con_pass_prompt new_value v_con_pass_prompt noprint
SELECT 'Password for '||'&&p_con_user'||' [default: password]: ' AS v_con_pass_prompt FROM dual;

SET TERMOUT ON

accept p_con_pass char prompt '&&v_con_pass_prompt' default 'password'
prompt

accept p_con_tbs_name char prompt 'Tablespace name for connect user [default: xstream_tbs]: ' default 'xstream_tbs'
prompt

-- ------------------------------------------
-- Tablespace Datafile Location
-- ------------------------------------------

prompt
prompt ======================================================================
prompt Tablespace Datafile Location
prompt ======================================================================
prompt
prompt If you chose Y for creating the Capture and/or Connect users, this
prompt generator generates CREATE TABLESPACE commands.
prompt
prompt To do so, it may use a base directory where new datafiles should be
prompt created.
prompt
prompt If you chose N for creating the Capture and Connect users, press Enter
prompt to skip
prompt
prompt Datafile naming used by this generator:
prompt   Non-CDB / CDB$ROOT:   <base_dir>/<tablespace>.dbf
prompt   CDB per-PDB:          <base_dir>/<pdb>/<tablespace>.dbf
prompt
prompt If you want to skip specifying a base directory (for custom naming or
prompt if no valid directory paths exist), press Enter.
prompt
prompt In that case, DATAFILE paths will be generated as:
prompt   <TO_BE_FILLED>/<tablespace>.dbf
prompt
prompt Existing datafile directories detected in this database (if any):
prompt

SELECT dir
FROM (
  SELECT DISTINCT regexp_replace(file_name, '[^/]*$', '') AS dir
  FROM cdb_data_files
  WHERE '&&v_is_cdb' = 'YES'
  UNION
  SELECT DISTINCT regexp_replace(file_name, '[^/]*$', '') AS dir
  FROM dba_data_files
  WHERE '&&v_is_cdb' <> 'YES'
)
ORDER BY dir;
prompt

accept p_tbs_dir char prompt 'Tablespace base directory (Press Enter to use placeholder or skip): '
prompt

SET TERMOUT OFF

column v_use_base_dir new_value v_use_base_dir noprint
SELECT CASE
         WHEN NVL(LENGTH(TRIM('&&p_tbs_dir')), 0) = 0 THEN 'NO'
         ELSE 'YES'
       END AS v_use_base_dir
FROM dual;

column v_tbs_dir new_value v_tbs_dir noprint
SELECT CASE
         WHEN NVL(LENGTH(TRIM('&&p_tbs_dir')), 0) = 0 THEN '<TO_BE_FILLED>/'
         WHEN SUBSTR(TRIM('&&p_tbs_dir'), -1) = '/' THEN TRIM('&&p_tbs_dir')
         ELSE TRIM('&&p_tbs_dir')||'/'
       END AS v_tbs_dir
FROM dual;

column v_cap_datafile new_value v_cap_datafile noprint
SELECT '&&v_tbs_dir'||'&&p_cap_tbs_name'||'.dbf' AS v_cap_datafile FROM dual;

column v_con_datafile new_value v_con_datafile noprint
SELECT '&&v_tbs_dir'||'&&p_con_tbs_name'||'.dbf' AS v_con_datafile FROM dual;

spool &&p_outfile append

SELECT '-- Step 4A: Capture User' FROM dual WHERE '&&v_create_cap' = 'YES';

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_create_cap' = 'YES';

-- Capture tablespace in ROOT/non-CDB
SELECT 'CREATE TABLESPACE &&p_cap_tbs_name DATAFILE '''||'&&v_cap_datafile'||''' '||
       'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;'
FROM dual
WHERE '&&v_create_cap' = 'YES';

SELECT '' FROM dual WHERE '&&v_create_cap' = 'YES';

-- Capture tablespace per PDB (CDB only)
SELECT '-- Create capture tablespace in all PDBs (CDB only)' FROM dual
WHERE '&&v_is_cdb' = 'YES' AND '&&v_create_cap' = 'YES';

SELECT
  'alter session set container = '||p.name||';'||chr(10)||
  'CREATE TABLESPACE &&p_cap_tbs_name DATAFILE '''||
  '&&v_tbs_dir'||
  CASE WHEN '&&v_use_base_dir' = 'YES' THEN p.name||'/' ELSE '' END ||
  '&&p_cap_tbs_name..dbf'' '||
  'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;'
FROM v$pdbs p
WHERE '&&v_is_cdb' = 'YES'
  AND p.name NOT IN ('PDB$SEED')
  AND '&&v_create_cap' = 'YES';

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT '' FROM dual;

SELECT 'CREATE USER &&p_cap_user IDENTIFIED BY "'||'&&p_cap_pass'||'" '||
       'DEFAULT TABLESPACE &&p_cap_tbs_name '||
       'QUOTA UNLIMITED ON &&p_cap_tbs_name'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual
WHERE '&&v_create_cap' = 'YES';

SELECT '' FROM dual WHERE '&&v_create_cap' = 'YES';

SELECT 'GRANT CREATE SESSION, SET CONTAINER TO &&p_cap_user CONTAINER=ALL;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT 'GRANT CREATE SESSION TO &&p_cap_user;' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';

SELECT '' FROM dual;

SELECT 'begin' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '  DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '    grantee                 => '''||'&&p_cap_user'||''',' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '    privilege_type          => ''CAPTURE'',' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '    grant_select_privileges => TRUE,' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '    container               => ''ALL'');' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT 'end;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '/' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT 'begin' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT '  DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT '    grantee                 => '''||'&&p_cap_user'||''',' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT '    privilege_type          => ''CAPTURE'',' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT '    grant_select_privileges => TRUE);' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT 'end;' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';
SELECT '/' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';

SELECT '' FROM dual;
SELECT '-- Step 4B: Connect User' FROM dual WHERE '&&v_create_con' = 'YES';

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_create_con' = 'YES';

-- Connect tablespace in ROOT/non-CDB
SELECT 'CREATE TABLESPACE &&p_con_tbs_name DATAFILE '''||'&&v_con_datafile'||''' '||
       'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;'
FROM dual
WHERE '&&v_create_con' = 'YES';

SELECT '' FROM dual WHERE '&&v_create_con' = 'YES';

-- Connect tablespace per PDB (CDB only)
SELECT '-- Create connect tablespace in all PDBs (CDB only)' FROM dual
WHERE '&&v_is_cdb' = 'YES' AND '&&v_create_con' = 'YES';

SELECT
  'alter session set container = '||p.name||';'||chr(10)||
  'CREATE TABLESPACE &&p_con_tbs_name DATAFILE '''||
  '&&v_tbs_dir'||
  CASE WHEN '&&v_use_base_dir' = 'YES' THEN p.name||'/' ELSE '' END ||
  '&&p_con_tbs_name..dbf'' '||
  'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;'
FROM v$pdbs p
WHERE '&&v_is_cdb' = 'YES'
  AND p.name NOT IN ('PDB$SEED')
  AND '&&v_create_con' = 'YES';

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT '' FROM dual;

SELECT 'CREATE USER &&p_con_user IDENTIFIED BY "'||'&&p_con_pass'||'" '||
       'DEFAULT TABLESPACE &&p_con_tbs_name '||
       'QUOTA UNLIMITED ON &&p_con_tbs_name'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual
WHERE '&&v_create_con' = 'YES';

SELECT '' FROM dual WHERE '&&v_create_con' = 'YES';

SELECT 'GRANT CREATE SESSION, SET CONTAINER TO &&p_con_user CONTAINER=ALL;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT 'GRANT CREATE SESSION TO &&p_con_user;' FROM dual
 WHERE '&&v_is_cdb' <> 'YES';

SELECT 'GRANT SELECT_CATALOG_ROLE TO &&p_con_user'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual;

SELECT '' FROM dual;
SELECT '-- Step 4C: Grant Snapshot Privileges' FROM dual;

SELECT 'GRANT SELECT ANY TABLE TO &&p_con_user'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual
 WHERE '&&v_supp_scope' = 'DB';

SELECT 'GRANT LOCK ANY TABLE TO &&p_con_user'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual
 WHERE '&&v_supp_scope' = 'DB';

SELECT 'GRANT FLASHBACK ANY TABLE TO &&p_con_user'||
       CASE WHEN '&&v_is_cdb' = 'YES' THEN ' CONTAINER=ALL;' ELSE ';' END
FROM dual
 WHERE '&&v_supp_scope' = 'DB';

SELECT 'alter session set container = &&v_src_container;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES'
   AND '&&v_is_cdb'  = 'YES';

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_file utl_file.file_type;
  l_list varchar2(32767);
BEGIN
   IF '&&v_supp_scope' = 'TABLES' THEN

    l_file := utl_file.fopen('SCRIPT_DIR','&&p_tables_file','r');
    utl_file.get_line(l_file, l_list);
    utl_file.fclose(l_file);

    FOR r IN (
      SELECT TRIM(regexp_substr(l_list,'[^,]+',1,level)) tbl
      FROM dual
      CONNECT BY regexp_substr(l_list,'[^,]+',1,level) IS NOT NULL
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(
        'GRANT SELECT ON '||r.tbl||' TO &&p_con_user;'
      );
      DBMS_OUTPUT.PUT_LINE(
        'GRANT FLASHBACK ON '||r.tbl||' TO &&p_con_user;'
      );
    END LOOP;

  END IF;
END;
/
SET SERVEROUTPUT OFF

SELECT '' FROM dual;

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SELECT '' FROM dual;
SELECT '-- ===============================' FROM dual;
SELECT '-- Step 5: Create XStream Outbound' FROM dual;
SELECT '-- ===============================' FROM dual;
SELECT '' FROM dual;

SPOOL OFF
SET TERMOUT ON

accept p_xout_name char prompt 'XStream outbound server name [default: xout]: ' default 'xout'
prompt

SET TERMOUT OFF
SPOOL &&P_OUTFILE APPEND

SELECT '-- Step 5A: Create XStream Outbound Server' FROM dual;

SELECT '-- Run as the XStream capture admin user' FROM dual;
SELECT 'connect &&p_cap_user/"&&p_cap_pass"' FROM dual;
SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '' FROM dual;

SELECT 'begin' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '    server_name           => ''&&p_xout_name'',' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '    source_container_name => ''&&v_src_container'');' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';
SELECT 'end;' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '/' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'DB';

SELECT 'begin' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '    server_name => ''&&p_xout_name'');' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'DB';
SELECT 'end;' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'DB';
SELECT '/' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'DB';

SELECT 'declare' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '  tables  DBMS_UTILITY.UNCL_ARRAY;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '  schemas DBMS_UTILITY.UNCL_ARRAY;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT 'begin' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '  schemas(1) := NULL;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_file utl_file.file_type;
  l_list varchar2(32767);
  l_i pls_integer := 0;
BEGIN
  IF '&&v_supp_scope' = 'TABLES' THEN

    l_file := utl_file.fopen('SCRIPT_DIR','&&p_tables_file','r');
    utl_file.get_line(l_file, l_list);
    utl_file.fclose(l_file);

    FOR r IN (
      SELECT TRIM(regexp_substr(l_list,'[^,]+',1,level)) tbl
      FROM dual
      CONNECT BY regexp_substr(l_list,'[^,]+',1,level) IS NOT NULL
    ) LOOP
      l_i := l_i + 1;
      dbms_output.put_line(
        '  tables('||l_i||') := '''||r.tbl||''';'
      );
    END LOOP;

  END IF;
END;
/
SET SERVEROUTPUT OFF

SELECT '  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    server_name           => ''&&p_xout_name'',' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    source_container_name => ''&&v_src_container'',' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    table_names           => tables,' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    schema_names          => schemas);' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND '&&v_supp_scope' = 'TABLES';

SELECT '  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    server_name  => ''&&p_xout_name'',' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    table_names  => tables,' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'TABLES';
SELECT '    schema_names => schemas);' FROM dual
 WHERE '&&v_is_cdb' <> 'YES' AND '&&v_supp_scope' = 'TABLES';

SELECT 'end;' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';
SELECT '/' FROM dual
 WHERE '&&v_supp_scope' = 'TABLES';

SELECT '' FROM dual;

SELECT '-- ==========================================================' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- NOTE: Additional privileges for ROOT-level XStream capture' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- ==========================================================' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- Capturing from CDB$ROOT exposes system-level metadata and' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- common object changes. This is recommended only when needed.' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '--' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- If the outbound capture fails to start with ORA-01031 or' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- dictionary access errors, consider granting:' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '--' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '--   GRANT SELECT_CATALOG_ROLE TO &&p_cap_user CONTAINER=ALL;' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '--' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';
SELECT '-- This grant is not required for PDB-level capture.' FROM dual
 WHERE '&&v_is_cdb' = 'YES' AND UPPER('&&v_src_container') = 'CDB$ROOT';

SELECT '-- Switch back to SYSDBA' FROM dual;
SELECT 'connect / as sysdba' FROM dual;
SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';
SELECT '' FROM dual;

SELECT '-- Step 5B: Assign Connect User' FROM dual;
SELECT 'begin' FROM dual;
SELECT '  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(' FROM dual;
SELECT '    server_name  => ''&&p_xout_name'',' FROM dual;
SELECT '    connect_user => ''&&p_con_user'');' FROM dual;
SELECT 'end;' FROM dual;
SELECT '/' FROM dual;

SELECT '' FROM dual;

SELECT '-- ==============================================' FROM dual;
SELECT '-- Step 6: XStream Performance / RAC Configuration' FROM dual;
SELECT '-- ==============================================' FROM dual;
SELECT '' FROM dual;

spool off

column v_streams_pool_mb new_value v_streams_pool_mb noprint
SELECT to_char(
         round(
           CASE
             WHEN regexp_like(value, '^[0-9]+(\.[0-9]+)?[KMG]$', 'i') THEN
               CASE UPPER(substr(value, -1))
                 WHEN 'K' THEN to_number(substr(value, 1, length(value)-1)) / 1024
                 WHEN 'M' THEN to_number(substr(value, 1, length(value)-1))
                 WHEN 'G' THEN to_number(substr(value, 1, length(value)-1)) * 1024
               END
             WHEN regexp_like(value, '^[0-9]+(\.[0-9]+)?$') THEN
               to_number(value) / 1024 / 1024  -- plain number treated as bytes
             ELSE NULL
           END
         )
       ) AS v_streams_pool_mb
FROM v$parameter
WHERE name = 'streams_pool_size';

column v_sga_target_mb new_value v_sga_target_mb noprint
SELECT to_char(
         round(
           CASE
             WHEN regexp_like(value, '^[0-9]+(\.[0-9]+)?[KMG]$', 'i') THEN
               CASE UPPER(substr(value, -1))
                 WHEN 'K' THEN to_number(substr(value, 1, length(value)-1)) / 1024
                 WHEN 'M' THEN to_number(substr(value, 1, length(value)-1))
                 WHEN 'G' THEN to_number(substr(value, 1, length(value)-1)) * 1024
               END
             WHEN regexp_like(value, '^[0-9]+(\.[0-9]+)?$') THEN
               to_number(value) / 1024 / 1024
             ELSE NULL
           END
         )
       ) AS v_sga_target_mb
FROM v$parameter
WHERE name = 'sga_target';

SET TERMOUT ON

prompt Configuring XStream Performance parameters (MAX_SGA_SIZE and CHECKPOINT_RETENTION_TIME)
prompt
prompt Note: The total SGA memory allocated for all components on a database must be less than the value of STREAMS_POOL_SIZE parameter. Review the STREAMS_POOL_SIZE and SGA_TARGET documentation to set MAX_SGA_SIZE correctly.
prompt
prompt Detected:
prompt streams_pool_size: &&v_streams_pool_mb MB
prompt sga_target: &&v_sga_target_mb MB
prompt

accept p_xs_max_sga char prompt 'XStream MAX_SGA_SIZE (MB) [default: 1024]: ' default '1024'
prompt

accept p_xs_ckpt_days char prompt 'Capture CHECKPOINT_RETENTION_TIME (days) [default: 7]: ' default '7'
prompt

-- ---------------------------
-- Positive integer validation
-- ---------------------------
DECLARE
  PROCEDURE assert_positive_int(p_value varchar2, p_label varchar2, p_err number) IS
  BEGIN
    IF NOT regexp_like(nvl(p_value, ''), '^[0-9]+$') OR to_number(p_value) <= 0 THEN
       RAISE_APPLICATION_ERROR(p_err, p_label || ' must be a positive integer.');
    END IF;
  END;
BEGIN
  assert_positive_int('&&p_xs_max_sga', 'MAX_SGA_SIZE (MB)', -20201);
  assert_positive_int('&&p_xs_ckpt_days', 'CHECKPOINT_RETENTION_TIME (days)', -20202);
END;
/

SET TERMOUT OFF
SPOOL &&P_OUTFILE APPEND

SELECT '-- Step 6A: Configure XStream parameters' FROM dual;
SELECT 'declare' FROM dual;
SELECT '  v_cap_name varchar2(128);' FROM dual;
SELECT 'begin' FROM dual;
SELECT '  SELECT capture_name' FROM dual;
SELECT '    into v_cap_name' FROM dual;
SELECT '    FROM dba_xstream_outbound' FROM dual;
SELECT '   WHERE upper(server_name) = upper(''&&p_xout_name'');' FROM dual;
SELECT '' FROM dual;

SELECT '  DBMS_XSTREAM_ADM.SET_PARAMETER(' FROM dual;
SELECT '    streams_type => ''capture'',' FROM dual;
SELECT '    streams_name => v_cap_name,' FROM dual;
SELECT '    parameter    => ''max_sga_size'',' FROM dual;
SELECT '    value        => ''&&p_xs_max_sga'');' FROM dual;
SELECT '' FROM dual;

SELECT '  DBMS_XSTREAM_ADM.SET_PARAMETER(' FROM dual;
SELECT '    streams_type => ''apply'',' FROM dual;
SELECT '    streams_name => ''&&p_xout_name'',' FROM dual;
SELECT '    parameter    => ''max_sga_size'',' FROM dual;
SELECT '    value        => ''&&p_xs_max_sga'');' FROM dual;
SELECT '' FROM dual;

SELECT '  DBMS_CAPTURE_ADM.ALTER_CAPTURE(' FROM dual;
SELECT '    capture_name              => v_cap_name,' FROM dual;
SELECT '    checkpoint_retention_time => &&p_xs_ckpt_days);' FROM dual;

SELECT '-- Step 6B: Configure RAC parameters' FROM dual
 WHERE '&&v_is_rac' = 'YES';

SELECT '  DBMS_CAPTURE_ADM.SET_PARAMETER(' FROM dual
 WHERE '&&v_is_rac' = 'YES';
SELECT '    capture_name => v_cap_name,' FROM dual
 WHERE '&&v_is_rac' = 'YES';
SELECT '    parameter    => ''USE_RAC_SERVICE'',' FROM dual
 WHERE '&&v_is_rac' = 'YES';
SELECT '    value        => ''Y'');' FROM dual
 WHERE '&&v_is_rac' = 'YES';

SELECT 'end;' FROM dual;
SELECT '/' FROM dual;

SELECT '' FROM dual;

SELECT 'alter session set container = CDB$ROOT;' FROM dual
 WHERE '&&v_is_cdb' = 'YES';

SPOOL OFF
SET TERMOUT ON

prompt Generated file: &&p_outfile
prompt
SELECT 'NOTE: Review ' || '&&p_outfile' ||
       ' for the (commented) maintenance-window commands to enable ARCHIVELOG mode.'
FROM dual
WHERE '&&v_log_mode' <> 'ARCHIVELOG';

SELECT 'NOTE: The CREATE TABLESPACE commands were generated with placeholder paths:' FROM dual
 WHERE '&&v_use_base_dir' = 'NO' AND ('&&v_create_con' = 'YES' OR '&&v_create_cap' = 'YES');
SELECT '      <TO_BE_FILLED>/<tablespace>.dbf' FROM dual
 WHERE '&&v_use_base_dir' = 'NO' AND ('&&v_create_con' = 'YES' OR '&&v_create_cap' = 'YES');
SELECT '      Update all DATAFILE paths in '||'&&p_outfile'||' before running it.' FROM dual
 WHERE '&&v_use_base_dir' = 'NO' AND ('&&v_create_con' = 'YES' OR '&&v_create_cap' = 'YES');

prompt
prompt Run it later using: @&&p_outfile

SET FEEDBACK ON
SET HEADING ON
SET PAGESIZE 100
