Set Up Exadata Warehouse

You can set up Exadata Warehouse by selecting a database, creating an analytics user and named credential, and then configuring it in Enterprise Manager.

The database that hosts Exadata Warehouse can be an on-premises database or an Autonomous AI Database and should be version 19c or later. A database hosted on Exadata provides greater space efficiency through Hybrid Columnar Compression. Ensure that the Database plug-in is deployed on all the agents monitoring the Database Machine.

To set up Exadata Warehouse:

  1. Identify a database monitored by Enterprise Manager to serve as Exadata Warehouse.

    Exadata Warehouse supports all database types, including on-premises databases and Autonomous AI Databases.

  2. Create an analytics user schema on the selected database by running the appropriate script in SQL Manager.
    • If Exadata Warehouse is hosted on an Autonomous AI Database, use the analytics-user script given below:
      --Scripts for customer to create exadata analytics user
set serveroutput on FORMAT WORD_WRAPPED
set echo on
set linesize 512
set serveroutput on size unlimited
 
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
 
declare
 
    l_user      varchar2(1024) := '&1'; -- replace with your user
    l_password  varchar2(1024) := '&2'; -- replace with your pwd
 
    function schema_exists(p_user in varchar2) return boolean as
        n      number;
    begin
        select count(1) into n from dba_users where username=upper(p_user);
        return (n != 0);
    end;
 
    procedure create_domain_user (p_user in varchar2, p_passwd in varchar2) is
        l_sql          varchar2(4000);
 
    begin
        dbms_output.put_line(' Checking if schema ' || p_user || '  exists');
        if not schema_exists(p_user) then
            dbms_output.put_line(' creating Schema ');
            l_sql := 'CREATE USER '|| p_user ||' IDENTIFIED BY "'|| p_passwd ||'"';
            execute immediate l_sql;
            dbms_output.put_line(' Schema user created ' || p_user);
        else
            dbms_output.put_line(' Schema exists ');
        end if;
    exception when others then
        dbms_output.put_line(' Error creating schema ' || p_user );
        dbms_output.put_line(' Error ' || SQLCODE  || '   Msg: ' || substr(SQLERRM,1,1024) );
        raise;
    end;
 
    procedure exec_sql(p_sql in varchar2) is
    begin
        execute immediate p_sql;
    exception when others then
        dbms_output.put_line(' Error running SQL  : ' || p_sql || '  ErrCode: ' || SQLCODE ||  '    Msg: ' || substr(SQLERRM,1,512));
    end;
 
    procedure grant_access_to_user (p_user in varchar2) is
        l_sql          varchar2(4000);
    begin
 
       l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE  PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SEQUENCE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SESSION TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT UNLIMITED TABLESPACE to  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT RESOURCE, CONNECT TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE ANY JOB TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT DWROLE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT EXECUTE ON DBMS_CLOUD to  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO  ' || l_user;
        exec_sql(l_sql);
 
        -- grants to the schema user
        l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE JOB TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CTXAPP TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$PGASTAT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$PARAMETER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$RESERVED_WORDS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.DBA_SEGMENTS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOB TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_OUTPUT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOCK TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_UTILITY TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_ASSERT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_APPLICATION_INFO TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_RLS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_INMEMORY TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'ALTER USER ' || l_user || ' QUOTA UNLIMITED ON DATA';
        exec_sql(l_sql);
    end;
 
    procedure enable_hints_to_system is
        l_sql          varchar2(4000);
        l_value        varchar2(4000);
    begin
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_hints';
 
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer hints');
            exec_sql(l_sql);
        end if;
 
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_parallel_hints';
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_PARALLEL_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer parallel hints');
            exec_sql(l_sql);
        end if;
    end;
 
begin
 
    dbms_output.put_line('Enabling Hints if not enabled');
    enable_hints_to_system();
 
    dbms_output.put_line('Schema processing  ' || l_user ||  '     Pwd ' || l_password);
    create_domain_user(l_user, l_password);
 
    grant_access_to_user(l_user);
    dbms_output.put_line('Schema processed ');
end;
/
    • If Exadata Warehouse is hosted on a database monitored as an on-premises database, use the create_xawh_user_local_db.sql script given below:
      Rem
Rem $Header: emgc/emxa/test/src/tvmxa/tvmxagef/tvmxagef_create_xawh_user_onpremdb.sql /main/1 2026/03/31 14:14:49 xuli Exp $
Rem
Rem tvmxagef_create_xawh_user_onpremdb.sql
Rem
Rem Copyright (c) 2026, Oracle and/or its affiliates.
Rem
Rem    NAME
Rem      tvmxagef_create_xawh_user_onpremdb.sql - Create exadata analytics user for exadata warehouse running on onprem db
Rem
Rem    DESCRIPTION
Rem      Script to create exadata analytics user for exadata warehouse running on onprem db
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emgc/emxa/test/src/tvmxa/tvmxagef/tvmxagef_create_xawh_user_onpremdb.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE:
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    SQL_CALLING_FILE:
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    xuli        03/14/26 - Create the XAWH Schema Name for On-prem Warehouse
Rem                           DB
Rem    xuli        03/14/26 - Created
Rem


set serveroutput on FORMAT WORD_WRAPPED
set echo on
set linesize 512
set serveroutput on size unlimited
 
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
 
declare
 -- adwuser IDENTIFIED BY We1come_12345 
    l_user      varchar2(1024) := 'adwuser';
    l_password  varchar2(1024) := 'We1come_12345';
 
    function schema_exists(p_user in varchar2) return boolean as
        n      number;
    begin
        select count(1) into n from dba_users where username=upper(p_user);
        return (n != 0);
    end;
 
    procedure create_domain_user (p_user in varchar2, p_passwd in varchar2) is
        l_sql          varchar2(4000);
 
    begin
        dbms_output.put_line(' Checking if schema ' || p_user || '  exists');
        if not schema_exists(p_user) then
            dbms_output.put_line(' creating Schema '); l_sql := 'CREATE USER '|| p_user ||' IDENTIFIED BY "'|| p_passwd ||'"';
            execute immediate l_sql;
            dbms_output.put_line(' Schema user created ' || p_user);
        else
            dbms_output.put_line(' Schema exists ');
        end if;
    exception when others then
        dbms_output.put_line(' Error creating schema ' || p_user );
        dbms_output.put_line(' Error ' || SQLCODE  || '   Msg: ' || substr(SQLERRM,1,1024) );
        raise;
    end;
 
    procedure exec_sql(p_sql in varchar2) is
    begin
        execute immediate p_sql;
    exception when others then
        dbms_output.put_line(' Error running SQL  : ' || p_sql || '  ErrCode: ' || SQLCODE ||  '    Msg: ' || substr(SQLERRM,1,512));
    end;
 
    procedure grant_access_to_user (p_user in varchar2) is
        l_sql          varchar2(4000);
    begin
 
       l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE  PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SEQUENCE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SESSION TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT UNLIMITED TABLESPACE to  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT RESOURCE, CONNECT TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE ANY JOB TO  ' || l_user;
        exec_sql(l_sql);

       l_sql := 'GRANT CREATE ANY DIRECTORY TO  ' || l_user;
        exec_sql(l_sql);

       l_sql :='GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO  ' || l_user;
        exec_sql(l_sql);
 
        -- grants to the schema user
        l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE JOB TO  ' || l_user;
        exec_sql(l_sql);

       l_sql := 'GRANT CREATE VIEW TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);

        l_sql := 'GRANT SELECT ON SYS.V_$PGASTAT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$PARAMETER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$RESERVED_WORDS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.DBA_SEGMENTS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOB TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_OUTPUT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOCK TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_UTILITY TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_ASSERT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_APPLICATION_INFO TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_RLS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_INMEMORY TO ' || l_user;
        exec_sql(l_sql);

/*        l_sql := 'ALTER USER ' || l_user || ' QUOTA UNLIMITED ON USERS';
        exec_sql(l_sql);  */

    end;
 
    procedure enable_hints_to_system is
        l_sql          varchar2(4000);
        l_value        varchar2(4000);
    begin
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_hints';
 
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer hints');
            exec_sql(l_sql);
        end if;
 
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_parallel_hints';
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_PARALLEL_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer parallel hints');
            exec_sql(l_sql);
        end if;
    end;
 
begin
    dbms_output.put_line('Enabling Hints if not enabled');
    enable_hints_to_system();
 
    dbms_output.put_line('Schema processing  ' || l_user ||  '     Pwd ' || l_password);
    create_domain_user(l_user, l_password);
 
    grant_access_to_user(l_user);
    dbms_output.put_line('Schema processed ');
end;
/

commit;
disconnect;
exit;
  3. Create a named credential for Exadata Warehouse in Enterprise Manager to allow the analytics user to access the selected database.
  4. Configure Exadata Warehouse and enable data upload.
    1. Log in to Enterprise Manager as a Super Administrator user.
    2. From the Setup menu, select Cloud Bridge.

      The Cloud Bridge page opens and is initially empty. For information on creating a Cloud Bridge, see Integrating Enterprise Manager with OCI Services in Oracle Enterprise Manager Administrator's Guide .

    3. Click Configure Exadata Warehouse.
    4. In the Configure Exadata Warehouse dialog box, select the Warehouse Database, specify the corresponding Database Credential, click Configure to create the Exadata Warehouse service, and then click OK to return to the Cloud Bridge page.

      Configure Exadata Warehouse

    5. On the Cloud Bridge page, click Enable Data Export.
    6. In the OCI Service Data Export dialog box, select Exadata Warehouse in the OCI Service field, select the target group containing the Database Machine targets in the Source field, select the OCI bridge, and then click Submit.

      Data export is enabled and data from the Enterprise Manager instance starts uploading to the specified OCI bucket.

  5. Verify the data upload by periodically checking the upload progress. For information, see Verify Data Uploads.
  6. Visualize historical and forecast data in Exadata Warehouse using the Metrics Dashboard, which provides customizable visualizations and curated views of Engineered System hardware and software metrics for resource planning. Forecast projections for selected metrics are available when sufficient historical data has been uploaded to Exadata Warehouse. For more information, see Metrics Dashboard.