Table Rename Oracle
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Table Rename Oracle as PDF for free.
More details
- Words: 358
- Pages: 1
Renaming a Table
Administration Tips
Renaming a Table The basic command to do this is: ALTER TABLE SCHEMA.TABLE_NAME RENAME TO SCHEMA.NEW_TABLE_NAME;
That command is guaranteed to work only in Oracle 8i and 9i -you'll have to experiment to see if it works on 8.0 or earlier versions. Issuing this command does not invalidate any indexes, constraints or permissions granted on the original table. For example, if Fred has been granted select privileges on the EMP table, and the EMP table is then renamed to be EMPLOYEES, Fred will now be able to select from EMPLOYEES without further grants being needed. Whilst indexes and constraints are all still valid and useable after a table is renamed, they retain their original names. For example, if you created a constraint called EMP_ID_PK, it will still be called that, even though it is now a constraint on the EMPLOYEES table. Similarly, an index called EMP_PK retains that name, regardless of the fact that the table it is an index of has changed its name. You might therefore want to issue the command: ALTER INDEX SCOTT.EMP_PK RENAME TO SCOTT.EMPLOYEES_PK;
However, there is no rename command for constraints, and hence they must retain their original names, unless you drop them and re-create them. Finally, renaming a table means performing DDL on a table -and performing DDL to any table renders all procedures, packages and triggers that reference the table invalid. You can check that by looking at the STATUS column of the DBA_OBJECTS view. All invalid packages, procedures and triggers are automatically recompiled the first time they are called after their invalidation (subject to some provisos), but you can also manually recompile them if you wish: ALTER TRIGGER BLAH COMPILE;, for example. Incidentally, if you query DBA_TRIGGERS (as an example), you can see the code making up a particular trigger (in the TRIGGER_BODY column) -and you'll discover that what was once typed in as 'create trigger blah before update on emp' automatically gets re-jigged by Oracle to read '...update on employees'. The rename command therefore even re-writes your code for you, which is nice!
Copyright © Howard Rogers 2001
10/17/2001
Page 1 of 1
Administration Tips
Renaming a Table The basic command to do this is: ALTER TABLE SCHEMA.TABLE_NAME RENAME TO SCHEMA.NEW_TABLE_NAME;
That command is guaranteed to work only in Oracle 8i and 9i -you'll have to experiment to see if it works on 8.0 or earlier versions. Issuing this command does not invalidate any indexes, constraints or permissions granted on the original table. For example, if Fred has been granted select privileges on the EMP table, and the EMP table is then renamed to be EMPLOYEES, Fred will now be able to select from EMPLOYEES without further grants being needed. Whilst indexes and constraints are all still valid and useable after a table is renamed, they retain their original names. For example, if you created a constraint called EMP_ID_PK, it will still be called that, even though it is now a constraint on the EMPLOYEES table. Similarly, an index called EMP_PK retains that name, regardless of the fact that the table it is an index of has changed its name. You might therefore want to issue the command: ALTER INDEX SCOTT.EMP_PK RENAME TO SCOTT.EMPLOYEES_PK;
However, there is no rename command for constraints, and hence they must retain their original names, unless you drop them and re-create them. Finally, renaming a table means performing DDL on a table -and performing DDL to any table renders all procedures, packages and triggers that reference the table invalid. You can check that by looking at the STATUS column of the DBA_OBJECTS view. All invalid packages, procedures and triggers are automatically recompiled the first time they are called after their invalidation (subject to some provisos), but you can also manually recompile them if you wish: ALTER TRIGGER BLAH COMPILE;, for example. Incidentally, if you query DBA_TRIGGERS (as an example), you can see the code making up a particular trigger (in the TRIGGER_BODY column) -and you'll discover that what was once typed in as 'create trigger blah before update on emp' automatically gets re-jigged by Oracle to read '...update on employees'. The rename command therefore even re-writes your code for you, which is nice!
Copyright © Howard Rogers 2001
10/17/2001
Page 1 of 1
Related Documents
Table Rename Oracle
May 2020 3
Column Rename 9i Oracle
May 2020 12
Rename Account
May 2020 1
One Table Space Oracle
May 2020 6
Copy And Rename An Oracle Database
May 2020 14