Renaming Tablespaces

Has anyone had any luck renaming a tablespace in Oracle (7.3, AIX)? I've got a  database where the main datafile has the same name as the database itself, which makes it's confusing when the database is cloned to a new name.

Here's what I've tried, but so far I've been unsuccessful.
- Made a cold backup of the database;

• Created a new tablespace called DATA1 and a new datafile called /path/data01.dbf;
• Dropped the old tablespace and datafile;
• Backed up my controlfile to trace with this new schema;
• Shut down the database;
• Deleted all of the database files and replaced them with my cold backup;
• Deleted the data01.dbf file and copied the old datafile to data01.dbf;
• Edited my init.ora file to point to only one copy of the control file;
• Deleted all of the control files;
• Edited the trace copy of the controlfile to create a new controlfile, including:
  • create controlfile set database "<SID>" resetlogs archivelog;
• Started the database nomount;
• Used the trace copy of the controlfile to create a new controlfile;
• Altered the database open resetlogs;
• Queried my V$datafile and dba_tablespaces tables.

The result is that I've got a new datafile data01.dbf under my old tablespace name. The DATA1 tablespace has disappeared. So basically I've just managed to rename my datafile which I could do much easier with the alter tablespace rename datafile command.

Am I making this way too difficult? Or have I missed a step? Or is this impossible and I'll have to move the tablespace objects manually.

Any advice would be appreciated. Thanks in advance.

Cheryl Ploegstra
DBA, Chugach Electric

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Apr 01 1998 - 00:00:00 CST