#################################################### # LOCALLY-MANAGED TEMPFILE ÀÇ RENAME ¹æ¹ý # #################################################### ÀÛ¼ºÀÚ : ÀåÇüÈ(hhjang97@venus.uos.ac.kr) ÀÛ¼ºÀÏ : 2005. 09. 23 ¼öÁ¤ÀÏ : ¿øº» : ¼³¸í : Temporaty File Rename ¹æ¹ý Default Table SpaceÀÎ TEMP ÀÇ °æ·Î¸¦ RENAME ÇÏ´Â °æ¿ì´Â ´ÙÀ½°ú °°ÀÌ ÇÏ¸é µÈ´Ù. 1. NEWTEMP ¸¦ »ý¼ºÇÑ´Ù.(Temporary Tablespace) 2. Default Tablespace ¸¦ TEMP ¿¡¼ NEWTEMP·Î º¯°æÇÑ´Ù. º¯°æÈÄ Á¤»óÀûÀ¸·Î º¯°æµÇ¾ú´ÂÁö È®ÀÎÇÑ´Ù. 3. TEMP ¸¦ Á¦°ÅÇÑ´Ù. 4. TEMP ¸¦ Àç»ý¼º ÇÑ´Ù.(Temporary Tablespace) º¯°æÇÒ °æ·Î·Î »ý¼º 5. Default Tablespace ¸¦ NEWTEMP ¿¡¼ TEMP·Î º¯°æÇÑ´Ù. 6. NEWTEMP¸¦ Á¦°ÅÇÑ´Ù. ################################# ################################# ################################# No. 18720 LOCALLY-MANAGED TEMPFILE ÀÇ RENAME ¹æ¹ý ======================================= Purpose -------- Locally-managed temporary fileÀÇ À§Ä¡ º¯°æ ¹æ¹ý Problem Description ------------------------------- Temporary tablespace Áß Dictionary managed fileÀº ALTER DATABASE RENAME FILE Command ¸¦ ÀÌ¿ëÇÏ¿© rename ÀÌ °¡´ÉÇÏ´Ù. SQL> select TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT from dba_tablespaces; TABLESPACE_NAME CONTENTS EXTENT_MAN --------------- ----------- ---------- TEMP TEMPORARY DICTIONARY SQL> alter tablespace temp offline; Tablespace altered. SQL> alter database rename file '/V816/temp01.dbf' to '/V816/temp02.dbf'; Database altered. SQL> recover tablespace temp; Media recovery complete. SQL> alter tablespace temp online; Tablespace altered. ±×·¯³ª Locally-managed tempfile Àº ´ÙÀ½°ú °°Àº error °¡ ³¯°ÍÀÌ´Ù. SQL> select name from v$tempfile; NAME ------------------------------------------------------------- /V816/temp01.dbf SQL> alter database rename file '/V816/temp01.dbf' to '/V816/temp02.dbf'; alter database rename file *ORA-01511: error in renaming log/data files ORA-01516: nonexistent log file, datafile or tempfile '/V816/temp01.dbf' SQL> alter tablespace user_temp_1 offline; alter tablespace user_temp_1 offline *ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE Solution Description -------------------- Solution 1 ---------- ±âÁ¸ÀÇ Locally managed tablespace¸¦ dropÇÏ°í »õ·Î¿î À§Ä¡ÀÇ tempfile À» °¡Áø tablespace ¸¦ ´Ù½Ã »ý¼º. SQL> select file_name, tablespace_name from DBA_TEMP_FILES; FILE_NAME TABLESPACE_NAME --------------------------------------------------- ------------------ /V816/temp01.dbf USER_TEMP_1 SQL> drop tablespace user_temp_1; Tablespace dropped. SQL> create temporary tablespace user_temp_1 2 tempfile '/V816/temp02.dbf' size 2M extent management local; Tablespace created. Solution 2 ---------- TempfileÀ» dropÇÏ°í ±âÁ¸ÀÇ tablespace ¿¡ »õ·Î¿î À§Ä¡¸¦ add ÈÄ Os ¿¡¼ file À» »èÁ¦ SQL> alter database tempfile '/V90164/temp02.dbf' drop; Database altered. SQL> alter tablespace USER_TEMP_1 add tempfile '/V90164/temp03.dbf' size 2M; Tablespace altered. $ rm /V90164/temp02.dbf 9i¿¡¼´Â ,´ÙÀ½°ú °°ÀÌ ÇÑ ¹®ÀåÀ¸·Î tempfile°ú os fileÀ» ¾ø¾Ù¼ö ÀÖ´Ù. SQL> alter database tempfile '/V901/temp02.dbf' drop including datafiles; Database altered. SQL> alter tablespace USER_TEMP_1 add tempfile '/V90164/temp03.dbf' size 2M; Tablespace altered. ################################# # #################################