LOCKED and LOCKED(TIMED)

This example, which was tested on Oracle 11.2, shows the difference between a user with an ACCOUNT_STATUS of LOCKED and one with an ACCOUNT_STATUS of LOCKED(TIMED). First I created a profile with a PASSWORD_LOCK_TIME of 0.0007 days i.e. roughly 1 minute and a FAILED_LOGIN_ATTEMPTS limit of 1:

SQL> create profile for_andrew

  2  limit failed_login_attempts 1

  3  password_lock_time 0.0007

  4  /

 

Profile created.

 

SQL>

Then I created a user and gave it this profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

I checked that the user had an ACCOUNT_STATUS of OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Then I locked the user. This is how you get an ACCOUNT_STATUS of LOCKED:

SQL> alter user andrew account lock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED

 

SQL>

If you try to login to a LOCKED user, you get an ORA-28000: 

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Then I unlocked the user to give it an ACCOUNT_STATUS of OPEN again: 

SQL> alter user andrew account unlock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Next, I tried to login with an incorrect password:

SQL> conn andrew/blah

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

This changed the ACCOUNT_STATUS to LOCKED(TIMED) as the user's profile only allows one 1 FAILED_LOGIN_ATTEMPT: 

SQL> conn / as sysdba

Connected.

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED(TIMED)

 

SQL>

... so even when I tried to login with the correct password, I got an ORA-28000 again:

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Once the user has an ACCOUNT_STATUS of LOCKED(TIMED), he cannot connect to the database for PASSWORD_LOCK_TIME days (around one minute in this case). I checked the current time:

SQL> conn / as sysdba

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:02:02

 

SQL>

... then I waited until the user's ACCOUNT_STATUS was OPEN again:

SQL> declare

  2    andrews_account_status

  3      dba_users.account_status%type := 'BLAH';

  4  begin

  5    while andrews_account_status != 'OPEN' loop

  6      dbms_lock.sleep(10);

  7      select account_status into andrews_account_status

  8        from dba_users

  9        where username = 'ANDREW';

 10    end loop;

 11  end;

 12  /

 

PL/SQL procedure successfully completed.

 

SQL>

I checked the time again to see how long it had taken. This may be longer than the value specified by PASSWORD_LOCK_TIME:

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:03:03

 

SQL>

I verified that the user's ACCOUNT_STATUS was OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

... and checked that he could login again:

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL>

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 this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

ORA-02494

This was tested on Oracle 11.2. If you create a tablespace with a datafile with autoextend on, the file’s maxsize cannot be less than its size. If it is, you get an ORA-02494: 

SQL> l

  1  create tablespace andrew

  2  datafile '/tmp/andrew.dbf'

  3* size 200m autoextend on maxsize 100m

SQL> /

size 200m autoextend on maxsize 100m

                                   *

ERROR at line 3:

ORA-02494: invalid or missing maximum file size in

MAXSIZE clause

 

SQL> l

  1  create tablespace andrew

  2  datafile '/tmp/andrew.dbf'

  3* size 200m autoextend on maxsize 300m

SQL> /

 

Tablespace created.

 

SQL>

ABS Function

This was run on Oracle 11.1.0.6.0 on Windows XP. The ABS function returns the absolute value of a column or value:

SQL> select abs(4.3) from dual;

  ABS(4.3)
----------
       4.3

SQL> select abs(-2.9) from dual;

 ABS(-2.9)
----------
       2.9

SQL>

Typing oracle at the Command Prompt

This applies to Oracle 9 and below. If you get an ORA-00600 in your alert log and the first and only argument which follows is [12235], this may well have been caused by somebody typing the word oracle at the command prompt. Here is one I created earlier:

 

ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []

This only happens if the database is open at the time. The message appears to go to the alert log of the database in the $ORACLE_SID.

Sequences (Part 1)

(Tested on an Oracle 10 database.)

A sequence creates a list of unique numbers which can be added to one or more tables. You might use one to generate employee numbers for a personnel application. The same sequence can be used simultaneously by one, two or several users.

The example below creates a table, a sequence and trigger. Each time a row is added to the table, the trigger gets the next value from the sequence and puts it in the andrews_counter column:

SQL> create table andrews_table
  2  (andrews_counter number,
  3   andrews_name varchar2(10))
  4  /

Table created.

SQL> create sequence andrews_sequence
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  /

Sequence created.

SQL> create trigger andrews_trigger
  2  before insert on andrews_table
  3  for each row
  4  begin
  5  select andrews_sequence.nextval
  6  into :new.andrews_counter
  7  from dual;
  8  end;
  9  /

Trigger created.

SQL> insert into andrews_table
  2  values (null, 'Alan')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'Brian')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'Clive')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'David')
  3  /

1 row created.

SQL> select *
  2  from andrews_table
  3  /

ANDREWS_COUNTER ANDREWS_NAME
--------------- ------------
              1 Alan
              2 Brian
              3 Clive
              4 David

SQL>

Go to part 2