Datapump Does not Export Permissions on Objects Owned by SYS

I was reminded recently that Datapump does not export permissions on objects owned by SYS so I decided to write a post about it for my blog. It was tested on an Oracle database. First I created a user called USER1:

SQL> conn / as sysdba
SQL> create user user1
  2  identified by user1
  3  /

User created.

SQL> grant create session to user1
  2  /


Deferred Segment Creation not Supported for Partitioned Tables in Oracle

I tried to create a partitioned table with deferred segment creation in an Oracle database.

First I tried to do so explicitly but this did not work:

SQL> create table partitioned_table
  2  (refno number)
  3  segment creation deferred
  4  partition by range (refno)
  5  (partition partition1 values less than (10)
  6   tablespace users,
  7   partition partition2 (more...)

How to Automatically Trace a User’s Sessions

This post shows how you can use a logon trigger to automatically trace all sessions for a given user. This can be useful where an application is launched from a desktop but fails before the DBA has had time to identify the SID and SERIAL# to start tracing the session. I tested it in an Oracle database running on Windows 8.

First I created a database user:

SQL> create user ford

SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle database. To find out, I created a table:

SQL> conn andrew/reid
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  (more...)

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;



Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test (more...)

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle to Oracle Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release Production on Mon Jul 18 14:03:08 2016



I read about this parameter, which is supposed to force a redo log switch after a certain number of seconds. I decided to try it out in an Oracle database. This is the record from the alert log showing me setting the parameter:

Wed May 25 18:00:39 2016
ALTER SYSTEM SET archive_lag_target=1800 SCOPE=BOTH;
Wed May 25 18:00:39 2016

I expected this to force a redo log switch every 30 minutes but (more...)


I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle database. I mounted the database and tried to take the USERS tablespace offline. Oracle returned an ORA-01109. I opened the database then I was able to take the tablespace offline:
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes

How to See When an Oracle Role Was Created

You can get the date and time a role was created from the CTIME column in SYS.USER$. You can see what I mean in the example below, which I tested in an Oracle database. First I created a role between DATE_AND_TIME1 and DATE_AND_TIME2. As they were the same, to the nearest second, the role must have been created at 16:11:05 on 27th May 2016.

I then waited 5 seconds and (more...)

%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba
SQL> create user user1
  2  identified (more...)

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)
SQL> variable bv2 number
SQL> begin
  2  select 'ABC' into :bv1 from dual;
  3  select 123 into :bv2 from dual;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print bv1


SQL> print bv2


RMAN Backup With Compression

I repeated the test from the previous post but this time I tried to compress the backup. At this stage I need your help. I believe I have used the free compression which does not require an extra licence. If somebody who knows more about this than me could add a comment below, telling me if I am right or wrong, that would be very helpful. I backed up the database as before:

C:UsersAJ0294094>rman nocatalog (more...)

RMAN Backup Without Compression

I wanted to test the effect of RMAN backup compression on an Oracle database running on Windows. I configured the flash recovery area, closed the database and mounted it. Then I did a backup without compression:

C:UsersAJ0294094>rman nocatalog target=andrew/reid

Recovery Manager: Release - Production on Fri Mar 11 18:42:42 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: (more...)

Creating Tables in the UNDO Tablespace??

I was reading an article written by Martin Widlake in Oracle Scene Issue 58 (Autumn/Winter 2015). It said:

The second new item is the UNDO tablespace. This is a special tablespace that is only used for internal purposes and one that users cannot put any tables or indexes into.

This seemed perfectly reasonable so I wondered what might happen if I tried to do it. In an Oracle database Oracle returned (more...)


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


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


This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle database. First I created a role which was identified by a password:
SQL> conn / as sysdba
SQL> create role low identified by secret_password
  2  /
Role created.
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...)