pl/sql developer 5.x not working with 9i

[Bruce Sanders]

We are running 5.0.1.480 and are not able to retrieve data from tables on Oracle 9I databases.

8I works just fine.

The error message we get for 9I is "table/view doesn't exists" when running a select statement. However, we do see the tables on the left navigation window.

We installed 9i client but this was not the answer. bruce.sanders@mail.sprint.com

 

[Marco Kalter]

I suspect a problem with the Automatic Statistics feature.

To use the Automatic Statistics feature you need select privileges for the he v$session, v$sesstat and v$statname views. I think one or two privileges are missing.

The SYS user owns these views, so only the SYS user can grant privileges on them. Oracle has defined a standard "plustrace" role that contains exactly these privileges. You can find the script to create this role in %oracle_home%/sqlplus/admin/plustrce.sql. Just start SQL*Plus, connect as SYS, and execute this script. If you do not have this script, here are its contents:

create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;

Now that you have created the plustrace role, make sure that it is granted to the PL/SQL Developer user(s), and you're done.

 

[Bruce Sanders]

It worked, thanks.

 

[T-Gergely]

V_$session is no longer granted to plustrace since RDMBS 9.2.0.3 AFAIK. It is not needed for tracing: v$mystat is sufficient. But (unpached) PSD 7.0.3.1123 refuses to use Automatic Statistics when it can't execute "select sid, serial# from v$session where audsid = userenv('SESSIONID')". Is that fixed in later versions?

 

[Marco Kalter]

No, you still need select privileges on v$session.

 

[T-Gergely]

Would you add this to your Todo list, then?

 

[Marco Kalter]

I have added this to the list of enhancement requests.