Oracle Database Data Dictionary & SQL Catalog¶

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    object_name,
    package_name,
    procedure_name,
    argument_name,
    position,
    sequence,
    data_level,
    data_type,
    defaulted,
    default_value,
    in_out,
    data_length,
    data_precision,
    data_scale,
    type_owner,
    type_name,
    type_subname,
    pls_type,
    char_length,
    char_used,
    origin_con_id
FROM all_arguments
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR object_name = :object_name)
ORDER BY owner, object_name, package_name, subprogram_id, sequence;

-- name: by_schema
-- dialect: oracle
WITH pk_columns AS (
    SELECT DISTINCT
        acc.owner,
        acc.table_name,
        acc.column_name
    FROM all_constraints ac
    JOIN all_cons_columns acc
      ON acc.owner = ac.owner
     AND acc.constraint_name = ac.constraint_name
     AND acc.table_name = ac.table_name
    WHERE ac.owner = COALESCE(:schema_name, USER)
      AND (:table_name IS NULL OR ac.table_name = :table_name)
      AND ac.constraint_type = 'P'
)
SELECT
    c.owner AS schema_name,
    c.table_name,
    c.column_name,
    c.data_type,
    c.data_type_owner,
    c.data_length AS max_length,
    c.data_precision AS numeric_precision,
    c.data_scale AS numeric_scale,
    c.nullable AS is_nullable,
    c.column_id AS ordinal_position,
    c.data_default AS column_default,
    CASE WHEN c.hidden_column = 'YES' THEN 1 ELSE 0 END AS is_hidden,
    CASE WHEN c.virtual_column = 'YES' THEN 1 ELSE 0 END AS is_generated,
    c.segment_column_id,
    c.internal_column_id,
    CASE WHEN c.identity_column = 'YES' THEN 1 ELSE 0 END AS is_identity,
    CASE
        WHEN ic.generation_type = 'BY DEFAULT' AND c.default_on_null = 'YES' THEN 'BY DEFAULT ON NULL'
        ELSE ic.generation_type
    END AS identity_generation,
    ic.sequence_name AS sequence_name,
    CASE WHEN pk.column_name IS NOT NULL THEN 1 ELSE 0 END AS is_primary,
    c.collation,
    cc.comments AS column_comment
FROM all_tab_cols c
LEFT JOIN pk_columns pk
  ON pk.owner = c.owner
 AND pk.table_name = c.table_name
 AND pk.column_name = c.column_name
LEFT JOIN all_tab_identity_cols ic
  ON ic.owner = c.owner
 AND ic.table_name = c.table_name
 AND ic.column_name = c.column_name
LEFT JOIN all_col_comments cc
  ON cc.owner = c.owner
 AND cc.table_name = c.table_name
 AND cc.column_name = c.column_name
WHERE c.owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR c.table_name = :table_name)
ORDER BY c.owner, c.table_name, c.internal_column_id;

-- name: by_table
-- dialect: oracle
WITH pk_columns AS (
    SELECT DISTINCT
        acc.owner,
        acc.table_name,
        acc.column_name
    FROM all_constraints ac
    JOIN all_cons_columns acc
      ON acc.owner = ac.owner
     AND acc.constraint_name = ac.constraint_name
     AND acc.table_name = ac.table_name
    WHERE ac.owner = COALESCE(:schema_name, USER)
      AND ac.table_name = :table_name
      AND ac.constraint_type = 'P'
)
SELECT
    c.owner AS schema_name,
    c.table_name AS table_name,
    c.column_name AS column_name,
    c.data_type AS data_type,
    c.nullable AS is_nullable,
    c.data_default AS column_default,
    c.column_id AS ordinal_position,
    CASE WHEN c.hidden_column = 'YES' THEN 1 ELSE 0 END AS is_hidden,
    CASE WHEN c.virtual_column = 'YES' THEN 1 ELSE 0 END AS is_generated,
    CASE WHEN c.identity_column = 'YES' THEN 1 ELSE 0 END AS is_identity,
    CASE
        WHEN ic.generation_type = 'BY DEFAULT' AND c.default_on_null = 'YES' THEN 'BY DEFAULT ON NULL'
        ELSE ic.generation_type
    END AS identity_generation,
    ic.sequence_name AS sequence_name,
    CASE WHEN pk.column_name IS NOT NULL THEN 1 ELSE 0 END AS is_primary
FROM all_tab_cols c
LEFT JOIN pk_columns pk
  ON pk.owner = c.owner
 AND pk.table_name = c.table_name
 AND pk.column_name = c.column_name
LEFT JOIN all_tab_identity_cols ic
  ON ic.owner = c.owner
 AND ic.table_name = c.table_name
 AND ic.column_name = c.column_name
WHERE c.owner = COALESCE(:schema_name, USER)
  AND c.table_name = :table_name
ORDER BY c.column_id;

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    table_name,
    NULL AS column_name,
    table_type AS object_type,
    comments
FROM all_tab_comments
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR table_name = :object_name)
UNION ALL
SELECT
    owner AS schema_name,
    table_name,
    column_name,
    'COLUMN' AS object_type,
    comments
FROM all_col_comments
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR table_name = :object_name)
ORDER BY schema_name, table_name, column_name;

-- name: by_schema
-- dialect: oracle
SELECT
    c.owner AS schema_name,
    c.table_name,
    c.constraint_name,
    c.constraint_type,
    c.search_condition_vc AS search_condition,
    c.r_owner AS referenced_schema,
    r.table_name AS referenced_table,
    c.r_constraint_name AS referenced_constraint,
    c.delete_rule,
    c.status,
    c.deferrable,
    c.deferred,
    c.validated,
    c.generated,
    c.bad,
    c.rely,
    c.last_change,
    LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS columns
FROM all_constraints c
LEFT JOIN all_cons_columns cc
  ON cc.owner = c.owner
  AND cc.constraint_name = c.constraint_name
LEFT JOIN all_constraints r
  ON r.owner = c.r_owner
  AND r.constraint_name = c.r_constraint_name
WHERE c.owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR c.table_name = :table_name)
GROUP BY
    c.owner,
    c.table_name,
    c.constraint_name,
    c.constraint_type,
    c.search_condition_vc,
    c.r_owner,
    r.table_name,
    c.r_constraint_name,
    c.delete_rule,
    c.status,
    c.deferrable,
    c.deferred,
    c.validated,
    c.generated,
    c.bad,
    c.rely,
    c.last_change
ORDER BY c.owner, c.table_name, c.constraint_name;

-- name: by_object
-- dialect: oracle
SELECT DBMS_METADATA.GET_DDL(:object_type, :object_name, :owner) AS ddl
FROM dual;

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    name AS object_name,
    type AS object_type,
    referenced_owner,
    referenced_name,
    referenced_type,
    referenced_link_name,
    dependency_type,
    origin_con_id
FROM all_dependencies
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR name = :object_name)
ORDER BY owner, name, referenced_owner, referenced_name, referenced_type;

-- name: by_table
-- dialect: oracle
SELECT
    c.table_name,
    cc.column_name,
    r.table_name AS referenced_table_name,
    rcc.column_name AS referenced_column_name,
    c.constraint_name,
    c.owner AS table_schema,
    r.owner AS referenced_table_schema
FROM all_constraints c
JOIN all_cons_columns cc
  ON c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
JOIN all_constraints r
  ON c.r_owner = r.owner
  AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rcc
  ON r.owner = rcc.owner
  AND r.constraint_name = rcc.constraint_name
  AND cc.position = rcc.position
WHERE c.constraint_type = 'R'
  AND c.owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR c.table_name = :table_name);

-- name: by_schema
-- dialect: oracle
SELECT
    c.table_name,
    cc.column_name,
    r.table_name AS referenced_table_name,
    rcc.column_name AS referenced_column_name,
    c.constraint_name,
    c.owner AS table_schema,
    r.owner AS referenced_table_schema
FROM all_constraints c
JOIN all_cons_columns cc
  ON c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
JOIN all_constraints r
  ON c.r_owner = r.owner
  AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rcc
  ON r.owner = rcc.owner
  AND r.constraint_name = rcc.constraint_name
  AND cc.position = rcc.position
WHERE c.constraint_type = 'R'
  AND c.owner = COALESCE(:schema_name, USER);

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    table_name AS object_name,
    NULL AS column_name,
    grantor,
    grantee,
    privilege,
    grantable,
    hierarchy,
    type,
    common,
    inherited
FROM all_tab_privs
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR table_name = :object_name)
UNION ALL
SELECT
    owner AS schema_name,
    table_name AS object_name,
    column_name,
    grantor,
    grantee,
    privilege,
    grantable,
    NULL AS hierarchy,
    'COLUMN' AS type,
    common,
    inherited
FROM all_col_privs
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR table_name = :object_name)
ORDER BY schema_name, object_name, column_name, grantee, privilege;

-- name: by_schema
-- dialect: oracle
SELECT
    i.owner AS schema_name,
    i.table_name,
    i.index_name,
    i.index_type,
    i.uniqueness,
    i.compression,
    i.prefix_length,
    i.tablespace_name,
    i.status,
    i.partitioned,
    i.temporary,
    i.generated,
    i.visibility,
    i.blevel,
    i.leaf_blocks,
    i.distinct_keys,
    i.avg_leaf_blocks_per_key,
    i.avg_data_blocks_per_key,
    i.clustering_factor,
    LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_position) AS columns,
    LISTAGG(ie.column_expression, ',') WITHIN GROUP (ORDER BY ie.column_position) AS expressions
FROM all_indexes i
LEFT JOIN all_ind_columns ic
  ON ic.index_owner = i.owner
  AND ic.index_name = i.index_name
LEFT JOIN all_ind_expressions ie
  ON ie.index_owner = i.owner
  AND ie.index_name = i.index_name
  AND ie.column_position = ic.column_position
WHERE i.owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR i.table_name = :table_name)
GROUP BY
    i.owner,
    i.table_name,
    i.index_name,
    i.index_type,
    i.uniqueness,
    i.compression,
    i.prefix_length,
    i.tablespace_name,
    i.status,
    i.partitioned,
    i.temporary,
    i.generated,
    i.visibility,
    i.blevel,
    i.leaf_blocks,
    i.distinct_keys,
    i.avg_leaf_blocks_per_key,
    i.avg_data_blocks_per_key,
    i.clustering_factor
ORDER BY i.owner, i.table_name, i.index_name;

-- name: by_table
-- dialect: oracle
SELECT
    i.index_name,
    i.table_name AS table_name,
    CASE WHEN i.uniqueness = 'UNIQUE' THEN 1 ELSE 0 END AS is_unique,
    CASE
        WHEN MAX(CASE WHEN c.constraint_type = 'P' THEN 1 ELSE 0 END) = 1 THEN 1
        ELSE 0
    END AS is_primary,
    LISTAGG(ic.column_name, ',') WITHIN GROUP (ORDER BY ic.column_position) AS columns
FROM all_indexes i
JOIN all_ind_columns ic
  ON i.owner = ic.index_owner
  AND i.index_name = ic.index_name
LEFT JOIN all_constraints c
  ON c.owner = i.owner
  AND c.index_name = i.index_name
  AND c.constraint_type = 'P'
WHERE i.table_name = :table_name
  AND i.owner = COALESCE(:schema_name, USER)
GROUP BY i.index_name, i.table_name, i.uniqueness;

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    table_name,
    column_name,
    segment_name,
    tablespace_name,
    index_name,
    chunk,
    pctversion,
    retention,
    freepools,
    cache,
    logging,
    encrypt,
    compression,
    deduplication,
    in_row,
    format,
    partitioned,
    securefile,
    retention_type,
    retention_value
FROM all_lobs
WHERE owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR table_name = :table_name)
ORDER BY owner, table_name, column_name;

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    mview_name,
    container_name,
    query,
    updatable,
    update_log,
    master_rollback_seg,
    master_link,
    rewrite_enabled,
    rewrite_capability,
    refresh_mode,
    refresh_method,
    build_mode,
    fast_refreshable,
    last_refresh_type,
    last_refresh_date,
    staleness,
    compile_state,
    use_no_index,
    stale_since
FROM all_mviews
WHERE owner = COALESCE(:schema_name, USER)
  AND (:mview_name IS NULL OR mview_name = :mview_name)
ORDER BY owner, mview_name;

-- name: by_schema
-- dialect: oracle
SELECT
    owner AS schema_name,
    object_name,
    object_type,
    status,
    created,
    last_ddl_time,
    temporary,
    generated,
    secondary,
    editionable,
    oracle_maintained
FROM all_objects
WHERE owner = COALESCE(:schema_name, USER)
  AND (:object_name IS NULL OR object_name = :object_name)
ORDER BY owner, object_type, object_name;

-- name: cloud_ai_available
-- dialect: oracle
SELECT
    COUNT(*) AS "cnt"
FROM all_objects
WHERE object_name = 'DBMS_CLOUD_AI'
  AND object_type = 'PACKAGE';

-- name: by_schema
-- dialect: oracle
SELECT
    table_owner AS schema_name,
    table_name,
    partition_name,
    composite,
    subpartition_count,
    high_value,
    high_value_length,
    partition_position,
    tablespace_name,
    pct_free,
    pct_used,
    ini_trans,
    max_trans,
    initial_extent,
    next_extent,
    min_extent,
    max_extent,
    max_size,
    pct_increase,
    freelists,
    freelist_groups,
    logging,
    compression,
    compress_for,
    num_rows,
    blocks,
    empty_blocks,
    avg_space,
    chain_cnt,
    avg_row_len,
    sample_size,
    last_analyzed,
    interval,
    segment_created,
    indexing,
    read_only,
    inmemory
FROM all_tab_partitions
WHERE table_owner = COALESCE(:schema_name, USER)
  AND (:table_name IS NULL OR table_name = :table_name)
ORDER BY table_owner, table_name, partition_position;