Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Create the DPA monitoring user for Oracle

Create the DPA monitoring user for Oracle

Table of contents
No headers
Created by Interspire Import, last modified by Melanie Boyd on Jun 28, 2018

Views: 1,645 Votes: 1 Revisions: 18

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.

 

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;
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 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 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;
Last modified

Tags

Classifications

Public