Re: Renaming Table-Space

From: Graham Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Thu, 04 Feb 1999 12:42:42 -0600
Message-ID: <36B9EA22.3E1E@ln.ssw.abbott.com>

Nicolas Bronke wrote:
>
> Is it possible to rename a tablespace?
>
> --
> Kind regards
> Nicolas Bronke

Yes,

But it is very dangerous, and should only be attempted with great caution....

First of all make sure ALL users are logged out of the database and you are
using restricted session to make sure someone else doesn't sneek in whilst
you are not looking.

Let's take our Oracle 7.3 on OpenVMS development database as an example. Let's
alter the name of the USER_TBS tablespace to USER2_TBS. First we need to know
the table-space number of the chosen tablespace....

SVRMGR> select ts#, name from ts$;
TS# NAME

---------- ------------------------------
         0 SYSTEM                        
         1 CAPD_DATA_TBS                 
         2 INDEX_TBS                     
         3 AUDIT_TBS                     
         4 NOVAMANAGE_TBS                
         5 ROLLBACK_TBS                  
         6 TEMPORARY_TBS                 
         7 USER_TBS                      

8 rows selected.

The table-space number is 7. We can now re-name the tablespace....

SVRMGR> update ts$ set name = 'USER2_TBS' where ts# = 7; 1 row processed.

Before doing anything else we need to flush the shared pool, otherwise cached objects with the tablespace hard coded could cause the DBWR to go bezerk....

SVRMGR> alter system flush shared_pool; Statement processed.

The tablespace is now renamed, you can check with....

SVRMGR> select ts#, name from ts$;
TS# NAME

---------- ------------------------------
         0 SYSTEM                        
         1 CAPD_DATA_TBS                 
         2 INDEX_TBS                     
         3 AUDIT_TBS                     
         4 NOVAMANAGE_TBS                
         5 ROLLBACK_TBS                  
         6 TEMPORARY_TBS                 
         7 USER2_TBS                     

8 rows selected.

Checking entries in DBA_USERS, DBA_TABLES, DBA_DATA_FILES etc will reflect this change. Remember the name is really just a label for the TS#, but many objects will reference the label rather than the name. You will need to make sure that stored procedures, backup scripts and anything else that might reference the tablespace directly will be updated after you have made the change.

Despite all of the above, I would not recommend this for a production environment. I don't believe Oracle recommend this approach, and most DBAs would tell you to export/import which is good advice.

Graham. Received on Thu Feb 04 1999 - 12:42:42 CST