//************************************************ -- µ¥ÀÌÅÍ µñ¼Å³Ê¸®(Data Dictionary) : TABLE°ú VIEWµéÀÇ ÁýÇÕÀ¸·Î DATABASE¿¡ -- ´ëÇÑ Á¤º¸¸¦ Á¦°øÇÏ´Â Áß¿äÇÑ ºÎºÐÀ¸·Î DATABASE »ý¼º½Ã SYS schema ¾ÈÀÇ -- ³»ºÎ TABLE·Î ±¸¼º ------------------------------------------------------------------ SELECT COUNT(*) FROM DICTIONARY; ------------------------------------------------------------------ SELECT COUNT(*) FROM USER_TABLES; SELECT COUNT(*) FROM TABS; SELECT * FROM TABS; -- USER_TABLES ¿Í µ¿ÀÏ SELECT * FROM TAB; ------------------------------------------------------------------ -- Ä÷³¸í¸¸ È®ÀÎ SELECT cname FROM col WHERE tname='INSA'; ------------------------------------------------------------------ -- Ä÷³¸í ¹× ÀÚ·áÇüµî Ãâ·Â SELECT column_name, data_type, data_length,data_precision FROM USER_tab_columns WHERE table_name='INSA'; SELECT column_name, data_type, data_length,data_precision FROM cols WHERE table_name='INSA'; ------------------------------------------------------------------ -- Á¦¾àÁ¶°Ç È®ÀÎ SELECT * FROM USER_constraints WHERE table_name='INSA'; SELECT constraint_name, table_name, constraint_type FROM user_constraints; ------------------------------------------------------------------ -- »ç¿ëÀÚ¿¡°Ô ÁÖ¾îÁø ±ÇÇÑ SELECT * FROM USER_sys_privs; //************************************************ -- ÀÚ·áÇü ------------------------------------------------------------------ -- ¹®ÀÚ µ¥ÀÌÅÍ Å¸ÀÔ CHAR(n) : °íÁ¤±æÀÌ ¹®ÀÚ µ¥ÀÌÅ͸¦ ÀúÀåÇϸç ÃÖ´ë ±æÀÌ´Â 2000ÀÚÀÌ°í ±æÀ̸¦ ¸í½ÃÇÏÁö ¾ÊÀ¸¸é ±âº» ±æÀÌ´Â 1 NLS(±¹°¡º° ¾ð¾î ÁýÇÕ)´Â Çѱ۰ú ¿µ¹®¸¸ °¡´É ÇÏ´Ù. ³²Àº °ø°£Àº °ø¹é(space)·Î ä¿î´Ù. VARCHAR2(n) : °¡º¯ ±æÀÌ ¹®ÀÚ µ¥ÀÌÅ͸¦ ÀúÀåÇϸç ÃÖ´ë ±æÀÌ´Â 4000ÀÚÀÌ°í, ¹Ýµå½Ã ±æÀ̸¦ ¸í½ÃÇØ¾ß ÇÑ´Ù. NLS(±¹°¡º° ¾ð¾î ÁýÇÕ)´Â Çѱ۰ú ¿µ¹®¸¸ °¡´ÉÇÏ´Ù. VARCHAR ´Â ÃÖ´ë 2000°³ ¹®ÀÚ¸¦ ÀúÀåÇϸç VARCHAR2¿Í´Â ´Ù¸£°Ô VARCHAR(10)·Î ¼±¾ðÇϸé nullÀ» ä¿ö ½ÇÁ¦·Î´Â 10°³ÀÇ °ø°£À» »ç¿ëÇÑ´Ù. ÇÏÁö¸¸ VARCHAR2(10)´Â ÇÊ¿äÇÑ ¹®ÀÚ±îÁö¸¸ ÀúÀåÇÏ´Â variable lengthÀ̸ç ÃÖ´ë 4000°³ ¹®ÀÚ±îÁö ÀúÀåÇÒ ¼ö ÀÖ´Ù. NCHAR, NVARCHAR2 : ´Ù¾çÇÑ ¾ð¾îÀÇ ¹®ÀÚ °ª(±¹°¡º° ¾ð¾î ÁýÇÕ : NLS)À» ÀúÀåÇÏ°í Á¶È¸ÇÒ ¼ö ÀÖ´Ù. ¹ÙÀÌÆ® ´ÜÀ§°¡ ¾Æ´Ñ ¹®ÀÚ ´ÜÀ§·Î Ä÷³ ±æÀ̸¦ ÁöÁ¤ÇÑ´Ù. ¿¹¸¦ µé¾î NCHAR(7) ´Â Çѹ®ÀÚ°¡ 2ByteÀÎ °æ¿ì 14Byte°¡ »ç¿ëµÈ´Ù. -- ¼ýÀÚ µ¥ÀÌÅÍ Å¸ÀÔ NUMBER(P, S) : P(1~38)´Â Á¤¹Ðµµ·Î Àüü ÀÚ¸®¼ö¸¦ ³ªÅ¸³»¸ç ±âº» °ªÀÌ 38ÀÌ°í S(-84~127)´Â ¼Ò¼öÁ¡ ÀÌÇÏÀÇ ÀÚ¸´¼öÀÌ´Ù. Á¤¼ö³ª ½Ç¼ö ÀúÀåÇϱâ À§ÇÑ °¡º¯±æÀÌÀÇ Ç¥ÁØ ³»ºÎ Çü½ÄÀÌ´Ù. -- »ç¿ë ¿¹ - NUMBER : NUMBER(38, 0)°ú µ¿ÀÏ - NUMBER(5) : ´Ù¼¸ ÀÚ¸® Á¤¼ö - NUMBER(10, 3) : 1234567.6789 ÀÔ·Â ½Ã 1234567.679°¡ ÀúÀåµÇ¸ç 12345678.678 ÀÔ·Â ½Ã Overflow ¿¡·¯°¡ ¹ß»ý ÇÑ´Ù. -- ³¯Â¥ µ¥ÀÌÅÍ Å¸ÀÔ DATE : ¡º³â/¿ù/ÀÏ ½Ã:ºÐ:ÃÊ¡»±îÁö ÀúÀåÇϸç, ±âº»ÀûÀ¸·Î ³â/¿ù/ÀÏ Á¤º¸¸¦ Ãâ·ÂÇÑ´Ù. //************************************************ -- Å×À̺í ÀÛ¼º ¾ÆÀ̵ð ¼ýÀÚ 7 À̸§ ¹®ÀÚ 20 ³ªÀÌ ¼ýÀÚ 3 ÀüÈ­¹øÈ£ ¹®ÀÚ 25 (¿ìÆí¹øÈ£, Çйø .. : ¹®ÀÚ) -- Å×À̺í ÀÛ¼º CREATE TABLE test1 ( id NUMBER(7) , name VARCHAR2(20) , age NUMBER(3) , tel VARCHAR2(25) ); -- Å×ÀÌºí ¸ñ·Ï È®ÀÎ SELECT * FROM tab; SELECT table_name FROM tabs; -- Å×ÀÌºí ±¸Á¶ È®ÀÎ DESC test1; //************************************************ -- Å×ÀÌºí¿¡ Çʵå Ãß°¡ ALTER TABLE Å×À̺íÀ̸§ ADD (Ä÷³¸í datatype [DEFAULT °ª] [, Ä÷³¸í datatype]...); ALTER TABLE test1 ADD (birth DATE, bigo VARCHAR2(255)); DESC test1; //************************************************ -- Å×À̺íÀÇ Ä÷³Æø ¹× ÀÚ·áÇü º¯°æ ALTER TABLE Å×À̺í¸í MODIFY (Ä÷³¸í datatype [DEFAULT °ª] [, Ä÷³¸í datatype]...); ALTER TABLE test1 MODIFY (bigo VARCHAR2(200)); DESC test1; //************************************************ -- Å×À̺íÀÇ Çʵå¸í º¯°æ ALTER TABLE Å×À̺í¸í RENAME COLUMN º¯°æÀüÄ÷³¸í TO »õ·Î¿îÄ÷³¸í; ALTER TABLE test1 RENAME COLUMN bigo TO memo DESC test1; //************************************************ -- Å×À̺íÀÇ ÇÊµå »èÁ¦ ALTER TABLE Å×À̺í¸í DROP COLUMN Ä÷³¸í; ALTER TABLE test1 DROP COLUMN age; DESC test1; //************************************************ -- Å×À̺í¸í º¯°æ : Å×À̺í, ºä, ½ÃÄö½º, ½Ã³Ê´ÔÀÇ À̸§À» º¯°æ RENAME old_name TO new_name; RENAME test1 TO exam1; SELECT * FROM tab; //************************************************ -- ±âÁ¸ Å×À̺íÀ» ÀÌ¿ëÇÏ¿© Å×À̺í ÀÛ¼º CREATE TABLE Å×À̺í¸í [Ä÷³¸í (, Ä÷³¸í) ,...] AS subquery; CREATE TABLE insa1 AS SELECT num, name, basicpay, sudang, basicpay+sudang pay FROM INSA; SELECT * FROM tab; DESC insa1 SELECT * FROM insa1; -- Å×ÀÌºí ±¸Á¶¸¸ º¹»ç(Á¦¾àÁ¶°ÇÀº º¹»ç µÇÁö ¾Ê´Â´Ù) CREATE TABLE insa2 AS SELECT * FROM INSA WHERE 1=0; //************************************************ -- Å×ÀÌºí »èÁ¦ DROP TABLE Å×À̺í¸í [CASCADE CONSTRAINTS] [PURGE]; //************************************************ -- ÈÞÁöÅë DROP TABLE insa1; -- ÈÞÁöÅë¿¡ »èÁ¦µÈ Å×À̺í ÀúÀå SELECT * FROM TAB; -- ÈÞÁöÅë È®ÀÎ SELECT object_name, original_name, droptime, dropscn FROM recyclebin; -- ÈÞÁöÅë Å×ÀÌºí ³»¿ë È®ÀÎ(ÈÞÁöÅë Á¤º¸ È®ÀÎ ÈÄ object_name ¸íÀ¸·Î È®ÀÎ) SELECT * FROM "BIN$lMG0N7noSnCaFQ8LkJZm2Q==$0" ; -- ÈÞÁöÅë º¹¿ø(insa1 Àº »èÁ¦Çϱâ ÀüÀÇ ¿ø·¡ Å×À̺í¸í) FLASHBACK TABLE insa1 TO BEFORE DROP; SELECT * FROM TAB; -- ÈÞÁöÅë º¹¿ø(BIN À̸§(object_name)À¸·Î µÇµ¹¸®±â) FLASHBACK TABLE "BIN$lMG0N7noSnCaFQ8LkJZm2Q==$0" TO BEFORE DROP; -- ÈÞÁöÅë ºñ¿ì±â DROP TABLE insa2; PURGE RECYCLEBIN; SELECT * FROM TAB; -- ÈÞÁöÅë¿¡ ¹ö¸®Áö ¾Ê°í ¹Ù·Î Å×ÀÌºí »èÁ¦ DROP TABLE insa1 PURGE; SELECT * FROM TAB;