Re: Renaming Tablespaces

Yes, this seems way to difficult to me.

Try this:

Export all of the objects from the tablespace. Drop the tablespace including contents.
Recreate the tablespace.
Import the objects back in.

To get the export you can either a) generate a list of users in that tablespace and do user-level exports or b) generate a list of objects and do object-level exports. Do whichever one has the shortest list.

To get a list of users use:

        select owner from dba_segments where tablespace_name='YOURTS'

To get a list of objects in that tablespace use:

	select owner, object_name, object_type from dba_segments where 
	tablespace name='YOURTS'

Remember to recreate the TS before IMPORTING, then use the IGNORE=Y option for the import.

• Dave

cheryl_ploegstra_at_chugachelectric.com wrote:
>
> 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