-
Hi friends,
I was trying to compile the following procedure found in the forum. It is not compiling properly.
Can someone help me what is the problem?
create or replace procedure kill_locked_usr
(time in integer) as
my_cursor integer;
my_statement varchar2(80);
result integer;
cursor c1 is
select 'alter system kill session ' ||
||to_char(a.sid)||','||to_char(a.serial#)||
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6 and
a.username like 'THE_BOREING_USER' and
b.ctime > time;
begin
open c1;
loop
fetch c1 into my_statement;
exit when c1%notfound;
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor,my_statement,dbms_sql.v7);
result :=dbms_sql.execute(my_cursor);
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
end;
/
Thanks
manjunath
-
What are the error messages did you get?
Sam
Thanx
Sam
Life is a journey, not a destination!
-
-
Originally posted by manjunathk
cursor c1 is
select 'alter system kill session ' ||
||to_char(a.sid)||','||to_char(a.serial#)||
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6 and
a.username like 'THE_BOREING_USER' and
b.ctime > time;
cursor c1 is
select 'alter system kill session = '''
||to_char(a.sid)||', '||to_char(a.serial#)||''' IMMEDIATE ;'
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6
and a.username like 'THE_BOREING_USER'
and b.ctime > time;
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Hi,
To compile it successfully, just remove first and last || in
||to_char(a.sid)||', '||to_char(a.serial#)||
and think about how to pass THE_BOREING_USER parameter in your procedure.
Hope this helps,
-
Thanks Sam, Dmitriy.
I am still getting the following errors when I tried Sam's suggestion.
Errors for PROCEDURE KILL_LOCKED_USR:
7/1 PL/SQL: SQL Statement ignored
7/8 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
9/6 PLS-00201: identifier 'SYS.V_$SESSION' must be declared
17/1 PL/SQL: SQL Statement ignored
The select 'alter.... statement works fine, in sql plus but is returning the above error when tried in procedure
Manjunath
-
To undestand more, which script did you use? who is the owner of this script? Does the owner has a select-Catalog role granted?
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Yes, the user who is trying to create this procedure has dba privileges.
Anyway i tried once more after giving grant on catalog specifically, but the same errors repeat.
I used the following :
create or replace procedure kill_locked_usr
(time in integer) as
my_cursor integer;
my_statement varchar2(80);
result integer;
cursor c1 is
select 'alter system kill session '''
||to_char(a.sid)||','||to_char(a.serial#)||''';'
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6
and a.username= 'TDS_LOADER'
and b.ctime > time;
begin
open c1;
loop
fetch c1 into my_statement;
exit when c1%notfound;
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor,my_statement,dbms_sql.v7);
result :=dbms_sql.execute(my_cursor);
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
end;
/
Thanks
manjunath
-
Code:
connect internal
grant select on v$session to user;
grant select on v$lock to user;
select priv through a role is useless in pl/sql
-
Originally posted by pando
Code:
connect internal
grant select on v$session to user;
grant select on v$lock to user;
select priv through a role is useless in pl/sql
This would fire
ORA-02030: can only select from fixed tables/views
The way to do is,
sqlplus sys@service_name
SQL> GRANT SELECT ON v_$session TO user;
SQL> GRANT SELECT ON v_$lock TO user;
SQL> connect user/passwd@service_name
SQL> @your_procedure
This will work.
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|