Renaming a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Renaming a table

  1. #1
    Join Date
    Sep 2001
    Posts
    112
    If I rename a table does its indexes become invalid?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    Yes if you rename a table indexes will become inavlid.

    Just rebuild the indexes alter index index-name rebuild

    regards
    Hrishy

  3. #3
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    Renaming table: How does that affect the associated objects?
    ------------------------------------------------------------

    1. Indexes:
    Oracle automatically transfers indexes and the integrity constraints on the
    old object to the new object.

    2. Grants:
    Oracle automatically transfers the grants associated with the old table to
    the new table.

    3. 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:

    a) 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 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.

    sonofsita
    http://www.ordba.net

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by hrishy
    Yes if you rename a table indexes will become inavlid.
    I don't think so.

    Code:
    SQL> create table xyz (x number(10));
    Table created.
    SQL> create index xyz_1 on xyz (x);
    Index created.
    SQL> insert into xyz values (1);
    1 row created.
    SQL> insert into xyz values (2);
    1 row created.
    SQL> commit;
    Commit complete.
    
    SQL> select index_name, status
      2  from user_indexes;
    
    INDEX_NAME                     STATUS
    ------------------------------ --------
    XYZ_1                          VALID
    
    SQL> rename xyz to abc;
    Table renamed.
    
    SQL> select index_name, status
      2  from user_indexes;
    
    INDEX_NAME                     STATUS
    ------------------------------ --------
    XYZ_1                          VALID
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width