Ops$tkyte%ORA11GR2> create user util identified by util do not use thees accounts - use your OWN accounts. Thirdly - take the above and change SYS to SYSTEM, same applies. sys is ours, sys is special, sys is magic, things work differently as sys than for other users. I strongly encourage you to STOP THIS, do not do this, it is misleading, time wasting, not useful. Further - flushing the caches presents you with a situation you would NEVER SEE in real life. Search this site for "secondary sga" - to see what I'm talking about. That means, the file system cache is already caching the stuff and just because we say we did a physical IO - it doesn't mean we did. You are probably using a file system - a buffered file system. ORA-06512: at "SYSTEM.DEV_FLUSH_CACHE", line 6ĭid you know that flushing the caches for "performance testing" is a really horribly bad, misleading, time wasting, not good for anything approach? SQL> grant execute on v_flush_cache to test SQL> create user test identified by test SQL> CREATE OR REPLACE PROCEDURE v_flush_cache ASĤ l_ddl := 'ALTER SYSTEM FLUSH SHARED_POOL' ħ l_ddl := 'ALTER SYSTEM FLUSH BUFFER_CACHE' This is for developer to flush memory cache when tuning their SQLs. This approach doesn't seem to work in 11.2.0.2 and I'm trying to wrap ALTER SYSTEM FLUSH SHARED_POOL and ALTER SYSTEM FLUSH BUFFER_CACHE in a procedure created in SYSTEM and grant the execute privilege to a TEST user.
ORA-06512: at "FXBLOTTER.KILL_SESSION", line 9 SQL> exec testdbauser.kill_session(1016,808) īEGIN testdbauser.kill_session(1016,808) END
Grant execute on kill_session to testuser OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY I granted the owner the following grants. I notices this thread was created in Oracle 8. That view will show them their sessions only. Select * from v$session where username = USER Īnd grant them select on that view. You would probably want to "grant select on v_$session" when connected as SYS to these people as well so they can 'see' the v$session dynamic performance view to get their sid/serial# pairs. It would allow them to kill any session they own (running under their username). You would then grant execute on this procedure to anyone you want. To see why the caveat on granting directly to them is needed, please see: O ALTER SYSTEM granted directly to them - not via a role.
This grant must be directly to them, not via a role. O SELECT on v_$session granted to them by SYS. The owner of this procedure needs to have It could look something like:Ĭreate or replace procedure kill_session( p_sid in varchar2,Ĭursor_name pls_integer default dbms_sql.open_cursor In 7.3, we would use the dbms_sql package to accomplish this. We must use dynamic sql to accomplish this feat. Where sid and serial# are obtained from the v$session dynamic performance table.Īlter system is considered DDL and hence cannot be executed directly from PLSQL.
0 kommentar(er)
