Re: Help Please...

From: Sweet Pete <pgadah_at_magicnet.net>
Date: Thu, 20 Sep 2001 20:28:29 GMT
Message-ID: <3baa5042.14757407@news.magicnet.net>

Rob,

If you have access to MetaLink, you should use it, because this information is contained there:

Doc ID: Note:115948.1
Subject: How to Rename a Table

Regards,
Peter

<< Here is the Note: 115948.1 in its' entirety >>

PURPOSE
This document provides an overview of how to rename a table and its implication on associated objects.

SCOPE & APPLICATION
Informational.

How to Rename a Table:

There are two ways of renaming a table, depending on the version of Oracle you are running:

For Oracle7:

Use the RENAME command:

SQL> RENAME <old_name> TO <new_name>;

To rename the objects, they should be in your own schema.

If you try renaming it from another schema with a qualifier:

        
      SQL> RENAME <schema>.<old_name> TO <schema>.<new_name>;

you will receive this error:

ORA-01765: specifying table's owner name is not allowed.

  You can use the RENAME command to change the name of table, view, sequence or
  private synonym from its old name to a new one. The new name must be neither a
  reserved word nor the name of an existing object for the user.

For Oracle 8.0.x and higher:

In this case, you have two options:

Use the RENAME command. This is very similar to the option mentioned above. The object to be renamed must be in your own schema and you should not qualify the object name with the schema name.
Use the ALTER TABLE RENAME command:

SQL> ALTER TABLE <schema>.<old_name> RENAME TO <new_name>;

Notes: Here, the object can be renamed by any user who has the ALTER TABLE

            privilege to modify the object. 
            Also, The <new_name> is *not* qualified by schema name. If
you 
            qualify it with a schema name, Oracle will treat the new
name as a 
            partitioned object and will raise an error: 
                ORA-14048: a partition maintenance operation may not
be combined 
                           with other operations.

Naming Conventions:

Names must be from 1 to 30 characters long.
Names must begin with a letter.
It must contain only A-Z, a-z, 0-9, _, $ and #. You are strongly discouraged from using $ and #.
Names are not case sensitive, unless they are created under double quotes. For example: RENAME emp TO employee;

     is different from 
              
                  RENAME emp TO "Employee";

      In the first case, you can simply issue a:
                 SELECT * FROM emp;
          OR     SELECT * FROM EMP;
          OR     SELECT * FROM eMp;

      And all of them will return rows (of course, if it contains

data).

In the second case, you can only view data from the table if you issue:

                  SELECT * FROM "Employee"    
         OR       SELECT * FROM scott."Employee" 
       (Notice the double quotes around the object)
          
     Any other way and you will encounter an error:
         ORA-942: Table or view does not exist

5. Names must not be Oracle server reserved words.

Renaming table: How does that affect the associated objects?

Indexes: Oracle automatically transfers indexes and the integrity constraints on the old object to the new object.
Grants: Oracle automatically transfers the grants associated with the old table to the new table.
Views: The views dependent on the table would be invalidated, once the table is renamed. The status of view in DBA_OBJECTS or USER_OBJECTS would show up as INVALID.

To revalidate the view, we could do either of the following:

Rename table back to its original name (if possible). Then explicitly recompile the view, although this is not necessary, since Oracle automatically recompiles view and checks for validity when it is next accessed:

SQL> ALTER VIEW <viewname> COMPILE;

b) Drop and recreate the view on the renamed table.

4. Synonyms:

If we access the synonym after the table is renamed, Oracle would raise this

     error:
         ORA-00980: synonym translation is no longer valid.

     We would then have to either:
     a) Rename the table back to its original name, before accessing
the synonym.
     b) Drop and recreate the synonym for the new tablename.

     Please note the following:
     Even after the table is renamed, the synonym for the table would
still show 
     up as VALID under DBA_OBJECTS or USER_OBJECTS. That's because you
can create
     a synonym even if the base table does not exist. You would only
get an error
     if you were to access this synonym.

  5. Triggers: 
     The behaviour of triggers is very similar to views. The trigger
associated 
     with the table will be invalidated once the table is renamed. The
status of 
     the trigger under DBA_OBJECTS or USER_OBJECTS would show up as
INVALID. The 
     status of the trigger under DBA_TRIGGERS or USER_TRIGGERS would
show up as 
     ENABLED.

     However, you don't have to explicitly recompile the trigger OR
drop and 
     recreate the trigger. If an action is performed on the renamed
table, Oracle 
     will automatically recompile the trigger, validate it and then
fire the 
     trigger.

     If you wish to explicitly recompile a trigger, you could issue:
              
         SQL> ALTER TRIGGER trig COMPILE;

  6. Stored Procedures and Functions:
     Renaming the table will invalidate all the stored procedures and
functions 
     that refer to this table. 

     The procedure and function must then be dropped and recreated by
referencing 
     the new tablename.

Renaming a partition of table:

SQL> ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

Where you *CANNOT* use RENAME or ALTER TABLE RENAME:

To rename PUBLIC SYNONYMS. To rename a public synonym, you must first drop it with the DROP SYNONYM command and then create another public synonym with the new name using the CREATE PUBLIC SYNONYM command.
You cannot use the RENAME command to rename columns. To rename a column, you have to use the CREATE TABLE AS SELECT clause. For example:

         SQL> CREATE TABLE test 
                (no number,
                 name varchar2(10));

     Now, I want to change the column "name" to "empname". I would do
the 
     following:

         SQL> RENAME test TO oldtest;

         SQL> CREATE TABLE test (no, empname)
              AS SELECT * FROM oldtest;

         SQL> DROP TABLE oldtest;

     NOTE: When you do a CREATE TABLE AS SELECT (CTAS), you are not
allowed to 
           specify datatypes for the columns, else you will end up

with ORA-01773.

References:

  Oracle8 Server Concepts
  Oracle8 SQL Server Reference
  Oracle8i SQL Server Reference

On Fri, 14 Sep 2001 09:26:27 -0500, "Rob Panosh" <rob_panosh_at_asdsoftware.com> wrote:

>Hi,
>
>In Microsoft SQL server to rename an existing table I would call a system
>stored proc sp_rename <table name>, <new tabe name>. Can I rename a table
>in Oracle? If so could somebody please provide an example.
>
>Thanks,
>--
>Rob Panosh
>Advanced Software Designs
>
>
>
>
Received on Thu Sep 20 2001 - 15:28:29 CDT