0

I am trying to get the Oracle DBMS_OUTPUT of V$RESOURCE_LIMIT results via a procedure.

However, I am getting the error about c1 not being complete or malformed.

So far, I have the following code:

CREATE OR REPLACE PROCEDURE resource_output AS

CURSOR c1 IS SELECT resource_name , current_utilization , max_utilization , initial_allocation , limit_value FROM v$resource_limit;
TYPE rl_typ IS TABLE OF c1%ROWTYPE;
rl rl_typ;

BEGIN
 dbms_output.enable(100000);

   FOR rl IN (
    select * BULK COLLECT INTO rl from v$resource_limit
 )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Resource Name = ' || rl.resource_name ||
                         ', Current Utilization = ' || rl.current_utilization ||
                         ', Max Utilization = ' || rl.max_utilization ||
                         ', Initial Allocation = ' || rl.initial_allocation ||
                         ', Limit Value = ' || rl.limit_value);
  END LOOP;

END resource_output;
Annon
  • 85
  • 1
  • 1
  • 8

3 Answers3

1

Sorry I am not in front of an Oracle instance to help you more fully. However, I see nothing wrong with your declaration of c1.

However, you are not actually using c1 in your procedure. Your FOR loop should be:

FOR rl IN (
  select * from v$resource_limit
) LOOP

... no BULK COLLECT INTO r1.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
0
CREATE OR REPLACE PROCEDURE resource_output AS


BEGIN
 dbms_output.enable(100000);

   FOR rl IN (
    select *  from v$resource_limit
 )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Resource Name = ' || rl.resource_name ||
                         ', Current Utilization = ' || rl.current_utilization ||
                         ', Max Utilization = ' || rl.max_utilization ||
                         ', Initial Allocation = ' || rl.initial_allocation ||
                         ', Limit Value = ' || rl.limit_value);
  END LOOP;

END resource_output;

exec resource_output;

output:

Resource Name = processes, Current Utilization = 103, Max Utilization = 179, Initial Allocation =        900, Limit Value =        900
Resource Name = sessions, Current Utilization = 117, Max Utilization = 212, Initial Allocation =       1372, Limit Value =       1372
Resource Name = enqueue_locks, Current Utilization = 105, Max Utilization = 223, Initial Allocation =      15820, Limit Value =      15820
Resource Name = enqueue_resources, Current Utilization = 35, Max Utilization = 94, Initial Allocation =       6256, Limit Value =  UNLIMITED
Resource Name = ges_procs, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =          0
Resource Name = ges_ress, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = ges_locks, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = ges_cache_ress, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = ges_reg_msgs, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = ges_big_msgs, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = ges_rsv_msgs, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =          0
Resource Name = gcs_resources, Current Utilization = 0, Max Utilization = 0, Initial Allocation =  UNLIMITED, Limit Value =  UNLIMITED
Resource Name = gcs_shadows, Current Utilization = 0, Max Utilization = 0, Initial Allocation =  UNLIMITED, Limit Value =  UNLIMITED
Resource Name = smartio_overhead_memory, Current Utilization = 0, Max Utilization = 68632, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = smartio_buffer_memory, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = smartio_metadata_memory, Current Utilization = 0, Max Utilization = 0, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = smartio_sessions, Current Utilization = 0, Max Utilization = 1, Initial Allocation =          0, Limit Value =  UNLIMITED
Resource Name = dml_locks, Current Utilization = 0, Max Utilization = 0, Initial Allocation =       6036, Limit Value =  UNLIMITED
Resource Name = temporary_table_locks, Current Utilization = 0, Max Utilization = 65, Initial Allocation =  UNLIMITED, Limit Value =  UNLIMITED
Resource Name = transactions, Current Utilization = 5, Max Utilization = 5, Initial Allocation =       1509, Limit Value =  UNLIMITED
Resource Name = branches, Current Utilization = 0, Max Utilization = 0, Initial Allocation =       1509, Limit Value =  UNLIMITED
Resource Name = cmtcallbk, Current Utilization = 3, Max Utilization = 19, Initial Allocation =       1509, Limit Value =  UNLIMITED
Resource Name = max_rollback_segments, Current Utilization = 14, Max Utilization = 21, Initial Allocation =       1509, Limit Value =      65535
Resource Name = sort_segment_locks, Current Utilization = 16, Max Utilization = 40, Initial Allocation =  UNLIMITED, Limit Value =  UNLIMITED
Resource Name = k2q_locks, Current Utilization = 0, Max Utilization = 0, Initial Allocation =       2744, Limit Value =  UNLIMITED
Resource Name = max_shared_servers, Current Utilization = 0, Max Utilization = 0, Initial Allocation =  UNLIMITED, Limit Value =  UNLIMITED
Resource Name = parallel_max_servers, Current Utilization = 0, Max Utilization = 16, Initial Allocation =         40, Limit Value =       3600
  • May I know your Oracle version pls? Because I copied and pasted the code but still getting the errors , ORA-00942 and PLS-00364. – Annon Jan 30 '17 at 09:13
  • ORA -00942 is table or view does not exists and PLS-00364 is loop index variable 'RL' use is invalid. – Annon Jan 30 '17 at 09:15
  • I think it is v$resource_limit issue. I tried it with HR.departments and have no issue. May I know the above was executed using SYS as SYSDBA? – Annon Jan 30 '17 at 09:25
  • Yes i have used sysdba. OK so HR user having direct privilege to v$resource_limit? – Jayaprakash Nagarajan Jan 30 '17 at 10:01
  • Yes. I can do select * from v$resource_limit. But I just can't run the procedure. Anyway , I have no doubt of the code above. I will mark it as answer since it does what it is supposed to do. Loop and print the result. Thanks – Annon Jan 30 '17 at 10:21
  • Hi annon,one information running from editor is different from running inside the SP.pl/sql won't accept the privileges which provided via the role.So check whether that user is got the select any dictionary privilege via any role or you just try the same code by editing the procedure as create or replace procedure resource_ouput AUTHID CURRENT_USER as.......remaining code and compile and try – Jayaprakash Nagarajan Jan 30 '17 at 10:29
  • As noted below , grant select any dictionary to HR did the trick. Thank you for your kind assistance. – Annon Jan 30 '17 at 13:55
0

grant select any dictionary to that particular user; or if you want to give privilege only to v$resource_limit then grant select on v$resource_limit to particular user; and then try