오라클 테이블 조회
--오라클 전체 테이블 조회
SELECT * FROM ALL_TABLES;
SELECT * FROM DBA_TABLES;
--접속 계정 테이블 조회
SELECT * FROM TABS;
SELECT * FROM USER_TABLES
조회 결과
OWNER|TABLE_NAME |TABLESPACE_NAME|CLUSTER_NAME |IOT_NAME|STATUS|PCT_FREE|PCT_USED|INI_TRANS|MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|FREELISTS|FREELIST_GROUPS|LOGGING|BACKED_UP|NUM_ROWS|BLOCKS|EMPTY_BLOCKS|AVG_SPACE|CHAIN_CNT|AVG_ROW_LEN|AVG_SPACE_FREELIST_BLOCKS|NUM_FREELIST_BLOCKS|DEGREE |INSTANCES |CACHE|TABLE_LOCK|SAMPLE_SIZE|LAST_ANALYZED |PARTITIONED|IOT_TYPE|TEMPORARY|SECONDARY|NESTED|BUFFER_POOL|FLASH_CACHE|CELL_FLASH_CACHE|ROW_MOVEMENT|GLOBAL_STATS|USER_STATS|DURATION|SKIP_CORRUPT|MONITORING|CLUSTER_OWNER|DEPENDENCIES|COMPRESSION|COMPRESS_FOR|DROPPED|READ_ONLY|SEGMENT_CREATED|RESULT_CACHE|
-----|------------------------------|---------------|--------------------|--------|------|--------|--------|---------|---------|--------------|-----------|-----------|-----------|------------|---------|---------------|-------|---------|--------|------|------------|---------|---------|-----------|-------------------------|-------------------|----------|----------|-----|----------|-----------|-------------------|-----------|--------|---------|---------|------|-----------|-----------|----------------|------------|------------|----------|--------|------------|----------|-------------|------------|-----------|------------|-------|---------|---------------|------------|
SYS |TYPE_MISC$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 1312| 734| 0| 0| 0| 46| 0| 0| 1| 1| N|ENABLED | 1312|2010-04-02 13:28:58|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |ATTRCOL$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 1383| 1359| 0| 0| 0| 43| 0| 0| 1| 1| N|ENABLED | 1383|2019-10-28 22:01:05|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |ASSEMBLY$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 0| 729| 0| 0| 0| 0| 0| 0| 1| 1| N|ENABLED | 0|2010-04-02 13:28:01|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |LIBRARY$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 139| 734| 0| 0| 0| 47| 0| 0| 1| 1| N|ENABLED | 139|2010-04-02 13:28:23|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |VIEWTRCOL$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 1| 1359| 0| 0| 0| 28| 0| 0| 1| 1| N|ENABLED | 1|2019-10-28 22:00:29|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |ICOLDEP$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 23| 734| 0| 0| 0| 11| 0| 0| 1| 1| N|ENABLED | 23|2010-04-02 13:28:19|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |OPQTYPE$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 182| 1359| 0| 0| 0| 27| 0| 0| 1| 1| N|ENABLED | 182|2019-10-28 22:01:07|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |REFCON$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 57| 1359| 0| 0| 0| 14| 0| 0| 1| 1| N|ENABLED | 57|2019-10-28 22:00:28|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |NTAB$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 241| 734| 0| 0| 0| 16| 0| 0| 1| 1| N|ENABLED | 241|2010-04-02 13:28:28|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |SUBCOLTYPE$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 82| 1359| 0| 0| 0| 45| 0| 0| 1| 1| N|ENABLED | 82|2019-10-28 22:01:05|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |COLTYPE$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 1462| 734| 0| 0| 0| 48| 0| 0| 1| 1| N|ENABLED | 1462|2010-04-02 13:28:08|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
SYS |LOB$ |SYSTEM |C_OBJ# | |VALID | 0| 0| 0| 0| 139264| 204800| 1| 2147483645| | 1| 1|YES |N | 901| 1359| 0| 0| 0| 50| 0| 0| 1| 1| N|ENABLED | 901|2019-10-28 22:01:07|NO | |N |N |NO |DEFAULT |DEFAULT |DEFAULT |DISABLED |YES |NO | |DISABLED |YES |SYS |DISABLED |DISABLED | |NO |NO |YES |DEFAULT |
오라클 테이블 코멘트 조회
--오라클 테이블 코멘트 조회
SELECT * FROM DBA_TAB_COMMENTS;
SELECT * FROM ALL_TAB_COMMENTS;
SELECT * FROM USER_TAB_COMMENTS;
조회 결과
OWNER|TABLE_NAME |TABLE_TYPE|COMMENTS |
-----|------------------------------|----------|--------------------------------------------------------------------------------------------------|
SYS |JAVA$COMPILER$OPTIONS |TABLE | |
SYS |JAVA$JVM$RJBC |TABLE | |
SYS |JAVA$JVM$RUNTIME$PARAMETERS |TABLE | |
SYS |JAVA$JVM$STATUS |TABLE | |
SYS |JAVA$JVM$STEPS$DONE |TABLE | |
SYS |JAVA$MC$ |TABLE | |
SYS |JAVA$MC$DEPS |TABLE | |
SYS |JAVA$METHOD$METADATA |TABLE | |
SYS |JAVA$POLICY$ |TABLE | |
오라클 컬럼 조회
--오라클 컬럼 조회
SELECT * FROM COLS;
SELECT * FROM DBA_TAB_COLUMNS;
SELECT * FROM ALL_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLUMNS;
조회 결과
OWNER|TABLE_NAME |COLUMN_NAME |DATA_TYPE|DATA_TYPE_MOD|DATA_TYPE_OWNER|DATA_LENGTH|DATA_PRECISION|DATA_SCALE|NULLABLE|COLUMN_ID|DEFAULT_LENGTH|DATA_DEFAULT|NUM_DISTINCT|LOW_VALUE |HIGH_VALUE |DENSITY |NUM_NULLS|NUM_BUCKETS|LAST_ANALYZED |SAMPLE_SIZE|CHARACTER_SET_NAME|CHAR_COL_DECL_LENGTH|GLOBAL_STATS|USER_STATS|AVG_COL_LEN|CHAR_LENGTH|CHAR_USED|V80_FMT_IMAGE|DATA_UPGRADED|HISTOGRAM |
-----|----------------|-------------------|---------|-------------|---------------|-----------|--------------|----------|--------|---------|--------------|------------|------------|--------------------------------|--------------------|----------------------|---------|-----------|-------------------|-----------|------------------|--------------------|------------|----------|-----------|-----------|---------|-------------|-------------|---------------|
SYS |ICOL$ |SPARE6 |DATE | | | 7| | |Y | 14| | | 0| | | 0| 2791| 0|2010-04-02 13:28:19| | | |YES |NO | 1| 0| |NO |YES |NONE |
SYS |ICOL$ |SPARE5 |VARCHAR2 | | | 1000| | |Y | 13| | | 0| | | 0| 2791| 0|2010-04-02 13:28:19| |CHAR_CS | 1000|YES |NO | 0| 1000|B |NO |YES |NONE |
SYS |ICOL$ |SPARE4 |VARCHAR2 | | | 1000| | |Y | 12| | | 0| | | 0| 2791| 0|2010-04-02 13:28:19| |CHAR_CS | 1000|YES |NO | 0| 1000|B |NO |YES |NONE |
SYS |ICOL$ |SPARE3 |NUMBER | | | 22| | |Y | 11| | | 1| | | 1| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 2| 0| |NO |YES |NONE |
SYS |ICOL$ |SPARE2 |NUMBER | | | 22| | |Y | 10| | | 1| | | 0.000179147259046937| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 2| 0| |NO |YES |FREQUENCY |
SYS |ICOL$ |SPARE1 |NUMBER | | | 22| | |Y | 9| | | 2| |¾ | 0.5| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 3| 0| |NO |YES |NONE |
SYS |ICOL$ |INTCOL# |NUMBER | | | 22| | |N | 8| | | 42|¾ |½ ; | 0.000179147259046937| 0| 42|2010-04-02 13:28:19| 2791| | |YES |NO | 4| 0| |NO |YES |FREQUENCY |
SYS |ICOL$ |OFFSET |NUMBER | | | 22| | |N | 7| | | 1| | | 1| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 2| 0| |NO |YES |NONE |
SYS |ICOL$ |SEGCOLLENGTH |NUMBER | | | 22| | |N | 6| | | 1| | | 1| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 2| 0| |NO |YES |NONE |
SYS |ICOL$ |SEGCOL# |NUMBER | | | 22| | |N | 5| | | 1| | | 1| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 2| 0| |NO |YES |NONE |
SYS |ICOL$ |POS# |NUMBER | | | 22| | |N | 4| | | 32|¾ |¾! | 0.03125| 0| 1|2010-04-02 13:28:19| 2791| | |YES |NO | 3| 0| |NO |YES |NONE |
오라클 컬럼 코멘트 조회
--오라클 컬럼 코멘트 조회
SELECT * FROM DBA_COL_COMMENTS;
SELECT * FROM ALL_COL_COMMENTS;
SELECT * FROM USER_COL_COMMENTS;
조회 결과
OWNER|TABLE_NAME |COLUMN_NAME |COMMENTS|
-----|----------------|-------------------|--------|
SYS |ICOL$ |OBJ# | |
SYS |ICOL$ |BO# | |
SYS |ICOL$ |COL# | |
SYS |ICOL$ |POS# | |
SYS |ICOL$ |SEGCOL# | |
SYS |ICOL$ |SEGCOLLENGTH | |
SYS |ICOL$ |OFFSET | |
SYS |ICOL$ |INTCOL# | |
SYS |ICOL$ |SPARE1 | |
SYS |ICOL$ |SPARE2 | |
SYS |ICOL$ |SPARE3 | |
SYS |ICOL$ |SPARE4 | |'DATABASE > ORACLE' 카테고리의 다른 글
| 오라클 현재 접속 세션의 스키마 변경 (0) | 2020.01.30 |
|---|---|
| 오라클 디렉토리 만들기 (0) | 2020.01.28 |
| 오라클 테이블 컬럼 추가/변경/삭제/컬럼명 변경 방법 (0) | 2020.01.13 |
| 오라클 사용자 LOCK 풀기 암호만료 해결하기(ORA-28000) (0) | 2020.01.13 |
| 오라클 시노님(동의어) 생성/삭제/조회 방법 (0) | 2020.01.13 |
