Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Database Performance Analyzer (DPA) > Recreate the DPA monitoring user for Oracle

Recreate the DPA monitoring user for Oracle

Table of contents
No headers
Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 66 Votes: 0 Revisions: 10

If you have deleted or lost the user that DPA uses for monitoring your Oracle database instance, you can use this script to recreate that user with proper rights and privileges.

  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.


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 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_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;
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 v_$parameter to &DPA_Username;
grant select on v_$instance to &DPA_Username;
create synonym &DPA_Username..x$ksuse for sys.x_$ksuse;
create synonym &DPA_Username..x$ksusecst for sys.x_$ksusecst;
create synonym &DPA_Username..x$kcccf for sys.x_$kcccf;
create synonym &DPA_Username..x$kglna for sys.x_$kglna;
create synonym &DPA_Username..x$kglna1 for sys.x_$kglna1;
create synonym &DPA_Username..x$kglcursor for sys.x_$kglcursor;
create synonym &DPA_Username..x$kcfio for sys.x_$kcfio;
create synonym &DPA_Username..x$kcftio for sys.x_$kcftio;
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
@"C:\Program Files\SolarWinds\DPA\iwc\tomcat\webapps\idc\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;
Last modified
19:17, 22 Jun 2016