DBMS_SYSTEM.KCFRMS

I read about this in the book advertised at the end of this post. It was tested on Oracle 11.2. V$SESSION_EVENT holds similar information to V$SYSTEM_EVENT but it is broken down by session (only currently logged in sessions appear - there is no history). There is a MAX_WAIT column which shows the maximum time a session has had to wait for a particular event.There is no timestamp on this so you cannot tell (more...)

ORA-01951, ORA-01952 and ORA-01045

I saw a strange question on a forum and decided to reproduce it in an Oracle 12.1 database. First I created a user:
SQL> conn / as sysdba
Connected.
SQL> create user a identified by b
  2  /
 
User created.
 
SQL>
 
Then I found that the user could apparently login without the CREATE SESSION privilege:
 
SQL> conn a/b
Connected.
SQL>
 
After logging in, the user had a role (more...)

ORA-02205

I found some notes from a course I took in 1990. They said that it was only possible to GRANT ALTER or GRANT SELECT on a sequence. This seemed reasonable to me but I wanted to check if it was still the case. I did this test on Oracle 12.1. First I created a user who would own a sequence:

SQL> create user u1 identified by pw1
  2  /
 
User created.
 

How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>

Bug 8477973

I ran the following query on an Oracle 11.1.0.6 database but it failed with an ORA-02020:
 
SQL> SELECT ppc.sttlmnt_dt day_date
  2  FROM   vrm_d18_ppc   ppc,
  3         meter_nhh     mtr,
  4         mtd_registers reg
  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr
  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id
  7  AND    ppc.prfl_clss_id = mtr.profile_class_id
  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id
  9  AND    reg. (more...)

Worked Examples with the SET ROLE Command

Before I start, I wonder if anybody can help me. Some time ago, I saw several adverts on the Internet similar to the one below:

I am looking for an Oracle DBA (French speaking) - Hampshire (South Coast of England)

Languages: ENGLISH and FRENCH


We are recruiting for a major blue chip company based in the South of English (Hampshire area), where we seek a proven Oracle Database Administrator (HPUX, Linux, and AIX) who can (more...)

ORA-02391

I tested this on an Oracle 11.1 database.
 
Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
 
SQL> grant create session to andrew
  2  identified by reid
  3  /
 
Grant succeeded.
 
SQL> select profile (more...)

Bug 5497611

I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle 10.2.0.3 database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):


This produced the following ORA-00600 message several times in the alert log:
 
Wed Oct  1 18:13:21 2014
Errors in (more...)

DBMS_STATS Causes ORA-00600 [15851]

Uncategorized
| Apr 3, 2013
I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:
 
SQL> SELECT COUNT(*) FROM DBA_INDEXES