what permssions needed to view session details

[Kevin Herring]

Hi

When I bring up the sessions window using the schema owner I can view the list of all the current sessions. However when I click on one of them the Cursors and SQl Text tabs at the bottom simply contain the text:

ORA-00942: table or view does not exist

Now I know this is just a permissions issue, but I cant for the life of me see which tables/views I need to have access to. I don't have control over the database, and the DBA wont let my user be put in the DBA role, so I need to know the exact tables/views so that I can ask them. I already have access to v$session.

As a suggestion, there is nothing in this in the help file. Could you improve this and maybe even add a helpful message instead of just showing the ORA-00942?

 

[Marco Kalter]

If you press the "Define Session Queries" button on the Session Window toolbar, you can see a tab page for Session Filter queries and Session Details queries. On the Details tab page you can see the queries that are used for the various detail screens. You can check which tables and views are used there.

 

[CTzen]

Kevin,

ask your dba to grant you SELECT_CATALOG_ROLE.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as slava

SQL> create user test_u identified by test_u;

User created

Executed in 0.328 seconds

SQL> grant create table, create session, select_catalog_role to test_u;

Grant succeeded

Executed in 0.031 seconds

SQL> connect test_u/test_u@xe
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as test_u

SQL> select * from v$session where username = user;

SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME
-------- ---------- ---------- ---------- -------- ---------- ----------
2295F530         24          3        871 22935164         40 TEST_U
2296E958         37         26        873 22935D44         40 TEST_U

Executed in 0.032 seconds

SQL>
SQL> connect system@xe
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as system

SQL> drop user test_u;

User dropped

Executed in 4.171 seconds

 

[Kevin Herring]

Thanks chaps, thats brilliant.