테이블명의 컬럼, 인덱스, 파티션 내역 조회
SPOOL TABLE_INDEX_PARTITION_LIST.TXT;
SET LINESIZE 1000;
SET PAGESIZE 10000;
SELECT * FROM USER_TABLES ORDER BY TABLE_NAME;
COL DATA_TYPE FORMAT A50;
SELECT * FROM USER_TAB_COLUMNS ORDER BY TABLE_NAME;
SELECT * FROM USER_TAB_PARTITIONS ORDER BY TABLE_NAME;
COL PARAMETERS FORMAT A50;
SELECT * FROM USER_INDEXES ORDER BY TABLE_NAME;
COL COLUMN_NAME FORMAT A50;
SELECT * FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
SELECT * FROM USER_IND_PARTITIONS;
SPOOL OFF;
<결과파일>
SPOOL TABLE_INDEX_PARTITION_LIST.TXT
SQL> desc USER_TABLES
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
SQL>
SQL> desc USER_TAB_COLUMNS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
SQL>
SQL> desc USER_TAB_PARTITIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
SQL>
SQL> desc USER_INDEXES
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
SQL>
SQL> desc USER_IND_COLUMNS
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_POSITION NUMBER
COLUMN_LENGTH NUMBER
CHAR_LENGTH NUMBER
DESCEND VARCHAR2(4)
SQL>
SQL> desc USER_IND_PARTITIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
STATUS VARCHAR2(8)
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
PCT_DIRECT_ACCESS NUMBER
GLOBAL_STATS VARCHAR2(3)
DOMIDX_OPSTATUS VARCHAR2(6)
PARAMETERS VARCHAR2(1000)
SQL>
