Common Oracle users use the show parameter Method

Common Oracle users use the show parameter Method

In Oracle, only sys users can use show parameter to view parameter settings. If you want common users, you can also use show parameter, the SELECT permission must be granted to common users in the V _ $ PARAMETER view.

Oracle official explanation:

Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected. you need select on v _ $ PARAMETER object privileges to use the PARAMETERS clause, otherwise you will receive a message

ORA-00942: table or view does not exist

SQL> conn/as sysdba
Connected.
SQL> show parameter count

NAME TYPE VALUE
-----------------------------------------------------------------------------
Active_instance_count integer
Cpu_count integer 2
Db_file_multiblock_read_count integer 46

SQL> create user lyn identified by oracle default tablespace users;

User created.

SQL> grant connect, resource to lyn;

Grant succeeded.

SQL> conn lyn/oracle
Connected.
SQL> show parameter count
ORA-00942: table or view does not exist
SQL> conn/as sysdba
Connected.
SQL> grant select on v _ $ parameter to lyn;

Grant succeeded.

SQL> conn lyn/oracle
Connected.
SQL> show parameter count

NAME TYPE VALUE
-----------------------------------------------------------------------------
Active_instance_count integer
Cpu_count integer 2
Db_file_multiblock_read_count integer 46

 

Similarly, if you use show sga, You need to grant the select permission of V _ $ SGA to a common user.

SQL> conn lyn/oracle
Connected.
SQL> show sga
ORA-00942: table or view does not exist

SQL> conn/as sysdba
Connected.
SQL> grant select on v _ $ sga to lyn;

Grant succeeded.

SQL> conn lyn/oracle
Connected.
SQL> show sga

Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes