Oracle ¿¡¼ Å×À̺í°ú ¸®¼Ò½ºÀÇ Á¸Àç ¿©ºÎ¸¦ ¾Ë¾Æº¸´Â Äõ¸®
Oracle ¿¡¼ Å×À̺í°ú ¸®¼Ò½ºÀÇ Á¸Àç ¿©ºÎ¸¦ ¾Ë¾Æº¸´Â Äõ¸® |
°³¹ßȯ°æ : Oracle 11g, window 7 32bit |
¸ðµç ¿À¶óŬ¿¡ ÀÖ´Â Á¤º¸¸¦ ¾Ë¼ö ÀÖ´Â Å×À̺íµéÀºUSER_* ·Î ½ÃÀÛÇÑ´Ù.
¾Æ·¡´Â ÀÌ·± Á¤º¸µéÀ» ¾Ë¼ö ÀÖ´Â Å×ÀÌºí¿¡ ´ëÇÑ Á¤º¸ÀÌ´Ù
±×¸²Àº user_tables À» Á¶È¸ÇßÀ»¶§ÀÇ ³»¿ªÀÌ´Ù.
1. USER_TABLES : Å×À̺íÁ¤º¸
2. USER_TAB_COLUMNS : Ä÷³Á¤º¸
3. USER_OBJECTS : ¸ðµç ¿ÀºêÁ§Æ®ÀÇ Á¤º¸¸¦ ¾Ë·ÁÁÜ
4. USER_VIEWS : ºä¿¡ ´ëÇÑ Á¤º¸
5. USER_SYNONYMS : µ¿ÀÇÀÇ Á¤º¸
6. USER_SEQUENCES : ½ÃÄö½º Á¤º¸
7. USER_CONSTRAINTS : Á¦¾àÁ¶°Ç¿¡ ´ëÇÑ Á¤º¸
8. USER_CONS_COLUMNS : Á¦¾àÁ¶°Ç¿¡ ´ëÇÑ Ä÷³Á¤º¸
9. USER_TAB_COMMENTS : Å×À̺í/ºä¿¡ ´ëÇÑ ÁÖ¼®
10. USER_COL_COMMENTS : Ä÷³¿¡ ´ëÇÑ ÁÖ¼®
11. USER_INDEXES : À妽º¿¡ ´ëÇÑ Á¤º¸
12. USER_IND_COLUMNS : À妽º Ä÷³¿¡ ´ëÇÑ Á¤º¸
13. USER_CLUSTERS : Ŭ·¯½ºÅÍ¿¡ ´ëÇÑ Á¤º¸
14. USER_DB_LINKS : µ¥ÀÌÅͺ£À̽º ¸µÅ© Á¤º¸
15. USER_TRIGGERS : Æ®¸®°Å Á¤º¸
16. USER_SOURCE : ÇÁ·Î½ÃÀú, ÇÔ¼ö, ÆÐÅ°Áö Á¤º¸
17. USER_ERRORS : ÄÚµå ¿¡·¯¿¡ ´ëÇÑ Á¤º¸
18. USER_TABLESPACES : Å×ÀÌºí ½ºÆäÀ̽º Á¤º¸
19. USER_USERS : »ç¿ëÀÚ¿¡ ´ëÇÑ Á¤º¸
20. USER_TAB_PRIVS : Å×ÀÌºí ±ÇÇÑ¿¡ ´ëÇÑ Á¤º¸
21. USER_COL_PRIVS : Å×ÀÌºí¿ ±ÇÇÑ¿¡ ´ëÇÑ Á¤º¸
22. USER_SYS_PRIVS : ½Ã½ºÅÛ ±ÇÇÑ¿¡ ´ëÇÑ Á¤º¸
¾Æ·¡ ¿¹Á¦´Â À§¿¡¼ ¼Ò°³ÇÑ Å×À̺í Á¤º¸¸¦ Á¶ÇÕ¿¡¼ º¸±â Æí¸®ÇÏ°Ô ¸¸µç Äõ¸®ÀÌ´Ù.
Å×ÀÌºí¿¡ ´ëÇÑ Á¤º¸¸¦ »ó¼¼ÇÏ°Ô º¸¿©ÁØ´Ù.
SELECT COL.TABLE_NAME AS TNAME,
TCOM.COMMENTS AS TCMT,
COL.COLUMN_NAME AS COL_NM,
CCOM.COMMENTS AS COL_CMT,
COL.DATA_TYPE AS TYPE_CD,
DECODE (COL.DATA_TYPE,
'NUMBER', COL.DATA_PRECISION || '.' || COL.DATA_SCALE,
COL.DATA_LENGTH)
AS LENGTH,
COL.NULLABLE AS NULL_YN,
COL.DATA_DEFAULT AS D_DEFAULT,
COL.NUM_DISTINCT AS NUM_DISTINCT
FROM USER_TAB_COLUMNS COL, USER_TAB_COMMENTS TCOM, USER_COL_COMMENTS CCOM
WHERE COL.TABLE_NAME = TCOM.TABLE_NAME
AND COL.TABLE_NAME = CCOM.TABLE_NAME
AND COL.COLUMN_NAME = CCOM.COLUMN_NAME
AND COL.TABLE_NAME = 'COUNTRIES'
ORDER BY COL.TABLE_NAME, COL.COLUMN_ID
Å×À̺í Á¤º¸¿Í Á¦¾àÁ¶°ÇÀ» ¾Ë·ÁÁÖ´Â Á¤º¸Å×À̺íÀ» Á¶ÇÕÇؼ PK Á¤º¸¸¦ Á¶È¸ÇÑ´Ù.
SELECT COL.TABLE_NAME AS TNAME,
TCOM.COMMENTS AS TCMT,
COL.COLUMN_NAME AS COL_NM,
CCOM.COMMENTS AS COL_CMT,
COL.COLUMN_ID ID,
PKCOL.COLUMN_POSITION AS PK,
COL.DATA_TYPE AS TYPE_CD,
DECODE (COL.DATA_TYPE,
'NUMBER', COL.DATA_PRECISION || '.' || COL.DATA_SCALE,
COL.DATA_LENGTH)
AS LENGTH,
COL.NULLABLE AS NULL_YN,
COL.DATA_DEFAULT AS D_DEFAULT,
COL.NUM_DISTINCT AS NUM_DISTINCT
FROM USER_TAB_COLUMNS COL,
USER_TAB_COMMENTS TCOM,
USER_COL_COMMENTS CCOM,
(SELECT AA.TABLE_NAME,
AA.INDEX_NAME,
AA.COLUMN_NAME,
AA.COLUMN_POSITION
FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB
WHERE BB.CONSTRAINT_TYPE = 'P'
AND AA.TABLE_NAME = BB.TABLE_NAME
AND AA.INDEX_NAME = BB.CONSTRAINT_NAME) PKCOL
WHERE COL.TABLE_NAME = TCOM.TABLE_NAME
AND COL.TABLE_NAME = CCOM.TABLE_NAME
AND COL.COLUMN_NAME = CCOM.COLUMN_NAME
AND COL.TABLE_NAME = 'COUNTRIES'
AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)
AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)
ORDER BY COL.TABLE_NAME, COL.COLUMN_ID
USER °¡ ¾Æ´Ñ ALL ·Î ½ÃÀÛÇÏ´Â Å×À̺í·Î Á¤º¸¸¦ ¾Ë¼ö ÀÖ´Â °Íµµ ÀÖ´Ù.
Á¶È¸ÇÏ¸é ³Ê¹« ¸¹À¸¹Ç·Î OWNER Çʵ忡 »ç¿ëÀÚ °èÁ¤À» Á¶°ÇÀ¸·Î °ÉÀÚ.
1. ALL_CATALOG : ¸ðµç Å×À̺í, ºäÁ¤º¸ Á¶È¸
2. ALL_OBJECT_TABLES : ¸ðµç ¿ÀºêÁ§Æ® Å×À̺í Á¤º¸
3. ALL_TAB_COMMENTS : ¸ðµç Å×À̺í ÁÖ¼® Á¤º¸
4. ALL_TYPES : ¸ðµç ¿ÀºêÁ§Æ® ŸÀÔ¿¡ ´ëÇÑ Á¤º¸
5. ALL_USERS : ¸ðµç »ç¿ëÀÚ Á¤º¸
2012-11-2 Ãß°¡ ÀÚ·á : ½Ã½ºÅÛ Á¤º¸ Á¶È¸ Å×À̺í |
Ãß°¡·Î ½Ã½ºÅÛ Á¤º¸¸¦ Á¶È¸ÇÒ¼ö ÀÖ´Â Å×ÀÌºí ¸ñ·ÏÀÔ´Ï´Ù.
* ALL_OBJECTS : ¿ÀºêÁ§Æ® Á¶È¸
select * from all_objects where object_name like '¿ÀºêÁ§Æ®¸í';
* ALL_SYNONYMS : ½Ã³ëÆÀ Á¶È¸
select * from all_synonyms where synonym_name='½Ã³ë´Ô¸í';
* ALL_IND_COLUMNS : Å×À̺í À妽º Á¤º¸ Á¶È¸
select * from all_ind_columns where table_name='Å×À̺í¸í';
* ALL_TAB_COLUMNS : Å×ÀÌºíº° Ä÷³Á¤º¸ Á¶È¸
select * from all_tab_columns where table_name='Å×À̺í¸í';
* ALL_COL_COMMENTS : Å×À̺í Ä÷³ comment Á¶È¸
select * from all_col_comments where table_name='Å×À̺í¸í';