Rename Oracle 9i, 10g, 11g Datafile with Blank Space/White Space/Special/New line Characters


The Eucharistic Miracles of the World
There are chances of mistake in life and the same kind of mistakes can happen in the DBA life also. The mistake on the production database is not advisable. J But anyway, there are chances of adding special characters by mistake in the data file name while adding the datafile to the tablespace. You can rename the datafile using following steps.

Here in this example I am adding one datafile with special character and new line character.
SQL> alter tablespace SQCTST add datafile '/u01/oradata/sqctst/sqctst@~!
     08.dbf' size 2m;
Tablespace altered.

SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name='SQCTST'
SQL> /
FILE_NAME                                BYTES/1024/1024
---------------------------------------- ---------------
/u01/oradata/sqctst/sqctstsqctst~!                        2
8 rows selected.
Using below sqls statements you can find out the ASCII characters for the file name. The highlighted ones are the special characters. Anyway this is not relevant for our renaming.
SQL> select dump(file_name) from dba_data_files where file_name like '%sqctstsqctst%';
Typ=1 Len=38: 47,117,48,49,47,111,114,97,100,97,116,97,47,109,113,116,115,116,47

SQL>  select chr(10), chr(48), chr(33),chr(126),chr(32) from dual;
- - - - -
  0 ! ~
Here is the renaming procedure
Step 1
        Shutdown the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
File details using OS commands.
-rw-r----- 1 oracle oinstall    2105344 Feb  5 11:27 sqctstsqctst~!?08.dbf
Step 2
Move/rename the file into actual name which you want or expected.
$ mv -i sqctstsqctst*dbf sqctst08.dbf
Step 3
Startup the database in mount state
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size                  2237776 bytes
Variable Size            8254393008 bytes
Database Buffers         2415919104 bytes
Redo Buffers               16924672 bytes
Database mounted.
You can verify the datafile with the special characters are still plugged with tablespace using the following query.
SQL> select name from v$datafile where name like '%sqctstsqctst%';
Step 4
Rename the database file using alter database rename command.
SQL> alter database rename file '/u01/oradata/sqctst/sqctstsqctst~!
     08.dbf' to '/u01/oradata/sqctst/sqctst08.dbf';

Database altered.
SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name='SQCTST';
FILE_NAME                                BYTES/1024/1024
---------------------------------------- ---------------
/u01/oradata/sqctst/sqctst08.dbf                         2
8 rows selected.
I am dropping the datafile after test.
SQL> alter tablespace sqctst drop datafile '/u01/oradata/sqctst/sqctst08.dbf';
Tablespace altered.



Post a comment


Oracle (575) General (60) Unix (47) Blog (23) Script (12) OCI (3) SQL* Loader (3) Software (1) Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer