I want to get the number of sessions in Oracle using the SQL query:

SELECT value FROM v$parameter WHERE name = 'sessions'

But I get this error:

Error starting at line 1 in command:
SELECT value FROM v$parameter WHERE name = 'sessions'
Error at Command Line:1 Column:18
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:

Maybe the Oracle user that I use is not privileged?

2

4 Answers

Generally the better approach is to use a procedure and grant the necessary privileges to this procedure. However if you want use SQL directly, you can grant SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY to the user.

Probably. To grant the rights, you need to use the table name as V_$PARAMETER. It comes from some restriction when granting rights on dynamic views.

4

If you want to use SQL directly (referring to the second option in the accepted answer)

As of Feb 2023, using Oracle version 19, this works...

Connect as SYSTEM and run

grant SELECT ANY DICTIONARY to <user>;

But SELECT_CATALOG_ROLE didn't work for me...

grant SELECT_CATALOG_ROLE to ...;

There are documented differences between the two here:

This is where the author gives the following info and warning:

Well, SELECT_CATALOG_ROLE allows access to some things Oracle deemed not allowed by SELECT ANY DICTIONARY so we need to be careful of granting this role on these grounds. BUT, the overwhelming issue for me is that SELECT_CATALOG_ROLE gives access to 4539 objects and SELECT ANY DICTIONARY gives access to 6228 objects (both numbers in 18c XE)

I am not sure why Oracle do not publish the full list of exclusions in SELECT ANY DICTIONARY but they do publish all of the main tables. We can easily find out anyway. For me, i want to know what does SELECT ANY DICTIONARY really mean. I want to know what i am actually granting if I give out that privilege; well it means access to 6228 tables and views in 18cXE

Both of these rights should not be used; they are a sledgehammer to crack a peanut. If someone needs access to V$SESSION or V$DATABASE and there is a legitimate reason to have that access then grant access on the individual views not SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE.

using the privileges: - select any table, alter any table when running the grant as SYS with SYSDBA in Oracle 12c solved the issue for me.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.