{"id":951,"date":"2015-09-02T05:34:41","date_gmt":"2015-09-02T05:34:41","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=951"},"modified":"2015-10-07T04:06:40","modified_gmt":"2015-10-07T04:06:40","slug":"oracle-notes","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/oracle-notes\/","title":{"rendered":"Oracle Notes"},"content":{"rendered":"<p>When deleting a Table to run new script the following should be done.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDROP TABLE TABLE_NAME;\r\nDROP SEQUENCE SEQUENCE_NAME;\r\nDROP PUBLIC SYNONYM SYNONYM_NAME;\r\n<\/pre>\n<p>In case you are deleting multiple Table the same thing should be done with tables grouped together as one below.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDROP TABLE TABLE_NAME;\r\n.\r\n.\r\n.\r\n\r\nDROP SEQUENCE SEQUENCE_NAME;\r\n.\r\n.\r\n.\r\n\r\nDROP PUBLIC SYNONYM SYNONYM_NAME;\r\n.\r\n.\r\n.\r\n.\r\n<\/pre>\n<p>The Child Table referencing the Parent table should be deleted first.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n DROP TABLE CHILD_TABLE_NAME;\r\n DROP TABLE PARENT_TABLE_NAME;\r\n<\/pre>\n<p>Checking if the table column is referenced some where by Child Table in form of Primary Key<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT TABLE_NAME AS &quot;CHILD_TABLE&quot;\r\n       ,CONSTRAINT_NAME\r\n  FROM ALL_CONSTRAINTS T\r\n WHERE R_OWNER = 'OWNER_NAME'\r\n   AND CONSTRAINT_TYPE = 'R'\r\n   AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME\r\n                               FROM ALL_CONSTRAINTS\r\n                              WHERE CONSTRAINT_TYPE IN ('P', 'U')\r\n                                AND TABLE_NAME = 'TABLE_NAME'\r\n                                AND OWNER = 'OWNER_NAME')\r\n ORDER BY TABLE_NAME\r\n         ,CONSTRAINT_NAME;\r\n<\/pre>\n<p><strong>Sequence<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE SEQUENCE TEST_SEQ\r\nMINVALUE 1\r\nMAXVALUE 999999999999999999999999999\r\nSTART WITH 1\r\nINCREMENT BY 1\r\nCACHE 20;\r\n<\/pre>\n<p><strong>Audit Trigger<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE TRIGGER TRIGGER_NAME\r\nBEFORE INSERT OR UPDATE ON TABLE_NAME\r\nREFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW\r\nBEGIN\r\n  IF INSERTING THEN\r\n    :NEW.CRE_USER_UID := USERUID;\r\n    :NEW.CRE_TIMESTAMP := SYSDATE;\r\n  ELSIF UPDATING THEN\r\n    :NEW.UPD_USER_UID := USERUID;\r\n    :NEW.UPD_TIMESTAMP := SYSDATE;\r\n  END IF;\r\n\/\r\nEND TRIGGER_NAME;\r\n<\/pre>\n<p><em>Note the \/ in the End of Trigger<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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; . . .&hellip; <a href=\"https:\/\/codethataint.com\/blog\/oracle-notes\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[165],"tags":[],"class_list":["post-951","post","type-post","status-publish","format-standard","hentry","category-oracle-notes"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/951","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=951"}],"version-history":[{"count":8,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/951\/revisions"}],"predecessor-version":[{"id":953,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/951\/revisions\/953"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=951"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=951"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=951"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}