• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake

by admin

Question: We have mistakenly create a datafile with special charatcers. Also we get error ORA-01516 when we try to rename it using the normal method:

sql> alter database rename file 'old_file_name' to 'new_file_name';

The Solution

1. Bring the Tablespace offline.

sql> alter tablespace [tablespace name] offline;

2. Copy/Move the File at the OS level specifying the correct file name.

3. If there is no special character then the solution is:

sql> alter database rename file [old_file_name] to [new_file_name];

4. If there is special character then, we need to find out the datafile name by giving the datafile number and rename the datafile. We can use the below procedure:

DECLARE
str varchar2(50);     <<======== Size needs to be adjusted based on the length of datafile name and path.
BEGIN
Select name into str from v$datafile where file# = 33;
execute immediate 'alter database rename file ''' || str || ''' to ''''';
END;

5. Bring back the Tablespace online:

sql> alter tablespace [tablespace name] online;

For 12c

For 12c databases, there is an easier solution for this using the new 12c “move datafile” command.

sql> alter tablespace INDX_DATA$OLD rename to INDX_DATA_OLD;
sql> alter database move datafile '+DATADG/XPCLRC/DATAFILE/indx_data$old.1168.888920899' to '+DATADG';

Filed Under: oracle, Oracle 10g, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  2. How to duplicate a Oracle Database to a previous Incarnation
  3. Oracle SQL script to Show current Users and SQL being Executed
  4. Oracle DGMGRL (Data Guard Manager) Command Line Reference (Cheat Sheet)
  5. How to reclaim entire space of an oracle database table with “Truncate Table” statement
  6. Oracle Database 12c New Feature – Move a Datafile Online
  7. Automatic Undo Management and Oracle RAC
  8. ORA-00031: session marked for kill
  9. Stored Procedures and Functions in PL/SQL
  10. Oracle Grid 12c: Read Only Instances on Leaf Nodes

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright