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
Connect to an Oracle datasource
- In the left pane select Datasources*
- In the main page select Create Datasource +.
- Enter a Datasource Name, then for Connector Type select Oracle.
- Under Configure connector, provide the following inputs:
- 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.
- After entering the required settings and any optional settings that apply, below the Configure connector section select Test Connection.
- After a successful connection test, select Save.
- Your new datasource appears in the list of available datasources. By default, these are listed in alphabetical order, so you might have to scroll or change the sort order to see your new datasource.
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 Scheduling, Enforce indexed timestamps, Enable data storage, 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:
- Tables
- Views
Supported Versions
- 19c
Updated 2 months ago