flush buffer cache within stored procedure [message #391577] |
Thu, 12 March 2009 11:18  |
firefly
Messages: 53 Registered: March 2009 Location: Europe
|
Member |
|
|
Hi,
I'm trying to create a stored procedure that can be used by a developer to flush the buffer cache on a test database so I don't have to grant him alter system directly.
My proc is below - does anyone know if this is possible...?
Thanks in advance!
SQL> CREATE OR REPLACE PROCEDURE flush_buffer_cache
2 AS
3 BEGIN
4 alter system flush buffer cache;
5 -- NULL;
6 END;
7 /
Warning: Procedure created with compilation errors.
SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE FLUSH_BUFFER_CACHE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "ALTER" when expecting one of
the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: flush buffer cache within stored procedure [message #391853 is a reply to message #391818] |
Sat, 14 March 2009 00:52  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I was thinking along the same lines, except that I thought a database trigger might do it (after startup trigger, if something like that exists).
It would be (or might be) a waste to spend time or adjust code to account for the odd occasion of an empty buffer cache. Better to fire up some of the key queries to mimic end user behaviour after restarting the database.
|
|
|