AWS SCT および AWS DMS を使用した移行後のデータベースオブジェクトの検証

SELECT object_name AS package_name
  FROM all_objects 
 WHERE object_type = 'PACKAGE'
   AND owner = upper('your_schema')
ORDER BY upper(object_name);

SELECT upper(schema_name) AS package_name
  FROM information_schema.schemata
 WHERE schema_name not in('pg_catalog','information_schema', lower('your_schema'))
ORDER BY upper(schema_name);

SELECT 
       owner AS schema_name,
       trigger_name,
       table_name,
       triggering_event,
       trigger_type
  FROM ALL_TRIGGERS
 WHERE owner = upper('your_schema')
ORDER BY trigger_name;

SELECT
       upper(trigger_schema)     AS schema_name,
       upper(trigger_name)       AS trigger_name,
       upper(event_object_table) AS table_name,
       string_agg(upper(event_manipulation), ' OR ' ORDER BY CASE WHEN  event_manipulation = 'INSERT' THEN 1 WHEN event_manipulation = 'UPDATE' THEN 2 ELSE 3 END) AS triggering_event,
       upper(action_timing) || ' ' || CASE WHEN action_orientation = 'ROW' THEN 'EACH ROW' ELSE action_orientation END AS trigger_type
  FROM information_schema.triggers
 WHERE trigger_schema = lower('your_schema')
GROUP BY trigger_schema, trigger_name, event_object_table, action_timing, action_orientation
ORDER BY upper(trigger_name);

SELECT owner           AS schema_name,
       table_name,
       constraint_name AS object_name,
       'PRIMARY KEY'   AS object_type
  FROM all_constraints
 WHERE owner = upper('your_schema') 
   AND constraint_type = 'P';

SELECT upper(n.nspname)        AS schema_name,
       trim(upper(conrelid::regclass::varchar), '"') AS table_name,
       upper(conname::varchar) AS object_name,
       'PRIMARY KEY'           AS object_type
  FROM pg_constraint c
       JOIN pg_namespace n ON n.oid = c.connamespace
 WHERE contype in ('p') 
   AND n.nspname = lower('your_schema');

WITH cols AS (
    SELECT idx.owner AS schema_name, idx.table_name, idx.index_name, cols.column_name, cols.column_position, idx.uniqueness, decode(cols.descend, 'ASC', '', ' '||cols.descend) descend
      FROM ALL_INDEXES idx, ALL_IND_COLUMNS cols
     WHERE idx.owner = cols.index_owner AND idx.table_name = cols.table_name AND idx.index_name = cols.index_name
       AND idx.owner = upper('your_schema')
),
expr AS (
   SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')         AS table_name
   ,      extractValue(xs.object_value, '/ROW/INDEX_NAME')         AS index_name
   ,      extractValue(xs.object_value, '/ROW/COLUMN_EXPRESSION')  AS column_expression
   ,      extractValue(xs.object_value, '/ROW/COLUMN_POSITION')    AS column_position
  FROM (
         SELECT XMLTYPE(
              DBMS_XMLGEN.GETXML('SELECT table_name, index_name, column_expression, column_position FROM ALL_IND_EXPRESSIONS WHERE index_owner = upper(''your_schema'') '
              )
           ) AS xml FROM DUAL
       ) x
   , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
SELECT 
       cols.schema_name,
       cols.table_name,
       cols.index_name AS object_name,
       'INDEX'         AS object_type,
       'CREATE'|| decode(cols.uniqueness, 'UNIQUE', ' '||cols.uniqueness) || ' INDEX ' || cols.index_name || ' ON VESTEK.' || cols.table_name || ' USING BTREE (' ||
            listagg(CASE WHEN cols.column_name LIKE 'SYS_N%' THEN expr.column_expression || cols.descend ELSE cols.column_name || cols.descend END, ', ') within group(order by cols.column_position) || ')' AS condition_column
FROM cols
     LEFT OUTER JOIN expr ON cols.table_name = expr.table_name
           AND cols.index_name = expr.index_name
           AND cols.column_position = expr.column_position
GROUP BY cols.schema_name, cols.table_name, cols.index_name, cols.uniqueness;

SELECT upper(schemaname) AS schema_name,
       upper(tablename)  AS table_name,
       upper(indexname)  AS object_name,
       'INDEX'           AS object_type,
       upper(indexdef)   AS condition_column
  FROM pg_indexes
 WHERE schemaname = lower('your_schema');

WITH ref AS (
 SELECT   extractValue(xs.object_value, '/ROW/OWNER')            AS schema_name
   ,      extractValue(xs.object_value, '/ROW/TABLE_NAME')       AS table_name
   ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME')  AS object_name
   ,      extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS condition_column
   ,      extractValue(xs.object_value, '/ROW/COLUMN_NAME')      AS column_name
  FROM (
         SELECT XMLTYPE(
              DBMS_XMLGEN.GETXML('SELECT cons.owner, cons.table_name, cons.constraint_name, cons.search_condition, cols.column_name
                           FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols
                           WHERE cons.owner =  cols.owner AND cons.table_name =  cols.table_name AND cons.constraint_name = cols.constraint_name
                             AND cons.owner = upper(''your_schema'') AND cons.constraint_type = ''C'' '
                             )
           ) AS xml FROM DUAL
       ) x
   , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
SELECT * FROM (
SELECT 
       schema_name,
       table_name,
       object_name,
       'CHECK'                AS object_type,
       condition_column       AS check_condition
FROM ref
UNION
SELECT 
       owner                  AS schema_name,
       table_name,
       'SYS_C0000'||column_id AS object_name,
       'CHECK'                AS object_type,
       '"'||column_name||'" IS NOT NULL' AS check_condition
FROM all_tab_columns tcols where owner = upper('your_schema') and nullable = 'N'
AND NOT EXISTS ( SELECT 1 FROM ref WHERE ref.table_name = tcols.table_name
                          AND ref.column_name = tcols.column_name
                          AND ref.condition_column = '"'||tcols.column_name||'" IS NOT NULL')
);

SELECT * FROM (
    SELECT 
           upper(n.nspname)                       AS schema_name,
           upper(c.relname)                       AS table_name,
           'SYS_C0000'||attnum                    AS object_name,
           'CHECK'                                AS constraint_type,
           '"'||upper(a.attname)||'" IS NOT NULL' AS check_condition
      FROM pg_attribute a, pg_class c, pg_namespace n
     WHERE a.attrelid = c.oid and c.relnamespace = n.oid AND n.nspname = 'vestek'
       AND attnotnull AND attstattarget <> 0
     UNION
    SELECT 
           upper(n.nspname),
           upper(conrelid::regclass::varchar)     AS table_name,
           upper(conname::varchar)                AS object_name,
           'CHECK'                                AS object_type,
           upper(pg_get_constraintdef(c.oid))     AS check_condition
      FROM pg_constraint c
           JOIN pg_namespace n ON n.oid = c.connamespace
     WHERE  contype in ('c')
       AND conrelid::regclass::varchar <> '-'
) a
WHERE schema_name = lower('your_schema');

SELECT 
       c.owner           AS schema_name,
       c.table_name,
       c.constraint_name AS object_name,
       'FOREIGN KEY'     AS object_type,
       'FOREIGN KEY ('|| cc.fk_column || ') REFERENCES ' || p.table_name || '('|| pc.ref_column ||') NOT VALID' AS condition_column
FROM ( SELECT owner, table_name, constraint_name, r_constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type = 'R') c,
     ( SELECT table_name, constraint_name FROM ALL_CONSTRAINTS WHERE owner = upper('your_schema') AND constraint_type IN('P', 'U') ) p,
     ( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) fk_column
         FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) cc,
     ( SELECT owner, table_name, constraint_name, listagg(column_name, ', ') WITHIN group(ORDER BY position) ref_column
         FROM ALL_CONS_COLUMNS WHERE owner = upper('your_schema') GROUP BY owner, table_name, constraint_name ) pc
WHERE c.r_constraint_name = p.constraint_name
  AND c.table_name = cc.table_name AND c.constraint_name = cc.constraint_name
  AND p.table_name = pc.table_name AND p.constraint_name = pc.constraint_name;

SELECT n.nspname::text     AS schema_name,
       trim(upper(conrelid::regclass::varchar), '"') AS table_name, upper(conname::varchar)    AS object_name,
       'FOREIGN KEY'       AS object_type,
       CASE contype WHEN 'f' THEN upper(pg_get_constraintdef(c.oid)) END AS  condition_column
  FROM pg_constraint c
       JOIN pg_namespace n ON n.oid = c.connamespace
 WHERE contype in ('f')
   AND c.connamespace::text = lower('your_schema');