Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error while executing the procedure :

3324453Feb 7 2017 — edited Feb 7 2017

When I execute the below procedure I am getting the below mentioned error

SQL> CREATE PROCEDURE sql_tuning_demo (

  2  hint_in IN VARCHAR2 DEFAULT NULL

  3   ) AS

  4 

  5  v_task VARCHAR2(30);

  6  v_sql  CLOB;

  7 

  8  BEGIN

  9 

10   /* Assign our de-dupe statement... */

11  v_sql := ' DELETE /*+ ' || hint_in || ' */ FROM t1 a

12 WHERE a.ROWID > ( SELECT min( b.ROWID )

13 FROM t1 b

14 WHERE a.owner = b.owner

15 AND a.name = b.name

16 AND a.type = b.type

17 AND a.line = b.line )';

18 

19   /* Drop the task in case we are re-running... */

20   BEGIN

21   DBMS_SQLTUNE.DROP_TUNING_TASK(

22  task_name => 'sql_tuning_task'

23   );

24   EXCEPTION

25   WHEN OTHERS THEN -- ORA-13605

26   NULL;

27   END;

28 

29   /* Create a SQL Tuning task for our SQL... */

30  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

31  sql_text  => v_sql,

32  time_limit  => 1,

33   scope   => 'COMPREHENSIVE',

34  task_name  => 'sql_tuning_task',

35  description => 'Demo of DBMS_SQLTUNE'

36   );

37 

38   /* Execute the task... */

39   DBMS_SQLTUNE.EXECUTE_TUNING_TASK(

40  task_name => 'sql_tuning_task'

41   );

42 

43   /* We want to run this again... */

44   ROLLBACK;

45 

46  END sql_tuning_demo;

47  /

Error details :

ERROR at line 1:

ORA-13616: The current user scott has not been granted the ADVISOR privilege.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_ADVISOR", line 941

ORA-06512: at "SYS.DBMS_SQLTUNE", line 622

ORA-06512: at "scott.SQL_TUNING_DEMO", line 95

ORA-06512: at line 1

Please help me on this .

Thanks in advance ............

This post has been answered by mathguy on Feb 7 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 7 2017
Added on Feb 7 2017
12 comments
1,157 views