Friday, April 29, 2011

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 >

Saturday, April 23, 2011

ANALYZE (Part 1)

Nero 10 HD

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

Thursday, April 21, 2011

STARTUP FORCE

I was asked to bounce an Oracle 11 test database by Dilip
(http://www.linkedin.com/pub/dilip-seshan/5/469/29b). There were several sessions in the database originating from a variety of server processes so I decided to use STARTUP FORCE. This does a SHUTDOWN ABORT then opens the database again. Note the ORA-32004 because the parameter file has not been fully updated since the upgrade from an earlier version of Oracle:

SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  313245696 bytes
Fixed Size                  2147672 bytes
Variable Size             197139112 bytes
Database Buffers          109051904 bytes
Redo Buffers                4907008 bytes
Database mounted.
Database opened.
SQL>

Appropriate messages appear in the alert log:

Tue Apr 19 10:04:55 2011
Shutting down instance (abort)
License high water mark = 65
USER (ospid: 23820): terminating the instance
Instance terminated by USER, pid = 23820
Tue Apr 19 10:04:57 2011
Instance shutdown complete
Tue Apr 19 10:04:58 2011
Adjusting the default value of parameter parallel_max_servers
from 160 to 85 due to the value of parameter processes (100)
Starting ORACLE instance (normal)
Etc.

And a crash recovery takes place:

ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2804399374
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Apr 19 10:05:07 2011
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 1835 KB redo, 646 data blocks need recovery
Started redo application at
 Thread 1: logseq 99, block 42986
Recovery of Online Redo Log: Thread 1 Group 3 Seq 99 Reading mem 0
  Mem# 0: /database/BUSSOST1/bus_redo1/redo03a.log
  Mem# 1: /database/BUSSOST1/bus_redo2/redo03b.log
Completed redo application of 1.27MB
Completed crash recovery at
 Thread 1: logseq 99, block 46657, scn 1701984663986
 646 data blocks read, 646 data blocks written, 1835 redo k-bytes read
Tue Apr 19 10:05:14 2011

Friday, April 15, 2011

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)

Wednesday, April 13, 2011

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>

Tuesday, April 12, 2011

Chained Rows (Part 1)

BusinessPresence_468x60

The table fetch continued row count in V$SYSSTAT is incremented by 1 each time Oracle accesses a row which spans more than 1 block:

SQL> SELECT STATISTIC#, VALUE TFCR
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table fetch continued row'
  4  /
 
STATISTIC#       TFCR
---------- ----------
       355    2313557
 
SQL>

The lower this value is the better but you should always look at it as a percentage of the total number of rows read. You can calculate this by adding together the following 2 values:

SQL> SELECT STATISTIC#, VALUE TSRG
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table scan rows gotten'
  4  /
 
STATISTIC#       TSRG
---------- ----------
       352 4122983672
 
SQL> SELECT STATISTIC#, VALUE TFBR
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table fetch by rowid'
  4  /
 
STATISTIC#       TFBR
---------- ----------
       354  698738408
 
SQL>

Then you can calculate the table fetch continued rows figure as a percentage of the total number of rows read like this:

SQL> SELECT TFCR/(TSRG+TFBR)*100 PERCENTAGE FROM
  2  (SELECT VALUE TFCR FROM V$SYSSTAT
  3   WHERE NAME = 'table fetch continued row'),
  4  (SELECT VALUE TSRG FROM V$SYSSTAT
  5   WHERE NAME = 'table scan rows gotten'),
  6  (SELECT VALUE TFBR FROM V$SYSSTAT
  7   WHERE NAME = 'table fetch by rowid')
  8  /
 
PERCENTAGE
----------
.047981964
 
SQL>

Or this:

SQL> SELECT
  2   (SELECT VALUE FROM V$SYSSTAT
  3    WHERE NAME = 'table fetch continued row') /
  4  ((SELECT VALUE FROM V$SYSSTAT
  5    WHERE NAME = 'table scan rows gotten') +
  6   (SELECT VALUE TFBR FROM V$SYSSTAT
  7    WHERE NAME = 'table fetch by rowid')) * 100 PERCENTAGE
  8  FROM DUAL
  9  /
 
PERCENTAGE
----------
.047981953
 
SQL>

Or this:

SQL> SELECT A.VALUE / (B.VALUE + C.VALUE) * 100 AS PERCENTAGE
  2  FROM   V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
  3  WHERE  A.NAME = 'table fetch continued row'
  4  AND    B.NAME = 'table scan rows gotten'
  5  AND    C.NAME = 'table fetch by rowid'
  6  /
 
PERCENTAGE
----------
.047981939
 
SQL>

The figures differ slightly. This could be caused by different rounding in each calculation. However, the numbers came from a very busy database. It is more likely that increases in the 3 statistics between 1 calculation and the next were the real reason.

You then need to decide what percentage of chained row reads to allow. This is the difficult bit as some people say that anything over 0.1% is too high whereas others say that up to 5% is acceptable.

Monday, April 11, 2011

Swapping Data Between 2 Columns (Part 2)

Create your own FREE Website


Alan Smale, a colleague of mine, suggested that Oracle takes data from a consistent point in time when it does updates. This allows you to update column 1 with column 2 and vice versa in the same statement. You can see what I mean in the example below:

SQL> create table swap2
  2  (first_name varchar2(10),
  3   surname    varchar2(10));
 
Table created.
 
SQL> insert into swap2 values ('Bloggs', 'Joe');
 
1 row created.
 
SQL> select * from swap2;
 
FIRST_NAME SURNAME
---------- ----------
Bloggs     Joe
 
SQL> update swap2 set
  2  first_name = surname,
  3  surname = first_name;
 
1 row updated.
 
SQL> select * from swap2;
 
FIRST_NAME SURNAME
---------- ----------
Joe        Bloggs
 
SQL>

Swapping Data Between 2 Columns (Part 1)

Create your own  Social Website

I was not sure how to swap data between columns. It occurred to me that if I updated column 1 with column 2, I would then be unable to update column 2 with column 1. I decided you would have to do it using a temporary column as shown below. I will not provide any more explanation than that because once you have read part 2, you will not be using this method anyway:

SQL> create table swap1
  2  (first_name varchar2(10),
  3   surname    varchar2(10));
 
Table created.
 
SQL> insert into swap1 values ('Smith', 'John');
 
1 row created.
 
SQL> select * from swap1;
 
FIRST_NAME SURNAME
---------- ----------
Smith      John
 
SQL> alter table swap1 add
  2  (temporary_column varchar2(10));
 
Table altered.
 
SQL> update swap1 set temporary_column = first_name;
 
1 row updated.
 
SQL> update swap1 set first_name = surname;
 
1 row updated.
 
SQL> update swap1 set surname = temporary_column;
 
1 row updated.
 
SQL> alter table swap1 drop column temporary_column;
 
Table altered.
 
SQL> select * from swap1;
 
FIRST_NAME SURNAME
---------- ----------
John       Smith
 
SQL>

Saturday, April 09, 2011

Sequences (Part 1)

Redesign_468x60
(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>

Thursday, April 07, 2011

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>

Wednesday, April 06, 2011

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>

Tuesday, April 05, 2011

Calculating Averages (Part 2)

CatholicSingles.com-Meet Singles With Like Faith Values!  468x60 Banner

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>

Monday, April 04, 2011

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>