RENAME TABLE

When we rename table how many dependent objects effected

SQL> create table TAJ ( no number, ddate date, name varchar2(20));

Table created.

PRIMARY KEY and FOREIGN KEY
SQL> alter table taj add constraint pk_no primary key(no);

Table altered.

SQL> alter table taj
2 add constraint fk_name foreign key (name)
3 references master(name);

Table altered.

Database Object ( INDEX,VIEW,SYNONYM,PROCEDURE,TRIGGER,FUNCTION)
SQL> create index idx_name on taj(name);

Index created.

SQL> create view vw_taj as select * from taj;

View created.

SQL> create synonym sy_taj for taj;

Synonym created.

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure PRO_TAJ
2 is
3 begin
4 for x in ( select * from taj) loop
5 dbms_output.put_line(X.NO);
6 end loop;
7* end;
SQL> /

Procedure created.


SQL> ed
Wrote file afiedt.buf

1 create or replace trigger tr_taj
2 after insert
3 on TAJ
4 for each row
5 begin
6 insert into TAJ_COPY values ( :new.no,:new.ddate,:new.name);
7* end;
8 /

Trigger created.

SQL> show err
No errors.
SQL> insert into taj values ( 2,sysdate,'TAJ');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from taj_copy;

COUNT(*)
----------
2


SQL> create or replace FUNCTION fun_taj
2 (no_in in number)
3 return varchar2
4 is
5 cname varchar2(20);
6 cursor c1 is
7 select name
8 from taj
9 where no = no_in;
10 begin
11 open c1;
12 fetch c1 into cname;
13 close c1;
14 return cname;
15 end;
16 /

Function created.

PRIVILEGES
SQL> grant SELECT on taj to SCOTT;

Grant succeeded.

TWO WAY TO CHANGE TABLE NAME
1. alter table [oldtablename] rename to [newtablename];
2. rename [oldtablename] to [newtablename];


SQL> rename TAJ to NEW_TAJ;

Table renamed.


SQL> select TABLE_NAME,CONSTRAINT_NAME
2 from user_constraints
3 where table_name in ('TAJ','NEW_TAJ');

TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
NEW_TAJ PK_NO
NEW_TAJ FK_NAME


SQL> select TABLE_NAME, INDEX_NAME
2 from user_indexes
3 where table_name in ('TAJ','NEW_TAJ');

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
NEW_TAJ PK_NO
NEW_TAJ IDX_NAME


SQL> column object_name format a20
SQL> ed
Wrote file afiedt.buf

1 select OBJECT_NAME,OBJECT_TYPE,STATUS
2 from user_objects
3 where object_type in ('PROCEDURE','FUNCTION','TRIGGER','VIEW','SYNONYM')
4* order by status
SQL> /

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
VW_TAJ VIEW INVALID
SY_TAJ SYNONYM INVALID
PRO_TAJ PROCEDURE INVALID
FUN_TAJ FUNCTION INVALID
TR_TAJ TRIGGER INVALID


Note: We have to recreate ALL INVALID OBJECTS (MENTION IN ABOVE QUERY)when table name is changed