Create the DPA monitoring user for Oracle

You can use the following script to create the user that DPA uses to monitor your Oracle database instance. This script creates a monitoring user with the required rights and privileges. Use this script, for example, when you have deleted or lost the monitoring user that was created when you registered the instance, or when you do not wish to create a monitoring user during registration. 

1. Connect as SYS to the monitored database.
2. Save the following script to a file, and run it on the monitored database.
3. Provide the information the script asks you.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

REM connect as SYS on Mon DB.
REM create monitoring user for SolarWinds DPA for Oracle.
 
prompt Enter DPA Username:
accept DPA_Username
prompt Enter DPA Password:
accept DPA_Password
prompt Enter DPA Tablespace:
accept TS
prompt Enter DPA Temporary Tablespace:
accept TTS
 
rem drop user &DPA_Username cascade;
 
create user &DPA_Username identified by &DPA_Password
default tablespace &TS temporary tablespace &TTS;
 
grant create table to &DPA_Username;
grant create or replace synonym to &DPA_Username;
grant create session to &DPA_Username;
grant create sequence to &DPA_Username;
grant unlimited tablespace to &DPA_Username;
 
grant select on dba_views to &DPA_Username;
grant select on dba_objects to &DPA_Username;
grant select on user_synonyms to &DPA_Username;
 
REM For 8i the "select any dictionary" privilege does not exist
grant select any dictionary to &DPA_Username;
grant select_catalog_role to &DPA_Username;
grant select on v_$database to &DPA_Username;
grant select on v_$instance to &DPA_Username;
grant select on v_$active_instances to &DPA_Username;
grant select on V_$parameter to &DPA_Username;
grant select on V_$latch to &DPA_Username;
grant select on V_$sqlarea to &DPA_Username;
grant select on V_$datafile to &DPA_Username;
grant select on V_$event_name to &DPA_Username;
grant select on V_$version to &DPA_Username;
 
REM create views 
create or replace view x_$KSUSE as select * from x$ksuse;
create or replace view x_$ksusecst as select * from x$ksusecst;
create or replace view X_$KCCCF as select * from x$KCCCF;
create or replace view X_$KGLNA1 AS select * from x$kglna1;
create or replace view X_$KGLNA AS select * from x$KGLNA;
create or replace view x_$KGLCURSOR AS select * from x$KGLCURSOR;
create or replace view x_$KCFIO AS select * from x$KCFIO;
create or replace view x_$KCFTIO AS select * from x$KCFTIO;
create or replace view x_$dbgalertext as select * from x$dbgalertext; -- for Oracle 11 and higher

grant select on x_$ksuse to &DPA_Username;
grant select on x_$ksusecst to &DPA_Username;
grant select on x_$kcccf to &DPA_Username;
grant select on x_$kglna1 to &DPA_Username;
grant select on x_$kglna to &DPA_Username;
grant select on x_$kglcursor to &DPA_Username;
grant select on x_$kcfio to &DPA_Username;
grant select on x_$kcftio to &DPA_Username;
grant select on x_$dbgalertext to &DPA_Username; -- for Oracle 11 and higher
 
grant select on v_$parameter to &DPA_Username;
grant select on v_$instance to &DPA_Username;

create or replace synonym &DPA_Username..x$ksuse for sys.x_$ksuse;
create or replace synonym &DPA_Username..x$ksusecst for sys.x_$ksusecst;
create or replace synonym &DPA_Username..x$kcccf for sys.x_$kcccf;
create or replace synonym &DPA_Username..x$kglna for sys.x_$kglna;
create or replace synonym &DPA_Username..x$kglna1 for sys.x_$kglna1;
create or replace synonym &DPA_Username..x$kglcursor for sys.x_$kglcursor;
create or replace synonym &DPA_Username..x$kcfio for sys.x_$kcfio;
create or replace synonym &DPA_Username..x$kcftio for sys.x_$kcftio;
create or replace synonym &DPA_Username..x$dbgalertext for sys.x_$dbgalertext; -- for Oracle 11 and higher
 
create table &DPA_Username..confio (id number);
create table &DPA_Username..mproc (id number);
 
-- Create utl_con package used for getting explain plan and block data.
-- If the utl_con package creation fails, try creating it with the package that matches the Oracle version:
--      utl_con_9i.plb
--      utl_con_10g.plb
--      utl_con_11g.plb
--      utl_con_12c.plb
@"<DPA install directory>\iwc\tomcat\webapps\iwc\WEB-INF\classes\resources\database\Oracle\utl_con_8iplus.plb"
 
grant execute on sys.utl_con to &DPA_Username;
grant execute on sys.dbms_sql to &DPA_Username;