troubleshooting Question

how to create procedure to delete rows from STATS$SNAPSHOT in Oracle

Avatar of delmandiyar
delmandiyar asked on
Oracle Database
5 Comments2 Solutions666 ViewsLast Modified:
Hi Experts,

I'm about to create a simple stored procedure in oracle that should delete data in the table called STATS$SNAPSHOT where snap_time is older than x days.

when i run this query:
delete from STATS$SNAPSHOT where snap_time < (sysdate - 2);
 it works fine, but when I run my procedure (EXECUTE DelStatspackData(2);) then i get some error:
Error starting at line 2 in command:
EXECUTE DelStatspackData(1);
Error report:
ORA-06550: line 1, column 7:
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Could you please help me with that?

create or replace PROCEDURE DelStatspackData (i IN NUMBER) IS


  delete from STATS$SNAPSHOT where snap_time < (sysdate - i);

end DelStatspackData;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros