SQL*Plus on UNIX and ps -ef

This example was tested on an Oracle 10 database running on Solaris.

Imagine you have a UNIX user called smithj. He logs into the system database user as follows:

TEST10 > whoami

smithj

TEST10 > sqlplus system/secret

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 15:13:23 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

However, while he is doing this, another user, e.g. bloggsf, can use the ps command to see the password for the system database user as follows:

TEST10 > whoami

bloggsf

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 4324 29829 0 15:13:23 pts/12 0:00 sqlplus system/secret
TEST10 >

To stop this happening, smithj should login to the database with the username and wait for Oracle to prompt him for the password:

TEST10 > sqlplus system

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 16:12:37 2011

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

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

Then bloggsf can see that smithj has logged in to the system user but cannot see the password:

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 21306 20953 0 16:12:37 pts/12 0:00 sqlplus system

TEST10 >

ANALYZE (Part 1)

(Tested on an Oracle 10 database.)

I was going through some old notes recently and was reminded of the ANALYZE command. This allows you to calculate statistics on a table or index. The optimizer can then use these to improve the efficiency of subsequent DML on that object. Oracle have said for a long time now that you should DBMS_STATS in preference to ANALYZE. However, I thought it might still be useful to look at ANALYZE in case you find it in an old SQL script yourself.

In the example below, a table is created and its rows counted. At this point, NUM_ROWS (the number of rows in the table when it was last analyzed) is blank. SAMPLE_SIZE (the number of rows used for the last ANALYZE) is also blank. The table is then analyzed with the COMPUTE STATISTICS option. This analyzes the entire table and populates these two columns in the process.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 100 ROWS option. You might expect SAMPLE_SIZE to be 100 afterwards but it is not. I will be returning to this in a future post.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 20 PERCENT option. You might expect SAMPLE_SIZE to be 306 afterwards but it is not. I will be returning to this too in a future post.

Finally, the table is analyzed with the DELETE STATISTICS option. This removes the statistics so the NUM_ROWS and SAMPLE_SIZE columns become null again:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1531

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL> analyze table andrew compute statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 100 rows
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 20 percent
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew delete statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL>

License_max_users

This was tested on an Oracle 10 database.

The license_max_users parameter determines how many users you can have in a database. How you set this parameter depends on your licensing agreement with Oracle.

If it is zero, you can create as many users as you want:

SQL> l
  1  select value from v$parameter
  2* where name = 'license_max_users'
SQL> /

VALUE
----------
0

SQL>

It is not relevant at the session level so you cannot change it there:

SQL> l

  1* alter session set license_max_users = 10

SQL> /

alter session set license_max_users = 10

                  *

ERROR at line 1:

ORA-02096: specified initialization parameter is not

modifiable with this option

SQL> 

You cannot set license_max_users lower than the number of users currently in a database:

SQL> select count(*) from dba_users;

  COUNT(*)
----------
        22

SQL> alter system set license_max_users = 21;
alter system set license_max_users = 21
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because
specified value is invalid
ORA-00035: LICENSE_MAX_USERS cannot be less than
current number of users

SQL> alter system set license_max_users = 22;

System altered.

SQL>

And if you try to create more users once you have reached the limit imposed by license_max_users, that will fail too:

SQL> create user fred identified by bloggs;
create user fred identified by bloggs
                               *
ERROR at line 1:
ORA-01985: cannot create user as LICENSE_MAX_USERS
parameter exceeded

SQL>

And a message goes to your alert log:

Create user disallowed, current users equal maximum (22)

License_max_sessions

(Tested on an Oracle 10 database.)

If license_max_sessions is set to zero (the default), there is no limit to the number of concurrent user sessions:

SQL> select value

  2  from v$parameter

  3  where name = 'license_max_sessions'

  4  /

VALUE

----------

0

SQL>

But the documentation does not make it clear if this figure includes:

• All sessions.
• Only sessions where the username is not null (i.e. excluding Oracle's background processes).
• Sessions for users with the restricted session privilege.

This example tries to clarify this. First create a test user:

SQL> grant create session to andrew identified by reid

  2  /

Grant succeeded.

SQL>

Then set the parameter to a suitable value for testing:

SQL> alter system set license_max_sessions = 6;

System altered.

SQL>

And set up a series of simultaneous sessions:

SQL> conn andrew/reid

Connected.

SQL>

Until you see the following error:

SQL> conn andrew/reid
ERROR:
ORA-00019: maximum number of session licenses exceeded

Warning: You are no longer connected to ORACLE.
SQL>

At this point the following message appears in the alert log:

Non-DBA logon denied; current logons equal maximum (6)

Then count the sessions in the database:

SQL> l
  1  select nvl(username,'NULL'), count(*)
  2  from v$session
  3* group by nvl(username,'NULL')
SQL> /

NVL(USERNAME,'NULL')             COUNT(*)
------------------------------ ----------
ANDREW                                  3
NULL                                   15
SYS                                     2

SQL>

So it looks to me as if:

• The background processes are not counted.
• Sessions for users (e.g. SYS) with the restricted session privilege are counted.
• The actual number of sessions allowed is 1 less than the limit set by license_max_sessions. I am a bit suspicious of this so I hope to recheck it on some other systems and report back.

If you grant restricted session to andrew, this allows him to log in again:

SQL> conn andrew/reid
ERROR:
ORA-00019: maximum number of session licenses
exceeded

SQL> conn / as sysdba

Connected.
SQL> grant restricted session to andrew;

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL>

And a further message appears in the alert log:

License maximum (6) exceeded, DBA logon allowed

There appears to be nothing to stop you setting license_max_sessions to a number lower than the current number of sessions. Note that we now have 6 sessions as Andrew was able to connect again once he had the restricted session privilege:

SQL> select count(*) from v$session
  2  where username is not null;

  COUNT(*)
----------
        6

SQL> alter system set license_max_sessions = 4;

System altered.

SQL>

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 

Function Based Indexes

(Tested on an Oracle 10 database.)

This post introduces function based indexes. A normal B-tree index allows you to access a table quickly using the full column value. A function based index, on the other hand, allows you to apply a function to a column then use the resultant value to access the table. First create a table:

SQL> CREATE TABLE ANDREWS_TABLE
  2  (FIRST_NAME VARCHAR2(10),
  3   DOB        DATE)
  4  /

Table created.

SQL>

Next add some data:

SQL> INSERT INTO ANDREWS_TABLE
  2  VALUES (
  3  'Fred',
  4  TO_DATE('10-MAY-1983','DD-MON-YYYY'))
  5  /

1 row created.

SQL>

Then create a function based index. This one will allow you to access the table using the year of birth in YYYY format.:

SQL> CREATE INDEX YEAR_BORN_INDEX
  2  ON ANDREWS_TABLE(TO_CHAR(DOB,'YYYY'))
  3  /

Index created.

SQL>

And a B-tree index for comparison:

SQL> CREATE INDEX FIRST_NAME_INDEX
  2  ON ANDREWS_TABLE(FIRST_NAME)
  3  /

Index created.

SQL>

USER_IND_COLUMNS shows which columns are indexed. Note that Oracle has added a pseudo column for the function based index:

SQL> SELECT INDEX_NAME, COLUMN_POSITION, COLUMN_NAME
  2  FROM USER_IND_COLUMNS
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  ORDER BY 1,2
  5  /

INDEX_NAME           COLUMN_POSITION COLUMN_NAME
-------------------- --------------- --------------------
FIRST_NAME_INDEX                   1 FIRST_NAME
YEAR_BORN_INDEX                    1 SYS_NC00003$

SQL>

You can identify function based indexes as they have
INDEX_TYPE = FUNCTION-BASED NORMAL and FUNCIDX_STATUS = ENABLED:

SQL> SELECT INDEX_NAME, INDEX_TYPE, FUNCIDX_STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  /

INDEX_NAME           INDEX_TYPE                  FUNCIDX_STATUS
-------------------- --------------------------- --------------
FIRST_NAME_INDEX     NORMAL
YEAR_BORN_INDEX      FUNCTION-BASED NORMAL       ENABLED

SQL>

You cannot see the pseudo column used by the function based index when you describe the table:

SQL> DESC ANDREWS_TABLE
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 FIRST_NAME                             VARCHAR2(10)
 DOB                                    DATE

SQL>

But you can see it in USER_TAB_COLS. The DATA_DEFAULT column shows how it is populated and the HIDDEN_COLUMN column shows that it is hidden:

SQL> SELECT TABLE_NAME, COLUMN_NAME,
  2  DATA_DEFAULT DERIVATION, HIDDEN_COLUMN HIDDEN
  3  FROM USER_TAB_COLS
  4  WHERE COLUMN_NAME = 'SYS_NC00003$'
  5  /

TABLE_NAME    COLUMN_NAME   DERIVATION             HIDDEN
------------- ------------- ---------------------- ------
ANDREWS_TABLE SYS_NC00003$  TO_CHAR("DOB",'YYYY')  YES

SQL>

And you can also select the contents of the pseudo column:

SQL> SELECT SYS_NC00003$ "Pseudo Column" FROM ANDREWS_TABLE
  2  /

Pseudo Column
-------------
1983

SQL>

Comments in SQL*Plus

(Tested on Oracle 9.)

You can comment out a whole SQL statement by typing rem at the start of the line:

SQL>

SQL> rem select sysdate today from dual;

SQL>

If the SQL statement goes over more than one line, you cannot use rem to comment out part of it:

SQL> select

  2  rem Tomorrow

  3  sysdate + 1 tomorrow from dual;

sysdate + 1 tomorrow from dual

*

ERROR at line 3:

ORA-00923: FROM keyword not found where expected

SQL>

You have to use two hyphens at the start of the line to do that:

SQL> select

  2  -- Tomorrow

  3  sysdate + 1 tomorrow from dual;

TOMORROW

---------

07-APR-11

SQL>

Alternatively you can use /* and */ in the same way:

SQL> select

  2  /* Yesterday */

  3  sysdate - 1 yesterday from dual;

YESTERDAY

---------

05-APR-11

SQL>

You can also use -- at the end of a line to turn the remaining text into a comment:

SQL> select count(*)

  2  from -- Comment

  3  dba_tables;

  COUNT(*)

----------

      3017

SQL>

But it does not behave consistently. In the next example the comment is treated as an alias instead:

SQL> select count(*) -- Comment

  2  from dba_tables;

COUNT(*)--COMMENT

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

             3017

SQL>

The same goes for /* and */:

SQL> select count(*)

  2  from /* Comment */

  3  dba_indexes;

  COUNT(*)

----------

      2280

SQL> select count(*) /* Comment */

  2  from dba_indexes;

COUNT(*)/*COMMENT*/

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

               2280

SQL>

Calculating Averages (Part 2)

Go to part 1

(Tested on Oracle 9 but probably not on the database used for part 1.)

In Calculating Averages (Part 1) I ran the following SQL:

select avg(num_rows) from dba_tables

where num_rows is not null;

Since then I have read that the where clause was unnecessary as Oracle does not include null values when it works out averages. I decided to see if this was true. First I checked that there were both null and not null values in the num_rows column in dba_tables:

SQL> select count(*) from dba_tables

  2  where num_rows is null;

  COUNT(*)

----------

       459

SQL> select count(*) from dba_tables

  2  where num_rows is not null;

  COUNT(*)

----------

       366

SQL>

Incidentally, you can combine the two statements above into one by using decode:

SQL> select

  2  decode(num_rows,null,'NULL','NOT NULL') row_count,

  3  count(*)

  4  from dba_tables

  5  group by decode(num_rows,null,'NULL','NOT NULL');

ROW_COUNT   COUNT(*)

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

NOT NULL         366

NULL             459

SQL>

Then I calculated the average as before:

SQL> select avg(num_rows) from dba_tables

  2  where num_rows is not null;

AVG(NUM_ROWS)

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

   783867.724

SQL>

Finally, I omitted the where clause and the result was the same. This proves that avg ignores null values:

SQL> select avg(num_rows) from dba_tables;

AVG(NUM_ROWS)

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

   783867.724

SQL>

ORA-01516

This example illustrates a problem I had recently. I created a tablespace:

  1  create tablespace andrew

  2  datafile '/usr/users/oracle/andrew.dbf'

  3* size 10m

SQL> /

Tablespace created.

SQL>

Some time later I decided to resize its datafile. I remembered how big it was but I was not sure of its name so I queried DBA_DATA_FILES:

SQL> col file_name format a40

SQL> l

  1  select file_name

  2  from dba_data_files

  3* where tablespace_name = 'ANDREW'

SQL> /

FILE_NAME

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

/usr/users/oracle/andrew.dbf

SQL>

But when I tried to resize it I had an ORA-01516 error:

SQL> alter database datafile

  2  '/usr/users/oracle/andrew.dbf'

  3  resize 100m;

alter database datafile

*

ERROR at line 1:

ORA-01516: nonexistent log file, datafile, or tempfile

"/usr/users/oracle/andrew.dbf"

SQL>

The reason for this was not obvious until I looked in the Oracle alert log:

Wed Mar  9 15:35:58 2011

create tablespace andrew

datafile '/usr/users/oracle/and^[[6~rew.dbf'

size 10m

Wed Mar  9 15:35:59 2011

Completed: create tablespace andrew

By careless use of a cursor control key, I had introduced a control character into the file name. This control character could not be seen when querying the file's name in DBA_DATA_FILES. The only way to resize the file was to use the full name displayed in the Oracle alert log:

SQL> alter database datafile
  2  '/usr/users/oracle/and^[[6~rew.dbf'

  3  resize 20m;

Database altered.

SQL>