User Tools

Site Tools


renaming_a_table

Renaming a Table

Consider the case where you want to rename the table JOB_DESCRIPTION to JOB_INFORMATION. Using SQL*Plus (or any other tool of your choice) execute the following statement:

SQL>  ALTER TABLE job_description RENAME TO job_information;

Now you can use Lure to export the updated table source with the following result:

Export Output After Table Rename

Export Output After Table Rename

Lure logs that it removed the table source code for table JOB_DESCRIPTION from its source file. A new file, according to the new table name JOB_INFORMATION, was created. Lastly Lure created a patch file with the following content:

Generated Patch File After Table Rename

Lure detects that the table JOB_DESCRIPTION is not present in the database any more and that a new table with name JOB_INFORMATION was added. This generated patch file will succeed in the sense that the database will now contain a table with the right name. However all the data in the table will have been lost. Lure is not able to determine that the change was achieved by renaming of a table as this information is not stored as part of the table meta data within Oracle. See Table Changes and Automation for a more detailed discussion of this aspect.

This patch file has to be reviewed and corrected. In this case the user has to delete the DROP statement and replace it with the TABLE RENAME statement as follows:

Corrected Patch File For Table Rename

Note: No other changes are required, i.e. the source files JOB_DESCRIPTION.table and JOB_INFORMATION.table should be left as is.

Before committing this modified patch file to the version control system you have to test it first using Lure's Patch Test command.

The Patch Test command has the following output:

Patch Test Output For Table Rename

Lure finds the patch file with the smallest revision for the table (revision 63) and retrieves that version of the file JOB_DESCRIPTION.table from the version control system. Lure installs this older version of the table source file and then commences with normal deploy processing during which the single patch file is installed which renames the table. Lastly Lure compares the two source files JOB_DESCRIPTION.table and JOB_INFORMATION.table (as currently on the local file system) with the latest status of the corresponding table(s) in the database. Lure confirms that the patch file succeeded in “dropping” the table and “creating” a table with the correct new name.

You can now commit these latest changes (including the new patch file) to the version control system.

Note: Do not reference or attempt to alter any dependent constraints or indexes within this patch file. Lure will take care of those automatically.

renaming_a_table.txt · Last modified: 2013/07/31 14:47 by admin

Page Tools