{"id":591,"date":"2012-02-23T23:55:12","date_gmt":"2012-02-23T15:55:12","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=591"},"modified":"2012-02-25T10:55:40","modified_gmt":"2012-02-25T02:55:40","slug":"ddl%e8%a7%a6%e5%8f%91%e5%99%a8%e8%ae%be%e7%bd%ae%e5%af%bc%e8%87%b4ddl%e6%97%a0%e6%b3%95%e6%89%a7%e8%a1%8c%ef%bc%88%e4%b8%80%ef%bc%89","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=591","title":{"rendered":"DDL\u89e6\u53d1\u5668\u8bbe\u7f6e\u5bfc\u81f4DDL\u65e0\u6cd5\u6267\u884c\uff08\u4e00\uff09"},"content":{"rendered":"<p>\u516c\u53f8\u6d4b\u8bd5\u6570\u636e\u5e93\u53d1\u73b0\u6267\u884cDDL\u62a5\u9519\u3002<br \/>\n\u7531\u4e8e\u7bc7\u5e45\u6240\u9650\uff0c\u8fd9\u91cc\u7b80\u5355\u63cf\u8ff0\u4e00\u4e0b\u95ee\u9898\u4ea7\u751f\u7684\u73b0\u8c61\u3002<br \/>\n\u6253\u7b97\u8fdb\u884c\u4e2a\u6d4b\u8bd5\uff0c\u7ed3\u679c\u53d1\u73b0\u5efa\u8868\u65f6\u62a5\u9519\uff1a<\/p>\n<pre lang='SQL'>SQL> CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))\r\n   2 PARTITION BY RANGE (CREATED) SUBPARTITION BY LIST (TYPE)\r\n   3 (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1', 'YYYY-MM'))\r\n   4 (SUBPARTITION P1SP1 VALUES ('TABLE'),\r\n   5 SUBPARTITION P1SP2 VALUES ('INDEX'),\r\n   6 SUBPARTITION P1SP3 VALUES ('VIEW'),\r\n   7 SUBPARTITION P1SP4 VALUES ('SYNONYM'),\r\n   8 SUBPARTITION P1SP5 VALUES (DEFAULT)), \r\n   9 PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2', 'YYYY-MM'))\r\n  10 (SUBPARTITION P2SP1 VALUES ('TABLE'),\r\n  11 SUBPARTITION P2SP2 VALUES ('INDEX'),\r\n  12 SUBPARTITION P2SP3 VALUES ('VIEW'),\r\n  13 SUBPARTITION P2SP4 VALUES ('SYNONYM'),\r\n  14 SUBPARTITION P2SP5 VALUES (DEFAULT)),\r\n  15 PARTITION P3 VALUES LESS THAN (MAXVALUE)\r\n  16 (SUBPARTITION P3SP1 VALUES ('TABLE'),\r\n  17 SUBPARTITION P3SP2 VALUES ('INDEX'),\r\n  18 SUBPARTITION P3SP3 VALUES ('VIEW'),\r\n  19 SUBPARTITION P3SP4 VALUES ('SYNONYM'),\r\n  20 SUBPARTITION P3SP5 VALUES (DEFAULT)));\r\nCREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))\r\n*\r\nERROR at line 1:\r\nORA-00604: error occurred at recursive SQL level 1\r\nORA-04020: deadlock detected while trying to lock object\r\nEYGLE.BIN$trcEn8qthIjgQKjAEwAm+g==$0\r\nORA-06512: at line 24\r\n\r\nSQL> CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))\r\n   2 PARTITION BY RANGE (CREATED) SUBPARTITION BY LIST (TYPE)\r\n   3 (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1', 'YYYY-MM'))\r\n   4 (SUBPARTITION P1SP1 VALUES ('TABLE'),\r\n   5 SUBPARTITION P1SP2 VALUES ('INDEX'),\r\n   6 SUBPARTITION P1SP3 VALUES ('VIEW'),\r\n   7 SUBPARTITION P1SP4 VALUES ('SYNONYM'),\r\n   8 SUBPARTITION P1SP5 VALUES (DEFAULT)), \r\n   9 PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2', 'YYYY-MM'))\r\n  10 (SUBPARTITION P2SP1 VALUES ('TABLE'),\r\n  11 SUBPARTITION P2SP2 VALUES ('INDEX'),\r\n  12 SUBPARTITION P2SP3 VALUES ('VIEW'),\r\n  13 SUBPARTITION P2SP4 VALUES ('SYNONYM'),\r\n  14 SUBPARTITION P2SP5 VALUES (DEFAULT)),\r\n  15 PARTITION P3 VALUES LESS THAN (MAXVALUE)\r\n  16 (SUBPARTITION P3SP1 VALUES ('TABLE'),\r\n  17 SUBPARTITION P3SP2 VALUES ('INDEX'),\r\n  18 SUBPARTITION P3SP3 VALUES ('VIEW'),\r\n  19 SUBPARTITION P3SP4 VALUES ('SYNONYM'),\r\n  20 SUBPARTITION P3SP5 VALUES (DEFAULT)));\r\nCREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))\r\n*\r\nERROR at line 1:\r\nORA-00604: error occurred at recursive SQL level 2\r\nORA-30511: invalid DDL operation in system triggers\r\nORA-06512: at line 24<\/pre>\n<p>\u524d\u540e\u4e24\u6b21\u7684\u62a5\u9519\u4fe1\u606f\u8fd8\u4e0d\u4e00\u6837\uff0c\u800c\u4e14\u4e8c\u8005\u5305\u542b\u7684\u4fe1\u606f\u90fd\u6709\u610f\u4e49\u3002\u4ece\u7b2c\u4e00\u6b21\u6267\u884c\u53ef\u4ee5\u770b\u51fa\uff0c\u6267\u884cDDL\u64cd\u4f5c\u5f15\u53d1\u4e86ORA-4020\u6b7b\u9501\uff0c\u800c\u7b2c\u4e8c\u6b21\u5219\u8868\u793a\u5bfc\u81f4\u9519\u8bef\u51fa\u73b0\u7684\u56e0\u7d20\u548cDDL\u89e6\u53d1\u5668\u6709\u5173\u3002<br \/>\n\u7531\u4e8e\u662f\u6d4b\u8bd5\u73af\u5883\uff0c\u90e8\u7f72\u7684\u73af\u5883\u6bd4\u8f83\u590d\u6742\uff0c\u5f88\u53ef\u80fd\u662f\u5176\u4ed6\u7ec4\u4ef6\u6216\u8005\u67d0\u4e9b\u6d4b\u8bd5\u4ee3\u7801\u5bfc\u81f4DDL\u89e6\u53d1\u5668\u51fa\u73b0\u9519\u8bef\u3002<br \/>\n\u68c0\u67e5\u4e86\u4e00\u4e0b\u53d1\u751f\u6b7b\u9501\u65f6\u62a5\u9519\u5bf9\u8c61\uff0c\u8fd9\u662f\u56de\u6536\u7ad9\u4e2d\u7684\u4e00\u4e2a\u5bf9\u8c61\uff1a<\/p>\n<pre lang='SQL'>SQL> select owner, object_name, original_name, operation, type  \r\n  2  from dba_recyclebin\r\n  3  where object_name = 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0';\r\nOWNER OBJECT_NAME                    ORIGINAL_NAME OPERATION TYPE\r\n----- ------------------------------ ------------- --------- -----\r\nEYGLE BIN$trcI7ykLAu7gQKjAEwAnkA==$0 T_PWD         DROP      TABLE\r\nSQL> select * from dba_dependencies where type = 'TRIGGER' and REFERENCED_NAME in ('T_PWD', 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0');\r\nno rows selected\r\nSQL> select * from dba_dependencies where  REFERENCED_NAME in ('T_PWD', 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0');\r\nno rows selected<\/pre>\n<p>\u7cfb\u7edf\u4e2d\u6ca1\u6709\u4efb\u4f55\u5bf9\u8c61\u4f9d\u8d56\u4e8e\u56de\u6536\u7ad9\u4e2d\u7684\u8fd9\u4e2a\u5bf9\u8c61\uff0c\u751a\u81f3\u6ca1\u6709\u4efb\u4f55\u5bf9\u8c61\u4f9d\u8d56\u8fd9\u4e2a\u56de\u6536\u7ad9\u5bf9\u8c61\u5220\u9664\u524d\u7684\u539f\u59cb\u5bf9\u8c61\u3002<\/p>\n<pre lang='SQL'>SQL> SELECT OWNER, TRIGGERING_EVENT, COUNT(*) FROM DBA_TRIGGERS GROUP BY OWNER, TRIGGERING_EVENT ORDER BY 1;\r\nOWNER                          TRIGGERING_EVENT                           COUNT(*)\r\n------------------------------ ---------------------------------------- ----------\r\nDBFW_CONSOLE_ACCESS            DDL                                               1\r\nDBFW_CONSOLE_ACCESS            LOGOFF                                            1\r\nDBFW_CONSOLE_ACCESS            LOGON                                             1\r\nEXFSYS                         ALTER OR RENAME                                   1\r\nEXFSYS                         CREATE OR ALTER                                   1\r\nEXFSYS                         DROP                                              2\r\nEXFSYS                         TRUNCATE                                          1\r\nMDSYS                          CREATE                                            1\r\nMDSYS                          DELETE                                            8\r\nMDSYS                          DROP                                              7\r\nMDSYS                          INSERT                                            9\r\nMDSYS                          INSERT OR UPDATE                                  3\r\nMDSYS                          INSERT OR UPDATE OR DELETE                        3\r\nMDSYS                          TRUNCATE                                          1\r\nMDSYS                          UPDATE                                            6\r\nOLAPSYS                        DELETE                                            8\r\nOLAPSYS                        INSERT OR UPDATE                                 40\r\nSYS                            ALTER                                             1\r\nSYS                            CREATE                                            2\r\nSYS                            DROP                                              2\r\nSYS                            SHUTDOWN                                          2\r\nSYS                            STARTUP                                           2\r\nSYSMAN                         DELETE                                           16\r\nSYSMAN                         INSERT                                           18\r\nSYSMAN                         INSERT OR UPDATE                                  6\r\nSYSMAN                         INSERT OR UPDATE OR DELETE                        1\r\nSYSMAN                         UPDATE                                            6\r\nSYSMAN                         UPDATE OR DELETE                                  1\r\nSYSTEM                         INSERT                                            1\r\nSYSTEM                         UPDATE OR DELETE                                  1\r\nTEST                           INSERT OR UPDATE OR DELETE                        1\r\nWMSYS                          CREATE OR ALTER OR DROP OR RENAME                 1\r\nWMSYS                          DROP                                              1\r\nXDB                            DROP OR TRUNCATE                                  1\r\nXDB                            INSERT OR UPDATE                                  1\r\nXDB                            INSERT OR UPDATE OR DELETE                        2\r\nXDB                            UPDATE OR DELETE                                  8\r\n37 rows selected.<\/pre>\n<p>\u7cfb\u7edf\u4e2d\u53ea\u6709\u4e00\u4e2aDDL\u89e6\u53d1\u5668\uff0c\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<pre lang='SQL'>SQL> select trigger_body from dba_triggers\r\n   2 where trigger_name = 'TRIGGER_LOGIN';\r\nTRIGGER_BODY\r\n--------------------------------------------------------------------------------\r\nbegin\r\nif dbfw_console_access.is_local then\r\ninsert into dbfw_console_access.event(id,username,sessionid,event,text)\r\nselect dbfw_console_access.event_seq.nextval,\r\nsys_context('USERENV','SESSION_USER'),\r\nsys_context('USERENV','SESSIONID'),\r\n'LOGIN',\r\nnull\r\nfrom dual;\r\nend if;\r\nend;<\/pre>\n<p>\u6709\u610f\u601d\u7684\u65f6\uff0c\u56de\u6536\u7ad9\u4e2d\u62a5\u9519\u7684\u8868\u662fEygle\u6d4b\u8bd5\u5bc6\u7801\u7684\u4e34\u65f6\u8868\uff0c\u4f7f\u7528\u5b8c\u6bd5\u540e\u88ab\u4ed6\u5220\u9664\u3002\u800c\u8fd9\u4e2a\u89e6\u53d1\u5668\u662fKamus\u6d4b\u8bd5FireWall\u529f\u80fd\u521b\u5efa\u7684\u3002\u800c\u5f53\u6211\u6267\u884cDDL\u65f6\uff0c\u4e24\u4e2a\u5b8c\u5168\u6ca1\u6709\u5173\u7cfb\u7684\u5bf9\u8c61\u7ec4\u5408\u5728\u4e00\u8d77\u62a5\u9519\u3002<br \/>\nEygle\u521b\u5efa\u5e76\u5220\u9664\u7684\u8868\u672c\u8eab\u5e76\u6ca1\u6709\u4ec0\u4e48\u7279\u6b8a\u4e4b\u5904\uff0c\u800c\u4e14\u5df2\u7ecf\u5728\u56de\u6536\u7ad9\u4e2d\uff0c\u5c31\u66f4\u4e0d\u4f1a\u5bf9\u7cfb\u7edf\u6709\u4ec0\u4e48\u989d\u5916\u7684\u5f71\u54cd\u3002\u76f8\u6bd4\u8f83\uff0cKamus\u521b\u5efa\u7684\u89e6\u53d1\u5668\u5c31\u6bd4\u8f83\u53ef\u7591\u4e86\uff0c\u6bd5\u7adf\u8fd9\u662f\u4e00\u4e2aDDL\u89e6\u53d1\u5668\uff0c\u5728\u6267\u884cDDL\u8bed\u53e5\u65f6\u5c31\u4f1a\u89e6\u53d1\uff0c\u95ee\u9898\u591a\u534a\u662f\u8fd9\u4e2a\u89e6\u53d1\u5668\u5bfc\u81f4\u7684\u3002\u4f46\u662f\u8fd9\u4e2a\u89e6\u53d1\u5668\u5b9e\u8d28\u4e0a\u53ea\u6709\u4e00\u4e2aINSERT\u8bed\u53e5\uff0c\u6ca1\u6709\u9053\u7406\u5bfc\u81f4\u6b7b\u9501\u7684\u4ea7\u751f\uff0c\u4f55\u51b5\u89e6\u53d1\u5668\u548c\u56de\u6536\u7ad9\u4e2d\u7684\u5bf9\u8c61\u5b8c\u5168\u6ca1\u6709\u4efb\u4f55\u8054\u7cfb\u3002<br \/>\n\u7b80\u5355\u7684\u7981\u7528\u6216\u5220\u9664\u89e6\u53d1\u5668\u540c\u6837\u4f1a\u5f15\u53d1\u9519\u8bef\uff1a<\/p>\n<pre lang='SQL'>SQL> conn \/ as sysdba\r\nConnected.\r\nSQL> alter trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN disable;\r\nalter trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN disable\r\n*\r\nERROR at line 1:\r\nORA-00604: error occurred at recursive SQL level 1\r\nORA-30511: invalid DDL operation in system triggers\r\nORA-06512: at line 24\r\nSQL> drop trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN;\r\ndrop trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN\r\n*\r\nERROR at line 1:\r\nORA-00604: error occurred at recursive SQL level 1\r\nORA-30511: invalid DDL operation in system triggers\r\nORA-06512: at line 24<\/pre>\n<p>\u770b\u6765\u95ee\u9898\u4e0d\u50cf\u60f3\u8c61\u4e2d\u7684\u90a3\u4e48\u7b80\u5355\uff0c\u5fc5\u987b\u627e\u5230\u95ee\u9898\u7684\u539f\u56e0\u624d\u53ef\u4ee5\u5f7b\u5e95\u89e3\u51b3\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u516c\u53f8\u6d4b\u8bd5\u6570\u636e\u5e93\u53d1\u73b0\u6267\u884cDDL\u62a5\u9519\u3002 \u7531\u4e8e\u7bc7\u5e45\u6240\u9650\uff0c\u8fd9\u91cc\u7b80\u5355\u63cf\u8ff0\u4e00\u4e0b\u95ee\u9898\u4ea7\u751f\u7684\u73b0\u8c61\u3002 \u6253\u7b97\u8fdb\u884c\u4e2a\u6d4b\u8bd5\uff0c\u7ed3\u679c\u53d1\u73b0\u5efa\u8868\u65f6\u62a5\u9519\uff1a SQL> CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18)) 2 PARTITION BY RANGE (CREATED) SUBPARTITION BY LIST (TYPE) 3 (PARTITION P1 VALUES LESS THAN (TO_DATE(&#8216;2012-1&#8217;, &#8216;YYYY-MM&#8217;)) 4 (SUBPARTITION P1SP1 VALUES (&#8216;TABLE&#8217;), 5 SUBPARTITION P1SP2 VALUES (&#8216;INDEX&#8217;), &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=591\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[482,481,484,485,483],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-9x","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/591"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=591"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/591\/revisions"}],"predecessor-version":[{"id":592,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/591\/revisions\/592"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}