CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldbms-output
Annon
Annon

Reputation: 85

Oracle DBMS_OUTPUT of V$RESOURCE_LIMIT

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;

Upvotes: 0

Views: 1229

Answers (3)

Jayaprakash Nagarajan
Jayaprakash Nagarajan

Reputation: 89

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

Upvotes: 0

Jayaprakash Nagarajan
Jayaprakash Nagarajan

Reputation: 89

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

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17924

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.

Upvotes: 1

Related Questions