Oracle

Steps to prepare and connect to Oracle

Lightup account setup

Lightup needs an account that can query data and metadata for all tables you want to monitor. You might already have an account set up that will work, or you might need to create one. Give the account sufficient permissions, preferably using a security role.

Logged into your Oracle database as a DBA, you can run the following commands to create the Lightup user, a role with sufficient read access, and then grant that role to the Lightup user, with the following adjustments:

  • The script creates a user named lightup, but you can use an existing account or a different name.
  • The role name is up to you (read_access might already be in use in your system).
  • Substitute in a password of your own.
  • In Oracle 19c, you must GRANT SELECT ON each table that you want to monitor. In Oracle 23c, you can GRANT SELECT ON ALL TABLES ON each schema that has tables you want to monitor.
CREATE USER lightup IDENTIFIED BY <password>;
GRANT CREATE SESSION TO lightup;
GRANT SELECT ON <SCHEMA>.<TABLE> TO lightup; # repeat for each table
CREATE USER lightup IDENTIFIED BY <password>;
GRANT CREATE SESSION TO lightup;
GRANT SELECT ON ANY TABLE ON SCHEMA <schema> TO lightup; #repeat for each schema

Configure connector

  • Host - The URL or IP address of the host that processes connection requests (the string between https:// and /). Example: https://host.na.me/
  • Database Name - The name of the database instance that contains your data. Each datasource can only connect to one database. If you need to connect to more than one, you'll need to create a datasource for each.
  • Username - The username of the account used for connecting to the database and querying data.
  • Password - The username account's password.

Advanced/Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query Governance

Oracle datasources support the Query History, Scheduling, Enable data storage, and Maximum backfill duration, and Maximum distinct values settings. For steps, see Set query governance settings for a datasource.

Date/time data types

These Oracle date/time data types are supported:

  • DATE
  • TIMESTAMP WITH TIMEZONE

Object types

These Oracle object types are supported: