[오라클] DB 스페이스 정보 확인 및 테이블 정보 확인(8i버전)

* DB 스페이스 정보 확인 및 테이블 정보 확인(8i버전)

 

[USER_TABLES] 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(21)  INSTANCES                                          VARCHAR2(21)  CACHE                                              VARCHAR2(11)  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)

 

[DBA_DATA_FILES] 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

 

 

[dbf 파일 경로 확인] select e.tablespace_name,e.file_name,e.bytes, (e.bytes-sum(f.bytes)) result from dba_data_files e, dba_free_space f where e.file_id = f.file_id group by e.tablespace_name, e.file_name, e.bytes;

 

 

[테이블 스페이스 용량 확인 및 용량 확보] select a.tablespace_name "Tablespace Name",          round(a.size1/1024) "Original Size",          round(b.size2/1024) "Free Size",          round(nvl(a.size1-b.size2,0)/1024) "Used Size",          round(b.size3/1024) "Max Size",          TO_NUMBER(substr(round(nvl(a.size1-b.size2,0)/nvl(a.size1,0)*100),1,13)) "Rate "    from        ( select tablespace_name, sum(bytes) size1           from dba_data_files           group by tablespace_name) A,       ( select tablespace_name, sum(bytes) size2, max(bytes) size3           from dba_free_space           group by tablespace_name ) B   where A.tablespace_name = B.tablespace_name;     alter tablespace <tablespace_name> add datafile '<생성할 dbf 파일 경로>' size <생성할 dbf 파일 사이즈>;