\timing on
START TRANSACTION;
SET local client_min_messages = NOTICE;
SET local log_min_messages = NOTICE;

--sys.dba_tab_columns 91.569
--should keep same as rollback_catalog_maindb_91_569.sql rollback_catalog_otherdb_91_569.sql
CREATE OR REPLACE VIEW sys.dba_tab_columns AS
    SELECT
        CAST(owner.rolname AS VARCHAR2(64)) AS OWNER,
        CAST(tbl.relname AS VARCHAR2(64)) AS TABLE_NAME,
        CAST(col.attname AS VARCHAR2(64)) AS COLUMN_NAME,
        CAST(ty.typname AS VARCHAR2(128)) AS DATA_TYPE,
        CAST(col.attnum AS INTEGER) AS COLUMN_ID,
        CAST(
            (CASE (col.attlen < 0 AND ty.typname = 'varchar' AND spa.nspname = 'pg_catalog')
                WHEN TRUE THEN (col.atttypmod - 4) ::INTEGER
                ELSE col.attlen
            END) AS INTEGER) AS DATA_LENGTH,
        des.description AS COMMENTS,
        CAST(sta.avg_width AS NUMBER) AS AVG_COL_LEN,
        CASE
            WHEN (col.attnotnull) THEN 'n'::BPCHAR
            ELSE 'y'::BPCHAR
        END AS NULLABLE,
        CASE
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1)
                THEN (((col.atttypmod - 4) >> 16) & 65535) ::INTEGER
            ELSE null
        END AS DATA_PRECISION,
        CASE
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1)
                THEN ((col.atttypmod - 4) & 65535) ::INTEGER
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod = -1)
                THEN null
            ELSE 0::INTEGER
        END AS DATA_SCALE,
        CASE
            WHEN (ty.typname = 'varchar' OR ty.typname = 'nvarchar2'
                OR ty.typname = 'bpchar' OR ty.typname = 'char'
                AND spa.nspname = 'pg_catalog')
                THEN (col.atttypmod - 4) ::NUMBER
            ELSE 0 ::NUMBER
        END AS CHAR_LENGTH
    FROM pg_attribute col
        INNER JOIN pg_class tbl ON (col.attrelid = tbl.oid)
        INNER JOIN pg_authid owner ON (tbl.relowner = owner.oid)
        INNER JOIN pg_type ty ON (col.atttypid = ty.oid)
        LEFT JOIN PG_DESCRIPTION des ON (col.attrelid = des.objoid AND col.attnum = des.objsubid)
        LEFT JOIN pg_stats sta ON (tbl.relname = sta.tablename AND col.attname = sta.attname)
        INNER JOIN pg_namespace spa ON (spa.oid = ty.typnamespace)
    WHERE col.attnum > 0
        AND tbl.relkind='r';

--sys.all_tab_columns 91.569
--should keep same as rollback_catalog_maindb_91_569.sql rollback_catalog_otherdb_91_569.sql
CREATE OR REPLACE VIEW sys.all_tab_columns AS
    SELECT
        CAST(owner.rolname AS VARCHAR2(64)) AS OWNER,
        CAST(tbl.relname AS VARCHAR2(64)) AS TABLE_NAME,
        CAST(col.attname AS VARCHAR2(64)) AS COLUMN_NAME,
        CAST(ty.typname AS VARCHAR2(128)) AS DATA_TYPE,
        CAST(col.attnum AS INTEGER) AS COLUMN_ID,
        CAST(
            (CASE (col.attlen < 0 AND ty.typname = 'varchar' AND spa.nspname = 'pg_catalog')
                WHEN TRUE THEN (col.atttypmod - 4) ::INTEGER
                ELSE col.attlen
            END) AS INTEGER) AS DATA_LENGTH,
        des.description AS COMMENTS,
        CAST(sta.avg_width AS NUMBER) AS AVG_COL_LEN,
        CASE
            WHEN (col.attnotnull) THEN 'n'::BPCHAR
            ELSE 'y'::BPCHAR
        END AS NULLABLE,
        CASE
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1)
                THEN (((col.atttypmod - 4) >> 16) & 65535) ::INTEGER
            ELSE null
        END AS DATA_PRECISION,
        CASE
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1)
                THEN ((col.atttypmod - 4) & 65535) ::INTEGER
            WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod = -1)
                THEN null
            ELSE 0::INTEGER
        END AS DATA_SCALE,
        CASE
            WHEN (ty.typname = 'varchar' OR ty.typname = 'nvarchar2'
                OR ty.typname = 'bpchar' OR ty.typname = 'char'
                AND spa.nspname = 'pg_catalog')
                THEN (col.atttypmod - 4) ::NUMBER
            ELSE 0 ::NUMBER
        END AS CHAR_LENGTH
    FROM pg_attribute col
        INNER JOIN pg_class tbl ON (col.attrelid = tbl.oid)
        INNER JOIN pg_authid owner ON (tbl.relowner = owner.oid)
        INNER JOIN pg_type ty ON (col.atttypid = ty.oid)
        LEFT JOIN PG_DESCRIPTION des ON (col.attrelid = des.objoid AND col.attnum = des.objsubid)
        LEFT JOIN pg_stats sta ON (tbl.relname = sta.tablename AND col.attname = sta.attname)
        INNER JOIN pg_namespace spa ON (spa.oid = ty.typnamespace)
    WHERE col.attnum > 0
        AND tbl.relkind='r'
        AND (pg_catalog.pg_has_role(tbl.relowner, 'usage')
                OR pg_catalog.has_table_privilege(tbl.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
                OR pg_catalog.has_any_column_privilege(tbl.oid, 'select, insert, update, references'));

--sys.all_ind_columns 91.614
--should keep same as rollback_catalog_maindb_91_614.sql rollback_catalog_otherdb_91_614.sql
CREATE OR REPLACE VIEW SYS.ALL_IND_COLUMNS AS
    SELECT
        CAST(a1.rolname AS VARCHAR2(64)) AS INDEX_OWNER,
        CAST(c1.relname AS VARCHAR2(64)) AS INDEX_NAME,
        CAST(a2.rolname AS VARCHAR2(64)) AS TABLE_OWNER,
        CAST(c2.relname AS VARCHAR2(64)) AS TABLE_NAME,
        b.attname AS COLUMN_NAME,
        b.attnum AS COLUMN_POSITION
    FROM pg_class c1
        INNER JOIN pg_authid a1 ON (a1.oid = c1.relowner)
        INNER JOIN pg_attribute b ON (c1.oid = b.attrelid)
        INNER JOIN pg_index i ON (i.indexrelid = c1.oid)
        INNER JOIN pg_class c2 ON (c2.oid = i.indrelid)
        INNER JOIN pg_authid a2 ON (a2.oid = c2.relowner)
    WHERE c1.relkind = 'i'
        AND (pg_catalog.pg_has_role(c1.relowner, 'usage')
            OR pg_catalog.has_table_privilege(c1.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
            OR pg_catalog.has_any_column_privilege(c1.oid, 'select, insert, update, references'));

--sys.all_ind_expressions 91.614
--should keep same as rollback_catalog_maindb_91_614.sql rollback_catalog_otherdb_91_614.sql
CREATE OR REPLACE VIEW SYS.ALL_IND_EXPRESSIONS AS
    SELECT
        CAST(a1.rolname AS VARCHAR2(64)) AS INDEX_OWNER,
        CAST(c1.relname AS VARCHAR2(64)) AS INDEX_NAME,
        CAST(a2.rolname AS VARCHAR2(64)) AS TABLE_OWNER,
        CAST(c2.relname AS VARCHAR2(64)) AS TABLE_NAME,
        pg_catalog.pg_get_indexdef(i.indexrelid, b.attnum::int, true) AS COLUMN_EXPRESSION,
        b.attnum AS COLUMN_POSITION
    FROM pg_class c1
        INNER JOIN pg_authid a1 ON (a1.oid = c1.relowner)
        INNER JOIN pg_index i ON (i.indexrelid = c1.oid)
        INNER JOIN pg_class c2 ON (c2.oid = i.indrelid)
        INNER JOIN pg_authid a2 ON (a2.oid = c2.relowner)
        INNER JOIN pg_attribute b ON (c1.oid = b.attrelid)
    WHERE c1.relkind = 'i'
        AND i.indkey[b.attnum - 1] = '0'
        AND (pg_catalog.pg_has_role(c1.relowner, 'usage')
                OR pg_catalog.has_table_privilege(c1.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
                OR pg_catalog.has_any_column_privilege(c1.oid, 'select, insert, update, references'));

COMMIT;
