ORA-01031: insufficient privileges by EXECUTE IMMEDIATE

“ORA-01031: insufficient privileges” was received from a PL/SQL procedure “EXECUTE IMMEDIATE” state.

1)Create a simple PL/SQL procedure.

SQL> create or replace procedure proc_test
is
begin
          EXECUTE IMMEDIATE 'create table tbl_test ( id number)';
end;

2) logon as user ,and then execute above procedure.

SQL> exec proc_test;
BEGIN proc_test; END;

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

3) grant ‘create table’ privilege to user directly.

SQL> show user
USER is "SYS"
SQL> grant create table to test_user;

Grant succeeded.

4)logon as user ,and then execute above procedure.

SQL> show user
USER is "TEST_USER"

SQL> exec proc_test;

PL/SQL procedure successfully completed.

SQL> desc tbl_test;
Name   Null?   Type
----- -------- ----------------------------
ID             NUMBER

SQL> drop table tbl_test;

REASON: In stored procedure/function, any privileges granted by a role will NOT be effective. The privileges should be directly granted to the user.

How to Find Interval Partition Name by Date Column Value

Since HIGH_VALUE in DBA_TAB_PARTITION view is LONG type, so it is difficult to get the system generated interval partition name straight away. The following will lead you how to get it easily.

SQL> desc dba_tab_partitions
Name              Null?    Type
----------------- -------- ------------
TABLE_OWNER                VARCHAR2(30)
TABLE_NAME                 VARCHAR2(30)
COMPOSITE                  VARCHAR2(3)
PARTITION_NAME             VARCHAR2(30)
SUBPARTITION_COUNT         NUMBER
HIGH_VALUE                 LONG
...
.

Subscribe to get access

Read more of this content when you subscribe today.

enq: TM – contention

It is common for a DBA to see “enq: TM – contention” wait events. We are seeing this wait events from DMLs against a parent table ( teachers ).

SQL> insert into teachers values (3, 'Teacher 3');

Subscribe to get access

Read more of this content when you subscribe today.

Index on Timestamp Column in Oracle Database

SYMPTOM

Index on timestamp is not used when compared with systimestamp value.

SQL> desc tets_tbl
Name         Null?    Type
------------ -------- ----------------------
...
..
EXPIRY_TIME          TIMESTAMP(6)
...
..

Create an index on expiry_time column, and see the index is not used.

SQL>CREATE INDEX TEST_TBL_IDX1 ON TEST_TBL (EXPIRY_TIME);
SQL>SET AUTOT trace exp;
SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 692824678
---------------------------------------------------------------------
| Id  | Operation         | Name   | Rows|Bytes |Cost(%CPU)|Time
---------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 8147| 1630K| 1347  (1)|00:00:17
|*  1 |  TABLE ACCESS FULL|TEST_TBL| 8147| 1630K| 1347  (1)|00:00:17 
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

CAUSES

TIMESTAMP datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has contains fractional seconds but does NOT have a time zone.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

SOLUTION

1) Create a function index:

SQL> create index test_tbl_idx2_fn on test_tbl( sys_extract_utc(expiry_time));

Index created.

SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3272729254
--------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 |SELECT STATEMENT            |                    |  8147 |  1630K|  1224   (0)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TBL         |  8147 |  1630K|  1224   (0)| 00:00:15 |
|*  2 |   INDEX RANGE SCAN          | TEST_TBL_IDX2_FN |  1467 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

2) Define the column as “TIMESTAMP WITH TIME ZONE” instead of “TIMESTAMP”, then a function index is created automatically for this column.

3) Rewrite query by using sysdate instead of systimestamp:

SQL> select * from test_tbl where EXPIRY_TIME<sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash value: 205107284

-------------------------------------------------------------------------
| Id | Operation                  | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 1    | 205   | 4 (0)      | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL      | 1    | 205   | 4 (0)      | 00:00:01 |
|* 2 | INDEX RANGE SCAN           | TEST_TBL_IDX1 | 1    |       | 3 (0)      | 00:00:01 |
--------------------------------------------------------------------------

Too many AQ$_PLSQL_NTFN_XXXXX jobs In Oracle Database

SYMPTOM

There are more than one million AQ$_PLSQL_NTFN_xxxxxxxx scheduler jobs in oracle database.

sql hangs for querying dba_scheduler_jobs.

Please note there are a couple of bugs on this issue, the steps are not necessarily working for every database versions.

SQL>select count(*) from dba_scheduler_jobs;

WORKAROUND

1)set job_queue_processes =0

SQL>alter system set job_queue_processes=0 scope=memory;

2)Stop and Drop all those AQ$_PLSQL_NTFN scheduler jobs:

— add rownum<100000 into below sqls, if the sql hangs.

SQL>select 'EXEC DBMS_SCHEDULER.STOP_JOB ('''|| job_name ||''', force => TRUE);' 
    from dba_scheduler_jobs 
    where job_name like 'AQ$_PLSQL_NTFN_%'
    and state='RUNNING';


SQL> select ' exec dbms_scheduler.drop_job('''||job_name ||''');' 
     from dba_scheduler_jobs 
     where job_name like 'AQ$_PLSQL_NTFN_%';

SQL>select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
    where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

DECLARE 
po dbms_aqadm.aq$_purge_options_t; 
BEGIN 
po.block := TRUE; 
DBMS_AQADM.PURGE_QUEUE_TABLE( 
queue_table => 'SYS.AQ_SRVNTFN_TABLE_1', 
purge_condition => 'qtview.queue = ''AQ$_AQ_SRVNTFN_TABLE_1_E''', 
purge_options => po); 
END; 
/

select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

4) Collect stats.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

5) Rebuild indexes of DBA_SCHEDULER_JOBS

6) Bounce the instance.

SQL>shutdown immediate ;
SQL>startup;
SQL>alter system set job_queue_processes=1000 scope=memory;