Existing FK Constraints

Rename Constraints

Rename Table

Check FK Constraints

Note that all the FK is referencing the old PK

Renaming table, constraints, and indexes

DESC TABLE

Check Constraints

Check Indexes

Cannot prefix schema owner when renaming index

alter session set current_schema=

Rename Constraints

Rename Indexes

Rename table

ROLLBACK

Constraints

Indexes

Table

Check Constraints

Check Indexes

Existing FK Constraints

LAX:(SYS@db01)> select constraint_name,constraint_type,owner||'.'||table_name tn,r_constraint_name,status,bad,rely,validated,index_name,delete_rule

  2  from dba_constraints

  3  where r_owner='HR'

  4  and r_constraint_name in

  5  (select constraint_name from dba_constraints where owner='HR' and table_name='EMPLOYEES');

CONSTRAINT_NAME                C TN                   R_CONSTRAINT_NAME              STATUS   BAD RELY VALIDATED     INDEX_NAME                     DELETE_RU

------------------------------ - -------------------- ------------------------------ -------- --- ---- ------------- ------------------------------ ---------

DEPT_MGR_FK                    R HR.DEPARTMENTS       EMP_EMP_ID_PK                  DISABLED          NOT VALIDATED                                NO ACTION

EMP_MANAGER_FK                 R HR.EMPLOYEES         EMP_EMP_ID_PK                  ENABLED           VALIDATED                                    NO ACTION

JHIST_EMP_FK                   R HR.JOB_HISTORY       EMP_EMP_ID_PK                  ENABLED           VALIDATED                                    NO ACTION

Rename Constraints

LAX:(SYS@db01)> alter session set current_schema=HR;

Session altered.

LAX:(SYS@db01)> set serverout on

declare

  l_sql varchar2(2000);

LAX:(SYS@db01)>   2    3  begin

  4    for x in (

  5     select table_name nm, constraint_name cn

  6     from dba_constraints

  7     where constraint_type in ('P','U','R')

  8     and owner='HR'

  9      and table_name in ('EMPLOYEES')

 10      and constraint_name not like '%$'

 11    ) loop

 12    l_sql := 'alter table '||x.nm||' rename constraint '||x.cn||' to '||x.cn||'$';

 13    dbms_output.put_line(l_sql);

 14    execute immediate l_sql;

 15    end loop;

 16  end;

 17  /

alter table EMPLOYEES rename constraint EMP_EMAIL_UK to EMP_EMAIL_UK$

alter table EMPLOYEES rename constraint EMP_EMP_ID_PK to EMP_EMP_ID_PK$

alter table EMPLOYEES rename constraint EMP_JOB_FK to EMP_JOB_FK$

alter table EMPLOYEES rename constraint EMP_MANAGER_FK to EMP_MANAGER_FK$

alter table EMPLOYEES rename constraint EMP_DEPT_FK to EMP_DEPT_FK$

PL/SQL procedure successfully completed.

Rename Table

LAX:(SYS@db01)> alter table EMPLOYEES rename to EMPLOYEES$;

Table altered.

Check FK Constraints

Note that all the FK is referencing the old PK

LAX:(SYS@db01)> select constraint_name,constraint_type,owner||'.'||table_name tn,r_constraint_name,status,bad,rely,validated,index_name,delete_rule

from dba_constraints

  2    3  where r_owner='HR'

  4  and r_constraint_name in

  5  (select constraint_name from dba_constraints where owner='HR' and table_name='EMPLOYEES$');

CONSTRAINT_NAME                C TN                   R_CONSTRAINT_NAME              STATUS   BAD RELY VALIDATED     INDEX_NAME                     DELETE_RU

------------------------------ - -------------------- ------------------------------ -------- --- ---- ------------- ------------------------------ ---------

EMP_MANAGER_FK$                R HR.EMPLOYEES$        EMP_EMP_ID_PK$                 ENABLED           VALIDATED                                    NO ACTION

DEPT_MGR_FK                    R HR.DEPARTMENTS       EMP_EMP_ID_PK$                 DISABLED          NOT VALIDATED                                NO ACTION

JHIST_EMP_FK                   R HR.JOB_HISTORY       EMP_EMP_ID_PK$                 ENABLED           VALIDATED                                    NO ACTION

LAX:(SYS@db01)>

Renaming table, constraints, and indexes

DESC TABLE

LAX:(SYS@db01)> desc hr.employees

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)

 LAST_UPDATED                              NOT NULL DATE

 FIRST_NAME                                         VARCHAR2(20)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

 EMAIL                                     NOT NULL VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                                 NOT NULL DATE

 JOB_ID                                    NOT NULL VARCHAR2(10)

 SALARY                                             NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                         NUMBER(6)

 DEPARTMENT_ID                                      NUMBER(4)

Check Constraints

LAX:(SYS@db01)> set lines 100

LAX:(SYS@db01)> col tn for a20

LAX:(SYS@db01)> col cn for a20

LAX:(SYS@db01)> col old_name for a20

LAX:(SYS@db01)>         select table_name nm, constraint_name cn

        from dba_constraints

        where constraint_type in ('P','U','R')

        and owner='HR'

    and table_name in ('EMPLOYEES')

    and constraint_name not like '%$'

  2    3    4    5    6    7  ;

NM                             CN

------------------------------ --------------------

EMPLOYEES                      EMP_EMAIL_UK

EMPLOYEES                      EMP_EMP_ID_PK

EMPLOYEES                      EMP_JOB_FK

EMPLOYEES                      EMP_MANAGER_FK

EMPLOYEES                      EMP_DEPT_FK

Check Indexes

LAX:(SYS@db01)>         select index_name nm

        from dba_indexes

        where index_name not like '%$'

    and table_owner='HR'

    and table_name in ('EMPLOYEES')

  2    3    4    5    6  ;

NM

------------------------------

EMP_EMAIL_UK

EMP_EMP_ID_PK

EMP_DEPARTMENT_IX

EMP_JOB_IX

EMP_MANAGER_IX

EMP_NAME_IX

6 rows selected.

Cannot prefix schema owner when renaming index

LAX:(SYS@db01)> alter index HR.EMP_EMAIL_UK rename to HR.EMP_EMAIL_UK$;

alter index HR.EMP_EMAIL_UK rename to HR.EMP_EMAIL_UK$

                                        *

ERROR at line 1:

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

alter session set current_schema=

LAX:(SYS@db01)> alter session set current_schema=HR;

Session altered.

Rename Constraints

LAX:(SYS@db01)> set serverout on

declare

  l_sql varchar2(2000);

begin

  for x in (

        select table_name nm, constraint_name cn

        from dba_constraints

        where constraint_type in ('P','U','R')

        and owner='HR'

LAX:(SYS@db01)>   2    3    4    5    6    7      and table_name in ('EMPLOYEES')

  8    9   10      and constraint_name not like '%$'

 11    ) loop

 12    l_sql := 'alter table '||x.nm||' rename constraint '||x.cn||' to '||x.cn||'$';

 13    dbms_output.put_line(l_sql);

 14    execute immediate l_sql;

 15    end loop;

 16  end;

 17  /

alter table EMPLOYEES rename constraint EMP_EMAIL_UK to EMP_EMAIL_UK$

alter table EMPLOYEES rename constraint EMP_EMP_ID_PK to EMP_EMP_ID_PK$

alter table EMPLOYEES rename constraint EMP_JOB_FK to EMP_JOB_FK$

alter table EMPLOYEES rename constraint EMP_MANAGER_FK to EMP_MANAGER_FK$

alter table EMPLOYEES rename constraint EMP_DEPT_FK to EMP_DEPT_FK$

PL/SQL procedure successfully completed.

Rename Indexes

LAX:(SYS@db01)> set serverout on

declare

LAX:(SYS@db01)>   2    l_sql varchar2(2000);

begin

  3    4    for x in (

  5     select index_name nm

  6     from dba_indexes

  7     where index_name not like '%$'

  8      and table_owner='HR'

  9      and table_name in ('EMPLOYEES')

 10    ) loop

 11    l_sql := 'alter index '||x.nm||' rename to '||x.nm||'$';

 12    dbms_output.put_line(l_sql);

 13    execute immediate l_sql;

 14    end loop;

 15  end;

 16  /

alter index EMP_EMAIL_UK rename to EMP_EMAIL_UK$

alter index EMP_EMP_ID_PK rename to EMP_EMP_ID_PK$

alter index EMP_DEPARTMENT_IX rename to EMP_DEPARTMENT_IX$

alter index EMP_JOB_IX rename to EMP_JOB_IX$

alter index EMP_MANAGER_IX rename to EMP_MANAGER_IX$

alter index EMP_NAME_IX rename to EMP_NAME_IX$

PL/SQL procedure successfully completed.

Rename table

LAX:(SYS@db01)> alter table EMPLOYEES rename to EMPLOYEES$;

Table altered.

LAX:(SYS@db01)>

ROLLBACK

Constraints

LAX:(SYS@db01)> set serverout on

LAX:(SYS@db01)> declare

  2    l_sql varchar2(2000);

  3  begin

  4    for x in (

  5     select owner||'.'||table_name nm, constraint_name cn, substr(constraint_name,1,length(constraint_name)-1) old_name

        from dba_constraints

  6    7        where constraint_type in ('P','U','R')

  8     and owner='HR'

  9      and table_name in ('EMPLOYEES$')

 10      and constraint_name like '%$'

 11    ) loop

 12    l_sql := 'alter table '||x.nm||' rename constraint '||x.cn||' to '||x.old_name;

 13    dbms_output.put_line(l_sql);

 14    execute immediate l_sql;

 15    end loop;

 16  end;

 17  /

alter table HR.EMPLOYEES$ rename constraint EMP_EMAIL_UK$ to EMP_EMAIL_UK

alter table HR.EMPLOYEES$ rename constraint EMP_EMP_ID_PK$ to EMP_EMP_ID_PK

alter table HR.EMPLOYEES$ rename constraint EMP_JOB_FK$ to EMP_JOB_FK

alter table HR.EMPLOYEES$ rename constraint EMP_MANAGER_FK$ to EMP_MANAGER_FK

alter table HR.EMPLOYEES$ rename constraint EMP_DEPT_FK$ to EMP_DEPT_FK

PL/SQL procedure successfully completed.

Indexes

declare

LAX:(SYS@db01)>   2    l_sql varchar2(2000);

  3  begin

  for x in (

  4    5        select index_name nm, substr(index_name,1,length(index_name)-1) old_name

  6     from dba_indexes

  7     where index_name like '%$'

  8      and table_owner='HR'

  9      and table_name in ('EMPLOYEES$')

 10    ) loop

 11    l_sql := 'alter index '||x.nm||' rename to '||x.old_name;

 12    dbms_output.put_line(l_sql);

 13    execute immediate l_sql;

 14    end loop;

 15  end;

 16  /

alter index EMP_EMAIL_UK$ rename to EMP_EMAIL_UK

alter index EMP_EMP_ID_PK$ rename to EMP_EMP_ID_PK

alter index EMP_DEPARTMENT_IX$ rename to EMP_DEPARTMENT_IX

alter index EMP_JOB_IX$ rename to EMP_JOB_IX

alter index EMP_MANAGER_IX$ rename to EMP_MANAGER_IX

alter index EMP_NAME_IX$ rename to EMP_NAME_IX

PL/SQL procedure successfully completed.

Table

LAX:(SYS@db01)> alter table EMPLOYEES$ rename to EMPLOYEES;

Table altered.

LAX:(SYS@db01)>

Check Constraints

LAX:(SYS@db01)>         select table_name nm, constraint_name cn

        from dba_constraints

        where constraint_type in ('P','U','R')

        and owner='HR'

    and table_name in ('EMPLOYEES')

    and constraint_name not like '%$'

  2    3    4    5    6    7  ;

NM                             CN

------------------------------ --------------------

EMPLOYEES                      EMP_EMAIL_UK

EMPLOYEES                      EMP_EMP_ID_PK

EMPLOYEES                      EMP_JOB_FK

EMPLOYEES                      EMP_MANAGER_FK

EMPLOYEES                      EMP_DEPT_FK

Check Indexes

LAX:(SYS@db01)>         select index_name nm

        from dba_indexes

        where index_name not like '%$'

    and table_owner='HR'

    and table_name in ('EMPLOYEES')

  2    3    4    5    6  ;

NM

------------------------------

EMP_EMAIL_UK

EMP_EMP_ID_PK

EMP_DEPARTMENT_IX

EMP_JOB_IX

EMP_MANAGER_IX

EMP_NAME_IX

6 rows selected.

LAX:(SYS@db01)>