In Schema Browser, right-click on a table and select Rebuild Table, generates a script generated to rebuild a table. The script works without any errors in Oracle 9i databases, but in 10g database the table is not created the index is not dropped.
The resulting SQL:
ALTER TABLE SUEUSER.TBLX DROP CONSTRAINT TBLX_PK DROP INDEX;
The drop index portion is only good with DROP PRIMARY KEY not DROP CONSTRAINT according to Oracle 10 documentation.
In database 10g the command Alter table <Table_Name> Drop constraint <Primary_Key_Constraint_Name>, drops only the primary key constraint and not the index. Where in 9i databases the same command drops the primary key and the index.
WORKAROUND:
A change was made to an internal functionality of Oracle 10g databases. Refer to Microsofts Metalink Doc ID: Note: 309821.1 - Oracle 10G Does not Drop User Index Associated With Unique/Primary Key Constraints:
https://www.metalink.oracle.com/metalink/plsql/f?p=130:14:1503844754392153430::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,309821.1,1,1,1,helvetica
When connected to a 10g database, the generated script in Toad does not have this command and the rebuild table script cannot be used to rebuild a table unless this command is issued separately.
ALTER TABLE <Table_Name> DROP CONSTRAINT <Primary_Key_Constraint_Name>
ALTER TABLE <Table_Name> DROP INDEX <Primary_Key_Index_Name>
STATUS:
An enhancement request has been submitted to Development for consideration in future release of Toad for Oracle.
