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>