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...)

SELECT ANY SEQUENCE

I used to think that a user with SELECT ANY TABLE and SELECT ANY DICTIONARY could see anything in a database. I found out today that these 2 privileges do not allow you to SELECT from another user’s sequence. You can see what I mean in the example below, which I tested in an Oracle 11.1 database. First I created a user to own a sequence:
 
SQL> conn / as sysdba
Connected.

UK OUG Conference 2015

I went to the UK OUG Conference for the first time around 12 years ago, in 2003 if I remember correctly. I enjoyed all the presentations but the two which stuck in my mind were by Jonathan Lewis and Connor McDonald.

This year I will be chairing 8 sessions there, which means I will be introducing the speaker and making sure nothing goes wrong.

Two of these sessions are on 6th December 2015, which is (more...)

AUTHID CURRENT_USER

If USERA creates a function or procedure and allows USERB to run it, USERB does so with USERA’s permissions. However, if USERA adds the AUTHID CURRENT_USER clause to the code, USERB runs it with its own permissions. You can see what I mean in the example below, which I tested in an Oracle 11.1 database:
 
I created a user called USERB and allowed it to login to the database:
 
SQL> create user (more...)

O7_DICTIONARY_ACCESSIBILITY

Long ago, in Oracle 7 I believe, a user with the SELECT ANY TABLE privilege could access tables and views owned by SYS. Also, a user with the EXECUTE ANY  PROCEDURE privilege could run code owned by SYS. Nowadays, this behaviour is controlled by the O7_DICTIONARY_ACCESSIBILITY initialisation parameter. The default value for this is FALSE, as you can see in the query below, which I ran in an Oracle 11.1 database:
 

db_writer_processes

I wrote the first part of this example in 2012.
 
The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:
 
SQL> l
  1  select value from v$parameter
  2* where name (more...)

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.
 
I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba
Connected.
SQL> create profile (more...)

ALTER USER Hangs on row cache lock

I created the following UNIX shell script called loop1.ksh:

Oracle 11.1 > cat loop1.ksh
#!/bin/bash
export ORACLE_SID=PQEDPT1
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba << eof
grant create session to andrew
identified by reid1
/
exit
eof
echo "User created"
./loop2.ksh > loop2.log1 &
./loop2.ksh > loop2.log2 &
./loop2.ksh > loop2.log3 &
./loop2.ksh > loop2.log4 &
./loop2.ksh > loop2.log5 &

ORA-01166

We have some jobs which copy the datafiles from 1 database to another then recreate the control file to give the output database a new name. Some of these jobs are intelligent i.e. they query the input database to dynamically create the SQL to do the rename. This particular job is not. Part of it is shown below and you can see that the MAXDATAFILES parameter was set to 120:

STARTUP NOMOUNT
CREATE CONTROLFILE (more...)

Setting NUMWIDTH in PL/SQL Developer

This post is based on a problem I was asked to look at recently.
 
A colleague was using PL/SQL Developer to compare two tables in an Oracle 11 database. He was matching the rows on a key column then checking the corresponding values from a different column and reporting them if they did not match. His query returned almost 2000 rows but they appeared to have equal, not different values. Five people looked at (more...)

ON COMMIT Materialized View Causes ORA-02050 and ORA-02051

This example replicates a problem I discovered today. I tested it in an Oracle 11.2 database. First I created an empty table called tab1:

SQL> create table tab1
  2  (col1 varchar2(30),
  3   constraint con1 primary key (col1))
  4  /
 
Table created.
 
SQL>

Then I set up a materialized view on the table. Note that it is refreshed on commit:

SQL> create materialized view log on tab1