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 in red and a user logging in afterwards in green.

 

The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

Password changed

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> conn a/c

Connected.

SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11

 

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

ERROR:

ORA-01017: invalid username/password; logon denied

 

Password unchanged

Enter user-name: 

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)

SQL> variable bv2 number

SQL> begin

  2  select 'ABC' into :bv1 from dual;

  3  select 123 into :bv2 from dual;

  4  end;

  5  /

PL/SQL procedure successfully completed.

SQL> print bv1

BV1

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

ABC

SQL> print bv2

       BV2

----------

       123

SQL> execute :bv1 := 'XYZ';

PL/SQL procedure successfully completed.

SQL> execute :bv2 := 456;

PL/SQL procedure successfully completed.

SQL> print bv1

BV1

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

XYZ

SQL> print bv2

       BV2

----------

       456

SQL>

Setting NUMWIDTH in PL/SQL Developer

This post is based on a problem I was asked to look at recently.

 

A colleague was using PL/SQL Developer to compare two tables in an Oracle 11 database. He was matching the rows on a key column then checking the corresponding values from a different column and reporting them if they did not match. His query returned almost 2000 rows but they appeared to have equal, not different values. Five people looked at the problem and could not see the cause. They then gave it to me, suggested it might be an Oracle bug and asked me to look for a patch to fix it.

 

I have reproduced the issue in the screen print below although in my example, the two tables only have two columns and one row each. COL1 is set to PI in both tables. I have given this column an alias of NAME. The query joins the two tables using this value as a key. It then compares the COL2 values and reports them if they do not match. I have given TAB1.COL2 an alias of VALUE1 and TAB2.COL2 an alias of VALUE2. According to the screen print, COL2 has the same value in both tables. This being the case, the query should not have displayed these values. As usual, click on the image to enlarge it if necessary:

I’m not an expert with PL/SQL Developer so I decided to see if the same thing happened in SQL*Plus and it did:

 

SQL> select a.col1 name, a.col2 value1, b.col2 value2

  2  from tab1 a, tab2 b

  3  where a.col1 = b.col1

  4  and a.col2 != b.col2

  5  /

 

NAME     VALUE1     VALUE2

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

PI   3.14159265 3.14159265

 

SQL>

 

I noticed that the value of COL2 filled the output area in both cases so I checked the value of NUMWIDTH:

 

SQL> show numwidth

numwidth 10

SQL>

 

I gave it a higher value. At this point I cheated a bit because I had set up the test data so I knew how big to make it:

 

SQL> set numwidth 22

SQL>

 

I ran the query again and this time it showed that the values had been different all the time:

 

SQL> select a.col1 name, a.col2 value1, b.col2 value2

  2  from tab1 a, tab2 b

  3  where a.col1 = b.col1

  4  and a.col2 != b.col2

  5  /

 

NAME                 VALUE1                 VALUE2

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

PI         3.14159265358979  3.1415926535897932385

 

SQL>

 

I wondered how I might do the same thing in PL/SQL Developer so I did a bit of research. Then I clicked on Tools / Preferences / SQL Window and put a tick against Number fields to_char. This made the little box next to it show up as green in the screen print below:

I clicked on Apply and OK in the usual way and when I ran the query again, the difference between the column values was obvious:

 

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

SQL> select null dummy, event idle_event,

  2  round(time_waited/100) seconds_waited

  3  from v$system_event

  4  where wait_class = 'Idle'

  5  and round(time_waited/100) > 0

  6  order by seconds_waited

  7  /

 

IDLE_EVENT                                                       SECONDS_WAITED

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

PX Deq: Parse Reply                                                           1

SGA: MMAN sleep for component shrink                                          1

single-task message                                                           5

PX Deq: Execution Msg                                                         7

JOX Jit Process Sleep                                                       108

PL/SQL lock timer                                                           260

PX Idle Wait                                                               6000

jobq slave wait                                                           11025

VKRM Idle                                                                 14400

Streams AQ: waiting for time management or cleanup tasks                  75954

Space Manager: slave idle wait                                            77450

smon timer                                                                77459

pmon timer                                                                77657

Streams AQ: waiting for messages in the queue                             77671

Streams AQ: qmn slave idle wait                                           77729

Streams AQ: qmn coordinator idle wait                                     77740

DIAG idle wait                                                           155195

pipe get                                                                 241269

rdbms ipc message                                                       1319628

SQL*Net message from client                                            10756842

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

                                                                       13046401

 

20 rows selected.

 

SQL>

A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911: 

SQL> @test1

SQL> set echo on

SQL> select 'Comment->' from dual; /*Andrew was here*/

  2  select 'More SQL' from dual;

select 'Comment->' from dual; /*Andrew was here*/

                            *

ERROR at line 1:

ORA-00911: invalid character

 

SQL>

To get the code to work, you need to include the comment before the semi-colon:

SQL> @test2

SQL> set echo on

SQL> select 'Comment->' from dual /*Andrew was here*/;

 

'COMMENT-

---------

Comment->

 

SQL> select 'More SQL' from dual;

 

'MORESQL

--------

More SQL

 

SQL>

However, if you try this in SQL Developer, both options work (as usual, click on the images to enlarge them and bring them into focus):

 

 

ORA-01466 (SET TRANSACTION READ ONLY - Part 1)

Going through some SQL*Plus course notes from 1990 (as you do) I came across the SET TRANSACTION READ ONLY statement and decided to try it out in an Oracle 11.2 database. It didn’t quite work as expected:

 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has

changed

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL>

 

I found that if I waited a moment after the COMMIT statement, the problem disappeared:

 

SQL> create table tab2 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab2 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> host sleep 1

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> select * from tab2

  2  /

 

      COL1

----------

         1

 

SQL> drop table tab2

  2  /

 

Table dropped.

 

SQL>

CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba

Connected.

SQL> alter profile default

  2  limit connect_time 1

  3  /

 

Profile altered.

 

SQL>

 

Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:

 

SQL> create user andrew

  2  identified by reid

  3  profile default

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select limit from user_resource_limits

  2  where resource_name = 'CONNECT_TIME'

  3  /

 

LIMIT

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

1

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:40

 

SQL> host sleep 10

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:50

 

SQL> host sleep 10

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:00

 

SQL> host sleep 10

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:10

 

SQL> host sleep 10

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:20

 

SQL> host sleep 10

 

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

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:30

 

SQL> host sleep 10

 

Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:

 

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

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02399: exceeded maximum connect time, you are

being logged off

ORA-02399: exceeded maximum connect time, you are

being logged off

 

SQL>

 

… and a subsequent attempt to run the SQL showed that the session was no longer connected:

 

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

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 884

Session ID: 61 Serial number: 2851

 

SQL>