DROP TABLE … PURGE

Database Management and Performance
| May 21, 2013
I tested this on Oracle 11.2.0.2.7. If the recyclebin is in use:

SQL> conn / as sysdba
Connected.
SQL> select value from v$parameter
  2  where name = 'recyclebin'
  3  /
 
VALUE
----------
on
 
SQL> 

... and you create a table in a (more...)

Recyclebin does not Keep Tables from the SYSTEM Tablespace

Database Management and Performance
| May 17, 2013
Oracle introduced the recyclebin in version 10. This post shows that tables in the SYSTEM tablespace do not go into the recyclebin when they are dropped. It was tested on Oracle 11.2.0.2.7. First I created a table in the USERS tablespace: 

SQL> conn / as (more...)

Oracle export and deferred segment creation

Database Management and Performance
| May 9, 2013
A colleague told me about an issue when using export on an Oracle 11.2.0.1 database. He said that if the database contains tables created with segment creation deferred, those tables will not be exported. I decided to check this out. First I created a couple of (more...)

Raw Columns

Database Management and Performance
| May 3, 2013

SQL Developer Performance Issue

Database Management and Performance
| Apr 24, 2013
This happened when I was using SQL Developer in an Oracle 11.1.0.6 database. I clicked on the + sign next to Tables (Filtered) in the top left hand corner of the screen below (as usual, click on the image if necessary to enlarge it and bring it (more...)

LF_ROWS and DEL_LF_ROWS

Database Management and Performance
| Apr 19, 2013
This example was tested on Oracle 9.2.0.7. It shows how you can sometimes improve performance by rebuilding an index. I found a table:
 
SQL> desc braid.b_old_sp_profile_image
Name                       Null?    Type
-------------------------- -------- ------------------
SESSIONID                  NOT NULL NUMBER
SPLYPTID                   NOT NULL VARCHAR2(10)
START_DATE                 NOT NULL DATE
(more...)

What Can You Do With GRANT ALL ON?

Database Management and Performance
| Apr 18, 2013
I saw somebody using the GRANT ALL ON command recently and decided to see what I could do with it in an Oracle 11.2.0.2.7 database. First I logged in as OPS$ORACLE:
 
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL>
 
Then (more...)

ORA-02019

Database Management and Performance
| Apr 17, 2013
I had not seen this problem for a long time so, when it happened yesterday, it took me a while to see the cause. I therefore decided to record it for future reference. First I created a database link:  
 
ORCL /export/home/oracle/andrew > sqlplus /
 
SQL*Plus: Release 11. (more...)

ORA-01555 (SET TRANSACTION READ ONLY – Part 6)

Database Management and Performance
| Apr 10, 2013
In What it Does (SET TRANSACTION READ ONLY – Part 4), I explained that if you are in a read only transaction, select statements return data as it was when you ran the set transaction read only statement. Oracle uses before images in the undo tablespace to do this. (more...)

ORA-08176 (SET TRANSACTION READ ONLY – Part 5)

Database Management and Performance
| Apr 9, 2013
According to Jonathan Lewis, if you do a SET TRANSACTION READ ONLY, rebuild an index then use that rebuilt index in the READ ONLY session, you get an ORA-08176. I decided to check this out in an Oracle 11.2.0.2.7 database. First I created a (more...)

What it Does (SET TRANSACTION READ ONLY – Part 4)

Database Management and Performance
| Apr 5, 2013
This was tested on Oracle 11.2. So far, I have not explained what SET TRANSACTION READ ONLY does. To illustrate this, I first need to show what happens normally, i.e. when you are not in a READ ONLY transaction. I created a table and added a row to (more...)

ORA-01456 (SET TRANSACTION READ ONLY – Part 3)

Database Management and Performance
| Apr 4, 2013
This was tested on an Oracle 11.2 database. If you try to INSERT, UPDATE or DELETE rows in a table during a READ ONLY transaction, you get an ORA-01456:
 
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 (more...)

ORA-01453 (SET TRANSACTION READ ONLY – Part 2)

Database Management and Performance
| Apr 3, 2013
Continuing with my investigation of SET TRANSACTION READ ONLY on an Oracle 11.2 database, I noticed that it fails with an ORA-01453 if you try to run it from a session with pending transactions. Once you have used COMMIT (or ROLLBACK) to deal with the pending transactions, the (more...)

ORA-01466 (SET TRANSACTION READ ONLY – Part 1)

Database Management and Performance
| Apr 3, 2013
Going through some SQL*Plus course notes from 1990 (as you do) I came across the SET TRANSACTION READ ONLY statement and decided to try it out in an Oracle 11.2 database. It didn’t quite work as expected:
 
SQL> create table tab1 (col1 number)
  2  /
 
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

DBMS_SESSION.SET_SQL_TRACE

Database Management and Performance
| Mar 28, 2013
This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;
 
Session altered.
 
SQL> alter session set sql_trace = false;
 
Session altered.

V$SQL_BIND_CAPTURE

Database Management and Performance
| Mar 27, 2013
This simple example, tested on Oracle 11.2, shows how to use V$SQL_BIND_CAPTURE to see the value of bind variables used in a WHERE clause. First I created a table called TAB1 with 1 row of data: 

SQL> create table tab1 (col1 varchar2(10))
  2  /
 
Table created.
(more...)

V$RESERVED_WORDS

Database Management and Performance
| Mar 26, 2013
This was run on an Oracle 11.1 database. The v$reserved_words view lists Oracle's reserved words:

SQL> desc v$reserved_words
Name                       Null?    Type
-------------------------- -------- ------------------
KEYWORD                             VARCHAR2(30)
LENGTH                              NUMBER
RESERVED                            VARCHAR2(1)
RES_TYPE                            VARCHAR2(1)
RES_ATTR                            VARCHAR2(1)
RES_SEMI                            VARCHAR2(1)
DUPLICATE                           VARCHAR2(1)
 
SQL>

It has 1733 rows:

SQL> select count(*) from (more...)

ORA-04021

Database Management and Performance
| Mar 13, 2013
A colleague had another problem with a package compilation hanging in an Oracle 11.1.0.6.0 test database. I was able to reproduce it as follows: 

SQL> alter package srce.pk_pricing compile
  2  /
alter package srce.pk_pricing compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
 
SQL>
 
There were locks on this package according to V$DB_OBJECT_CACHE but this time, flushing the shared pool made no difference and I found that I still could not compile the package:
 
SQL> l
  1  select type, locks
  2  from (more...)

V$DB_OBJECT_CACHE

Database Management and Performance
| Mar 7, 2013
This happened on Oracle 11.1.0.6.0. A colleague was debugging a package in a test database and his debug session fell over. Subsequent attempts to compile the package timed out as shown below:
 
SQL> alter package srce.pk_deemed_offer compile;
alter package srce.pk_deemed_offer compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
 
SQL>
 
The DBA_BLOCKERS view produced no output while this request was waiting:
 
SQL> select * from dba_blockers;
 
no rows selected
 
SQL>
 
… and there was nothing in V$LOCK either:
 
SQL> select * (more...)