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

A Simple Example With V$BACKUP

I tested this in an Oracle 12.1 database. The V$BACKUP view tells you if a datafile is in hot backup mode. I started off with none of the datafiles in hot backup mode so they were all shown as NOT ACTIVE:

SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
    (more...)

Re-Creating Datafiles When Backups Are Unavailable

I found an old copy of the Oracle 9i User-Managed Backup and Recovery Guide and read the following:
 
If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:
 
·         All archived log files written after the creation of the original datafile are available
·         The control file contains the name of the damaged file (that is, the control file is (more...)

ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP

I logged into an Oracle 12.1 database, checked it was in ARCHIVELOG mode then ran the ALTER DATABASE BEGIN BACKUP command. This told Oracle I was about to do a hot backup:

C:UsersAndrew>sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 18 14:45:56 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1. (more...)

ORA-28221

This post shows that if a profile has a password verify function and you assign it to a user who does not have the ALTER USER privilege, that user will not be able to change his or her password without using the REPLACE option. You can see what I mean in the example below, which I tested in Oracle 11.1. First I created a password verify function:

SQL> conn / as sysdba
Connected.

What is an INDEX SKIP SCAN?

I tested this on Oracle 11.2. First I created a table with two columns. The first column, OWNER, had low cardinality i.e. it had only two possible values, PUBLIC and SYS. The second column, OBJECT_NAME, had the names of all objects owned by PUBLIC and/or SYS so it had high cardinality i.e. it had many different values:

SQL> conn system/manager
Connected.
SQL> create table t1 as
  2  select owner, (more...)

Using NOT = in a WHERE Clause

I ran this test in Oracle 12.1. First I created a table, added some data to it and made sure that the bytes column was set to zero in every row:
 
SQL> conn system/manager
Connected.
SQL> alter session set optimizer_mode = first_rows
  2  /
 
Session altered.
 
SQL> create table t1
  2  tablespace users
  3  as select segment_name, bytes
  4  from dba_segments
  5  /
 
Table created.

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify a pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40 




SQL>

When Oracle inserts rows into (more...)

FULL and NO_INDEX Hints

I was reading about hints and decided to try out a couple on an Oracle 11.2 database. First I created a table, added some data and created an index:

SQL> create table t1 (c1 varchar2(30))
  2  /
 
Table created.
 
SQL> insert into t1 select table_name from dba_tables
  2  /
 
3159 rows created.
 
SQL> create index i1 on t1(c1)
  2  /
 
Index created.
 
SQL>

I ran (more...)