I was asked to run some SQL today and it generated an ORA-01440. I did not remember having seen this error before so I decided to check it out in an Oracle 11.2 database. First I created a table and added a row of data to it:

SQL> create table tab1(col1 number)
  2  /
Table created.
SQL> insert into tab1 values(1)
 2  /
1 row created.

Then I (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...)

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 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;

I checked the value (more...)

ORA-39001, ORA-39000 and ORA-39142

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

PQECOG3 /database/DB_exports/andrew > impdp / parfile=params
Import: Release - 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...)

DBMS_STATS Causes ORA-00600 [15851]

| Apr 3, 2013
I noticed this in an Oracle database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index: