\n\t\u4e00\u3001\u56de\u6eda(ROLLBACK)\u548c\u64a4\u9500(UNDO)\n<\/p>\n
\n\t\t\t\t\t\t\t \u56de\u6eda\u548c\u524d\u6eda\u662f\u4fdd\u8bc1Oracle\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u5904\u4e8e\u4e00\u81f4\u6027\u72b6\u6001\u7684\u91cd\u8981\u624b\u6bb5\u3002 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u57289i\u7248\u672c\u4ee5\u524d\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Oracle\u4f7f\u7528\u6570\u636e\u5e93\u4e2d\u7684\u56de\u6eda\u6bb5\u6765\u5b9e\u73b0\u672a\u63d0\u4ea4\u6570\u636e\u6216\u56e0\u7cfb\u7edf\u6545\u969c\u5bfc\u81f4\u5b9e\u4f8b\u5d29\u6e83\u65f6\u8fdb\u884c\u56de\u6eda\u64cd\u4f5c\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u6bcf\u4e00\u4e2a\u8868\u7a7a\u95f4\u9700\u8981\u521b\u5efa\u56de\u6eda\u6bb5\uff0c\u5404\u4e2a\u8868\u7a7a\u95f4\u5bf9\u56de\u6eda\u6bb5\u5b9e\u73b0\u5404\u81ea\u7684\u7ba1\u7406\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u57289i\u53ca\u540e\u7eed\u7248\u672c\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u63d0\u4f9b\u4e86\u4e00\u79cd\u65b0\u7684\u56de\u6eda\u6570\u636e\u7684\u7ba1\u7406\u65b9\u5f0f\uff0c\u5373\u4f7f\u7528Oracle\u81ea\u52a8\u7ba1\u7406\u7684\u64a4\u9500(Undo)\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u81ea\u52a8\u64a4\u9500\u7ba1\u7406\u8868\u7a7a\u95f4\u7edf\u4e00\u7ba1\u7406\u6240\u6709DML\u7684\u56de\u6eda\u64cd\u4f5c\uff0c\u7b80\u5316\u4e86\u5bf9\u4e8e\u56de\u6eda\u5de5\u4f5c\u7684\u7ba1\u7406\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u57289i,10g\u4e2d\u7684\u56de\u6eda\u6bb5\u4ec5\u4ec5\u7528\u4f5c\u4fdd\u7559\u5411\u540e\u517c\u5bb9\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u64a4\u9500\u6bb5\u4ee3\u66ff\u4e86\u539f\u6709\u7248\u672c\u4e2d\u7684\u56de\u6eda\u6bb5\uff0c\u56e0\u6b64\u672c\u6587\u6240\u6709\u63cf\u8ff0\u5747\u4f7f\u7528\u64a4\u9500 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u64a4\u9500\u7684\u5b9e\u8d28\u610f\u5473\u7740\u5c06\u6240\u4f5c\u7684\u4fee\u6539\u9000\u56de\u5230\u4fee\u6539\u524d\u7684\u72b6\u6001\uff0c\u5373\u5012\u9000\u6240\u6709DML\u8bed\u53e5\u3002 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t\u4e8c\u3001\u64a4\u9500\u6bb5\u4e2d\u7684\u5185\u5bb9\u53ca\u76f8\u5173\u7279\u6027\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u5bf9\u4e8e\u4efb\u4f55DML\u64cd\u4f5c\u800c\u8a00\uff0c\u5fc5\u987b\u540c\u65f6\u5904\u7406\u6570\u636e\u5757\u548c\u64a4\u9500\u5757\uff0c\u5e76\u4e14\u8fd8\u4f1a\u751f\u6210\u91cd\u505a\u4fe1\u606f\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u5728ACID\u4e2d\uff0cA\u3001C\u3001I\u8981\u6c42\u751f\u6210\u64a4\u9500\uff0cD\u5219\u8981\u6c42\u751f\u6210\u91cd\u505a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INSERT\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u64a4\u9500\u6bb5\u8bb0\u5f55\u63d2\u5165\u8bb0\u5f55\u7684rowid,\u5982\u679c\u9700\u8981\u64a4\u9500\uff0c\u5219\u6839\u636erowid\u5c06\u8be5\u8bb0\u5f55\u5220\u9664\u5373\u53ef\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UPDATE:\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u64a4\u9500\u6bb5\u8bb0\u5f55\u88ab\u66f4\u65b0\u5b57\u6bb5\u7684\u539f\u59cb\u503c\uff0c\u64a4\u9500\u65f6\u5c06\u539f\u59cb\u503c\u8986\u76d6\u65b0\u503c\u5373\u53ef\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t DELETE\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u64a4\u9500\u6bb5\u8bb0\u5f55\u6574\u884c\u7684\u6570\u636e\uff0c\u64a4\u9500\u65f6\u6267\u884c\u53cd\u5411\u64cd\u4f5c\u5c06\u8be5\u8bb0\u5f55\u63d2\u5165\u539f\u8868\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u7531\u4e0a\u53ef\u77e5\uff0cUNDO\u6bb5\u4e2d\u7684\u5185\u5bb9\u603b\u7ed3\u5982\u4e0b\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u6570\u636e\u4e3a\u4fee\u6539\u4e4b\u524d\u7684\u526f\u672c\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4ece\u6bcf\u4e2a\u6539\u53d8\u6570\u636e\u7684\u4e8b\u52a1\u4e2d\u83b7\u5f97\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u5728\u4e8b\u52a1\u7ed3\u675f\u524d\u4e00\u76f4\u88ab\u4fdd\u7559 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u6bb5\u4e2d\u6570\u636e\u7684\u4f5c\u7528\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u7528\u4e8e\u56de\u6eda\u64cd\u4f5c\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u8bfb\u4e00\u81f4\u6027\u548c\u95ea\u56de\u67e5\u8be2\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u7528\u4e8e\u4e8b\u52a1\u5931\u8d25\u65f6\u7684\u6062\u590d \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u6bb5\u4e0e\u4e8b\u52a1\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4e00\u4e2a\u4e8b\u7269\u7684\u542f\u52a8\uff0cOracle\u5c06\u4e3a\u5176\u5206\u914d\u4ec5\u4ec5\u4e00\u4e2aUNDO\u6bb5,\u82e5\u8be5\u6bb5\u7528\u5b8c,\u5219Oracle\u4f1a\u81ea\u52a8\u4e3a\u8be5UNDO\u6bb5\u6dfb\u52a0\u53e6\u4e00\u4e2a\u533a\u95f4(extent)\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4e00\u4e2aUNDO\u6bb5\u80fd\u591f\u540c\u65f6\u4e3a\u591a\u4e2a\u4e8b\u52a1\u670d\u52a1 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u6bb5\u4e0eUNDO\u8868\u7a7a\u95f4\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u6bb5\u4e2d\u7684\u5185\u5bb9\u5b58\u50a8\u5728UNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4efb\u610f\u7ed9\u5b9a\u65f6\u523b\u53ea\u80fd\u4f7f\u7528\u4e00\u4e2aUDNO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u8868\u7a7a\u95f4\u5fc5\u987b\u88ab\u521b\u5efa\u4e3a\u6301\u4e45\u7684\u3001\u672c\u5730\u7ba1\u7406\u3001\u53ef\u81ea\u52a8\u6269\u5c55\u7684\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u6b63\u5728\u4f7f\u7528\u7684UNDO\u8868\u7a7a\u95f4\u4e0d\u80fd\u64a4\u9500\u6216\u5220\u9664\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u8868\u7a7a\u95f4\u4f7f\u7528\u5faa\u73af\u5199\u7684\u65b9\u5f0f\uff0c\u4e0e\u8054\u673a\u65e5\u5fd7\u6587\u4ef6\u5199\u76f8\u4f3c\uff0c\u4e0d\u540c\u7684\u662fUNDO\u4e2d\u53ef\u4ee5\u8bbe\u7f6e\u4e86undo_retention \u4fdd\u7559\u65f6\u95f4 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t UNDO\u6bb5\u7684\u4e24\u79cd\u7ba1\u7406\u65b9\u5f0f\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t AUTO \u81ea\u52a8\u7ba1\u7406(\u63a8\u8350)\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t MANUAL \u624b\u52a8\u7ba1\u7406(\u4ec5\u4fdd\u7559) \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t\u4e09\u3001\u4e0e\u64a4\u9500\u76f8\u5173\u7684\u51e0\u4e2a\u53c2\u6570\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u67e5\u770b\u672c\u673a\u4e2dOracle\u7684\u7248\u672c\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT * FROM v$version;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t BANNER\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————————————————————-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t PL\/SQL Release 10.2.0.1.0 – Production\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t CORE 10.2.0.1.0 Production\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TNS for Linux: Version 10.2.0.1.0 – Production\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NLSRTL Version 10.2.0.1.0 – Production\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u67e5\u770b\u548cUNDO\u76f8\u5173\u7684\u53c2\u6570\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SHOW PARAMETER undo;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NAME TYPE VALUE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ———————————— ———– ——————————\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_management string MANUAL\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_retention integer 900\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_tablespace string UNDOTBS1\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_management\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u8bbe\u7f6e\u6570\u636e\u5e93\u7684\u64a4\u9500\u6bb5\u662f\u5426\u4f7f\u7528\u81ea\u52a8\u7ba1\u7406\u6a21\u5f0f\uff0c\u503c\u53ef\u4ee5\u4e3aauto\u6216manual\uff0c\u5f53\u4e3amanual\u65f6\u5c06\u4e0d\u4f7f\u7528\u64a4\u9500\u6bb5\uff0c\u5373\u4e0d\u4f7f\u7528\u81ea\u52a8\u7ba1\u7406\u6a21\u5f0f\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u8be5\u53c2\u6570\u4e3a\u9759\u6001\u53c2\u6570\uff0c\u4fee\u6539\u540e\u9700\u91cd\u542f\u5b9e\u4f8b\u624d\u80fd\u751f\u6548 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_retention\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u6307\u5b9a\u64a4\u9500\u6bb5\u6570\u636e\u5728undo\u6bb5\u4e2d\u4e3a\u975e\u6d3b\u52a8\u72b6\u6001\u540e\u88ab\u8986\u76d6\u524d\u4fdd\u7559\u7684\u65f6\u95f4\uff0c\u5355\u4f4d\u4e3a\u79d2\u3002\u5728undo_management\u4f4dauto\u65f6\u751f\u6548\uff0c\u4e3a\u52a8\u6001\u53c2\u6570\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t undo_tablespace\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u6307\u5b9a\u4f7f\u7528\u54ea\u4e2a\u8868\u7a7a\u95f4\u6765\u5b9e\u73b0\u6570\u636e\u7684\u64a4\u9500\uff0c\u5728undo_management\u4f4dauto\u65f6\u751f\u6548\uff0c\u4e3a\u52a8\u6001\u53c2\u6570 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t retention guarantee\u5b50\u53e5\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4fdd\u8bc1\u64a4\u9500\u4fdd\u7559\uff0c\u4f7f\u7528\u4e0b\u9762\u7684\u64cd\u4f5c\u6765\u5b9e\u73b0\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u4e0b\u9762\u7684\u67e5\u8be2\u4e2d\u662f\u5f53undo_management\u4e3amanual\u65f6\u7684\u7ed3\u679c\u96c6\uff0c\u53ef\u4ee5\u770b\u51fa\u64a4\u9500\u8868\u7a7a\u95f4\u7684\u64a4\u9500\u6bb5\u90fd\u5904\u4e8eoffline\u72b6\u6001 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SEGMENT_NAME TABLESPACE_NAME STATUS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————————— —————————— —————-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SYSTEM SYSTEM ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU1$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU2$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU3$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU4$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU5$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU6$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU7$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU8$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU9$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU10$ UNDOTBS1 OFFLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u5728undo_management \u53c2\u6570\u4e3amanual\u65f6\uff0c\u5bf9scott.emp\u63d2\u5165\u4e00\u6761\u65b0\u8bb0\u5f55\uff0c\u6536\u5230\u4e86\u9519\u8bef\u63d0\u793a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u975e\u7cfb\u7edf\u8868\u7a7a\u95f4\u4e0d\u80fd\u591f\u4f7f\u7528\u56de\u6eda\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> INSERT INTO scott.emp(empno,ename,salary)\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 2 VALUES(6666,'Jenney',3000);\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INSERT INTO scott.emp(empno,ename,salary)\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t *\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ERROR at line 1:\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u67e5\u770b\u6bb5\u7684\u7c7b\u578b\uff0c\u53d1\u73b0\u4ec5\u4ec5system\u8868\u7a7a\u95f4\u5b58\u5728ROLLBACK \u6bb5\uff0c\u6240\u4ee5\u524d\u4e00\u6761\u63d2\u5165\u8bed\u53e5\u6536\u5230\u9519\u8bef\u63d0\u793a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 2 ORDER BY tablespace_name;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SEGMENT_TYPE TABLESPACE_NAME\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————— ——————————\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX PARTITION EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBINDEX EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBSEGMENT EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NESTED TABLE EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE PARTITION EXAMPLE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX PARTITION SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOB PARTITION SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBINDEX SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SEGMENT_TYPE TABLESPACE_NAME\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————— ——————————\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBSEGMENT SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NESTED TABLE SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE PARTITION SYSAUX\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t CLUSTER SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBINDEX SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBSEGMENT SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NESTED TABLE SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ROLLBACK SYSTEM –\u4e0e\u4e4b\u524d\u7684\u7248\u672c\u517c\u5bb9\u7684\u56de\u6eda\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE SYSTEM\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SEGMENT_TYPE TABLESPACE_NAME\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————— ——————————\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE TBS1\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TYPE2 UNDO UNDOTBS1 –9i\u4e4b\u540e\u4f7f\u7528\u7684\u64a4\u9500\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t INDEX USERS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBINDEX USERS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t LOBSEGMENT USERS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t NESTED TABLE USERS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TABLE USERS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u4e0b\u9762\u5c06undo_management\u6539\u4e3a\u652f\u6301\u81ea\u52a8\u7ba1\u7406\uff0c\u9700\u8981\u91cd\u542f\u5b9e\u4f8b\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> ALTER SYSTEM SET undo_management = 'auto' SCOPE = SPFILE;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t System altered.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SHUTDOWN IMMEDIATE;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Database closed.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Database dismounted.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ORACLE instance shut down.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> STARTUP;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ORACLE instance started.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Total System Global Area 251658240 bytes\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Fixed Size 1218796 bytes\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Variable Size 67110676 bytes\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Database Buffers 180355072 bytes\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Redo Buffers 2973696 bytes\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Database mounted.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t Database opened.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u518d\u6b21\u67e5\u770bdba_rollback_segs\u89c6\u56fe\u6240\u6709\u7684\u64a4\u9500\u6bb5\u5168\u90e8\u5904\u4e8eonline\u72b6\u6001\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u6ce8\u610f\u7b2c\u4e00\u884c\u4e3asystem\u8868\u7a7a\u95f4\u7684\u64a4\u9500\u6bb5\uff0c\u7528\u4e8e\u7cfb\u7edf\u8868\u7a7a\u95f4\u7684\u64a4\u9500\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SEGMENT_NAME TABLESPACE_NAME STATUS\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t —————————— —————————— —————-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SYSTEM SYSTEM ONLINE \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU1$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU2$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU3$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU4$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU5$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU6$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU7$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU8$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU9$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t _SYSSMU10$ UNDOTBS1 ONLINE\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u7531\u4e0a\u9762\u7684\u793a\u4f8b\u53ef\u77e5\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ROLLBACK \u6bb5\uff1a –\u4e0e\u4e4b\u524d\u7684\u7248\u672c\u517c\u5bb9\u7684\u56de\u6eda\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t TYPE2 UNDO \u6bb5\uff1a –9i\u4e4b\u540e\u4f7f\u7528\u7684\u64a4\u9500\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u5173\u4e8e\u56de\u6eda\uff0c\u4e00\u4e2a\u65f6\u523b\u4ec5\u80fd\u4f7f\u7528\u4e00\u79cd\u7c7b\u6bb5\u7c7b\u578b\uff0c\u5373\u8981\u4e48\u4f7f\u7528\u4e0e\u4ee5\u524d\u7248\u672c\u517c\u5bb9\u7684\u56de\u6eda\u6bb5\uff0c\u8981\u4e48\u4f7f\u7528\u64a4\u9500\u6bb5\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u4e8b\u5b9e\u4e0a\uff0c\u57289i\u4e4b\u540e\u4ec5\u4ec5\u652f\u6301\u64a4\u9500\u6bb5\uff0c\u4ece\u4e0a\u9762\u9519\u8bef\u7684\u63d0\u793a\u5373\u53ef\u8bc1\u5b9e\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u67e5\u770bDML\u8bed\u53e5\u4ea7\u751f\u7684\u4e8b\u52a1\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SHOW USER; \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t USER is "SYS"\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ———- ————— ——— ———- ——— ———- ———-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 7788 SCOTT ANALYST 7566 19-APR-87 3500 20\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> UPDATE scott.emp SET sal = sal * 2 WHERE ename = 'SCOTT';\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 1 row updated.\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT addr,xidusn,status,start_time,used_ublk\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 2 FROM v$transaction;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ADDR XIDUSN STATUS START_TIME USED_UBLK\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ——– ———- —————- ——————– ———-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 2DA2B17C 9 ACTIVE 07\/10\/10 20:29:08 1\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t –\u67e5\u770b\u5f53\u524d\u54ea\u4e9b\u7528\u6237\u4f7f\u7528\u64a4\u9500\u6bb5\u4ee5\u53ca\u6bb5\u7684\u5927\u5c0f\uff0c\u542f\u52a8\u65f6\u95f4\uff0c\u6d3b\u52a8\u72b6\u6001\u7b49\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t SQL> SELECT t.xidusn,t.start_time,t.used_ublk,t.status,\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t s.username,r.segment_name\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t FROM v$transaction t\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t JOIN v$session s\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ON t.ses_addr = s.saddr\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t JOIN dba_rollback_segs r\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ON r.segment_id = t.xidusn ;\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t XIDUSN START_TIME USED_UBLK STATUS USERNAME SEGMENT_NAME\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t ———- ——————– ———- —————- —————————— ————-\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t 9 07\/10\/10 20:29:08 1 ACTIVE SYS _SYSSMU9$\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t\u56db\u3001UNDO\u8868\u7a7a\u95f4\u7684\u521b\u5efa\u4e0e\u7ba1\u7406 \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u521b\u5efaUNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u521b\u5efa\u8bed\u6cd5\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t CREATE UNDO TABLESPACE tablespace_name DATAFILE '…' SIZE n\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u66f4\u591a\u8868\u7a7a\u95f4\u7684\u521b\u5efa\uff1a\n\t\t\t\t\t\t<\/p>\n
\n\t\t\t\t\t\t\t \u8bf7\u53c2\u7167\uff1aOracle \u8868\u7a7a\u95f4\u4e0e\u6570\u636e\u6587\u4ef6 <\/a>\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5207\u6362UNDO\u8868\u7a7a\u95f4 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5b9e\u4f8b\u4e2d\u5141\u8bb8\u591a\u4e2aUNDO\u8868\u7a7a\u95f4\u5b58\u5728\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u53ef\u4ee5\u4ece\u4e00\u4e2aUNDO\u8868\u7a7a\u95f4\u5207\u6362\u5230\u53e6\u5916\u4e00\u4e2aUNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u4efb\u4e00\u65f6\u523b\u53ea\u80fd\u6709\u4e00\u4e2aUNDO\u8868\u7a7a\u95f4\u88ab\u6307\u5b9a\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u4f7f\u7528ALTER SYSTEM SET undo_tablespace = undo_tablespace_name\u5b9e\u73b0\u5207\u6362\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5220\u9664UNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t DROP TABLESPACE undo_tablespace_name\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u4efb\u610f\u5b9e\u4f8b\u7684UNDO\u8868\u7a7a\u95f4\u5728\u975e\u6d3b\u52a8\u72b6\u6001\u53ef\u4ee5\u5220\u9664\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5bf9\u4e8e\u6d3b\u52a8\u72b6\u6001\u7684UNDO\u8868\u7a7a\u95f4\uff0c\u5e94\u5f53\u5148\u5c06\u5207\u6362\u5230\u5176\u5b83\u8868\u7a7a\u95f4\uff0c\u5728\u6240\u6709\u4e8b\u52a1\u5b8c\u6210\u540e\u518d\u5220\u9664\u8be5\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u6f14\u793a\u521b\u5efa\u3001\u5207\u6362\u53ca\u5220\u9664UNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u67e5\u770b\u5f53\u524d\u7cfb\u7edf\u4e2d\u7684\u8868\u7a7a\u95f4 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT file_name,tablespace_name FROM dba_data_files;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t FILE_NAME TABLESPACE_NAME\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———————————————————— ——————————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/tbs1_2.dbf TBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/tbs1_1.dbf TBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/example01.dbf EXAMPLE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/users01.dbf USERS\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/sysaux01.dbf SYSAUX\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/undotbs01.dbf UNDOTBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/system01.dbf SYSTEM\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u521b\u5efa\u4e00\u4e2a\u65b0\u7684UNDO\u8868\u7a7a\u95f4undo2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> CREATE UNDO TABLESPACE undo2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 DATAFILE '\/u01\/app\/oracle\/oradata\/orcl\/undotbs02.dbf' SIZE 3M\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 AUTOEXTEND ON;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Tablespace created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t FILE_NAME TABLESPACE_NAME\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———————————————————— ——————————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/undotbs01.dbf UNDOTBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \/u01\/app\/oracle\/oradata\/orcl\/undotbs02.dbf UNDO2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u67e5\u770b\u5f53\u524d\u7cfb\u7edf\u4f7f\u7528\u7684UNDO\u8868\u7a7a\u95f4\u4e3aUNDOTBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t NAME VALUE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t —————————— ————————————————–\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_management AUTO\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_tablespace UNDOTBS1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_retention 900\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u521b\u5efa\u4e00\u5f20\u8868tb_test\u7528\u4e8e\u6f14\u793a\uff0c\u5047\u5b9a\u8be5\u4f1a\u8bdd\u4e3asession1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> CREATE TABLE tb_test\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 (\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 ID INT,\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 4 Name VARCHAR2(20)\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 5 );\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Table created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u5230tb_test\uff0c\u6b64\u65f6\u672a\u63d0\u4ea4\u5c06\u4ea7\u751fUNDO \u4fe1\u606f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> INSERT INTO tb_test SELECT 1,'Robinson' FROM dual;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1 row created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u6b64\u65f6\u6253\u5f00\u53e6\u5916\u4e00\u4e2a\u56de\u8bdd\uff0c\u5047\u5b9a\u4e3asession2\uff0c\u5728session2\u4e2d\u5207\u6362\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER SYSTEM SET undo_tablespace = 'undo2'; \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t System altered. –undotbs1\u4e2d\u6709\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\uff0c\u7adf\u7136\u53ef\u4ee5\u6210\u529f\u5207\u6362\uff1f\u5982\u6b64\u8fd9\u822c\u95ea\u56de\u65f6\u4f30\u8ba1\u4f1a\u6709\u95ee\u9898\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SHOW PARAMETER undo;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t NAME TYPE VALUE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———————————— ———– ——————————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_management string AUTO\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_retention integer 900\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_tablespace string undo2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session1\u4e2d\u6267\u884ccommit\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> COMMIT;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Commit complete. –\u6210\u529f\u6267\u884c\u4e86commit,\u4e14\u4e0b\u9762\u7684\u67e5\u8be2\u770b\u5230\u4e86\u63d0\u4ea4\u540e\u7684\u7ed3\u679c\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT * FROM tb_test;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ID NAME\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———- ——————————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1 Robinson\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u767b\u51fa\u7cfb\u7edf\u540e\u518d\u6b21\u67e5\u770b\uff0c\u7ed3\u679c\u4f9d\u7136\u5b58\u5728\uff0c\u6bd4\u8f83\u7eb3\u95f7 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> exit\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t With the Partitioning, OLAP and Data Mining options\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t [uniread] Saved history (716 lines)\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t [oracle@robinson ~]$ sqlplus \/ as sysdba;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jul 10 21:29:36 2010\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Copyright (c) 1982, 2005, Oracle. All rights reserved.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Connected to:\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t With the Partitioning, OLAP and Data Mining options\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT * FROM tb_test;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ID NAME\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———- ——————–\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1 Robinson\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5220\u9664UNDO\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session1\u4e2d\u63d2\u5165\u4e00\u6761\u65b0\u8bb0\u5f55\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> INSERT INTO tb_test SELECT 2,'Jack' FROM DUAL;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1 row created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session2\u4e2d\u5c06\u64a4\u9500\u8868\u7a7a\u95f4\u5207\u6362\u4e3aundotbs1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t System altered.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u7d27\u63a5\u7740\u5728\u8be5\u56de\u8bdd\u4e2d\u5220\u9664undo2\uff0c\u63d0\u793a\u6b63\u5728\u4f7f\u7528\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> DROP TABLESPACE undo2;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t DROP TABLESPACE undo2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t *\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ERROR at line 1:\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ORA-30013: undo tablespace 'UNDO2' is currently in use\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session1\u4e2d\u63d0\u4ea4\u4e8b\u52a1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> COMMIT;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Commit complete.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session2\u4e2d\u518d\u6b21\u5220\u9664\u8868\u7a7a\u95f4undo2\uff0c\u6536\u5230\u4e86\u76f8\u540c\u7684\u9519\u8bef\u63d0\u793a\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> \/\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t DROP TABLESPACE undo2\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t *\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ERROR at line 1:\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ORA-30013: undo tablespace 'UNDO2' is currently in use\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SHOW PARAMETER undo –\u67e5\u770b\u7684\u786e\u662f\u5df2\u5207\u6362\u5230undotbs1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t NAME TYPE VALUE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———————————— ———– ——————————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_management string AUTO\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_retention integer 900\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t undo_tablespace string undotbs1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session2\u4e2d\u518d\u6b21\u6267\u884c\u8868\u7a7a\u95f4\u5207\u6362\u5230undotbs1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t System altered.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u6b64\u65f6undo2\u6210\u529f\u5220\u9664\uff0c\u53ef\u4ee5\u770b\u51fa\u9700\u8981\u5728\u5c06\u6d3b\u52a8\u4e8b\u52a1\u63d0\u4ea4\u6216\u56de\u6eda\u540e\uff0c\u518d\u5207\u6362\u4e4b\u540e\u624d\u80fd\u6210\u529f\u5220\u9664\u64a4\u9500\u8868\u7a7a\u95f4\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> DROP TABLESPACE undo2;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Tablespace dropped.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ho ls $ORACLE_BASE\/oradata\/orcl;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t control01.ctl redo03.log redo2.log system01.dbf undotbs02.dbf\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t control02.ctl redo04.log redo3.log tbs1_1.dbf users01.dbf\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t example01.dbf redo07.log redo7.log tbs1_2.dbf\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t redo01.log redo08.log redo8.log temp01.dbf\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t redo02.log redo1.log sysaux01.dbf undotbs01.dbf\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5220\u9664UNDO\u8868\u7a7a\u95f4\u7684\u7269\u7406\u6587\u4ef6\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ho rm $ORACLE_BASE\/oradata\/orcl\/undotbs02.dbf;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5728session1\u4e2d\u53ef\u4ee5\u770b\u5230\u4e24\u6761\u8bb0\u5f55\u4e5f\u88ab\u6210\u529f\u63d2\u5165\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT * FROM tb_test; \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ID NAME\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———- ——————–\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1 Robinson\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 Jack\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u67e5\u770b\u5f53\u524d\u64a4\u9500\u8868\u7a7a\u95f4\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT tablespace_name,bytes\/1024\/1024 FROM dba_data_files\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 WHERE tablespace_name = 'UNDOTBS1';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t TABLESPACE_NAME BYTES\/1024\/1024\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t —————————— —————\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t UNDOTBS1 30\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5faa\u73af\u63d2\u5165\u8bb0\u5f55\u5230tb_test\u540e\u67e5\u770bundo\u8868\u7a7a\u95f4\u7684\u4f7f\u7528\u60c5\u51b5\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> BEGIN\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 FOR i IN 1..20000\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 LOOP\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 4 INSERT INTO tb_test VALUES(i,'Unkown Name');\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 5 END LOOP;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 6 END;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 7 \/\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t PL\/SQL procedure successfully completed. \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u53ef\u4ee5\u770b\u5230UNDO \u8868\u7a7a\u95f4\u53ea\u7528\u4e86\u4e2a\u5757\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ADDR XIDUSN USED_UBLK\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ——– ———- ———-\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2D9FC160 6 174\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT 174 * 8 || 'KB' FROM dual;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 174*8|\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ——\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1392KB\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t\u4e94\u3001\u8ba1\u7b97UNDO\u8868\u7a7a\u95f4\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u8ba1\u7b97\u516c\u5f0f\uff1a\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t MAX(undoblks)\/600 * MAX(maxquerylen) \u4f4d\u4e8ev$undostat\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t * db_block_size \u4f4d\u4e8ev$parameter\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u521b\u5efa\u6f14\u793a\u73af\u5883\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 107 rows created\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> INSERT INTO tb_test SELECT * from tb_test;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 109 rows created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u591a\u6b21\u6267\u884c\u4e0a\u8ff0\u547d\u4ee4\uff0c\u4e0b\u9762\u662f\u7684tb_test\u8868\u4e2d\u7684\u8bb0\u5f55\u6570\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> \/\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 892928 rows created.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> COMMIT;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Commit complete.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u67e5\u770b\u5f53\u524dundo\u8868\u7a7a\u95f4\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT t.name,d.name,d.bytes\/1024\/1024 as TotalSize ,t.flashback_on,d.status\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 FROM v$tablespace t\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 JOIN v$datafile d\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 4 USING (ts#)\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 5 WHERE t.name LIKE 'UNDO%';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t NAME NAME TOTALSIZE FLA STATUS\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ——————————— ——————————————- ———- — ——-\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t UNDOTBS1 \/u01\/app\/oracle\/oradata\/orcl\/undotbs01.dbf 30 YES ONLINE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5c06undo\u8868\u7a7a\u95f4\u4fee\u6539\u4e3aRETENTION GUARANTEE\u53ca\u5173\u95ed\u81ea\u52a8\u6269\u5c55\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Tablespace altered.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER DATABASE DATAFILE '\/u01\/app\/oracle\/oradata\/orcl\/undotbs01.dbf' AUTOEXTEND OFF;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Database altered. \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT tablespace_name,contents,retention FROM dba_tablespaces\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 WHERE tablespace_name LIKE 'UNDO%';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t TABLESPACE_NAME CONTENTS RETENTION\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t —————————— ——— ———–\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t UNDOTBS1 UNDO GUARANTEE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u4fee\u6539\u4fdd\u7559\u65f6\u95f4\u4e3a\u5206\u949f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER SYSTEM SET undo_retention = 120;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t System altered.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u5faa\u73af\u5220\u9664tb_test\u4e2d\u7684\u8bb0\u5f55\uff0c\u63d0\u793aundo\u8868\u7a7a\u95f4\u7a7a\u95f4\u5bb9\u91cf\u4e0d\u591f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> BEGIN\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 FOR i IN 1..1000 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 LOOP\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 4 DELETE FROM tb_test WHERE rownum < 1001;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 5 COMMIT;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 6 END LOOP;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 7 END;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 8 \/\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t BEGIN\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t *\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ERROR at line 1:\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ORA-06512: at line 4 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u4fee\u6539\u56de\u8bdd\u7684\u65f6\u95f4\u53c2\u6570\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:MI:SS';\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Session altered.\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u67e5\u770bv$undostat\u89c6\u56fe\uff0c\u83b7\u5f97\u76f8\u5173\u4fe1\u606f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT begin_time,end_time,undoblks,maxquerylen, ssolderrcnt,nospaceerrcnt\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 FROM v$undostat;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ——————- ——————- ———- ———– ———– ————-\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 19:12:18 2010-07-12 19:22:18 6 0 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 19:02:18 2010-07-12 19:12:18 9 0 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:52:18 2010-07-12 19:02:18 47 0 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:42:18 2010-07-12 18:52:18 2136 0 0 1\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:32:18 2010-07-12 18:42:18 6 0 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:22:18 2010-07-12 18:32:18 413 1541 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:12:18 2010-07-12 18:22:18 179 938 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2010-07-12 18:02:18 2010-07-12 18:12:18 6 0 0 0\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u8ba1\u7b97undo\u8868\u7a7a\u95f4\u6240\u9700\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> SELECT (\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2 (SELECT MAX(undoblks)\/600 * MAX(maxquerylen) FROM v$undostat) *\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 3 (SELECT value FROM v$parameter WHERE name = 'db_block_size'))\/1024\/1024 as Need_Size\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 4 FROM dual;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t NEED_SIZE\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t ———-\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 42.8590625 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t –\u53d6\u6d88\u64a4\u9500\u4fdd\u7559\u9009\u9879\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t Tablespace altered \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t\u516d\u3001UNDO\u914d\u989d\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5bf9\u4e8e\u8d85\u957f\u7684\u4e8b\u52a1\u6216\u4e0d\u5f53\u7684SQL\u811a\u672c\u5c06\u8017\u7528\u5927\u91cf\u7684UNDO\u8868\u7a7a\u95f4\uff0c\u4f7f\u7528UNDO\u8868\u7a7a\u95f4\u914d\u989d\u53ef\u4ee5\u63d0\u9ad8\u8d44\u6e90\u7684\u5229\u7528\u7387\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5bf9\u4e8e\u4e0d\u540c\u7ec4\u7684\u7528\u6237\u53ef\u4ee5\u5206\u914d\u4e0d\u540c\u7684\u6700\u5927UNDO\u8868\u7a7a\u95f4\u914d\u989d\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5f53\u67d0\u4e2a\u7ec4\u8d85\u51fa\u4e86\u6700\u5927\u7684\u8d44\u6e90\u9650\u5236\uff0c\u5219\u8be5\u7ec4\u4e0d\u5141\u8bb8\u65b0\u7684\u4e8b\u52a1\u4ea7\u751f\uff0c\u76f4\u5230\u5f53\u524d\u7ec4\u7684UNDO\u8868\u7a7a\u95f4\u91ca\u653e\u6216\u7ec8\u6b62 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t\u4e03\u3001\u64a4\u9500\u5e38\u89c1\u7684\u4e24\u4e2a\u9519\u8bef\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 1.ORA-1555 snapshot too old \u5feb\u7167\u8fc7\u65e7\u9519\u8bef\u7684\u89e3\u51b3\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u914d\u7f6e\u5408\u9002\u7684\u4fdd\u7559\u65f6\u95f4(undo_retention)\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u8c03\u6574undo\u8868\u7a7a\u95f4\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u8003\u8651\u4fdd\u8bc1\u64a4\u9500\u4fdd\u7559\u7684\u4f7f\u7528(retention guarantee) \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t 2.ORA-30036 unable to extend segment in undo tablespace \u65e0\u6cd5\u6269\u5c55\u64a4\u9500\u8868\u7a7a\u95f4\u5185\u7684\u64a4\u9500\u6bb5\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u8c03\u6574undo\u8868\u7a7a\u95f4\u7684\u5927\u5c0f\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u786e\u4fdd\u5927\u91cf\u7684\u4e8b\u52a1\u80fd\u591f\u5468\u671f\u6027\u7684\u63d0\u4ea4 \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t\u516b\u3001UNDO\u6d89\u53ca\u7684\u51e0\u4e2a\u76f8\u5173\u89c6\u56fe\uff1a\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t V$TRANSACTION\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t V$SESSION\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t DBA_ROLLBACK_SEGS –\u663e\u793a\u6240\u6709\u7684segments\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t V$ROLLSTAT\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t V$UNDOSTAT\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t V$ROLLNAME –\u663e\u793a\u5f53\u524d\u5728\u7ebf\u7684segments \n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u5173\u4e8eUNDO\u6d89\u53ca\u89c6\u56fe\u7684\u66f4\u591a\u4fe1\u606f\uff0c\u8bf7\u53c2\u8003oracle\u7684\u5728\u7ebf\u6587\u6863\n\t\t\t\t\t\t<\/p>\n \n\t\t\t\t\t\t\t \u8f6c\u8f7d\u81ea\uff1ahttp:\/\/blog.csdn.net\/leshami\/article\/details\/5731158\n\t\t\t\t\t\t<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":" \u4e00\u3001\u56de\u6eda(ROLLBACK)\u548c\u64a4\u9500(UNDO) \u56de\u6eda\u548c\u524d\u6eda\u662f\u4fdd\u8bc1Orac … \u7ee7\u7eed\u9605\u8bfbOracle \u56de\u6eda(ROLLBACK)\u548c\u64a4\u9500(UNDO)<\/span>