Closed NickSharma closed 11 months ago
sualeh
commented
11 months ago @NickSharma You should limit by using --schemas also.
Please run this from the command-line rather than the shell. This way you can turn on logging. In order to see where time is being spent, you will attach logs to the issue.
NickSharma
commented
11 months ago @sualeh
./schemacrawler.sh --server=oracle --host=XXXXX.com --port=1523 --database=KRAKOW --schemas=GL --user=cdluser --password=abcde --grep-tables=.*.GL_JE_LINES --info-level=maximum --command=schema --log-level=CONFIG
./schemacrawler.sh --server=oracle --host=XXXXX.com --port=1523 --database=KRAKOW --schemas=GL --user=cdluser --password=abcde --tables=.*.GL_JE_LINES --info-level=maximum --command=schema --log-level=CONFIG
Both of these commands are running for a while now for just a single table, am I missing anything related to regex or command option?
LogStack:
THEN 0
ELSE 1
END AS FUNCTION_TYPE,
NULL AS REMARKS
FROM
DBA_PROCEDURES FUNCTIONS
LEFT OUTER JOIN DBA_ARGUMENTS ARGUMENTS
ON FUNCTIONS.OBJECT_ID = ARGUMENTS.OBJECT_ID
AND ARGUMENTS.ARGUMENT_NAME IS NULL
AND ARGUMENTS.POSITION = 0
INNER JOIN DBA_USERS USERS
ON FUNCTIONS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(FUNCTIONS.OWNER, '${schemas}')
AND FUNCTIONS.OBJECT_TYPE = 'FUNCTION'
ORDER BY
FUNCTION_SCHEM,
FUNCTION_NAME
",
"FUNCTION_COLUMNS": "SELECT
NULL AS FUNCTION_CAT,
COLUMNS.OWNER AS FUNCTION_SCHEM,
COLUMNS.OBJECT_NAME AS FUNCTION_NAME,
COLUMNS.ARGUMENT_NAME AS COLUMN_NAME,
CASE
WHEN COLUMNS.POSITION = 0 THEN 4
WHEN COLUMNS.IN_OUT = 'IN' THEN 1
WHEN COLUMNS.IN_OUT = 'OUT' THEN 3
WHEN COLUMNS.IN_OUT = 'IN/OUT' THEN 2
ELSE 0
END AS COLUMN_TYPE,
DECODE(
(SELECT A.TYPECODE
FROM DBA_TYPES A
WHERE A.TYPE_NAME = COLUMNS.DATA_TYPE
AND (A.OWNER = COLUMNS.OWNER OR A.OWNER IS NULL)),
'OBJECT', 2002,
'COLLECTION', 2003,
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 1, 9),
'TIMESTAMP',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 10, 1),
'(',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 19, 5),
'LOCAL', -102, 'TIME ', -101, 93),
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 16, 5),
'LOCAL', -102, 'TIME ', -101, 93)),
'INTERVAL ',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 10, 3),
'DAY', -104, 'YEA', -103),
DECODE(COLUMNS.DATA_TYPE,
'BINARY_DOUBLE', 101,
'BINARY_FLOAT', 100,
'BFILE', -13,
'BLOB', 2004,
'CHAR', 1,
'CLOB', 2005,
'COLLECTION', 2003,
'DATE', 93,
'FLOAT', 6,
'LONG', -1,
'LONG RAW', -4,
'NCHAR', -15,
'NCLOB', 2011,
'NUMBER', 3,
'NVARCHAR', -9,
'NVARCHAR2', -9,
'OBJECT', 2002,
'OPAQUE/XMLTYPE', 2009,
'RAW', -3,
'REF', 2006,
'ROWID', -8,
'SQLXML', 2009,
'UROWID', -8,
'VARCHAR2', 12,
'VARRAY', 2003,
'XMLTYPE', 2009,
1111)))
AS DATA_TYPE,
COLUMNS.DATA_TYPE AS TYPE_NAME,
DECODE (COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_SCALE, NULL, 0 , 38), DECODE (COLUMNS.DATA_TYPE, 'CHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR2', COLUMNS.CHAR_LENGTH, 'NVARCHAR2', COLUMNS.CHAR_LENGTH, 'NCHAR', COLUMNS.CHAR_LENGTH, 'NUMBER', 0, COLUMNS.DATA_LENGTH) ), COLUMNS.DATA_PRECISION)
AS PRECISION,
COLUMNS.DATA_LENGTH AS LENGTH,
DECODE (COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_SCALE, NULL, -127 , COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE)
AS SCALE,
COLUMNS.RADIX AS RADIX,
0 AS NULLABLE,
NULL AS REMARKS,
COLUMNS.DEFAULT_VALUE AS COLUMN_DEF,
0 AS SQL_DATA_TYPE,
0 AS SQL_DATETIME_SUB,
COLUMNS.DATA_LENGTH AS CHAR_OCTET_LENGTH,
COLUMNS.POSITION AS ORDINAL_POSITION,
'NO' AS IS_NULLABLE,
COLUMNS.OBJECT_NAME AS SPECIFIC_NAME
FROM
DBA_PROCEDURES FUNCTIONS
INNER JOIN DBA_ARGUMENTS COLUMNS
ON FUNCTIONS.OBJECT_ID = COLUMNS.OBJECT_ID
INNER JOIN DBA_USERS USERS
ON FUNCTIONS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(COLUMNS.OWNER, '${schemas}')
AND COLUMNS.OBJECT_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(COLUMNS.OBJECT_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
AND FUNCTIONS.OBJECT_TYPE = 'FUNCTION'
ORDER BY
FUNCTION_SCHEM,
FUNCTION_NAME,
ORDINAL_POSITION
",
"INDEXES": "SELECT
NULL AS TABLE_CAT,
INDEXES.OWNER AS TABLE_SCHEM,
INDEXES.TABLE_NAME,
DECODE (INDEXES.UNIQUENESS, 'UNIQUE', 0, 1) AS NON_UNIQUE,
NULL AS INDEX_QUALIFIER,
INDEXES.INDEX_NAME,
3 AS TYPE, -- JDBC index type "Other"
INDEX_COLUMNS.COLUMN_POSITION AS ORDINAL_POSITION,
INDEX_COLUMNS.COLUMN_NAME,
CASE WHEN DESCEND = 'ASC' THEN 'A' ELSE 'D' END AS ASC_OR_DESC,
INDEXES.DISTINCT_KEYS AS CARDINALITY,
INDEXES.LEAF_BLOCKS AS PAGES,
NULL AS FILTER_CONDITION
FROM
DBA_INDEXES INDEXES
INNER JOIN DBA_IND_COLUMNS INDEX_COLUMNS
ON
INDEXES.INDEX_NAME = INDEX_COLUMNS.INDEX_NAME
AND INDEXES.TABLE_OWNER = INDEX_COLUMNS.TABLE_OWNER
AND INDEXES.TABLE_NAME = INDEX_COLUMNS.TABLE_NAME
AND INDEXES.OWNER = INDEX_COLUMNS.INDEX_OWNER
INNER JOIN DBA_USERS USERS
ON INDEXES.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(INDEXES.OWNER, '${schemas}')
AND INDEXES.TABLE_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(INDEXES.TABLE_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
ORDER BY
TABLE_SCHEM,
TABLE_NAME,
INDEX_NAME,
NON_UNIQUE,
TYPE,
ORDINAL_POSITION
",
"PRIMARY_KEYS": "SELECT
NULL AS TABLE_CAT,
PRIMARY_KEYS.OWNER AS TABLE_SCHEM,
PRIMARY_KEYS.TABLE_NAME,
PRIMARY_KEYS.CONSTRAINT_NAME AS PK_NAME,
PK_COLUMNS.COLUMN_POSITION AS KEY_SEQ,
PK_COLUMNS.COLUMN_NAME
FROM
DBA_CONSTRAINTS PRIMARY_KEYS
INNER JOIN DBA_IND_COLUMNS PK_COLUMNS
ON
PRIMARY_KEYS.CONSTRAINT_NAME = PK_COLUMNS.INDEX_NAME
AND PRIMARY_KEYS.OWNER = PK_COLUMNS.TABLE_OWNER
AND PRIMARY_KEYS.TABLE_NAME = PK_COLUMNS.TABLE_NAME
AND PRIMARY_KEYS.OWNER = PK_COLUMNS.INDEX_OWNER
INNER JOIN DBA_USERS USERS
ON PRIMARY_KEYS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(PRIMARY_KEYS.OWNER, '${schemas}')
AND PRIMARY_KEYS.TABLE_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(PRIMARY_KEYS.TABLE_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
AND PRIMARY_KEYS.CONSTRAINT_TYPE = 'P'
ORDER BY
TABLE_SCHEM,
TABLE_NAME,
PK_NAME,
KEY_SEQ
",
"PROCEDURES": "SELECT
NULL AS PROCEDURE_CAT,
PROCEDURES.OWNER AS PROCEDURE_SCHEM,
PROCEDURES.OBJECT_NAME AS PROCEDURE_NAME,
PROCEDURES.OBJECT_NAME AS SPECIFIC_NAME,
CASE WHEN ARGUMENTS.OBJECT_ID IS NULL
THEN 1
ELSE 2
END AS PROCEDURE_TYPE,
NULL AS REMARKS
FROM
DBA_PROCEDURES PROCEDURES
LEFT OUTER JOIN DBA_ARGUMENTS ARGUMENTS
ON PROCEDURES.OBJECT_ID = ARGUMENTS.OBJECT_ID
AND ARGUMENTS.ARGUMENT_NAME IS NULL
AND ARGUMENTS.POSITION = 0
INNER JOIN DBA_USERS USERS
ON PROCEDURES.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(PROCEDURES.OWNER, '${schemas}')
AND PROCEDURES.OBJECT_TYPE = 'PROCEDURE'
ORDER BY
PROCEDURE_SCHEM,
PROCEDURE_NAME
",
"PROCEDURE_COLUMNS": "SELECT
NULL AS PROCEDURE_CAT,
COLUMNS.OWNER AS PROCEDURE_SCHEM,
COLUMNS.OBJECT_NAME AS PROCEDURE_NAME,
COLUMNS.ARGUMENT_NAME AS COLUMN_NAME,
CASE
WHEN COLUMNS.POSITION = 0 THEN 5
WHEN COLUMNS.IN_OUT = 'IN' THEN 1
WHEN COLUMNS.IN_OUT = 'OUT' THEN 4
WHEN COLUMNS.IN_OUT = 'IN/OUT' THEN 2
ELSE 0
END AS COLUMN_TYPE,
DECODE(
(SELECT A.TYPECODE
FROM DBA_TYPES A
WHERE A.TYPE_NAME = COLUMNS.DATA_TYPE
AND (A.OWNER = COLUMNS.OWNER OR A.OWNER IS NULL)),
'OBJECT', 2002,
'COLLECTION', 2003,
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 1, 9),
'TIMESTAMP',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 10, 1),
'(',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 19, 5),
'LOCAL', -102, 'TIME ', -101, 93),
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 16, 5),
'LOCAL', -102, 'TIME ', -101, 93)),
'INTERVAL ',
DECODE(SUBSTR(COLUMNS.DATA_TYPE, 10, 3),
'DAY', -104, 'YEA', -103),
DECODE(COLUMNS.DATA_TYPE,
'BINARY_DOUBLE', 101,
'BINARY_FLOAT', 100,
'BFILE', -13,
'BLOB', 2004,
'CHAR', 1,
'CLOB', 2005,
'COLLECTION', 2003,
'DATE', 93,
'FLOAT', 6,
'LONG', -1,
'LONG RAW', -4,
'NCHAR', -15,
'NCLOB', 2011,
'NUMBER', 3,
'NVARCHAR', -9,
'NVARCHAR2', -9,
'OBJECT', 2002,
'OPAQUE/XMLTYPE', 2009,
'RAW', -3,
'REF', 2006,
'ROWID', -8,
'SQLXML', 2009,
'UROWID', -8,
'VARCHAR2', 12,
'VARRAY', 2003,
'XMLTYPE', 2009,
1111)))
AS DATA_TYPE,
COLUMNS.DATA_TYPE AS TYPE_NAME,
DECODE (COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_SCALE, NULL, 0 , 38), DECODE (COLUMNS.DATA_TYPE, 'CHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR2', COLUMNS.CHAR_LENGTH, 'NVARCHAR2', COLUMNS.CHAR_LENGTH, 'NCHAR', COLUMNS.CHAR_LENGTH, 'NUMBER', 0, COLUMNS.DATA_LENGTH) ), COLUMNS.DATA_PRECISION)
AS PRECISION,
COLUMNS.DATA_LENGTH AS LENGTH,
DECODE (COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_SCALE, NULL, -127 , COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE)
AS SCALE,
COLUMNS.RADIX AS RADIX,
0 AS NULLABLE,
NULL AS REMARKS,
COLUMNS.DEFAULT_VALUE AS COLUMN_DEF,
0 AS SQL_DATA_TYPE,
0 AS SQL_DATETIME_SUB,
COLUMNS.DATA_LENGTH AS CHAR_OCTET_LENGTH,
COLUMNS.POSITION AS ORDINAL_POSITION,
'NO' AS IS_NULLABLE,
COLUMNS.OBJECT_NAME AS SPECIFIC_NAME
FROM
DBA_PROCEDURES PROCEDURES
INNER JOIN DBA_ARGUMENTS COLUMNS
ON PROCEDURES.OBJECT_ID = COLUMNS.OBJECT_ID
INNER JOIN DBA_USERS USERS
ON PROCEDURES.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(COLUMNS.OWNER, '${schemas}')
AND COLUMNS.OBJECT_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(COLUMNS.OBJECT_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
AND PROCEDURES.OBJECT_TYPE = 'PROCEDURE'
ORDER BY
PROCEDURE_SCHEM,
PROCEDURE_NAME,
ORDINAL_POSITION
",
"ROUTINES": "SELECT
NULL AS ROUTINE_CATALOG,
PROCEDURES.OWNER AS ROUTINE_SCHEMA,
PROCEDURES.OBJECT_NAME AS ROUTINE_NAME,
PROCEDURES.OBJECT_NAME AS SPECIFIC_NAME,
'SQL' AS ROUTINE_BODY,
DBMS_METADATA.GET_DDL(OBJECT_TYPE, PROCEDURES.OBJECT_NAME, PROCEDURES.OWNER)
AS ROUTINE_DEFINITION
FROM
DBA_PROCEDURES PROCEDURES
INNER JOIN DBA_USERS USERS
ON PROCEDURES.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(PROCEDURES.OWNER, '${schemas}')
ORDER BY
ROUTINE_SCHEMA,
ROUTINE_NAME
",
"SEQUENCES": "SELECT
NULL AS SEQUENCE_CATALOG,
SEQUENCES.SEQUENCE_OWNER AS SEQUENCE_SCHEMA,
SEQUENCES.SEQUENCE_NAME AS SEQUENCE_NAME,
SEQUENCES.INCREMENT_BY AS "INCREMENT",
NULL AS START_VALUE,
SEQUENCES.MIN_VALUE AS MINIMUM_VALUE,
SEQUENCES.MAX_VALUE AS MAXIMUM_VALUE,
CASE WHEN SEQUENCES.CYCLE_FLAG = 'Y' THEN 'YES' ELSE 'NO' END AS CYCLE_OPTION,
SEQUENCES.ORDER_FLAG,
SEQUENCES.CACHE_SIZE,
SEQUENCES.LAST_NUMBER
FROM
DBA_SEQUENCES SEQUENCES
INNER JOIN DBA_USERS USERS
ON SEQUENCES.SEQUENCE_OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(SEQUENCES.SEQUENCE_OWNER, '${schemas}')
ORDER BY
SEQUENCE_OWNER,
SEQUENCE_NAME
",
"SERVER_INFORMATION": "SELECT
'GLOBAL_NAME' AS NAME,
GLOBAL_NAME AS VALUE,
'' AS DESCRIPTION
FROM
GLOBAL_NAME
",
"TABLES": "SELECT
NULL AS TABLE_CAT,
TABLES.OWNER AS TABLE_SCHEM,
TABLES.TABLE_NAME AS TABLE_NAME,
TABLES.TABLE_TYPE AS TABLE_TYPE,
TABLES.COMMENTS AS REMARKS
FROM
DBA_TAB_COMMENTS TABLES
INNER JOIN DBA_USERS USERS
ON TABLES.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(TABLES.OWNER, '${schemas}')
AND TABLES.TABLE_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(TABLES.TABLE_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
UNION ALL
SELECT
NULL AS TABLE_CAT,
MVIEWS.OWNER AS TABLE_SCHEM,
MVIEWS.MVIEW_NAME AS TABLE_NAME,
'MATERIALIZED VIEW' AS TABLE_TYPE,
MVIEWS.COMMENTS AS REMARKS
FROM
DBA_MVIEW_COMMENTS MVIEWS
INNER JOIN DBA_USERS USERS
ON MVIEWS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(MVIEWS.OWNER, '${schemas}')
",
"TABLE_COLUMNS": "SELECT
NULL AS TABLE_CAT,
COLUMNS.OWNER AS TABLE_SCHEM,
COLUMNS.TABLE_NAME AS TABLE_NAME,
COLUMNS.COLUMN_NAME AS COLUMN_NAME,
DECODE(
(SELECT A.TYPECODE
FROM DBA_TYPES A
WHERE A.TYPE_NAME = COLUMNS.DATA_TYPE
AND (A.OWNER = COLUMNS.OWNER OR A.OWNER IS NULL)),
'OBJECT', 2002,
'COLLECTION', 2003,
DECODE(substr(COLUMNS.data_type, 1, 9),
'TIMESTAMP',
DECODE(substr(COLUMNS.data_type, 10, 1),
'(',
DECODE(substr(COLUMNS.data_type, 19, 5),
'LOCAL', -102, 'TIME ', -101, 93),
DECODE(substr(COLUMNS.data_type, 16, 5),
'LOCAL', -102, 'TIME ', -101, 93)),
'INTERVAL ',
DECODE(substr(COLUMNS.data_type, 10, 3),
'DAY', -104, 'YEA', -103),
DECODE(COLUMNS.data_type,
'BINARY_DOUBLE', 101,
'BINARY_FLOAT', 100,
'BFILE', -13,
'BLOB', 2004,
'CHAR', 1,
'CLOB', 2005,
'COLLECTION', 2003,
'DATE', 93,
'FLOAT', 6,
'LONG', -1,
'LONG RAW', -4,
'NCHAR', -15,
'NCLOB', 2011,
'NUMBER', 3,
'NVARCHAR', -9,
'NVARCHAR2', -9,
'OBJECT', 2002,
'OPAQUE/XMLTYPE', 2009,
'RAW', -3,
'REF', 2006,
'ROWID', -8,
'SQLXML', 2009,
'UROWID', -8,
'VARCHAR2', 12,
'VARRAY', 2003,
'XMLTYPE', 2009,
1111)))
AS DATA_TYPE,
COLUMNS.DATA_TYPE AS TYPE_NAME,
DECODE (COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_SCALE, NULL, 0 , 38), DECODE (COLUMNS.DATA_TYPE, 'CHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR', COLUMNS.CHAR_LENGTH, 'VARCHAR2', COLUMNS.CHAR_LENGTH, 'NVARCHAR2', COLUMNS.CHAR_LENGTH, 'NCHAR', COLUMNS.CHAR_LENGTH, 'NUMBER', 0, COLUMNS.DATA_LENGTH) ), COLUMNS.DATA_PRECISION)
AS COLUMN_SIZE,
0 AS BUFFER_LENGTH,
DECODE (COLUMNS.DATA_TYPE, 'NUMBER', DECODE(COLUMNS.DATA_PRECISION, NULL, DECODE(COLUMNS.DATA_SCALE, NULL, -127 , COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE), COLUMNS.DATA_SCALE)
AS DECIMAL_DIGITS,
10 AS NUM_PREC_RADIX,
DECODE (COLUMNS.NULLABLE, 'N', 0, 1) AS NULLABLE,
REMARKS.COMMENTS AS REMARKS,
COLUMNS.DATA_DEFAULT AS COLUMN_DEF,
0 AS SQL_DATA_TYPE,
0 AS SQL_DATETIME_SUB,
COLUMNS.DATA_LENGTH AS CHAR_OCTET_LENGTH,
COLUMNS.COLUMN_ID AS ORDINAL_POSITION,
DECODE (COLUMNS.NULLABLE, 'N', 'NO', 'YES') AS IS_NULLABLE,
NULL AS SCOPE_CATALOG,
NULL AS SCOPE_SCHEMA,
NULL AS SCOPE_TABLE,
NULL AS SOURCE_DATA_TYPE,
IDENTITY_COLUMN AS IS_AUTOINCREMENT,
COLUMNS.VIRTUAL_COLUMN AS IS_GENERATEDCOLUMN
FROM
DBA_TAB_COLS COLUMNS
LEFT OUTER JOIN DBA_COL_COMMENTS REMARKS
ON
COLUMNS.OWNER = REMARKS.OWNER
AND COLUMNS.TABLE_NAME = REMARKS.TABLE_NAME
AND COLUMNS.COLUMN_NAME = REMARKS.COLUMN_NAME
INNER JOIN DBA_USERS USERS
ON COLUMNS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(COLUMNS.OWNER, '${schemas}')
AND COLUMNS.TABLE_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(COLUMNS.TABLE_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
ORDER BY
TABLE_SCHEM,
TABLE_NAME,
ORDINAL_POSITION
",
"TABLE_CONSTRAINTS": "SELECT
NULL AS CONSTRAINT_CATALOG,
CONSTRAINTS.OWNER AS CONSTRAINT_SCHEMA,
CONSTRAINTS.CONSTRAINT_NAME,
NULL AS TABLE_CATALOG,
CONSTRAINTS.OWNER AS TABLE_SCHEMA,
CONSTRAINTS.TABLE_NAME,
CASE CONSTRAINTS.CONSTRAINT_TYPE WHEN 'C' THEN 'CHECK' WHEN 'U' THEN 'UNIQUE' WHEN 'P' THEN 'PRIMARY KEY' WHEN 'R' THEN 'FOREIGN KEY' END
AS CONSTRAINT_TYPE,
CASE WHEN CONSTRAINTS.DEFERRABLE = 'NOT DEFERRABLE' THEN 'N' ELSE 'Y' END
AS IS_DEFERRABLE,
CASE WHEN CONSTRAINTS.DEFERRED = 'IMMEDIATE' THEN 'N' ELSE 'Y' END
AS INITIALLY_DEFERRED
FROM
DBA_CONSTRAINTS CONSTRAINTS
INNER JOIN DBA_USERS USERS
ON CONSTRAINTS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(CONSTRAINTS.OWNER, '${schemas}')
AND CONSTRAINTS.TABLE_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(CONSTRAINTS.TABLE_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).*$')
AND CONSTRAINT_TYPE IN ('C', 'U', 'P', 'R')
",
"TRIGGERS": "SELECT
NULL AS TRIGGER_CATALOG,
TRIGGERS.OWNER AS TRIGGER_SCHEMA,
TRIGGER_NAME AS TRIGGER_NAME,
CASE
WHEN TRIGGERS.TRIGGERING_EVENT LIKE '%INSERT%' THEN 'INSERT'
WHEN TRIGGERS.TRIGGERING_EVENT LIKE '%UPDATE%' THEN 'UPDATE'
WHEN TRIGGERS.TRIGGERING_EVENT LIKE '%DELETE%' THEN 'DELETE'
ELSE 'UNKNOWN'
END
AS EVENT_MANIPULATION,
NULL AS EVENT_OBJECT_CATALOG,
TRIGGERS.TABLE_OWNER AS EVENT_OBJECT_SCHEMA,
TRIGGERS.TABLE_NAME AS EVENT_OBJECT_TABLE,
0
AS ACTION_ORDER,
TRIGGERS.WHEN_CLAUSE AS ACTION_CONDITION,
CASE
WHEN TRIGGERS.TRIGGER_TYPE LIKE '%ROW' THEN 'ROW'
WHEN TRIGGERS.TRIGGER_TYPE LIKE '%STATEMENT' THEN 'STATEMENT'
ELSE 'UNKNOWN'
END
AS ACTION_ORIENTATION,
CASE
WHEN TRIGGERS.TRIGGER_TYPE LIKE 'AFTER%' THEN 'AFTER'
WHEN TRIGGERS.TRIGGER_TYPE LIKE 'BEFORE%' THEN 'BEFORE'
ELSE 'INSTEAD OF'
END
AS CONDITION_TIMING,
TRIGGERS.TRIGGER_BODY AS ACTION_STATEMENT
FROM
DBA_TRIGGERS TRIGGERS
INNER JOIN DBA_USERS USERS
ON TRIGGERS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(TRIGGERS.OWNER, '${schemas}')
",
"TYPE_INFO": "SELECT
'NUMBER' AS TYPE_NAME, 2 AS DATA_TYPE, 38 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 1 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'NUMBER' AS LOCAL_TYPE_NAME, -84 AS MINIMUM_SCALE, 127 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'CHAR' AS TYPE_NAME, 1 AS DATA_TYPE, 2000 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'CHAR' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'VARCHAR2' AS TYPE_NAME, 12 AS DATA_TYPE, 4000 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'VARCHAR2' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'DATE' AS TYPE_NAME, 91 AS DATA_TYPE, 7 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'DATE' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'TIMESTAMP' AS TYPE_NAME, 93 AS DATA_TYPE, 11 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'TIMESTAMP' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'TIMESTAMP WITH TIME ZONE' AS TYPE_NAME, -101 AS DATA_TYPE, 13 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'TIMESTAMP WITH TIME ZONE' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'TIMESTAMP WITH LOCAL TIME ZONE' AS TYPE_NAME, -102 AS DATA_TYPE, 11 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'TIMESTAMP WITH LOCAL TIME ZONE' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'INTERVALYM' AS TYPE_NAME, -103 AS DATA_TYPE, 5 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'INTERVALYM' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'INTERVALDS' AS TYPE_NAME, -104 AS DATA_TYPE, 4 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'INTERVALDS' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'RAW' AS TYPE_NAME, -3 AS DATA_TYPE, 2000 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'RAW' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'LONG' AS TYPE_NAME, -1 AS DATA_TYPE, 2147483647 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'LONG' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'LONG RAW' AS TYPE_NAME, -4 AS DATA_TYPE, 2147483647 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'LONG RAW' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT 'FLOAT' AS TYPE_NAME, 6 AS DATA_TYPE, 63 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX,
NULL AS CREATE_PARAMS, 1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 1 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'FLOAT' AS LOCAL_TYPE_NAME, -84 AS MINIMUM_SCALE, 127 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT 'REAL' AS TYPE_NAME, 7 AS DATA_TYPE, 63 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX,
NULL AS CREATE_PARAMS, 1 AS NULLABLE, 0 AS CASE_SENSITIVE, 3 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 1 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'REAL' AS LOCAL_TYPE_NAME, -84 AS MINIMUM_SCALE, 127 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'BLOB' AS TYPE_NAME, 2004 AS DATA_TYPE, -1 AS PRECISION,
NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 0 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'BLOB' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'CLOB' AS TYPE_NAME, 2005 AS DATA_TYPE, -1 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'CLOB' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'REF' AS TYPE_NAME, 2006 AS DATA_TYPE, 0 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'REF' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'ARRAY' AS TYPE_NAME, 2003 AS DATA_TYPE, 0 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'ARRAY' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
UNION ALL
SELECT
'STRUCT' AS TYPE_NAME, 2002 AS DATA_TYPE, 0 AS PRECISION,
'''' AS LITERAL_PREFIX, '''' AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS,
1 AS NULLABLE, 1 AS CASE_SENSITIVE, 0 AS SEARCHABLE,
0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT,
'STRUCT' AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE,
NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX
FROM DUAL
ORDER BY
DATA_TYPE
",
"VIEWS": "SELECT
NULL AS TABLE_CATALOG,
VIEWS.OWNER AS TABLE_SCHEMA,
VIEWS.VIEW_NAME AS TABLE_NAME,
VIEWS.TEXT AS VIEW_DEFINITION,
'UNKNOWN' AS CHECK_OPTION,
CASE WHEN VIEWS.READ_ONLY = 'N' THEN 'Y' ELSE 'N' END AS IS_UPDATABLE
FROM
DBA_VIEWS VIEWS
INNER JOIN DBA_USERS USERS
ON VIEWS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(VIEWS.OWNER, '${schemas}')
AND VIEWS.VIEW_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(VIEWS.VIEW_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
UNION ALL
SELECT
NULL AS TABLE_CATALOG,
MVIEWS.OWNER AS TABLE_SCHEMA,
MVIEWS.MVIEW_NAME AS TABLE_NAME,
MVIEWS.QUERY AS VIEW_DEFINITION,
'UNKNOWN' AS CHECK_OPTION,
'N' AS IS_UPDATABLE
FROM
DBA_MVIEWS MVIEWS
INNER JOIN DBA_USERS USERS
ON MVIEWS.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
REGEXP_LIKE(MVIEWS.OWNER, '${schemas}')
AND MVIEWS.MVIEW_NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(MVIEWS.MVIEW_NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
",
"VIEW_TABLE_USAGE": "SELECT
NULL AS VIEW_CATALOG,
VIEW_TABLE_USAGE.OWNER AS VIEW_SCHEMA,
VIEW_TABLE_USAGE.NAME AS VIEW_NAME,
NULL AS TABLE_CATALOG,
VIEW_TABLE_USAGE.REFERENCED_OWNER AS TABLE_SCHEMA,
VIEW_TABLE_USAGE.REFERENCED_NAME AS TABLE_NAME
FROM
DBA_DEPENDENCIES VIEW_TABLE_USAGE
INNER JOIN DBA_USERS USERS
ON VIEW_TABLE_USAGE.OWNER = USERS.USERNAME
AND USERS.ORACLE_MAINTAINED = 'N'
AND NOT REGEXPLIKE(USERS.USERNAME, '^APEX[0-9]{6}$')
AND NOT REGEXPLIKE(USERS.USERNAME, '^FLOWS[0-9]{5}$')
WHERE
VIEW_TABLE_USAGE.TYPE = 'VIEW'
AND REGEXP_LIKE(VIEW_TABLE_USAGE.OWNER, '${schemas}')
AND VIEW_TABLE_USAGE.NAME NOT LIKE 'BIN$%'
AND NOT REGEXP_LIKE(VIEW_TABLE_USAGE.NAME, '^(SYSIOT|MDOS|MDRS|MDRT|MDOT|MDXT).$')
"
}",
"metadataRetrievalStrategyMap": {
"foreignKeysRetrievalStrategy": "data_dictionary_all",
"functionParametersRetrievalStrategy": "data_dictionary_all",
"functionsRetrievalStrategy": "data_dictionary_all",
"indexesRetrievalStrategy": "data_dictionary_all",
"primaryKeysRetrievalStrategy": "data_dictionary_all",
"procedureParametersRetrievalStrategy": "data_dictionary_all",
"proceduresRetrievalStrategy": "data_dictionary_all",
"tableColumnPrivilegesRetrievalStrategy": "metadata",
"tableColumnsRetrievalStrategy": "data_dictionary_all",
"tablePrivilegesRetrievalStrategy": "metadata",
"tablesRetrievalStrategy": "data_dictionary_all",
"typeInfoRetrievalStrategy": "data_dictionary_all"
},
"supportsCatalogs": false,
"supportsSchemas": true,
"tableTypes": "[table, view, synonym]",
"typeMap": {
"ARRAY": "interface java.sql.Array",
"BIGINT": "class java.lang.Long",
"BINARY": "class [B",
"BIT": "class java.lang.Boolean",
"BLOB": "interface java.sql.Blob",
"BOOLEAN": "class java.lang.Boolean",
"CHAR": "class java.lang.String",
"CLOB": "interface java.sql.Clob",
"DATALINK": "class java.net.URL",
"DATE": "class java.sql.Date",
"DECIMAL": "class java.math.BigDecimal",
"DISTINCT": "class java.lang.Object",
"DOUBLE": "class java.lang.Double",
"FLOAT": "class java.lang.Double",
"INTEGER": "class java.lang.Integer",
"JAVA_OBJECT": "class java.lang.Object",
"LONGNVARCHAR": "class java.lang.String",
"LONGVARBINARY": "class [B",
"LONGVARCHAR": "class java.lang.String",
"NCHAR": "class java.lang.String",
"NCLOB": "interface java.sql.NClob",
"NULL": "class java.lang.Void",
"NUMERIC": "class java.math.BigDecimal",
"NVARCHAR": "class java.lang.String",
"OTHER": "class java.lang.Object",
"REAL": "class java.lang.Float",
"REF": "interface java.sql.Ref",
"REF_CURSOR": "class java.lang.Object",
"ROWID": "interface java.sql.RowId",
"SMALLINT": "class java.lang.Integer",
"SQLXML": "interface java.sql.SQLXML",
"STRUCT": "interface java.sql.Struct",
"TIME": "class java.sql.Time",
"TIMESTAMP": "class java.sql.Timestamp",
"TIMESTAMP_WITH_TIMEZONE": "class java.time.OffsetDateTime",
"TIME_WITH_TIMEZONE": "class java.time.OffsetTime",
"TINYINT": "class java.lang.Integer",
"VARBINARY": "class [B",
"VARCHAR": "class java.lang.String"
}
}
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.SchemaCrawlerCommandLine executeCommandLine
INFO: Running command LoadCommand
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.LoadCommand saveCommandOptions
INFO: Loaded command loader options
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.LoadCommand saveCommandOptions
CONFIG: {
}
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.LoadCommand run
CONFIG: Not loading catalog, since this is deferred
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.SchemaCrawlerCommandLine executeCommandLine
INFO: Running command ExecuteCommand
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.ExecuteCommand saveCommandOptions
INFO: Loaded command config
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.ExecuteCommand saveCommandOptions
CONFIG: {
}
nov. 23, 2023 12:37:34 EM schemacrawler.tools.commandline.command.ExecuteCommand configureExecutable
INFO: Setting up SchemaCrawler command
nov. 23, 2023 12:37:34 EM us.fatehi.utility.scheduler.TaskRunners getTaskRunner
CONFIG: Loading database schema using multiple threads
nov. 23, 2023 12:37:34 EM us.fatehi.utility.scheduler.MultiThreadedTaskRunner
nov. 23, 2023 12:37:35 EM us.fatehi.utility.scheduler.TimedTask call
INFO: Running
NickSharma
commented
11 months ago @sualeh :
After 45 minutes we got the below error:
Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "mysql-cj-abandoned-connection-cleanup" nov. 23, 2023 1:22:11 EM us.fatehi.utility.UtilityLogger logSafeArguments INFO: Command line: --server=oracle --host=XXXXXX.com --port=1523 --database=KRAKOW --schemas=GL --user=cdluser
sualeh
commented
11 months ago @NickSharma You should be using --tables not --grep-tables. From your log, it seems that you have 196,013 table in the schema? That is a very large number of tables! I will need to see the complete log. Please could you capture the entire log in a file, and give me that file?
Another thing you can do is to look at the Java command inside "schemacrawler.sh" and increase the heap size.
NickSharma
commented
11 months ago @sualeh : Used the --tables option and here is the continued log:
Its taking a lot of time and since we have integrated that with AWS Lambda, its getting timed out after 15 minutes.
INFO: -- generated by: SchemaCrawler 16.20.6 -- generated on: 2023-11-23T14:16:36.9678989 -- database: Oracle Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 -- driver: Oracle JDBC driver 21.11.0.0.0 -- operating system: Windows 10 10.0 -- JVM system: Oracle Corporation Java HotSpot(TM) 64-Bit Server VM 20.0.1+9-29
nov. 23, 2023 3:16:37 EM us.fatehi.utility.scheduler.TimedTask call
INFO: Running
nov. 23, 2023 4:17:37 EM schemacrawler.crawl.RetrievalTaskRunner stopAndLogTime
INFO: Total time taken for
nov. 23, 2023 4:17:37 EM us.fatehi.utility.UtilityLogger logSafeArguments INFO: Command line: --server=oracle --host=XXXXX.com --port=1523 --database=KRAKOW --schemas=GL --user=cdluser
NickSharma
commented
11 months ago sualeh
commented
11 months ago @NickSharma Thanks. At this point, your best bet is to increase the heap space available to the Java process by modifying the shell script. You may need a faster processor as well. Otherwise, you will have to consider a custom solution with SchemaCrawler since you have so many tables.
NickSharma
commented
11 months ago @sualeh Thanks for your reply and prompt help. I'll take a look and decide how to go further with it.
sualeh
commented
11 months ago @NickSharma I am thinking of a way to get this to work faster. Will you be willing to try it out?
adrianboangiu
commented
11 months ago Hello to both of you. I also hit this problem of long loading time. I will describe my case here because I think that there are some problems in the metadata loading. First of all, I use SchemaCrawler 16.20.6 via Docker on a 32GB RAM Windows 10 machine. The Oracle database version is 19.3. I have on the database 2 schemas:
I use limit --schemas to address each schema and --table-types=TABLE or --table-types=VIEW to address only tables or views on each schema. On the second schema I also tried to grep for one or two tables and filter using children and parent equal to 0 or 1. But the time spent in load --info-level=standard is the same despite the size of the schema chosen or the grep or filter applied before (around 5 minutes). This is very strange for me because I thought and expected that loading considers the limits and filters established before.
NickSharma
commented
11 months ago @NickSharma I am thinking of a way to get this to work faster. Will you be willing to try it out?
@sualeh I'd be happy to try it out, let me know the steps!
sualeh
commented
11 months ago @adrianboangiu With large databases, it will take time to load the schema. For Oracle specifically, SchemaCrawler limits schemas based on the regular expression supplied on the command-line on the database server. It does not do a similar thing for table regular expressions - instead of limiting on the database server, but limits in the code. I am trying to write some experimental code to see if I can limit tables on the database server, and I can give it to both of you to try out.
adrianboangiu
commented
11 months ago From the response times I have for load command with different sizes Oracle schemas used in limit (or in config file) I understood that the filter of the limit is done on the database response. In other words, the server is reading the metadata for all the schemas despite the limit. So the problem is not with large databases but with Oracle instances containing several schemas, large and small. The small ones will be affected by the presence of one large schema.
sualeh
commented
11 months ago @adrianboangiu Thanks for the information. If you use --schemas, the inclusion pattern will be used to limit schemas on the database server. I am in the process of making changes so that the --tables inclusion pattern will also be used. I will request you to test for me.
sualeh
commented
11 months ago @NickSharma Are you also using the SchemaCrawer Docker image?
sualeh
commented
11 months ago @NickSharma @adrianboangiu Please use the SchemaCrawler Docker image tagged "early-access-release" and try it out by limiting --tables to one table. Please let me know if this is quicker than it used to be.
adrianboangiu
commented
11 months ago @sualeh I have tested with the image you indicated to me.
I have tried the following sequence of operations with different limits on the same database I described above.
connect --server=oracle --host=... --port=1521 --database=TRAD --user=system --password=...
limit ...
load --info-level=standard
1st attempt: limit on an unexisting table (--table=TRANSLATED_CONSTRAINTS) load time >6min
2nd attempt: limit on an existing table in the smaller schema (--table=TRANSLATED_CONSTRAINT) load time >6min
3rd attempt: limit on the smaller chema, tables and an existing table in the smaller schema (limit --schemas=DBTRANSLATE --table-types=TABLE --table=TRANSLATED_CONSTRAINT) load time 5sec
4th attempt: limit on the smaller chema and tables (limit --schemas=DBTRANSLATE --table-types=TABLE) load time >6min
5th attempt: limit on the smaller chema, tables and a nonexisting table in the smaller schema (limit --schemas=DBTRANSLATE --table-types=TABLE --table=TRANSLATED_CONSTRAINTS) load time >6min
So it seems that only the combination of the table and schema is effective. Only schema or only table are not working. And limiting on non existing tables does not limit at all.
sualeh
commented
11 months ago Thanks for testing, @adrianboangiu Please note that --tables limits on the fully qualified table name. So, you will need to use something like --tables=DBTRANSLATE.TRANSLATED_CONSTRAINTS.
And limiting on non existing tables does not limit at all.
Please use --tables.
sualeh
commented
11 months ago @adrianboangiu Please use --tables, not --table.
sualeh
commented
11 months ago @NickSharma @adrianboangiu Were you able to try this out?
NickSharma
commented
11 months ago @sualeh : I was on a week break but back now, I'll test and will let you soon
adrianboangiu
commented
11 months ago @sualeh I tested using your last advice:
limit --schemas=DBTRANSLATE --table-types=TABLE --tables=.*\.TRANSLATED_CONSTRAINTS
and
load --info-level=standard
took less than 1sec
So it works. Thank you.
sualeh
commented
11 months ago @adrianboangiu Great! I will release this version as soon as I can.
sualeh
commented
11 months ago @adrianboangiu @NickSharma Please use SchemaCrawler 16.20.7
NickSharma
commented
10 months ago @sualeh I tried with the 16.20.7 against Oracle DB for huge schemas and its working like a charm now. Thanks for the prompt assistance.
sualeh
commented
10 months ago @NickSharma Good to hear
Description
Even for fetching the schema for a single table its taking a lot of time and then failing. Although the source DB schema contains number of tables in 1000s
$ schemacrawler --shell SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. schemacrawler> schemacrawler> connect --user=XXX --password=XXXXXX--database=XXXXX--host=XXXXX.sss.se.com --port=1523 --server=oracle connect --user=XXX --password=XXXXXX--database=XXXXX--host=XXXXX.sss.se.com --port=1523 --server=oracle schemacrawler> schemacrawler> limit --tables=.GL_JE_LINES limit --tables=.GL_JE_LINES schemacrawler> schemacrawler> load --info-level=standard load --info-level=standard ERROR: Cannot load catalog
How to Reproduce
No response
Relevant log output
SchemaCrawler Version
16.20.6
Java Version
20.0.1
Operating System and Version
Windows 10 Enterprise
Relational Database System and Version
Oracle
JDBC Driver and Version
N/A (latest I guess)