When deleting a Table to run new script the following should be done.
DROP TABLE TABLE_NAME; DROP SEQUENCE SEQUENCE_NAME; DROP PUBLIC SYNONYM SYNONYM_NAME;
In case you are deleting multiple Table the same thing should be done with tables grouped together as one below.
DROP TABLE TABLE_NAME; . . . DROP SEQUENCE SEQUENCE_NAME; . . . DROP PUBLIC SYNONYM SYNONYM_NAME; . . . .
The Child Table referencing the Parent table should be deleted first.
DROP TABLE CHILD_TABLE_NAME; DROP TABLE PARENT_TABLE_NAME;
Checking if the table column is referenced some where by Child Table in form of Primary Key
SELECT TABLE_NAME AS "CHILD_TABLE" ,CONSTRAINT_NAME FROM ALL_CONSTRAINTS T WHERE R_OWNER = 'OWNER_NAME' AND CONSTRAINT_TYPE = 'R' AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE IN ('P', 'U') AND TABLE_NAME = 'TABLE_NAME' AND OWNER = 'OWNER_NAME') ORDER BY TABLE_NAME ,CONSTRAINT_NAME;
Sequence
CREATE SEQUENCE TEST_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;
Audit Trigger
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE INSERT OR UPDATE ON TABLE_NAME REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF INSERTING THEN :NEW.CRE_USER_UID := USERUID; :NEW.CRE_TIMESTAMP := SYSDATE; ELSIF UPDATING THEN :NEW.UPD_USER_UID := USERUID; :NEW.UPD_TIMESTAMP := SYSDATE; END IF; / END TRIGGER_NAME;
Note the / in the End of Trigger