Database Reference
In-Depth Information
3.
Place the contents of the old table into the interim table:
EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE
('MUSIC','<
original table
>','<
interim table
>',NULL);
4.
Add constraints you want to the interim table, such as foreign
keys, using the ALTER TABLE command. These will be carried
over to the new, restructured table.
5.
Remove the original table, rename the interim table, and clean up
other intermediate structures used behind the scenes:
EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE
('MUSIC','<
original table
>','<
interim table
>');
Note:
DBMS_REDEFINITION works for purely relational tables only,
not object tables or relational tables containing object columns. Addition-
ally, DBMS_REDEFINITION requires special privileges.
Explicit details and examples are not given for the
DBMS_REDEFINITION package, because it is not precisely Oracle SQL
and perhaps a little beyond the scope of this topic.
18.4.3
Renaming a Table
Take another quick peek at the ALTER TABLE syntax diagram in Figure
18.18. Can you see the RENAME TABLE section? Let's rename the table
called RELEASESIN2001 that we created in the previous section.
ALTER TABLE RELEASESIN2001 RENAME TO NEW_RELEASES_FOR_2001;
Using the following commands, you can see that the table
RELEASESIN2001 no longer exists and has been renamed to
NEW_RELEASES_FOR_2001. See the result in Figure 18.22. The first
DESC command produced an error.
DESC RELEASEIN2001
DESC NEW_RELEASES_FOR_2001
That covers changing table structure. Next we examine dropping and
truncating tables.
