Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help! What is wrong with this !

Re: Help! What is wrong with this !

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/07/20
Message-ID: <964119879.3410.3.pluto.d4ee154e@news.demon.nl>

<sjoshi_at_ingr.com> wrote in message news:8l7g6b$ta$1_at_nnrp1.deja.com...
> Hello PL/SQL Gurus:
> I getting errors of the type:
>
> 7/11 PLS-00201: identifier 'SYS.V$PARAMETER' must be declared
> 7/11 PL/SQL: Item ignored
> 10/19 PLS-00201: identifier 'SYS.V$SYSSTAT' must be declared
> 10/19 PL/SQL: Item ignored
>
> With the following SQL when trying to run it thru. SQL/PLUS. Any
> pointers are greatly appreciated.
> thanks
> Sunit
>
> CREATE OR REPLACE PROCEDURE checkTune
> AS
> v_dbname sys.v$parameter.value%TYPE;
> v_lchp NUMBER(3,2); --Library cache hit percent
> v_dchp NUMBER(3,2); --Dictionary cache hit percent
> v_consistentgets sys.v$sysstat.value%TYPE;
> v_dbblockgets sys.v$sysstat.value%TYPE;
> v_physicalreads sys.v$sysstat.value%TYPE;
> v_bchp NUMBER(3,2); --Buffer cache hit percent
> v_sortsdisk sys.v$sysstat.value%TYPE;
> v_sortsmem sys.v$sysstat.value%TYPE;
> v_sahp NUMBER(3,2); --Sort area hit percent
> v_iparm sys.v$parameter.name%TYPE;
> v_ivalue sys.v$parameter.value%TYPE;
> v_alloc NUMBER;
> v_free NUMBER;
> v_freePercent NUMBER;
>
>
> BEGIN
>
> select round(sgasize/(1024*1024),2), round(bytes/(1024*1024),2),
> round(bytes/sgasize*100,2)
> Into v_alloc, v_free, v_freePercent
> from (select sum(bytes) sgasize from sys.v_$sgastat) s,
> sys.v_$sgastat f
> where f.name='free memory';
>
> dbms_output.put_line ('Allocated Memory(MB): ' || v_alloc);
> dbms_output.put_line ('Free Memory(MB): ' || v_free);
> dbms_output.put_line ('% Free: ' || v_freePercent);
>
> -- Get database name
> SELECT value INTO v_dbname FROM v$parameter WHERE name = 'db_name';
> dbms_output.put_line ('Database name: '||v_dbname);
>
>
> dbms_output.put_line ('Measurement Goal');
> dbms_output.put_line ('------------------------------ --------');
>
> -- Get library cache hit percentage
>
> SELECT (1 - SUM(reloads)/SUM(pins)) INTO v_lchp FROM v$librarycache;
> IF v_lchp < 0.99 THEN
> dbms_output.put_line ('Library cache hit percent >=0.99
> '|| 'Results: ' ||to_char(v_lchp,'9999990.99')||' **Increase the
> SHARED_POOL_SIZE in INIT.ORA');
> SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
> name='shared_pool_size';
> dbms_output.put_line ('-- Current value: '||v_iparm||'
> = '||v_ivalue);
> ELSE
> dbms_output.put_line ('Library cache hit percent >=0.99
> '||'Results: ' ||to_char(v_lchp,'9999990.99')||' OK');
> END IF;
> --
> -- Get dictionary cache hit percentage
>
> SELECT (1 - SUM(getmisses)/SUM(gets)) INTO v_dchp FROM v$rowcache;
> IF v_dchp < 0.90 THEN
> dbms_output.put_line ('Dictionary cache hit percent >=0.90
> '||'Results: ' ||to_char(v_dchp,'9999990.99')||' **Increase the
> SHARED_POOL_SIZE in INIT.ORA');
> SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
> name='shared_pool_size';
> dbms_output.put_line ('-- Current value: '||v_iparm||'
> = '||v_ivalue);
> ELSE
> dbms_output.put_line ('Dictionary cache hit percent >=0.90
> '||'Results: ' ||to_char(v_dchp,'9999990.99')||' OK');
> END IF;
> --
> -- Get buffer cache hit percentage
>
> SELECT value INTO v_consistentgets FROM v$sysstat WHERE name
> = 'consistent gets';
> SELECT value INTO v_dbblockgets FROM v$sysstat WHERE name = 'db
> block gets';
> SELECT value INTO v_physicalreads FROM v$sysstat WHERE name
> = 'physical reads';
> v_bchp := 1 - (v_physicalreads/(v_consistentgets + v_dbblockgets));
> IF v_bchp < 0.90 THEN
> dbms_output.put_line ('Buffer cache hit percent >=0.90
> '||'Results: ' ||to_char(v_bchp,'9999990.99')||' **Increase the
> DB_BLOCK_BUFFERS in INIT.ORA');
> SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
> name='db_block_buffers';
> dbms_output.put_line ('-- Current value: '||v_iparm||'
> = '||v_ivalue);
> ELSE
> dbms_output.put_line ('Buffer cache hit percent >=0.90
> '||'Results: ' ||to_char(v_bchp,'9999990.99')||' OK');
> END IF;
>
> -- Get sort hit percentage
>
> SELECT value INTO v_sortsmem FROM v$sysstat WHERE name = 'sorts
> (memory)';
> SELECT value INTO v_sortsdisk FROM v$sysstat WHERE name = 'sorts
> (disk)';
> v_sahp := 1 - (v_sortsdisk)/(v_sortsmem + v_sortsdisk);
> IF v_sahp < 0.90 THEN
> dbms_output.put_line ('Sort area hit percent >=0.90
> '||'Results: ' ||to_char(v_sahp,'9999990.99')||' **Increase the
> SORT_AREA_SIZE in INIT.ORA');
> SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
> name='sort_area_size';
> dbms_output.put_line ('-- Current value: '||v_iparm||'
> = '||v_ivalue);
> ELSE
> dbms_output.put_line ('Sort area hit percent >=0.90
> '||'Results: ' ||to_char(v_sahp,'9999990.99')||' OK');
> END IF;
>
> END;
> /
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

This question has been answered numerous times. Roles are not enabled in stored procedures, as roles are floating. You need to either issue direct grants to the owner of this procedure, or (in Oracle 8i) use the invokers right pragma.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Jul 20 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US