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

Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1
  2  (c1 char,
  3   c2 char(1))
  4  /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  CHAR(1)
C2                                  CHAR(1)
 
SQL> 

However, if you rely on defaults like (more...)

VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l
  1  create table t1
  2  (c1 varchar(1),
  3*  c2 varchar2(1))
SQL> /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  VARCHAR2(1)
C2                                  VARCHAR2(1)
 
SQL>

According to a book I am working through, this has been the case since Oracle 8. However, (more...)

Oracle "read by other session" Wait Event

When a session needs to read data from disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba
Connected.

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used (more...)

ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

This example is based on an error which a colleague showed me recently. I logged into an Oracle 11.1 database, reset its SYSTEM password and checked the encrypted value: 

Oracle11 > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 4 18:10:38 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1. (more...)

ORA-01114 Not Recorded Correctly in Alert Log

A user reported an ORA-01114 and an ORA-27069 in a 3rd party application running against an Oracle 11.1 database:

ERROR 28/01/2015 10:32:37  INF2 LKPDP_10:READER_1_1  RR_4035    SQL Error [
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 524367
Additional information: 7
Additional information: 524289
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to (more...)