반응형

# 테이블스페이스의 데이터파일의 물리적위치와 파일명, 테이블스페이스의 이름, 테이블스페이스의 크기, 테이블스페이스의 이용가능여부확인

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;

 

 

 

 




반응형
Posted by 공간사랑
,