No. 3 [12c New Feature] TEMP_UNDO_ENABLED
KOVA
2013-11-11
2480
global temporary tables ¶Ç´Â temporary table transformations¸¦ »ç¿ëÇÒ °æ¿ì redo size ¹× undo size¸¦ ÁÙÀÏ ¼ö ÀÖ´Ù. ÇØ´ç ±â´ÉÀ» »ç¿ëÇϱâ À§Çؼ´Â COMPATIBLE ÆÄ¶ó¹ÌÅÍ´Â 12.0.0À¸·Î ¼³Á¤µÇ¾î ÀÖ¾î¾ßÇϸç, session / system ·¹º§·Î ¼³Á¤ °¡´ÉÇÏ´Ù. SQL> CREATE GLOBAL TEMPORARY TABLE test ( 2 no number(10) 3 , name varchar2(30) 4 ) ON COMMIT PRESERVE ROWS; SQL> alter session set temp_undo_enabled=FALSE; ¼¼¼ÇÀÌ º¯°æµÇ¾ú½À´Ï´Ù. ¡Ø ±âº»°ªÀº FALSE ÀÌ´Ù. SQL> BEGIN 2 FOR i IN 1..100000 3 LOOP 4 INSERT INTO test VALUES (i, 'test data'); 5 END LOOP; 6 END; 7 / PL/SQL 󸮰¡ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú½À´Ï´Ù. SQL> @redo REDO_ENTRY REDO_SIZE ---------- ---------- 100002 14454692 SQL> @unddo STATUS TABLESPACE_NAME MBYTES ------------------ --------------- ---------- ACTIVE UNDOTBS1 9 SQL> @temp percent used ------------ 4.54545455 ¡Ø ºñȰ¼ºÈ ½Ã REDO SIZE´Â 14454692 / UNDO SIZE´Â 9MB / TEMP »ç¿ë·üÀº 4.5%ÀÌ´Ù. SQL> exit C:\> sqlplus test/test SQL> alter session set temp_undo_enabled=TRUE; SQL> BEGIN 2 FOR i IN 1..100000 3 LOOP 4 INSERT INTO test VALUES (i, 'test data'); 5 END LOOP; 6 END; 7 / PL/SQL 󸮰¡ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú½À´Ï´Ù. SQL> @redo REDO_ENTRY REDO_SIZE ---------- ---------- 3 1016 SQL> @unddo STATUS TABLESPACE_NAME MBYTES ------------------ --------------- ---------- ACTIVE UNDOTBS1 1 SQL> @temp percent used ------------ 13.6363636 ¡Ø Ȱ¼ºÈ ½Ã REDO SIZE´Â 1016 / UNDO SIZE´Â 1MB / TEMP »ç¿ë·üÀº 13.6À¸·Î REDO ¹× UNDO SIZE´Â ÇöÀúÈ÷ ÁÙ¾îµç °ÍÀ» º¼ ¼öÀÖ´Ù. ÇÏÁö¸¸ ±â´É Ȱ¼ºÈ ½Ã ´õ ¸¹Àº TEMPORARY Å×ÀÌºí½ºÆäÀ̽º¸¦ »ç¿ëÇϹǷΠÁÖÀÇÇØ¾ßÇÑ´Ù.