오라클(Oracle) 테이블 용량 및 칼럼 정보 확인

1. tablespace조회

select file_name,tablespace_name
from   dba_data_files;

 

2. tablespace의 segment조회

 

select SEGMENT_NAME,TABLESPACE_NAME,BYTES,
       ROUND(BYTES /1024/1024,2)
from   dba_segments
WHERE  TABLESPACE_NAME ='EIS'
ORDER BY BYTES DESC;

--Table_Size 조회

select segment_name ,TO_NUMBER(sum(bytes))/1024/1024
from   user_extents
GROUP BY segment_name

 

3. 계정체크

SELECT USERNAME,
       ACCOUNT_STATUS,
       TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE          
FROM DBA_USERS;

SELECT * FROM ALL_USERS;

 

4. 테이블명

SELECT TABLE_NAME
FROM   USER_TABLES
WHERE  TABLESPACE_NAME ='EIS';

 

5. 테이블 제약조건의 확인1

SELECT table_name, constraint_name, constraint_type,
       A.*
FROM   USER_CONSTRAINTS A;

 

6. 테이블 제약조건의 확인2

SELECT  A.TABLE_NAME 테이블명,                             -- 테이블명
        SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,　　       -- 컬럼명
        DECODE(B.CONSTRAINT_TYPE,
               'P','PRIMARY KEY',
           　  'U','UNIQUE KEY',
          　　 'C','CHECK OR NOT NULL',
               'R','FOREIGN KEY') CONSTRAINT_TYPE,　　    -- 제약조건 TYPE
        A.CONSTRAINT_NAME　　 CONSTRAINT_NAME　    　     -- 제약 조건 명
        ,A.*
FROM　　USER_CONS_COLUMNS　 A,　 USER_CONSTRAINTS　 B　　
WHERE　 A.TABLE_NAME = B.TABLE_NAME        
  AND　 A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  AND   A.OWNER='EISPART'
ORDER BY 1;

 

7. TableSpace_데이터파일 확인

select A.*
from   dba_data_files A
WHERE  TABLESPACE_NAME='EIS'
ORDER BY FILE_NAME;

 

8. ser_Tab_Columns 조회

SELECT * 
FROM   user_tab_columns;

 

9. 테이블스페이스의 크기와 남은 용량 계산

select tablespace_name, "TOTAL_SIZE(MB)", "FREE_SIZE(MB)", file_name
from
  ( select file_name, tablespace_name, round(sum(bytes/1024/1024),2) "TOTAL_SIZE(MB)" from dba_data_files
    group by file_name, tablespace_name
  ) inner join
  (
    select tablespace_name, round(sum(bytes/1024/1024),2) "FREE_SIZE(MB)" from dba_free_space
    group by tablespace_name
  ) using (tablespace_name);

 

 

 

* 통계분석연구회 : http://cafe.daum.net/statsas