# 테이블스페이스의 데이터파일의 물리적위치와 파일명, 테이블스페이스의 이름, 테이블스페이스의 크기, 테이블스페이스의 이용가능여부확인
SQL> desc DBA_DATA_FILES;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> COL FILE_NAME FORMAT A80
SQL> COL TABLESPACE_NAME FORMAT A30
SQL> SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;
# 테이블 스페이스별 사용가능한 공간의 확인
SQL> desc DBA_FREE_SPACE;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> SELECT tablespace_name, SUM(bytes), MAX(bytes) FROM DBA_FREE_SPACE GROUP BY tablespace_name
# 데이터 파일에 대한 총 크기와 남아있는 공간, 사용한 용량, 남은 %율
SQL> COL FILE_NAME FORMAT A80
SQL> COL TABLESPACE_NAME FORMAT A30
SQL> SET LINESIZE 300
SQL> SELECT b.file_name "FILE_NAME", -- DataFile Name
b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name
b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes
((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량
(sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량
(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 "FREE %" -- 남은 %
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name;
SQL> SELECT * FROM TABLE_01
UNION ALL
SELECT * FROM TABLE_02
UNION ALL
SELECT * FROM TABLE_03
......
UNION ALL
SELECT * FROM TABLE_11
UNION ALL
SELECT * FROM TABLE_12;
# INSERT
SQL> INSERT INTO 테이블명(컬럼1, 컬럼2, ...) VALUES(데이터, '데이터', ....);
SQL> INSERT INTO 테이블명(컬럼1, 컬럼2, ...)
SELECT 컬럼1, 컬럼2, ...
FROM 테이블명
WHERE 조건;
# UPDATE
SQL> UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2 WHERE 조건;
# DELETE
SQL> DELETE FROM 테이블명 WHERE 조건;
# 데이터 사전 정보 조회
- 조회할수있는 모든 데이터사전의 테이블이름과 설명 조회 : DICTIONARY(DIC)
SQL> desc DICTIONARY
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> desc DICT
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> SELECT * FROM DICTIONARY WHERE table_name LIKE '%INDEX%';
SQL> SELECT * FROM DICT WHERE table_name LIKE '%INDEX%';
- 데이터사전의 컬럼에 대한 정보 조회
SQL> desc DICT_COLUMNS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
* 오브젝트: USER_OBJECTS(OBJ)
SQL> desc USER_OBJECTS
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
* 테이블 : USER_TABLES (TABS)
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)
* 열 : USER_TAB_COLUMNS (COLS)
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)
* 뷰 : USER_VIEWS
SQL> desc USER_VIEWS
Name Null? Type
----------------------------------------- -------- ----------------------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
* 동의어 : USER_SYNONYMS (SYN)
SQL> desc USER_SYNONYMS
Name Null? Type
----------------------------------------- -------- ----------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
SQL>
* 시퀀스 : USER_SEQUENCES (SEQ)
SQL> desc USER_SEQUENCES
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
* 제약조건 : USER_CONSTRAINTS
SQL> DESC USER_CONSTRAINTS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
* 제약조건열 : USER_CONS_COLUMNS ( 제약 조건을 가진 열에 대한 정보)
SQL> desc USER_CONS_COLUMNS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER
SQL>
* 제약조건의 예외사항 : EXCEPTIONS 제약조건을 활성화시 에러사항에 대한 정보
* 테이블 주석 : USER_TAB_COMMENTS 테이블/뷰에 대한 주석
SQL> desc USER_TAB_COMMENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
COMMENTS VARCHAR2(4000)
SQL>
* 열 주석 : USER_COL_COMMENTS ( 열에 대한 주석)
SQL> desc USER_COL_COMMENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL>
* 인덱스 : USER_INDEXES (IND) ( 인덱스에 관한 정보)
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>
* 인덱스 열 : USER_IND_COLUMNS 인덱스열에 대한 정보
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> SELECT * FROM USER_IND_COLUMNS WHERE ROWNUM < 10;
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- ----
BA_ANAL_IDX01 BA_ANAL SUM_DT 1 8 8 ASC
BA_ANAL_IDX01 BA_ANAL GRPH_TYPE 2 2 2 ASC
BA_ANAL_IDX01 BA_ANAL UPJA_CD 3 2 2 ASC
BA_ANAL_IDX01 BA_ANAL X_ITEM_ORD 4 2 2 ASC
BA_ANAL_IDX01 BA_ANAL X_ITEM_NM 5 20 20 ASC
PK_BA_ANAL_SUM2 BA_ANAL_SUM SUM_DT 1 8 8 ASC
PK_BA_ANAL_SUM2 BA_ANAL_SUM UPJA_CD 2 2 2 ASC
PK_BA_CAP_CHG BA_CAP_CHG NCN 1 20 20 ASC
PK_BA_CAP_CHG BA_CAP_CHG CAP_CHG_DT 2 8 8 ASC
9 rows selected.
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 1000
SQL> COL COLUMN_NAME FORMAT A50
SQL> SELECT * FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
* 클러스터 : USER_CLUSTERS (CLU) 클러스터와 관련된 정보
SQL> desc USER_CLUSTERS
Name Null? Type
----------------------------------------- -------- ----------------------------
CLUSTER_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
KEY_SIZE NUMBER
INI_TRANS NOT NULL NUMBER
MAX_TRANS NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NOT NULL NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
AVG_BLOCKS_PER_KEY NUMBER
CLUSTER_TYPE VARCHAR2(5)
FUNCTION VARCHAR2(15)
HASHKEYS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
BUFFER_POOL VARCHAR2(7)
SINGLE_TABLE VARCHAR2(5)
DEPENDENCIES VARCHAR2(8)
SQL>
* 데이터베이스 링크 : USER_DB_LINKS 링크에 관련된 정보
SQL> desc USER_DB_LINKS
Name Null? Type
----------------------------------------- -------- ----------------------------
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(30)
PASSWORD VARCHAR2(30)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE
SQL>
* 스냅샷 : USER_SNAPSHOTS
SQL> desc USER_SNAPSHOTS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MASTER_VIEW VARCHAR2(30)
MASTER_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
MASTER_LINK VARCHAR2(128)
CAN_USE_LOG VARCHAR2(3)
UPDATABLE VARCHAR2(3)
REFRESH_METHOD VARCHAR2(11)
LAST_REFRESH DATE
ERROR NUMBER
FR_OPERATIONS VARCHAR2(10)
CR_OPERATIONS VARCHAR2(10)
TYPE VARCHAR2(8)
NEXT VARCHAR2(200)
START_WITH DATE
REFRESH_GROUP NUMBER
UPDATE_TRIG VARCHAR2(30)
UPDATE_LOG VARCHAR2(30)
QUERY LONG
MASTER_ROLLBACK_SEG VARCHAR2(30)
STATUS VARCHAR2(7)
REFRESH_MODE VARCHAR2(8)
PREBUILT VARCHAR2(3)
SQL>
* 스냅샷 로그 : USER_SNAPSHOT_LOGS
SQL> desc USER_SNAPSHOT_LOGS
Name Null? Type
----------------------------------------- -------- ----------------------------
LOG_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
LOG_TABLE VARCHAR2(30)
LOG_TRIGGER VARCHAR2(30)
ROWIDS VARCHAR2(3)
PRIMARY_KEY VARCHAR2(3)
OBJECT_ID VARCHAR2(3)
FILTER_COLUMNS VARCHAR2(3)
SEQUENCE VARCHAR2(3)
INCLUDE_NEW_VALUES VARCHAR2(3)
CURRENT_SNAPSHOTS DATE
SNAPSHOT_ID NUMBER
SQL>
* 트리거 : USER_TRIGGERS
SQL> desc USER_TRIGGERS
Name Null? Type
----------------------------------------- -------- ----------------------------
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
* 프로시저, 함수 및 패키지 : USER_SOURCE
SQL> desc USER_SOURCE
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL>
* 코드 오류 : USER_ERRORS
SQL> desc USER_ERRORS
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
SEQUENCE NOT NULL NUMBER
LINE NOT NULL NUMBER
POSITION NOT NULL NUMBER
TEXT NOT NULL VARCHAR2(4000)
SQL>
* 테이블스페이스 : USER_TABLESPACES
SQL> desc USER_TABLESPACES
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
SQL>
SQL> SELECT * FROM USER_TABLESPACES WHERE ROWNUM < 10;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- ------
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM MANUAL
UNDOTBS1 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM MANUAL
TEMP 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM MANUAL
CWMLITE 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
DRSYS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
INDX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
ODM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
TOOLS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM AUTO
9 rows selected.
* 영역 할당량 : USER_TS_QUOTAS 테이블스페이스 단위로 사용자가 이용할 수 있는 영역의 최대크기와 할당된 영역의 크기 파악에 대한 정보
SQL> desc USER_TS_QUOTAS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCKS NOT NULL NUMBER
MAX_BLOCKS NUMBER
SQL>
* 세그먼트와 익스텐트 : USER_SEGMENTS 와 USER_EXTENTS
SQL> desc USER_SEGMENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
BUFFER_POOL VARCHAR2(7)
SQL> desc USER_EXTENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
SQL>
* 여유 영역 : USER_FREE_SPACE 현재 여유로 표시된 영역이 얼마인지에 대한 정보
SQL> desc USER_FREE_SPACE
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL>
SQL> SELECT * FROM USER_FREE_SPACE WHERE TABLESPACE_NAME ='테이블스페이스이름';
SQL> SELECT * FROM USER_FREE_SPACE;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM 1 125065 13565952 1656 1
SYSTEM 32 5065 10944512 1336 32
UNDOTBS1 2 129 65536 8 2
UNDOTBS1 2 145 65536 8 2
UNDOTBS1 2 169 65536 8 2
UNDOTBS1 2 193 589824 72 2
UNDOTBS1 2 649 3145728 384 2
UNDOTBS1 2 1161 3145728 384 2
UNDOTBS1 2 2057 1048576 128 2
UNDOTBS1 2 2441 2077163520 253560 2
CWMLITE 3 737 262144 32 3
CWMLITE 3 1233 10878976 1328 3
DRSYS 4 1241 10813440 1320 4
EXAMPLE 5 19121 157941760 19280 5
INDX 6 233 65536 8 6
이하생략
* 사용자 : USER_USERS
SQL> desc USER_USERS
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL>
SQL> SELECT * FROM USER_USERS;
USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME
------------------------------ ---------- -------------------------------- --------- --------- ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------
PS 60 OPEN PS TEMP 22-MAR-04 DEFAULT_CONSUMER_GROUP
* 자원 제한량 : USER_RESOURCE_LIMITS
SQL> desc USER_RESOURCE_LIMITS
Name Null? Type
----------------------------------------- -------- ----------------------------
RESOURCE_NAME NOT NULL VARCHAR2(32)
LIMIT VARCHAR2(40)
SQL> SELECT * FROM USER_RESOURCE_LIMITS;
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
9 rows selected.
* 테이블 권한 : USER_TAB_PRIVS
SQL> desc USER_TAB_PRIVS
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
SQL> SELECT * FROM USER_TAB_PRIVS;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
SYS PS SMP_VIEW_REGISTRY PS SELECT YES NO
SYS PS SMP_VIEW_VERSIONS PS SELECT YES NO
SYS PS SMP_VIEW_ADMINISTRATORS PS SELECT YES NO
SYS PS SMP_VIEW_ADMIN_CREDENTIALS PS SELECT YES NO
SYS PS SMP_VIEW_ADMIN_SETUP PS SELECT YES NO
SYS PS SMP_VIEW_AGENTS PS SELECT YES NO
SYS PS SMP_VIEW_TARGETS PS SELECT YES NO
SYS PS SMP_VIEW_TARGET_PROPERTIES PS SELECT YES NO
SYS PS SMP_VIEW_NODES PS SELECT YES NO
SYS PS SMP_VIEW_NODE_SERVICES PS SELECT YES NO
SYS PS SMP_VIEW_GROUPS PS SELECT YES NO
이하 생략...
* 열 권한 : USER_COL_PRIVS
SQL> desc USER_COL_PRIVS
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
SQL>
* 시스템 권한 : USER_SYS_PRIVS
SQL> desc USER_SYS_PRIVS
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> SELECT * FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
PS UNLIMITED TABLESPACE NO
SQL>
* 파티션테이블현황보기 : USER_TAB_PARTITIONS
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>
* 파티션인덱스현황보기 : USER_IND_PARTITIONS
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>
SET LINESIZE 1000
SET PAGESIZE 1000
-- 사용자테이블목록
SELECT * FROM USER_TABLES;
--사용자테이블컬럼
COL DATA_TYPE FORMAT A50
SELECT * FROM USER_TAB_COLUMNS ORDER BY TABLE_NAME;
-- 사용자테이블의파티션구성
SELECT * FROM USER_TAB_PARTITIONS;
-- 사용자인덱스목록
SELECT * FROM USER_INDEXES;
--사용자인덱스컬럼
COL COLUMN_NAME FORMAT A50
SELECT * FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
-- 사용자인덱스파티션구성
SELECT * FROM USER_IND_PARTITIONS;
