Dienstag, 15. November 2011

Setting up the basic environment...

Ok maybe this is not so APEX related, but we need a basic structure for our application.Our setup will have at least 3 users

  • SYS the owner of the dbms_profiler package
  • MAINTENANCE the Apex workspace
  • HR as a placeholder for any user who runs the profiler. We will use HR to get some data.

Lets assume that you haved managed to install Oracle XE 11gR2 and to upgrade APEX to 4.1.

I will try to remember how that worked and write another post if wanted.

First you should create the Apex Workspace you want to use for your application. My goal is to create a maintenance application so I simply named my Workspace and the cooresponding database schema MAINTENANCE.

Setting up dbms_profiler
Run the script profload.sql as user sys. That will install the dbms_profiler package. It is mandatory to have the package installed within the sys schema.
After that grant the necessary privileges to the MAINTENANCE schema.

GRANT EXECUTE ON sys.dbms_profiler TO maintenance WITH GRANT OPTION;

GRANT SELECT ANY dictionary TO maintenance;
GRANT CREATE ANY SYNONYM TO maintenance;

Script => runasSys.sql

Setting up the profiler tables
Run the script proftab.sql as user maintenance. This will create the table used by dbms_profiler. Usually they are located in the schema that is runing the profiler (hr in our case), but I decided to have them in a central place as part of the apex workspace.

After that grant the necessary privileges to the HR schema.

GRANT INSERT,UPDATE,SELECT ON plsql_profiler_runs TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_units TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_data TO hr;
GRANT SELECT ON plsql_profiler_runnumber TO hr;

GRANT EXECUTE ON sys.dbms_profiler TO hr;

CREATE SYNONYM hr.plsql_profiler_runnumber FOR maintenance.plsql_profiler_runnumber;
CREATE SYNONYM hr.plsql_profiler_runs FOR maintenance.plsql_profiler_runs;
CREATE SYNONYM hr.plsql_profiler_units FOR maintenance.plsql_profiler_units;
CREATE SYNONYM hr.plsql_profiler_data FOR maintenance.plsql_profiler_data;

Script => runasMaintenance.sql
 
Test your environment
Create a very simple procedure as user HR

CREATE OR REPLACE
PROCEDURE test
AS
BEGIN
FOR x IN 1..100
LOOP
NULL;
END LOOP;
END test;
/

and execute it with an activated profiler.

BEGIN
dbms_profiler.start_profiler;
test;
dbms_profiler.stop_profiler;
END;
/

select * from plsql_profiler_runs;

If you see at least one (additional) line of data everything is fine and the basic setup of our environment is done.

 

 

 

 

 

 

You can delete the procedure if you want.

Script =>  runasHr.sql

Keine Kommentare:

Kommentar veröffentlichen