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

advertisements

_____________________________________________________________________________________________________________________

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

08.dbf

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%';
DUMP(FILE_NAME)

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

Typ=1 Len=38: 47,117,48,49,47,111,114,97,100,97,116,97,47,109,113,116,115,116,47

,109,113,116,115,116,109,113,116,115,116,126,33,10,48,56,46,100,98,102

SQL>  select chr(10), chr(48), chr(33),chr(126),chr(32) from dual;

C C C C C

- - - - -

  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%';

NAME

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

/u01/oradata/sqctst/sqctstsqctst~!08.dbf

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.

_____________________________________________________________________________________________________________________

Website Stats