oracle普通用户使用show parameter方法（转）

博客介绍了在Oracle中，通常只有sys用户能使用show parameter查看参数设置。若要普通用户也能使用，需授予其在V_$PARAMETER视图上的SELECT权限。使用show sga时，也需将V_$SGA的select权限授予普通用户。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在Oracle中一般只有sys用户可以使用show parameter来查看参数的设置，如果想要普通用户也可以使用show parameter，需要给普通用户授予在V_$PARAMETER视图上SELECT权限。

Oracle官方解释：

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

类似的，如果要是使用show sga，就需要将V_$SGA的select权限授予普通用户

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