SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Profiler Error "ORA-00942"... Oracle

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Profiler Error "ORA-00942"... Oracle
Author Message
Koala



Joined: 25 Mar 2003
Posts: 3

Post Profiler Error "ORA-00942"... Oracle 9.0 Reply with quote

Hi all.

I've got "DB Tools" and try SQL Profiler against
Oracle 9.0.2 installation.
When create Run, any startup code, even as dumb
as below:

BEGIN
NULL;
END;

produce an error "ORA-00942: table or view does
not exist".

What could be the reason please?
Thanks a lot.
WBR, Koala

Tue Mar 25, 2003 10:16 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Profiler Error "ORA-00942"... Oracle Reply with quote

You either don't have Oracle DBMS_PROFILER package and required profiler tables installed on the database or you do not have DB Tools SQL Profiler project table installed. Please see "Setting Up" topic in the PL/SQL Profiler section of the on-line help for more details.

: Hi all.

: I've got "DB Tools" and try SQL Profiler against
: Oracle 9.0.2 installation.
: When create Run, any startup code, even as dumb
: as below: BEGIN
: NULL;
: END;

: produce an error "ORA-00942: table or view does
: not exist".

: What could be the reason please?
: Thanks a lot.
: WBR, Koala

Tue Mar 25, 2003 2:07 pm View user's profile Send private message
Koala



Joined: 25 Mar 2003
Posts: 3

Post Re: Profiler Error "ORA-00942"... Oracle Reply with quote

: or you do not have DB Tools SQL Profiler project table installed.
Oh, yeh! It's very much a shot.
I found from "monitor.err" log it's really so.

Then I deinstalled DB Tools, installed them anew, run Profiler - and hope that
required schema (ora_monitor) and table (profiler_data) will be created.
Indeed, according to the Manual, "...First time you run PL/SQL Profiler it will
also create PROFILER_DATA table in the ORA_MONITOR schema."

But unfortunately nothing like this happened.
So could you please reveal the structure of the table and password for
user "ora_monitor" the Profiler then uses, in order to allow me help
the application and create the stuff manually?

Thanks,
Koala

Wed Mar 26, 2003 7:26 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Profiler Error "ORA-00942"... Oracle Reply with quote

Doesn't it say on startup that PL/SQL Profiler is not installed and prompts you to run DB Setup utility to install it now?

Anyway, please run the DB Setup and choose to install PL/SQL Profiler. Uncheck tools and options you don't want and leave only these that you want. This will create all required objects, ORA_MONITOR schema and grants the minimal privileges required for the profiling.

: Oh, yeh! It's very much a shot.
: I found from "monitor.err" log it's really so.

: Then I deinstalled DB Tools, installed them anew, run Profiler - and hope
: that
: required schema (ora_monitor) and table (profiler_data) will be created.
: Indeed, according to the Manual, "...First time you run PL/SQL Profiler
: it will
: also create PROFILER_DATA table in the ORA_MONITOR schema."

: But unfortunately nothing like this happened.
: So could you please reveal the structure of the table and password for
: user "ora_monitor" the Profiler then uses, in order to allow me
: help
: the application and create the stuff manually?

: Thanks,
: Koala

Wed Mar 26, 2003 9:27 am View user's profile Send private message
Koala



Joined: 25 Mar 2003
Posts: 3

Post Re: Profiler Error "ORA-00942"... Oracle Reply with quote

Hi.

: Doesn't it say on startup that PL/SQL Profiler is not installed and prompts
: you to run DB Setup utility to install it now?
Nope.

: Anyway, please run the DB Setup and choose to install PL/SQL Profiler.
: Uncheck tools and options you don't want and leave only these that you
: want. This will create all required objects, ORA_MONITOR schema and grants
: the minimal privileges required for the profiling.
Sorry, it creates only message box "DBMS_PROFILER package is not installed, but
setup can continue nevertheless" (by the way, I have run PROFLOAD.SQL script,
and it completed successfully, and actually I can see the package in SYS schema),
and then another message box "Error executing PL/SQL script".

After that, setup completes and required table still does not exist (athough
user ORA_MONITOR does).

In fact, when I search files in DB Tools directory for context "PROFILER_DATA",
nothing is found. It means the table is not gonna be created by these scripts...
Looks strange, heh.

With best regards,
Koala

Thu Mar 27, 2003 5:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Profiler Error "ORA-00942"... Oracle Reply with quote

The script is not in a text file. It is in a binary file that's why you cannot find it. Here it is

CREATE TABLE ora_monitor.profiler_data (

runid NUMBER NOT NULL PRIMARY KEY,

run_name VARCHAR2(100) CHECK (rtrim(run_name) != NULL),

start_time DATE DEFAULT sysdate NOT NULL,

user_runid NUMBER DEFAULT 1 NOT NULL,

run_seq INTEGER DEFAULT 1 NOT NULL,

user_sid NUMBER NOT NULL,

user_name VARCHAR2(30) DEFAULT USER NOT NULL,

run_cpu NUMBER DEFAULT 0,

run_io NUMBER DEFAULT 0,

run_memory NUMBER DEFAULT 0,

run_sorts NUMBER DEFAULT 0,

run_sort_ratio NUMBER DEFAULT 0,

run_gets NUMBER DEFAULT 0,

run_status VARCHAR2(12) DEFAULT 'INCOMPLETE',

session_name VARCHAR2(100) CHECK (rtrim(session_name) != NULL),

session_comments VARCHAR2(4000),

run_source LONG);

CREATE INDEX ora_monitor.i_profiler_data ON ora_monitor.profiler_data
(user_name, session_name);

GRANT SELECT, INSERT, DELETE, UPDATE ON ora_monitor.profiler_data TO PUBLIC;
GRANT EXECUTE ON sys.dbms_profiler TO PUBLIC;

Good luck!

: Hi.
: Nope.
: Sorry, it creates only message box "DBMS_PROFILER package is not
: installed, but
: setup can continue nevertheless" (by the way, I have run PROFLOAD.SQL
: script,
: and it completed successfully, and actually I can see the package in SYS
: schema),
: and then another message box "Error executing PL/SQL script".

: After that, setup completes and required table still does not exist (athough
: user ORA_MONITOR does).

: In fact, when I search files in DB Tools directory for context
: "PROFILER_DATA",
: nothing is found. It means the table is not gonna be created by these
: scripts...
: Looks strange, heh.

: With best regards,
: Koala

Thu Mar 27, 2003 9:02 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.