Database Reference
In-Depth Information
ERROR at line 2:
ORA-00942: table or view does not exist
SCOTT@ORA12CR1> show parameter db_block_s
ORA-00942: table or view does not exist
“Normal” accounts are not granted access to the
V$
performance views by default. Don't let that get you
down, however. There is a documented API typically available to all users that permits you to see the contents of
V$PARAMETER;
this little helper function helps you see what is set as a parameter. For example:
SCOTT@ORA12CR1> create or replace
2 function get_param( p_name in varchar2 )
3 return varchar2
4 as
5 l_param_type number;
6 l_intval binary_integer;
7 l_strval varchar2(256);
8 invalid_parameter exception;
9 pragma exception_init( invalid_parameter, -20000 );
10 begin
11 begin
12 l_param_type :=
13 dbms_utility.get_parameter_value
14 ( parnam => p_name,
15 intval => l_intval,
16 strval => l_strval );
17 exception
18 when invalid_parameter
19 then
20 return '*access denied*';
21 end;
22 if ( l_param_type = 0 )
23 then
24 l_strval := to_char(l_intval);
25 end if;
26 return l_strval;
27 end get_param;
28 /
Function created.
■
if you've applied the latest security patch for 11g r2 or 12c, then you may need to grant select on
v_$parameter
or
select_catalog_role
to the user executing this function.
Note
If you execute this function in SQL*Plus, you'll see:
SCOTT@ORA12CR1> exec dbms_output.put_line( get_param( 'db_block_size' ) );
8192
PL/SQL procedure successfully completed.
