SELECTs Do Not Block UPDATEs

I went on my first DBA course in 1997 and the lecturer there explained that readers do not block writers in an Oracle database. I had an issue recently which appeared to contradict this so I have reproduced it below in an Oracle 11.2.0.1 database.
 
I noticed in OEM that there was some issue in the database. As usual, click on the images to enlarge them and bring them into focus (more...)

INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ (more...)

Clustering_Factor = 0

I looked at clustering_factor in the previous post and wondered what might cause it to be well below the number of blocks in the table. Chris Saxon suggested it might be due to rows with null values. I decided to look first at the two indexes with a clustering_factor of zero in the same Oracle 11.2 database and checked which columns they were on. Then I looked to see how many of the rows (more...)

Clustering_Factor

I attended the UKOUG conference early in December. While I was there, I went to a presentation by Chris Saxon, where he explained how Oracle decides when to use an index. The example below, which I ran in an Oracle 11.1 database, is based on what I learnt.
 
I used to believe that if a where clause only returned a small percentage of the rows in a table, Oracle would use an (more...)

ORA-28405

This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle 11.1.0.6 database. First I created a role which was identified by a password:
 
SQL> conn / as sysdba
Connected.
SQL> create role low identified by secret_password
  2  /
 
Role created.
 
SQL>
 
I granted the role to a user and made sure it had (more...)

Oracle Live SQL

I had the opportunity to attend the UKOUG Conference recently. While I was there, I went to a Roundtable run by Shakeeb Rahman from Oracle. He told us about an Oracle tool which allows you to run SQL online without installing Oracle yourself. All you need is an account on My Oracle Support. I logged onto it here. It’s immediately obvious how it works so, once I had connected, I decided to see which version (more...)

A Simple Example of an Index Organised Table Without Overflow

For a long time I have had a note on my task list to learn about index organized tables. I never got round to doing it because I thought I would never see one. However, I came across several in a 3rd party application recently. An index organized table is a kind of index and table combined. You can see how they work in the example below, which I tested in an Oracle 11. (more...)

A Problem with REVOKE

If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege. This isn’t too much of an issue.
 
However, if you grant the UNLIMITED TABLESPACE system privilege to a user by itself THEN grant it the DBA role, Oracle seems to have no idea where the UNLIMITED TABLESPACE system (more...)

Segment Creation Deferred not Available in Standard Edition

If you use Oracle Standard Edition to create a production database, you need to be sure to create any corresponding test databases in Oracle Standard Edition too. Otherwise you may find that some SQL might be tested successfully, only to fail when you implement it in production. You can see what I mean in the example below. First I created a table with segment creation deferred in the Oracle Enterprise Edition test database:
 
C:UsersAJ0294094>sqlplus (more...)

GRANT SELECT Updates LAST_DDL_TIME

DDL stands for Data Definition Language. The CREATE TABLE, ALTER TABLE and DROP TABLE statements are examples of DDL. LAST_DDL_TIME is a column in the USER_OBJECTS view. It records the date and time of the most recent DDL statement applied to the object in question. Even granting SELECT access on a table will update its LAST_DDL_TIME. You can see this in the example below, which I tested in an Oracle 11.2 database.
 

PRAGMA EXCEPTION_INIT

You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration. You can see what I mean in the example below, which I tested in an Oracle 11.2 database. First I set up a table so I could test the procedure I was going to create:
 
SQL> create table (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