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

ORA-28010

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:

SQL> SELECT VERSION FROM PRODUCT_COMPONENT_VERSION
  2  WHERE PRODUCT LIKE 'Oracle Database%'
  3  /

VERSION
--------------------
10.2.0.3.0

SQL>


I noted the time and created a user:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME1 FROM DUAL
  3  /

DATE_AND_TIME1
(more...)

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
#!/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 &

LOCKED and LOCKED(TIMED)

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

Then I created (more...)

PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

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
Connected.
 
SQL> create profile for_andrew
  2  limit password_reuse_max 1
  3  /
 
Profile created.
 
SQL>
 
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 11.2.0.4 database.

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

PROFILE
------------------------------
DEFAULT

SQL> declare
(more...)

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
begin
null;
end;
/
prompt Creating proc2:
create or replace procedure proc2 is
begin
null;
end;
/
spool off

I started a SQL Developer session (version 4.2.0.17.089) 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 11.2.0.1 database. First I created a user called USER1:

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

User created.

SQL> grant create session to user1
  2  /

(more...)

Deferred Segment Creation not Supported for Partitioned Tables in Oracle 11.2.0.1

I tried to create a partitioned table with deferred segment creation in an Oracle 11.2.0.1 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 11.1.0.6 database running on Windows 8.

First I created a database user:


SQL> create user ford
(more...)

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 11.2.0.1 database. To find out, I created a table:

SQL> conn andrew/reid
Connected.
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 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>


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 11.2.0.4 to Oracle 12.1.0.2. 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 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

(more...)

ARCHIVE_LAG_TARGET on Oracle 11.2.0.4

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

ORA-01109

I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle 11.2.0.4 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
(more...)

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 11.2.0.4 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
Connected.
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

BV1
--------------------------------
ABC

SQL> print bv2

       (more...)