Renaming a table

If I rename a table does its indexes become invalid?

Hello

Yes if you rename a table indexes will become inavlid.

Just rebuild the indexes alter index index-name rebuild

regards
Hrishy

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.

Quote:

---

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

---