DB/Oracle/¥³¥Þ¥ó¥É/ɽÎΰè´ÉÍý
Ìܼ¡†
ɽÎΰè¤Ë´Ø¤¹¤ë¾ðÊó¡£
- Ìܼ¡
- ɽÎΰè¤ÎºîÀ®
- °ì»þɽÎΰè¤ÎºîÀ®
- UNDO ɽÎΰè¤ÎºîÀ®
- ɽÎΰè¤Îºï½ü
- ´û¸¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òºÆÍøÍѤ·¤ÆɽÎΰè¤òºî¤ë
- ɽÎΰè¤Î¥µ¥¤¥º¤ò³ÈÄ¥
- ɽÎΰè¤Î¥ª¥ó¥é¥¤¥ó¤È¥ª¥Õ¥é¥¤¥ó¤ÎÊѹ¹
- ɽÎΰè¤ÎÆɤ߽ñ¤µö²Ä¤òÊѹ¹¤¹¤ë
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°
- ¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤Ê¤¤É½Îΰè¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°
- UNDO ɽÎΰè¤ÎÀÚ¤êÂؤ¨
- ɽÎΰè¤Î¾ðÊó¤Î³Îǧ
ɽÎΰè¤ÎºîÀ®†
ɽÎΰè¤ò¿·¤·¤¯ºî¤ë¡£
ÊýË¡†
- ɽÎΰè¤òºîÀ®¤¹¤ë
-- ´ðËܤΥ³¥Þ¥ó¥É create tablespace <tablespace-name> datafile '/path/to/datafile.dbf' size <size> -- °Ê²¼¤Ï¥ª¥×¥·¥ç¥ó¤Ç»ØÄê²Äǽ autoextend on next <size> maxsize <size> blocksize <size> extent management local [ autoallocate | uniform size <size> ] segment space management [ manual | auto ] ;
- ¥ª¥×¥·¥ç¥ó¤Ï°Ê²¼
¥ª¥×¥·¥ç¥ó ÀâÌÀ tablespace <tablespace-name> ɽÎΰè̾ datafile '/path/to/datafile.dbf' ɽÎΰè¤Ç»ÈÍѤ¹¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë size <size> ɽÎΰè¤Î¥µ¥¤¥º autoextend on
next <size>
maxsize <size>ɽÎΰè¤Î¼«Æ°³ÈÄ¥¤ò͸ú¤Ë¤¹¤ë¡£NEXT ¤ÇÉÔ»þ¤ËÄɲ乤ëÍÆÎÌ¡¢MAXSIZE ¤ÇºÇÂ祵¥¤¥º¤ò»ØÄê blocksize <size> ¥Ö¥í¥Ã¥¯¥µ¥¤¥º extent management local
[ autoallocate | uniform size <size> ]¥¨¥¯¥¹¥Æ¥ó¥È´ÉÍý¤ò¥í¡¼¥«¥ë¤Ë¡£¥¨¥¯¥¹¥Æ¥ó¥È¤Î¥µ¥¤¥º¤ò autoallocate ¤Ç¼«Æ°¤Ë¡¢uniform size ¤Ç¶Ñ°ì¤Ë»ØÄê segment space management
[ manual | auto ]¥»¥°¥á¥ó¥È¤Î¶õ¤Îΰè¤Î´ÉÍý¤ò¼«Æ°¤«¼êÆ°¤«¡£PCTFREE ¤È¤« FREELIST ¤È¤«¤½¤³¤é¤Ø¤ó¤ÎÏÃ
- ¥ª¥×¥·¥ç¥ó¤Ï°Ê²¼
- ¤¤¤Þ¤Ç¤Ï¤â¤¦»È¤ï¤Ê¤¤¥ª¥×¥·¥ç¥ó¤Ï°Ê²¼¡£¤â¤Ï¤ä²¼°Ì¸ß´¹¤Î¤¿¤á¤Ë»Ä¤Ã¤Æ¤¤¤ë¤À¤±
¥ª¥×¥·¥ç¥ó ÀâÌÀ minimum extent ¥¨¥¯¥¹¥Æ¥ó¥È¤ÎºÇ¾®¥µ¥¤¥º default storage ÆâÉô¤Ëºî¤é¤ì¤ë¥ª¥Ö¥¸¥§¥¯¥È¤ËÂФ¹¤ëµ²±Îΰè¤Î¥Ñ¥é¥á¡¼¥¿ permanent ±Ê³ɽÎΰè¤Ë¤¹¤ë¡Ê¥Ç¥Õ¥©¥ë¥È¡Ë temporary °ì»þ¥ª¥Ö¥¸¥§¥¯¥È¤Î³ÊǼÍѤ˺îÀ®¡£¥Ç¥£¥¯¥·¥ç¥Ê¥ê´ÉÍý¤Ë¤Ê¤ë
°ì»þɽÎΰè¤ÎºîÀ®†
°ì»þɽÎΰè¤ÎºîÀ®¡£¥½¡¼¥È½èÍý¤Ê¤É¤Ç»È¤ï¤ì¤ë¡£
Ä̾¥µ¡¼¥Ð¥×¥í¥»¥¹¤Î PGA ¤Ë¥½¡¼¥ÈÎΰ褬¤¢¤ê¡¢¥½¡¼¥È¤Ï¤½¤³¤Ç¹Ô¤ï¤ì¤ë¤¬¡¢Îΰ褬¤ê¤Ê¤¤¾ì¹ç¤Ï°ì»þɽÎΰè¤Ë°ì»þ¥»¥°¥á¥ó¥È¤òºîÀ®¡¢Ãæ´Ö¥Ç¡¼¥¿¤ò³ÊǼ¤¹¤ë¤Ê¤É¤·¤ÆÂбþ¤¹¤ë¡£
´ÉÍý¼Ô¤¬°ì»þɽÎΰèÆâ¤ËÌÀ¼¨Åª¤Ëɽ¤òºîÀ®¤¹¤ë¤³¤È¤Ï¤Ê¤¤¡£¥µ¡¼¥Ð¥×¥í¥»¥¹¤Ê¤É¤¬É¬Íפ˱þ¤¸¤Æ¾¡¼ê¤Ë»È¤¦¤À¤±¡£
ÊýË¡†
- °ì»þɽÎΰè¤ÎºîÀ®
create temporary tablespace <tablespace-name> tempfile '/path/to/tempfile.dbf' size <size>;
- ¥æ¡¼¥¶¤Ø¤Î³ä¤êÅö¤Æ
-- create user ʸ¤Î¥ª¥×¥·¥ç¥ó¤È¤·¤Æ»ØÄꤹ¤ë create user <user-name> identified by <password> ... temporary tablespace <tablespace-name>... ...;
UNDO ɽÎΰè¤ÎºîÀ®†
UNDO ɽÎΰè¤ò¤Ä¤¯¤ë¡£´ðËÜŪ¤Ê¹½Ê¸¤ÏɽÎΰè¤ÎºîÀ®¤ÈƱ¤¸¡£
ÊýË¡†
- UNDO ɽÎΰè¤òºîÀ®¤¹¤ë
create undo tablespace <tablespace-name> datafile '/path/to/undofile.dbf' size <size> autoextend on next <size> maximum <size>;
抠
- autoextend on ¤ò¿ä¾©¡£UNDO ¥»¥°¥á¥ó¥È¤¬¤¤¤Ã¤Ñ¤¤¤Ë¤Ê¤ë¤È DB ¤ËÂФ¹¤ë¹¹¿·½èÍý¤¬¤Ç¤¤Ê¤¯¤Ê¤ë
ɽÎΰè¤Îºï½ü†
ɽÎΰè¤ò¾Ã¤·¤Á¤ã¤¦¡£
ÊýË¡†
- ɽÎΰè¤òºï½ü¤¹¤ë
-- ´ðËܤÎʸ drop tablespace <tablespace-name> -- °Ê²¼¤Ï¥ª¥×¥·¥ç¥ó¤Ç»ØÄê²Äǽ including contents and datafiles ;
- ¥ª¥×¥·¥ç¥ó¤Ï°Ê²¼
¥ª¥×¥·¥ç¥ó ÀâÌÀ including contents ɽ¤ÎÃæ¤Ë¥»¥°¥á¥ó¥È¤¬Â¸ºß¤¹¤ë¾ì¹ç¤Ï¤³¤ì¤¬¤Ê¤¤¤È¥¨¥é¡¼¤¬½Ð¤ë¡£Ãæ¿È¤´¤È¾Ã¤¹¤è¡¢¤Î°ÕÌ£ and datafiles ɽÎΰè¤Î¼ÂÂΤǤ¢¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤´¤Èºï½ü¤¹¤ë¤è¡¢¤Î°ÕÌ£¡£»ØÄꤷ¤Ê¤±¤ì¤Ð¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Ï»Ä¤ë
- ¥ª¥×¥·¥ç¥ó¤Ï°Ê²¼
´û¸¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òºÆÍøÍѤ·¤ÆɽÎΰè¤òºî¤ë†
´û¤Ë¤¢¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òºÆÍøÍѤ¹¤ë¡£Ãæ¤Î¥Ç¡¼¥¿¤Ï¾Ã¤¨¤ë¡£¾Ã¤¨¤ë¡Ä¡Ä ¾Ã¤¨¤Á¤ã¤¦¡Ä¡Ä¡£
Ãæ¿È¤Ï¾Ã¤¨¤ë¤Î¤ÇɽÎΰè¤Î°Ü¹Ô¤Ë¤Ï»È¤¨¤Ê¤¤¡£°Ü¹Ô¤ò¤¹¤ë¤Ê¤é Data Pump ¤Ê¤É¤Ç¥¨¥¯¥¹¥Ý¡¼¥È / ¥¤¥ó¥Ý¡¼¥È¤¹¤ë¡£
ÊýË¡†
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òºÆÍøÍѤ·¤ÆɽÎΰè¤òºîÀ®¤¹¤ë
create tablespace <tablespace-name> datafile '/path/to/datafile.dbf' size <size> reuse;
- ¤½¤Î¾¤Î¥ª¥×¥·¥ç¥ó¤ÏºÆ»ØÄê²Äǽ
ɽÎΰè¤Î¥µ¥¤¥º¤ò³ÈÄ¥†
¥Ñ¥¿¥ó¤¬¤¤¤¯¤Ä¤«¤¢¤ë¡£
- ɽÎΰè¤Ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òÄɲ乤ë
- ɽÎΰè¤Ë°¤¹¤ë´û¸¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î¥µ¥¤¥º¤ò³ÈÄ¥¤¹¤ë
- ¼«Æ°¤Ç³ÈÄ¥¤¹¤ë
- ¼êÆ°¤Ç³ÈÄ¥¤¹¤ë
ÊýË¡†
- ɽÎΰè¤ò³ÈÄ¥¤¹¤ë
-- ¿·¤·¤¤¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤ò´û¸¤ÎɽÎΰè¤ËÄɲ乤ë alter tablespace <tablespace-name> add datafile '/path/to/datafile.dbf' size <size>; -- ´û¸¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î¥µ¥¤¥º¤ò¼«Æ°¤Ç³ÈÄ¥¤¹¤ë alter database <database-name> datafile '/path/to/datafile.dbf' autoextend on next <size> maxsize <size>; -- ´û¸¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î¥µ¥¤¥º¤ò¼êÆ°¤Ç³ÈÄ¥¤¹¤ë alter database <database-name> datafile '/path/to/datafile.dbf' resize <size>;
抠
- ¿·¤·¤¤¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤ÎÄɲûþ¤â autoextend ¤Ê¤É¥ª¥×¥·¥ç¥ó¤Ï»ØÄê²Äǽ
ɽÎΰè¤Î¥ª¥ó¥é¥¤¥ó¤È¥ª¥Õ¥é¥¤¥ó¤ÎÊѹ¹†
ɽÎΰè¤ò¥ª¥ó¥é¥¤¥ó¤Ë¤·¤¿¤ê¥ª¥Õ¥é¥¤¥ó¤Ë¤·¤¿¤ê¤¹¤ë¡£
ÊýË¡†
- ɽÎΰè¤Î¥ª¥ó¥é¥¤¥ó¤È¥ª¥Õ¥é¥¤¥ó¤òÀÚ¤êÂؤ¨¤ë
-- ¥ª¥Õ¥é¥¤¥ó¤Ë¤¹¤ë alter tablespace <tablespace-name> offline; -- ¥ª¥ó¥é¥¤¥ó¤Ë¤¹¤ë alter tablespace <tablespace-name> online;
抠
- ¥ª¥Õ¥é¥¤¥ó¤Ë¤·¤¿¤È¤¡¢¤½¤ÎɽÎΰè¤Ë°¤¹¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Ë¥Á¥§¥Ã¥¯¥Ý¥¤¥ó¥È¤¬È¯À¸¤¹¤ë¡£»ÈÍѺѥХåե¡¤òÁ´¤Æ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Ë½ñ¤½Ð¤·¡¢¥Ø¥Ã¥À¤Ë¥Á¥§¥Ã¥¯¥Ý¥¤¥ó¥È¾ðÊó¡Ê¤½¤Î»þÅÀ¤ÇºÇ¿·¤Î SCN¡Ë¤ò½ñ¤¹þ¤ó¤Ç¤«¤é¥Õ¥¡¥¤¥ë¤ò¥¯¥í¡¼¥º¤¹¤ë
ɽÎΰè¤ÎÆɤ߽ñ¤µö²Ä¤òÊѹ¹¤¹¤ë†
ɽÎΰè¤òÆɤ߼è¤êÀìÍѡʽñ¤¹þ¤ß¶Ø»ß¡Ë¤Ë¤·¤¿¤ê¡¢Æɤ߽ñ¤²Äǽ¤Ë¤·¤¿¤ê¤¹¤ë¡£
ÊýË¡†
- ɽÎΰè¤ÎÆɤ߽ñ¤µö²Ä¤òÊѹ¹¤¹¤ë
-- ɽÎΰè¤òÆɤ߼è¤êÀìÍѤˤ¹¤ë alter tablespace <tablespace-name> read only; -- ɽÎΰè¤ò½ñ¤¹þ¤ßµö²Ä¤Ë¤¹¤ë alter tablespace <tablespace-name> read write;
抠
- Æɤ߽ñ¤µö²Ä¤òÊѹ¹¤¹¤ë¤È¤½¤ÎɽÎΰè¤Ë°¤¹¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Ë¥Á¥§¥Ã¥¯¥Ý¥¤¥ó¥È¤¬È¯À¸¤¹¤ë¡£CKPT ¤Ï¤½¤Î»þÅÀ¤ÇºÇ¿·¤Î SCN ¤ò¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î¥Ø¥Ã¥À¤Ë½ñ¤¹þ¤à
¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°†
Ä̾ï¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤òÊ̤ξì½ê¤Ë°ÜÆ°¡Ê¤Þ¤¿¤Ï¥ê¥Í¡¼¥à¡Ë¤¹¤ë¡£¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤ëɽÎΰè¸ÂÄê¡£¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤Ê¤¤É½Îΰè¤Ï¡¢°ìÅÙ¥·¥ã¥Ã¥È¥À¥¦¥ó¤·¤Æ¤«¤é°ÜÆ°¤µ¤»¤ëÊýË¡¡Ê¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤Ê¤¤É½Îΰè¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°¡Ë¤ò¼è¤ëɬÍפ¬¤¢¤ë¡£
ÊýË¡†
- ɽÎΰè¤ò¥ª¥Õ¥é¥¤¥ó¤Ë¤¹¤ë
alter tablespace <tablespace-name> offline;
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤ò°ÜÆ°¤¹¤ë
mv /path/to/datafile.dbf /new/path/to/newdatafile.dbf
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°¤òÅÐÏ¿¤¹¤ë
alter tablespace <tablespace-name> rename datafile '/path/to/datafile.dbf' to '/new/path/to/newdatafile.dbf';
- ɽÎΰè¤ò¥ª¥ó¥é¥¤¥ó¤Ë¤¹¤ë
alter tablespace <tablespace-name> online;
抠
- ɽÎΰè¤ò¥ª¥Õ¥é¥¤¥ó¤Ë¤¹¤ë¤È¡¢¤½¤ì¤Ë°¤¹¤ë¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤â¥ª¥Õ¥é¥¤¥ó¤Ë¤Ê¤ë
¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤Ê¤¤É½Îΰè¤Î¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°†
ɽÎΰ褬¥ª¥Õ¥é¥¤¥ó¤Ë¤Ç¤¤º¡¢Á°½Ò¤ÎÊýË¡¤Ç¤ÏÂбþ¤Ç¤¤Ê¤¤¾ì¹ç¤Ï¤³¤Á¤é¤ÎÊýË¡¤ò¼è¤ë¡£Î㤨¤Ð SYSTEM ɽÎΰè¤ä UNDO ɽÎΰ衣
ÊýË¡†
- ¥Ç¡¼¥¿¥Ù¡¼¥¹¤ò¥·¥ã¥Ã¥È¥À¥¦¥ó¤¹¤ë
shutdown immediate;
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤ò°ÜÆ°¤¹¤ë
mv /path/to/datafile.dbf /new/path/to/newdatafile.dbf
- ¥Ç¡¼¥¿¥Ù¡¼¥¹¤ò¥Þ¥¦¥ó¥È¾õÂ֤ǵ¯Æ°¤¹¤ë
startup mount
- ¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤Î°ÜÆ°¤òÅÐÏ¿¤¹¤ë
alter database <database-name> rename file '/path/to/datafile.dbf' to '/new/path/to/newdatafile.dbf';
- ¥Ç¡¼¥¿¥Ù¡¼¥¹¤ò¥ª¡¼¥×¥ó¤¹¤ë
alter database open;
抠
- °ÜÆ°¤ÎÅÐÏ¿¤Î¥³¥Þ¥ó¥É¡ÊALTER ... RENAME ...¡Ë¤¬Á°½Ò¤ÎÊýË¡¤È¤Ï¼ã´³°ã¤¦¤Î¤ÇÃí°Õ¡£ALTER TABLESPACE ¤Ç¤Ï¤Ê¤¯ ALTER DATABASE ¤À¤·¡¢RENAME DATAFILE ¤Ç¤Ï¤Ê¤¯ RENAME FILE¡£¤ä¤ä¤³¤·¤¤
UNDO ɽÎΰè¤ÎÀÚ¤êÂؤ¨†
UNDO ɽÎΰè¤È¤·¤ÆÊ̤ÎɽÎΰè¤ò»È¤¦¤è¤¦¤ËÀÚ¤êÂؤ¨¤ë¡£
ÊýË¡†
- UNDO ɽÎΰè¤òÀÚ¤êÂؤ¨¤ë
alter system set undo_tablespace = <tablespace-name>;
ɽÎΰè¤Î¾ðÊó¤Î³Îǧ†
Ä̾ï¤ÎɽÎΰè¤Î¾ðÊó¤Î³Îǧ†
ɽÎΰè´Ø·¸¤Î¾ðÊó¤Ï°Ê²¼¤Î¥Ó¥å¡¼¤«¤é³Îǧ¤Ç¤¤ë¡£
- dba_tablespaces
- dba_data_files
- v$tablespace
- v$datafile
ɽÎΰèÆâ¤Îɽ¥ª¥Ö¥¸¥§¥¯¥È¤ä¥»¥°¥á¥ó¥È¡¢¥¨¥¯¥¹¥Æ¥ó¥È¤Î¾ðÊó¤Ï°Ê²¼¤«¤é¡£
- dba_tables
- table_name, initial_extent, min_extents, owner, tablespace_name ¤Ê¤É
- ɽ¤È¤·¤ÆɬÍפʾðÊó¡£Î㤨¤Ð initial_extent ¤Ï¡ØɬÍפʽé´ü¥¨¥¯¥¹¥Æ¥ó¥È¥µ¥¤¥º¡Ù¤Ç¤¢¤Ã¤Æ¡¢¼ÂºÝ¤Ë³ä¤êÅö¤Æ¤é¤ì¤Æ¤¤¤ë½é´ü¥¨¥¯¥¹¥Æ¥ó¥È¥µ¥¤¥º¤È¤Ï°Û¤Ê¤ë²ÄǽÀ¤¬¤¢¤ë
- dba_segments
- owner, segment_name, bytes, extents, initial_extents, min_extents ¤Ê¤É
- dba_extents
- owner, segment_name, tablespace_name, file_id, extent_id, bytes ¤Ê¤É
- dba_rollback_segs
UNDO ɽÎΰè¤Ë´Ø¤¹¤ë¾ðÊó¤Î³Îǧ†
UNDO ¤Ë´Ø¤¹¤ë¾ðÊó¤Ï°Ê²¼¤Î½é´ü²½¥Ñ¥é¥á¡¼¥¿¤Ç³Îǧ¤Ç¤¤ë¡£
- undo_management
- ¼«Æ° UNDO ´ÉÍý¤ÎÀßÄê
- undo_retention
- UNDO ¾ðÊó¤ÎÊÝ»ý»þ´Ö
- ¤³¤ì¤Ë²Ã¤¨¤Æ±Ê³ɽÎΰè¤ËÂФ¹¤ë retention guarantee ¤Ë¤è¤Ã¤ÆÊݸ¤òÊݾڤǤ¤ë
- undo_tablespace
- »ÈÍѤ·¤Æ¤¤¤ë UNDO ɽÎΰè
Last-modified: 2011-11-02 (¿å) 11:13:49