How to remove spaces in Oracle Data File Names

There are often multiple spaces in the middle or end of the data file name when creating a tablespace. How can we remove the spaces in the name? How can we remove them in the following experiment. Rename

There are often multiple spaces in the middle or end of the data file name when creating a tablespace. How can we remove the spaces in the name? How can we remove them in the following experiment. Rename

Environment: Oracle Linux Server release 6.4 oracle 11.2.0.1.0

Problem description and analysis: There are often multiple spaces in the middle or end of the data file name when creating a tablespace. How can I remove the spaces in the name, next we will remove the experiment. Rename: checks data by changing the tablespace offline, system-level rename, database-level rename, and tablespace online.

1. Create a test table space

Sys @ ORCL> create tablespace aaa datafile '/11g/app/oracle/oradata/ORCL/aaa bbb. dbf' size 10 m;

Tablespace created.

2. offline the tablespace

Sys @ ORCL> alter tablespace aaa offline;

Tablespace altered.

3. An error is returned when you directly modify the data file name.

Sys @ ORCL> alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb. dbf 'to'/11g/app/oracle/oradata/ORCL/aaabbb. dbf ';

Alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb. dbf' to'/11g/app/oracle/oradata/ORCL/aaabbb. dbf'

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01141: error renaming data file 8-new file '/11g/app/oracle/oradata/ORCL/aaabbb. dbf' not

Found

ORA-01110: data file 8: '/11g/app/oracle/oradata/ORCL/aaa bbb. dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

4. Modify the name of the operating system-level data file

[Oracle @ test ORCL] $ mv aaa \ bbb. dbf aaabbb. dbf

[Oracle @ test ORCL] $ ls

Aaabbb. dbf redo01.log SYSAUX01.DBF TEST. DBF wmis01.dbf

Control01.ctl redo02.log SYSTEM01.DBF UNDOTBS01.DBF

EXAMPLE01.DBF redo03.log temptbs01.dbf USERS01.DBF

5. Modify the database-level data file name
Sys @ ORCL> alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb. dbf 'to'/11g/app/oracle/oradata/ORCL/aaabbb. dbf ';

Database altered.

6. upload the data file online

Sys @ ORCL> alter tablespace aaa online;

Tablespace altered.

Sys @ ORCL> select name from v $ datafile where file # = 8;

NAME

Bytes ----------------------------------------------------------------------------------------------------

/11g/app/oracle/oradata/ORCL/aaabbb. dbf

Summary: This test mainly involves renaming at the system level and modifying the name at the database level. The following is an example of the error sequence and an error message. Before the production database is changed, it is best to back up the database. DBA is a treasure-saving tool!

This article permanently updates the link address: