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

PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

ORA-12801 in utlrp

I tried to run a utlrp in an Oracle 11.1.0.6.0 database and saw the errors below:

ORA-12801: error signaled in parallel query server P035
ORA-00018: maximum number of sessions exceeded
ORA-06512: at "SYS.UTL_RECOMP", line 629
ORA-06512: at line 4

I checked the number of sessions in v$session but there were not very many:

SQL> select count(*) from v$session;
 
  COUNT(*)
----------
        32
 
SQL>

I checked the value (more...)

ORA-39001, ORA-39000 and ORA-39142

I exported 3 large tables from an Oracle 11.2.0.4.0 database then tried to import them into an Oracle 11.1.0.6.0 database but the impdp failed as follows:

PQECOG3 /database/DB_exports/andrew > impdp / parfile=params
 
Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 22 July, 2015 11:37:44
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition (more...)

Virtual Columns

Oracle 11g allows you to create virtual columns in a table. Values are not stored for these columns, Oracle calculates them at runtime. You can see the expression used to generate the value in the DATA_DEFAULT column of the USER_TAB_COLUMNS view.

As you might expect, you cannot UPDATE virtual columns directly. If you try, you get an ORA-54017.
 
It isn’t quite so obvious but you cannot use a virtual column to generate the value (more...)

A Simple Example Using COMPUTE SUM OF in SQL*Plus

I needed some SQL to show the time spent on idle events in an Oracle 11.2 database with a grand total at the end. I wrote this as shown below. The SQL*Plus syntax at the start is taken from the Oracle documentation but I wanted to record it so I would have my own worked example for future use: 

SQL> column dummy noprint;
SQL> compute sum of seconds_waited on dummy;
SQL> break on (more...)

INSERT /*+ APPEND */

This example demonstrates that an INSERT /*+ APPEND */ does not put rows into free space within a table, it adds them at the end instead. It also shows that Oracle inserts these rows using a DIRECT PATH mechanism. I tested it on Oracle 11.2. First I created an empty table:

SQL> conn /
Connected.
SQL> create table tab1
  2  as select * from dba_tables
  3  where 1 = 2
  4  (more...)

ORA-12838

If you do an INSERT /* APPEND */, you cannot query the table afterwards until you have done a COMMIT. If you try to do so, Oracle gives you an ORA-12838. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table tab1
  2  as select * from dba_tables
  3  where 1 = 2
  4  /
 
Table created.
 
SQL> insert (more...)

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA Hangs

This post demonstrates that if a database has an unfinished transaction and you try to run the above-mentioned command, it will hang. I logged into an Oracle 11 database, started a transaction but did not COMMIT it in the red session below:

PQEDPT1 /export/home/oracle > sqlplus /
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 

Oracle Pipes (Part 1)

Oracle pipes use the DBMS_PIPE package to allow one session to communicate with another. I decided to try this out in an Oracle 11.1 database. Pipes can be public or private. This example only looks at public pipes. First I created a user called ANDREW. I gave it SELECT ANY DICTIONARY so that it could look at V$DB_PIPES and I allowed it to execute DBMS_PIPE so that it would be able to create a (more...)

PL/SQL lock timer

I was looking through V$SYSTEM_EVENT on an Oracle 11.2 production database (as you do) and I noticed that it had waited for exactly 1 second on the PL/SQL lock timer event. Apparently this is the amount of time a database has been waiting for sessions which have been told to sleep by their application. I decided to check this out so I started a new session, slept for 6.7 seconds then looked to (more...)

ORA-39095

I kicked off an expdp with the following parameters. I set the filesize parameter but forgot to set the dumpfile parameter to match:

content=all
directory=data_pump_dir
dumpfile=andrew.dmp
exclude=table:"='DOCUMENTIMAGE'"
filesize=1000000000
full=y          
logfile=andrew.log

The job produced 1 file with the filesize I had specified:

D:oracle11.2.0adminAKTPRODdpdump>dir andrew.dmp
Volume in drive D is Datas
Volume Serial Number is ECE8-9011
 
Directory of D:oracle11.2.0adminAKTPRODdpdump
 
05/15/2015  12:20 PM       999,997,440 ANDREW.DMP
               1 File(s)    (more...)

ORA-01075

I tried to log on to an Oracle 11.2 test database, which the developers could no longer use, and saw the following error:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:41:24 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
ERROR:
ORA-01075: you are currently logged on
 
Enter user-name:

I looked in the alert log and saw (more...)

The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /
 
Table created.
 
SQL>

Then at some point in the future, you add a NOT NULL constraint like this:

SQL> alter table andrew modify (col1 not null)
  2  /
 
Table altered.
 
SQL>

Doing it (more...)

A Simple Introduction to ADDM and AWR

ADDM was introduced in Oracle 10 and stands for Automatic Database Diagnostic Monitor. You can use it to help solve performance problems. I was looking through some old copies of Oracle Magazine, found an article about it, written by Kimberly Floss in May/June 2004, and decided to give it a try. The article provided some SQL to produce an ADDM report so I ran it against an Oracle 12 database which nobody was using:

Overloading

I was looking at a 3rd party application running in an Oracle 11.2 database. I listed the source code of one of the packages and noticed that it contained 5 versions of the same procedure. I have never done any Oracle development so I am not a PL/SQL expert and this seemed strange to me at first. Then I thought about what I have been learning about Java recently and realized that this (more...)

ORA-00604 and ORA-00001

This happened in an Oracle 11.1.0.6.0 database:
 
A developer reported problems when running a CREATE OR REPLACE TYPE statement in a development database. It was failing with an ORA-00604 followed by an ORA-00001. These messages could be seen again and again in the alert log:

ORA-00604: error occurred at recursive SQL level 3
ORA-00001: unique constraint (SYS.I_OBJ1) violated

I could see no reason for this so I did some (more...)

COMMIT Causes ORA-03113

A colleague had a problem with an Oracle 11.2.0.1 database today. It affected the following table:

SQL> desc bepe.sd_national_holiday
Name                       Null?    Type
-------------------------- -------- ------------------
SD_NATIONAL_HOLIDAY_ID     NOT NULL NUMBER(15)
DAY_DATE                            DATE
DESCRIPTION                         VARCHAR2(500)
CREATED_BY                 NOT NULL VARCHAR2(100)
CREATION_DATE              NOT NULL TIMESTAMP(6)
ACTIVITY_BY                NOT NULL VARCHAR2(100)
ACTIVITY_DATE              NOT NULL TIMESTAMP(6)
TCN                        NOT NULL NUMBER
 
SQL>

The table was empty:

SQL> select count(*) from bepe.sd_national_holiday
  2  /
 

statement suspended, wait error to be cleared

This happened in an Oracle 11.2.0.4 database. A colleague complained of poor performance. I looked to see what his session was waiting for:

SQL> l
  1  select event
  2  from v$session_wait
  3  where sid = 226
  4* and state = 'WAITING'
SQL> /
 
EVENT
----------------------------------------------------------------
statement suspended, wait error to be cleared
 
SQL>

I looked in the alert log and saw the following error:

statement in (more...)

enq: TM – contention

This example was tested in an Oracle 11.1 database. I created a DEPT (or parent) table, added a couple of departments then made it available to other database users:

SQL> conn andrew/reid
Connected.
SQL> create table dept
  2  (dept_code varchar2(2),
  3   dept_desc varchar2(10))
  4  /
 
Table created.
 
SQL> insert into dept
  2  (dept_code, dept_desc)
  3  values
  4  ('10','IT')
  5  /
 
1 row created.