A Different Cause for enq: TM – contention

Doc ID 1905174.1 on My Oracle Support looks at how to resolve this wait event.
It says:
If contention is occurring, then the most common reason is missing Foreign Key (FK) index on the FK constraint columns in the Child tables.
In my experience until very recently, this has always been true. However, I have now found another possible cause, which I have recreated below in an Oracle 11.2.0. (more...)


I wondered what would happen if you tried to do a password expire on an externally identified user so I tried it out on an Oracle 11.2 database. As you might expect, it failed:
SQL> alter user system identified externally
  2  /
User altered.
SQL> alter user system password expire
  2  /
alter user system password expire
ERROR at line 1:
ORA-28010: cannot expire external or global accounts

Password Last Change Time

I read that the PTIME column in the SYS.USER$ table shows when a user’s password was last changed so I decided to try it out in an Oracle 10 database:

  2  WHERE PRODUCT LIKE 'Oracle Database%'
  3  /



I noted the time and created a user:

  3  /


Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password (more...)

ALTER USER Hangs on row cache lock

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

Oracle 11.1 > cat loop1.ksh
. oraenv
sqlplus / as sysdba << eof
grant create session to andrew
identified by reid1
echo "User created"
./loop2.ksh > loop2.log1 &
./loop2.ksh > loop2.log2 &
./loop2.ksh > loop2.log3 &
./loop2.ksh > loop2.log4 &
./loop2.ksh > loop2.log5 &


This example, which was tested on Oracle 11.2, shows the difference between a user with an ACCOUNT_STATUS of LOCKED and one with an ACCOUNT_STATUS of LOCKED(TIMED). First I created a profile with a PASSWORD_LOCK_TIME of 0.0007 days i.e roughly 1 minute and a FAILED_LOGIN_ATTEMPTS limit of 1:

SQL> create profile for_andrew
  2  limit failed_login_attempts 1
  3  password_lock_time 0.0007
  4  /
Profile created.

Then I created (more...)


I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:
SQL> conn / as sysdba
SQL> create profile for_andrew
  2  limit password_reuse_max 1
  3  /
Profile created.
I created a user called ANDREW and gave him the FOR_ANDREW profile:

How Many Profiles Can an Oracle Database Have?

When you create a database, Oracle gives you one profile, called DEFAULT. You can then create more profiles if you need them. I wondered if there might be a limit to the number of profiles you could create.  I wrote some SQL to create 100,000 profiles and ran it in an Oracle database.

SQL> conn system/manager
SQL> select distinct profile from dba_profiles
  2  /


SQL> declare

SQL Developer Stops Running in Oracle 9

I created the following simple SQL script:

spool test_output
prompt Creating proc1:
create or replace procedure proc1 is
prompt Creating proc2:
create or replace procedure proc2 is
spool off

I started a SQL Developer session (version and ran the script in an Oracle 10 database (as usual, click on the image to enlarge it/bring it into focus):

It worked as expected (more...)

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