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>

No comments: