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

Comments are closed.