Data Catalog Views

Data Catalog integration with Autonomous AI Database provides numerous tables and views.

These tables and views help you understand:
  • Available Data Catalog assets. Get information about any type of Data Catalog asset - including databases, object stores, and more.
  • Information about the Data Catalog Object Storage assets and entities that have been synchronized with Autonomous AI Database. This includes details about how Data Catalog items (assets, folders and entities) map to Autonomous AI Database objects (i.e. schemas and external tables).
  • Metadata sync executions. Review details about sync jobs, including any issues that may have occurred during synchronization.
View Description
ALL_CLOUD_CATALOG_DATABASES View Display information about OCI Data Catalog data assets and AWS Glue Data Catalog databases
ALL_CLOUD_CATALOG_TABLES View Used to display information about data entities for OCI Data Catalogs and tables for AWS Glue Data Catalogs
ALL_DCAT_ASSETS View List data catalog assets that this database is authorized to access
ALL_DCAT_ATTRIBUTES View List data catalog attributes this database is authorized to access
ALL_DCAT_CONNECTIONS View A view that contains information about the data catalog(s) connected to this instance
ALL_DCAT_ENTITIES View

Lists logical entities this database is authorized to access
ALL_DCAT_FOLDERS View List metadata for the Object Storage buckets containing the data files for the Logical Entities
ALL_DCAT_GLOBAL_ACCESSIBLE_CATALOGS View List all accessible catalogs across all regions, along with the level of access privileges for each catalog
ALL_DCAT_LOCAL_ACCESSIBLE_CATALOGS View List all accessible catalogs in the current region, along with the level of access privileges for each catalog
ALL_GLUE_DATABASES View Lists the AWS Glue Data Catalog databases that the data catalog credential is authorized to access
ALL_GLUE_TABLES View Shows all AWS Glue Data Catalog tables that the data catalog credential is authorized to access
DCAT_ATTRIBUTES View List the mapping of logical entity attributes to external table columns
DCAT_ENTITIES View Describes the mapping of logical entities to external tables
DBMS_DCAT$SYNC_LOG View Provides easy access to the log table for the last sync operation executed by the current user

ALL_CLOUD_CATALOG_DATABASES View

Use view ALL_CLOUD_CATALOG_DATABASES to display information about OCI Data Catalog data assets and AWS Glue Data Catalog databases.

Column Description
DCAT_CON_ID CON1
CATALOG_ID Data catalog unique identifier.

OCI Data Catalog example:

ocid1.datacatalog.oc1.ap-mumbai-1.….y35a

AWS Glue Data Catalog example:

NULL

579294766787
NAME Name of the data asset (OCI)/ database (AWS Glue).

OCI Data Catalog example:

OBJECT_STORE_AT_ASHBURN

AWS Glue Data Catalog example:

OBJECT_STORE_AT_N_CALIFORNIA
DESCRIPTION Description of the data asset (OCI)/ database (AWS Glue).

OCI Data Catalog example: 

Data stored in S3 (N. California)

AWS Glue Data Catalog example: 

Data stored in S3 (N. California)
TIME_CREATED The date and time the data asset (OCI) / databases (AWS Glue) were created in the data catalog.

OCI Data Catalog example:

26-SEP-22 10.56.01.395000 PM +00:00

AWS Glue Data Catalog example:

2022-06-15T09:45:35+01:00
DETAILS JSON document with metadata about each data entity (OCI) / database (AWS Glue).

OCI Data Catalog example: 

{
  "catalog-id": "ocid1.datacatalog.oc1.ap-mumbai-1.amaaa...",
  "description": null,
  "display-name": "OBJECT_STORE_AT_ASHBURN",
  "external-key": "https://swiftobjectstorage.us-ashburn-1....",
  "key": "bc95181c-3ac3-4959-9e5f-4e460d3fb82a",
  "lifecycle-state": "ACTIVE",
  "time-created": "2022-09-26T22:56:01.395000+00:00",
  "type-key": "3ea65bc5-f60d-477a-a591-f063665339f9",
  "uri": "/dcat/20190325/dataAssets/bc95181c-3ac3-4959-9e5f-4e460d3fb82a"
}

AWS Glue Data Catalog example: 

{
    "Name": "dbmsdcatpoc",
    "Parameters": {
        "somekey": "somevalue"
    },
    "CreateTime": "2022-06-15T09:45:35+01:00",
    "CreateTableDefaultPermissions": [
        {
            "Principal": {
                "DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"
            },
            "Permissions": [
                "ALL"
            ]
        }
    ],
    "CatalogId": "579294766787"
}

ALL_CLOUD_CATALOG_TABLES View

View ALL_CLOUD_CATALOG_TABLES is used to display information about data entities for OCI Data Catalogs and tables for AWS Glue Data Catalogs.

Column Description
DCAT_CON_ID Unique identifier of the data catalog. The connection id.

OCI Data Catalog example: CON1

AWS Glue Data Catalog example: CON1
CATALOG_ID Data catalog unique identifier.

OCI Data Catalog example: ocid1.datacatalog.oc1.ap-mumbai-1.….y35a

AWS Glue Data Catalog example: NULL

579294766787
DATABASE_NAME Name of the data asset (OCI)/ database (AWS Glue).

OCI Data Catalog example: OBJECT_STORE_AT_ASHBURN

AWS Glue Data Catalog example: OBJECT_STORE_AT_N_CALIFORNIA
NAME Name of the data entity (OCI) / table (AWS Glue).

OCI Data Catalog example: BIKES_TRIPS

AWS Glue Data Catalog example: BIKES_TRIPS
DESCRIPTION Description of the data entity (OCI) / table (AWS Glue).

OCI Data Catalog example: Table storing bike trips

AWS Glue Data Catalog example: Table storing bike trips
TIME_CREATED The date and time the data entity (OCI) / table (AWS Glue) was created in the data catalog.

OCI Data Catalog example: 26-SEP-22 10.56.01.395000 PM +00:00

AWS Glue Data Catalog example: 2022-06-15T09:45:35+01:00
TIME_UPDATED Last time a change was made to the data entity (OCI) / table (AWS Glue).

OCI Data Catalog example: 26-SEP-22 10.56.01.395000 PM +00:00

AWS Glue Data Catalog example: 2022-06-15T09:45:35+01:00
DETAILS JSON document with metadata about each each data entity (OCI) / table (AWS Glue)

OCI Data Catalog example: 

{  
  "business-name": null,
  "data-asset-key": "bc95181c-3ac3-4959-9e5f-...",
  "description": null,
  "display-name": "bikes_trips",
  "external-key": "LE: https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/..._trips",
  "folder-key": "9c4b542d-d6eb-4b83-bf59-...",
  "folder-name": "hive",
  "is-logical": true,
  "is-partition": false,
  "key": "fde30a69-a07c-478a-ab62-...",
  "lifecycle-state": "ACTIVE",
  "object-storage-url": "https://objectstorage.us-ashburn-1.oraclecloud.com/n/...",
  "path": "OBJECT_STORE_AT_ASHBURN/hive/hive",
  "pattern-key": "db21b3f1-1508-4045-aa80-...",
  "properties": {
    "default": {
      "CONTENT-LENGTH": "4310321",
      "LAST-MODIFIED": "Fri, 9 Oct 2020 20:16:52 UTC",
      "archivedPECount": "0",
      "dataEntityExpression": "{logicalEntity:[^/]+}.db/{logicalEntity:[^/]+}/.*",
      "harvestedFile": "bikes.db/trips/p_start_month=2019-09/000000_0",
      "patternName": "bikes_trips"
    },
    "harvestProps": {
      "characterset": "UTF8",
      "compression": "none",
      "type": "PARQUET"
    }
  },
  "realized-expression": "bikes.db/trips/.*",
  "time-created": "2022-09-26T22:56:35.063000+00:00",
  "time-updated": "2022-09-26T22:56:35.063000+00:00",
  "type-key": "6753c3af-7f88-44b9-be52-1d57bef462fb",
  "updated-by-id": "ocid1.user.oc1..r5l3tov7a",
  "uri": "/dcat/20190325/dataAssets/bc95181c-3ac3-4959-9e5f-..."
}

AWS Glue Data Catalog example: 

{
    "Name": "bikes_trips",
    "DatabaseName": "dbmsdcatpoc",
    "Owner": "owner",
    "CreateTime": "2022-06-23T13:24:20+01:00",
    "UpdateTime": "2022-06-23T13:24:20+01:00",
    "LastAccessTime": "2022-06-23T13:24:20+01:00",
    "Retention": 0,
    "StorageDescriptor": {
        "Columns": [
            {
                "Name": "trip_duration",
                "Type": "int"
            },
            {
                "Name": "start_month",
                "Type": "string"
            }, ...
        ],
        "Location": "s3://dbmsdcatpoc/hive/bikes.db/trips/",
        "InputFormat": "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat",
        "OutputFormat": "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat",
        "Compressed": false,
        "NumberOfBuckets": -1,
        "SerdeInfo":
 {            "SerializationLibrary": "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe",
            "Parameters": {
                "serialization.format": "1"
            }
        },
        "BucketColumns": [],
        "SortColumns": [],
        "Parameters": {
            "CrawlerSchemaDeserializerVersion": "1.0",
            "CrawlerSchemaSerializerVersion": "1.0",
            "UPDATED_BY_CRAWLER": "crawler-bikes",
            "averageRecordSize": "86",
            "classification": "parquet",
            "compressionType": "none",
            "objectCount": "12",
            "recordCount": "404947",
            "sizeKey": "35312159",
            "typeOfData": "file"
        },
        "StoredAsSubDirectories": false
    },
    "PartitionKeys": [
        {
            "Name": "p_start_month",
            "Type": "string"
        }
    ],
    "TableType": "EXTERNAL_TABLE",
    "Parameters": {
        "CrawlerSchemaDeserializerVersion": "1.0",
        "CrawlerSchemaSerializerVersion": "1.0",
        "UPDATED_BY_CRAWLER": "crawler-bikes",
        "averageRecordSize": "86",
        "classification": "parquet",
        "compressionType": "none",
        "objectCount": "12",
        "recordCount": "404947",
        "sizeKey": "35312159",
        "typeOfData": "file"
    },
    "CreatedBy": "arn:aws:sts::579294766787:assumed-role/AWSGlueServiceRole-dbmsdcat/AWS-Crawler",
    "IsRegisteredWithLakeFormation": false,
    "CatalogId": "579294766787",
    "VersionId": "0"
}

Example

ALL_DCAT_ASSETS View

The Data Catalog assets that this database is authorized to access.

Column Datatype Description
DCAT_CON_ID VARCHAR2 (4000) Connection identifier that is unique within the instance

KEY

 VARCHAR2(4000)

Asset key
DISPLAY_NAME VARCHAR2(4000) Asset display name
DESCRIPTION VARCHAR2(4000) Asset description
CATALOG_ID VARCHAR2(4000) OCID for the Data Catalog containing the asset
EXTERNAL_KEY VARCHAR2(4000) Base Object Storage URI for the asset
URI VARCHAR2(4000) Asset URI for the Data Catalog API
TIME_CREATED TIMESTAMP(6) WITH TIMEZONE The date and time the data asset was created
TYPE_KEY VARCHAR2(4000) The key of the data asset type (currently, only Object Storage data assets are supported). Type keys can be found via the '/types' Data Catalog endpoint.
LIFECYCLE_STATE VARCHAR2(4000) The current state of the data asset. For more information on possible life cycle states, see the Data Catalog DataAsset Reference for a list of possible states for lifecycleState.

ALL_DCAT_ATTRIBUTES View

The Data Catalog attributes this database is authorized to access.

Column Datatype Description
DCAT_CON_ID VARCHAR2 (4000) Connection identifier that is unique within the instance
KEY NUMBER Attribute key

DISPLAY_NAME

 VARCHAR2(4000)

Attribute display name
BUSINESS_NAME VARCHAR2(4000) Attribute business name
DESCRIPTION VARCHAR2(4000) Attribute description
DATA_ASSET_KEY VARCHAR2(4000) Data asset key
FOLDER_KEY VARCHAR2(4000) Folder key
ENTITY_KEY VARCHAR2(4000) Entity key
EXTERNAL_KEY VARCHAR2(4000) Unique external key for the attribute
LENGTH NUMBER Maximum allowed length of the attribute value
PRECISION NUMBER Precision of the attribute value (usually applies to float data type)
SCALE NUMBER Scale of the attribute value (usually applies to float data type)
IS_NULLABLE NUMBER Identifies if this attribute can be assigned null values
URI VARCHAR2(4000) URI to the attribute instance in the Data Catalog API
LIFECYCLE_STATE VARCHAR2(4000) The current state of the attribute. For more information on possible life cycle states, see the Data Catalog Attribute Reference for a list of possible states for lifecycleState.
TIME_CREATED TIMESTAMP(6) WITH TIME ZONE The date and time the attribute was created
EXTERNAL_DATA_TYPE VARCHAR2(4000) Data type of the attribute as defined in the external system
MIN_COLLECTION_COUNT NUMBER Minimum number of elements, if the type of the attribute is a collection type
MAX_COLLECTION_COUNT NUMBER Maximum number of elements, if the type of the attribute is a collection type
DATATYPE_ENTITY_KEY VARCHAR2(4000) Entity key that represents the datatype of this attribute, applicable if this attribute is a complex type
EXTERNAL_DATATYPE_ENTITY_KEY VARCHAR2(4000) External entity key that represents the datatype of this attribute, applicable if this attribute is a complex type
PARENT_ATTRIBUTE_KEY VARCHAR2(4000) Attribute key that represents the parent attribute of this attribute, applicable if the parent attribute is of complex datatype
EXTERNAL_PARENT_ATTRIBUTE_KEY VARCHAR2(4000) External attribute key that represents the parent attribute of this attribute, applicable if the parent attribute is of complex type
PATH VARCHAR2(4000) Full path of the attribute

ALL_DCAT_CONNECTIONS View

A view that contains information about the data catalog(s) connected to this instance.

Column Datatype Description
DCAT_CON_ID VARCHAR2(4000) Connection identifier that is unique within the instance
COMPARTMENT_ID VARCHAR2(4000) OCID for the compartment where the Data Catalog instance resides
INSTANCE_ID VARCHAR2(4000)

OCID for the Data Catalog instance

REGION

 VARCHAR2(4000)

Region for the Data Catalog instance
ENDPOINT VARCHAR2(4000) Endpoint for the Data Catalog instance
CREATED TIMESTAMP When the Data Catalog instance was created
NAME VARCHAR2(4000) Name of the Data Catalog instance
LAST_UPDATED TIMESTAMP Timestamp of the last update of the connection to the Data Catalog instance
LATEST_OPERATION_ID NUMBER The id of the last synchronization operation
DATA_CATALOG_CREDENTIAL VARCHAR2(128) Credential used for accessing the Data Catalog
OBJECT_STORE_CREDENTIAL VARCHAR2(128) Credential used by the external table driver for accessing the Object Store

ALL_DCAT_ENTITIES View

The Data Catalog logical entities this database is authorized to access.

Column Datatype Description
DCAT_CON_ID VARCHAR2(4000) Connection identifier that is unique within the instance
CATALOG_ID VARCHAR2(4000) OCID for the Data Catalog containing the asset

KEY

 VARCHAR2(4000)

Entity key
DISPLAY_NAME VARCHAR2(4000)

Entity display name
BUSINESS_NAME VARCHAR2(4000)

Entity business name
DESCRIPTION VARCHAR2(4000)

Logical entity description

DATA_ASSET_KEY

 VARCHAR2(4000)

Asset key

FOLDER_KEY

 VARCHAR2(4000)

Folder unique key
FOLDER_NAME VARCHAR2(4000) Folder name (bucket)
EXTERNAL_KEY VARCHAR2(4000) External key for the logical entity
PATTERN_KEY VARCHAR2(4000) Key of the associated pattern for the logical entity
REALIZED_EXPRESSION VARCHAR2(4000) The regular expression used to obtain the files for this logical entity
PATH VARCHAR2(4000) Full path for the logical entity
TIME_CREATED TIMESTAMP(6) WITH TIME ZONE Date and time the entity was created
TIME_UPDATED TIMESTAMP(6) WITH TIME ZONE Last time a change was made to the data entity
UPDATED_BY_ID VARCHAR2(4000) OCID of the user who updated this object in the Data Catalog
URI VARCHAR2(4000) URI of the entity instance in the API
LIFECYCLE_STATE VARCHAR2(4000) The current state of the entity. For more information on possible life cycle states, see the Data Catalog Entity Reference for a list of possible states for lifecycleState.

ALL_DCAT_FOLDERS View

Metadata for the Object Storage buckets containing the data files for the Logical Entities.

Column Datatype Description
DCAT_CON_ID VARCHAR2(4000) Connection identifier that is unique within the instance
CATALOG_ID VARCHAR2(4000) OCID for the Data Catalog containing the asset

KEY

 VARCHAR2(4000)

Folder key

DISPLAY_NAME

 VARCHAR2(4000)

Folder display name
BUSINESS_NAME VARCHAR2(4000) Folder business name
DESCRIPTION VARCHAR2(4000) Folder description
DATA_ASSET_KEY VARCHAR2(4000) Key for the data asset containing the folder
PARENT_FOLDER_KEY VARCHAR2(4000) Key for the parent folder (currently, this is the data asset key)
PATH VARCHAR2(4000) Full path for the folder
EXTERNAL_KEY VARCHAR2(4000) Object Storage URI for the bucket
TIME_EXTERNAL TIMESTAMP(6) WITH TIMEZONE The last modified timestamp of this folder
TIME_CREATED TIMESTAMP(6) WITH TIMEZONE The date/time the folder was created
URI VARCHAR2(4000) URI to the folder instance in the Data Catalog API.
LIFECYCLE_STATE VARCHAR2(4000) The current state of the folder. For more information on possible life cycle states, see the Data Catalog Folder Reference for a list of possible states for lifecycleState.

ALL_DCAT_GLOBAL_ACCESSIBLE_CATALOGS View

This view lists all accessible catalogs across all regions, along with the level of access privileges for each catalog.

Column Datatype Description

CATALOG_ID

 VARCHAR2(4000)

Catalog OCID
CATALOG_NAME VARCHAR2(4000) Name of the catalog
CATALOG_REGION VARCHAR2(4000) Name of the catalog region
CATALOG_SCORE NUMBER The catalog score is a numeric value calculated from the privileges configured for the Data Catalog access credential. A higher catalog score means greater privileges, which may equate to a higher likelihood that this catalog is intended for use with this Autonomous AI Database instance.

ALL_DCAT_LOCAL_ACCESSIBLE_CATALOGS View

This view lists all accessible catalogs in the current region, along with the level of access privileges for each catalog.

Column Datatype Description

CATALOG_ID

 VARCHAR2(4000)

Catalog OCID
CATALOG_NAME VARCHAR2(4000) Name of the catalog
CATALOG_SCORE NUMBER The catalog score is a numeric value calculated from the privileges configured for the Data Catalog access credential. A higher catalog score means greater privileges, which may equate to a higher likelihood that this catalog is intended for use with this Autonomous AI Database instance.

ALL_GLUE_DATABASES View

The AWS Glue Data Catalog databases that the data catalog credential is authorized to access.

Column Data Type Description
DCAT_CON_ID VARCHAR2(4000) Unique identifier of data catalog connection id.
CATALOG_ID VARCHAR2(255) Data Catalog unique identifier.
NAME VARCHAR2(255) Name of the database.
DESCRIPTION VARCHAR2(2048) Description of the database.
LOCATION_URI VARCHAR2(1024) The location of the database.
CREATE_TIME TIMESTAMP The time that the database was created in the data catalog.
PARAMETERS CLOB JSON document with key-value pairs that define parameters and properties of the database.
TARGET_DATABASE VARCHAR2(4000) JSON document that describes a target database for resource linking in AWS.

ALL_GLUE_TABLES View

This view shows all AWS Glue Data Catalog tables that the data catalog credential is authorized to access.

Column Data Type Description
DCAT_CON_ID VARCHAR2(4000) Unique identifier of data catalog connection id.
CATALOG_ID VARCHAR2(255) Catalog identifier
DATABASE_NAME VARCHAR2(255) Database name
NAME VARCHAR2(255) Table name
TABLE_TYPE VARCHAR2(255) Table type
CLASSIFICATION VARCHAR2(255)  
DESCRIPTION VARCHAR2(2048) Table description
OWNER VARCHAR2(255) Table owner
CREATED_BY VARCHAR2(255) Table creator
CREATE_TIME TIMESTAMP The time the table was created in the data catalog.
LAST_ANALYZED_TIME TIMESTAMP The last time column statistics were computed for this table.
LAST_ACCESS_TIME TIMESTAMP The last time the table was accessed.
UPDATE_TIME TIMESTAMP The last time the table was updated.
IS_REGISTERED_WITH_LAKE_FORMATION NUMBER Indicates whether the table is registered with AWS lake formation.
PARAMETERS CLOB JSON document with key-value pairs that define properties of the table.
PARTITION_KEYS CLOB JSON document with a list of columns by which the table is partitioned.
RETENTION NUMBER The retention time for this table.
STORAGE_DESCRIPTION CLOB JSON document with information about the physical storage of a table.
TARGET_TABLE VARCHAR2(4000) JSON document describing a target table used for resource linking in AWS.
VERSION_ID VARCHAR2(255) The version identifier for the table.
VIEW_EXPANDED_TEXT CLOB Introduced by AWS Glue for compatibility with Hive. Not used by AWS Glue.
VIEW_ORIGINAL_TEXT CLOB Introduced by AWS Glue for compatibility with Hive. Not used by AWS Glue.

DCAT_ATTRIBUTES View

Lists the mapping of logical entity attributes to external table columns.

Column Datatype Description
DCAT_CON_ID VARCHAR2 (4000) Connection identifier that is unique within the instance
ASSET_KEY VARCHAR2(4000) Data Catalog asset key

ENTITY_KEY

 VARCHAR2(4000)

Data Catalog entity key

ATTRIBUTE_KEY

 VARCHAR2(4000)

Data Catalog attribute key
ORACLE_COLUMN_NAME VARCHAR2(128) Mapped column name

DCAT_ENTITIES View

Describes the mapping of logical entities to external tables.

Column Datatype Description
DCAT_CON_ID VARCHAR2(4000) Connection identifier that is unique within the instance
ASSET_KEY VARCHAR2(4000) Data Catalog asset key

ENTITY_KEY

 VARCHAR2(4000)

Data Catalog entity key

FOLDER_KEY

 VARCHAR2(4000)

Data Catalog folder key
ORACLE_TABLE_NAME VARCHAR2(128) Mapped table name
ORACLE_SCHEMA_NAME VARCHAR2(128) Mapped schema name
ENTITY_ORACLE_DB_SCHEMA VARCHAR2(4000) The entity's oracle-db-schema custom property used to derive the schema
ASSET_ORACLE_DB_SCHEMA VARCHAR2(4000) The data asset's oracle-db-schema custom property used to derive the schema
FOLDER_ORACLE_DB_SCHEMA VARCHAR2(4000) The folder's oracle-db-schema custom property used to derive the schema

DBMS_DCAT$SYNC_LOG View

The DBMS_DCAT$SYNC_LOG view provides easy access to the log table for the last sync operation executed by the current user.

Every call to the RUN_SYNC procedure is logged to a new log table, pointed to by the LOGFILE_TABLE field of USER_LOAD_OPERATIONS. The log tables are automatically dropped after 2 days, and users can clear all sync logs using the DELETE_ALL_OPERATIONS Procedure where type is DCAT_SYNC.

The DBMS_DCAT$SYNC_LOG view automatically identifies the latest log table. The schema for the DBMS_DCAT$SYNC_LOG view is described below and the access permissions are identical to those of the individual log tables. By default READ is granted to the dbms_dcat role and to the ADMIN user.

The log tables have the following format:

Column Datatype Description

LOG_TIMESTAMP

 TIMESTAMP

Timestamp for the log entry.

LOG_LEVEL

 VARCHAR2(32)

The entry log level can have one of the following values: OFF, FATAL, ERROR, WARN, INFO, DEBUG, TRACE, ALL.

LOG_DETAILS VARCHAR2(32767) The log message.