OraFAQ Forum: SQL & PL/SQL » flush buffer cache within stored procedure

Home » SQL & PL/SQL » SQL & PL/SQL » flush buffer cache within stored procedure (9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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>

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391579 is a reply to message #391577]

Thu, 12 March 2009 11:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Direct DDL is not allowed within PL/SQL procedure.
must abuse EXECUTE IMMEDIATE.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391580 is a reply to message #391579]

Thu, 12 March 2009 11:25

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Thanks for the quick reply - how do I use EXECUTE IMMEDIATE in my scenario?

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391581 is a reply to message #391580]

Thu, 12 March 2009 11:29

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The usual way.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391582 is a reply to message #391577]

Thu, 12 March 2009 11:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Which is it?
Are you unwilling or incapable to Read The Fine Manual?
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#8074

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391583 is a reply to message #391580]

Thu, 12 March 2009 11:30

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I recommend you to read and search in:
PL/SQL User's Guide and Reference

Regards
Michel

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391588 is a reply to message #391577]

Thu, 12 March 2009 11:39

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Thanks for the link. I have the procedure compiled but am getting an error with insufficient privs - I am logged in as SYSTEM . Any ideas welcome!

SQL> show user
USER is "SYSTEM"
SQL>
SQL> CREATE OR REPLACE PROCEDURE flush_buffer_cache
2 is
3 sql_stmt varchar(200);
4
5 BEGIN
6 sql_stmt := 'alter system flush buffer_cache';
7 execute immediate sql_stmt;
8 -- NULL;
9 END;
10 /

Procedure created.

SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL> execute flush_buffer_cache
BEGIN flush_buffer_cache; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.FLUSH_BUFFER_CACHE", line 7
ORA-06512: at line 1

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391590 is a reply to message #391588]

Thu, 12 March 2009 11:44

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391592 is a reply to message #391590]

Thu, 12 March 2009 11:47

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Thanks Michel, but I am running this as SYSTEM - from which I can ALTER SYSTEM outside of the procedure...

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391595 is a reply to message #391577]

Thu, 12 March 2009 11:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Privileges acquired via ROLE do NOT apply within PL/SQL procedures.

Explicit GRANT is necessary.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391596 is a reply to message #391595]

Thu, 12 March 2009 11:51

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Sorry don't get this - do I need to grant system execute on this procedure even though the procedure was created by system?

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391598 is a reply to message #391592]

Thu, 12 March 2009 11:53

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please carefully read the article it explains why you have this error and how to verify if it really applies to you.

Regards
Michel

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391599 is a reply to message #391598]

Thu, 12 March 2009 12:02

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

I ran the test "set role none" and then tried the ALTER SYSTEM and it failed (so am I right in saying that SYSTEM gets the ALTER SYSTEM privilege from a role then?).
So am I correct in saying that the developer I am trying to grant this procedure to will actually require ALTER SYSTEM privilege granted to them explicitly before they can run this proc? If so this is not what I am looking for at all.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391600 is a reply to message #391599]

Thu, 12 March 2009 12:09

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Just to add - how would you then grant the user the ability to flush the buffer cache without having to grant the ALTER SYSTEM priv which will them the perform a number of other tasks?
Thanks!

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391602 is a reply to message #391577]

Thu, 12 March 2009 12:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The procedure should be owned by a user with sufficient privs granted to it.

GRANT EXECUTE ON flush_buffer_cache to DEV_A

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391603 is a reply to message #391599]

Thu, 12 March 2009 12:23

flyboy
Messages: 1903
Registered: November 2006

Senior Member

firefly wrote on Thu, 12 March 2009 18:02

I ran the test "set role none" and then tried the ALTER SYSTEM and it failed (so am I right in saying that SYSTEM gets the ALTER SYSTEM privilege from a role then?).

Yes.

firefly wrote on Thu, 12 March 2009 18:02

So am I correct in saying that the developer I am trying to grant this procedure to will actually require ALTER SYSTEM privilege granted to them explicitly before they can run this proc? If so this is not what I am looking for at all.

No, the ALTER SYSTEM privilege is (by default) required to the owner of that procedure (SYSTEM in your case).
It may be changed by specifying AUTHID CURRENT_USER clause (as documented for CREATE PROCEDURE statement); but from your post it seems you do not want this.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391604 is a reply to message #391602]

Thu, 12 March 2009 12:24

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

Got it! Created the procedure in SYS and granted execute to the Dev. I'll look at setting up a DBA account and grant ALTER SYSTEM directly to it from SYS and then re-create the proc there as I don't want any non "Oracle" objects in SYS. Thanks again for your help and links. Appreciate it!

Firefly.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391605 is a reply to message #391577]

Thu, 12 March 2009 12:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Created the procedure in SYS
NO, No, no!
Only use SYS for patching (upgrades).
NEVER EVER use SYS for anything other than Oracle maintenance!

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391607 is a reply to message #391604]

Thu, 12 March 2009 12:59

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition, why do you need to flush the buffer cache? I hope this is just a development or test database.

Regards
Michel

[Updated on: Thu, 12 March 2009 13:00]

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391727 is a reply to message #391577]

Fri, 13 March 2009 04:06

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

I agree re objects in SYS. Here's what I have done to get this working:

Dropped the procedure from SYS

Logged in as SYS and created a user with DBA privs (and ALTER SYSTEM). This user will not be known by the developer

Created the procedure in this DBA user

Granted execute on the procedure to the developer and it works

Does seem OK?

By the way, this is a dev environment (warehouse) where the developer wants to simulate the performace of SQL statements following the clearing out of the buffer cache following a server re-boot / instance crash etc

Thanks again for your valuable input. Learn something new...

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391729 is a reply to message #391727]

Fri, 13 March 2009 04:10

Frank
Messages: 7901
Registered: March 2000

Senior Member

The user does not need DBA privs if it has alter system.

Just out of curiosity: why are you interested in the performance of a system after a crash? That should be something that doesn't happen too often, and besides, there isn't an awful lot you can do to improve it. After all, all data has to be gotten through disk reads.

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391730 is a reply to message #391729]

Fri, 13 March 2009 04:13

firefly
Messages: 53
Registered: March 2009
Location: Europe

Member

The developer needs the timings for the SLA - slowest & normal timings. Thanks

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391818 is a reply to message #391730]

Fri, 13 March 2009 13:47

andrew again
Messages: 2577
Registered: March 2000

Senior Member

I've used a dbms_job to warm up the caches, to overcome slow response after a DB restart. It simply makes a few automated PL/SQL selects to mimic what a user does from the application. The objective is to get some data & SQL cached after a restart, before a user access the system. From what I recall, you need to change the tablespaces to read-only then back to normal to flush the buffer cache (flushing the shared pool isn't enough). This may be outdated approach now...

To me this automated warmup makes sense at it avoids the worst case scenario your developer is testing for SLA compliance...

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	Procedure needed without creating arrays
Next Topic:	Transaction log

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 30 22:37:21 CDT 2025