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.

One Cause of ORA-01092

I saw the following error in a job which had just recreated the control file of a test database after cloning:

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   3
2   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   (more...)

Recursion in Oracle 12

Oracle 12 has a new parameter called PGA_AGGREGATE_LIMIT:

SQL> conn system/manager
Connected.
SQL> l
  1  select value from v$parameter
  2* where name = 'pga_aggregate_limit'
SQL> /
 
VALUE
--------------------
2147483648
 
SQL>

You cannot set it below 2 gigabytes:

SQL> alter system
  2  set pga_aggregate_limit = 1g;
alter system
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because
specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M

RECOVER DATABASE TEST

I logged into an Oracle 12.1 database and ran the ALTER DATABASE BEGIN BACKUP command. This told Oracle I was about to start a hot backup:

C:UsersAdministrator>sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 26 10:08:51 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the (more...)