Oracle 유용한 Query

인덱스 사용여부 확인하기
1. alter index IDX_TB_VERIFY_HIST_LOG_06 monitoring usage;
2. alter index IDX_TB_VERIFY_HIST_LOG_06 nomonitoring usage;
3. select index_name,table_name,monitoring, used from V$OBJECT_USAGE
======================================================================

인덱스 삭제하기
drop index IDX_TB_DOWN_LOG_MDN;

인덱스 이름변경
ALTER INDEX index_name RENAME TO new_index_name;
======================================================================

테이블 정보보기
SELECT * FROM COLS WHERE TABLE_NAME = 'TB_GXG_SUC_LOG'
======================================================================

컬럼명 알아내기
SELECT COUNT(COLUMN_NAME) FROM COLS WHERE TABLE_NAME = 'ERP_TBL_MEMO'
======================================================================

테이블 스페이스 정보 보기
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;
======================================================================

프로세스 정보보기
SELECT   /*+ rule */
         s.status "Status", s.serial# "Serial#", s.TYPE "Type",
         s.username "DB User", s.osuser "Client User", s.server "Server",
         s.machine "Machine", s.module "Module", s.terminal "Terminal",
         s.program "Program", p.program "O.S. Program",
         s.logon_time "Connect Time", lockwait "Lock Wait",
         si.physical_reads "Physical Reads", si.block_gets "Block Gets",
         si.consistent_gets "Consistent Gets",
         si.block_changes "Block Changes",
         si.consistent_changes "Consistent Changes", s.process "Process",
         p.spid, p.pid, s.serial#, si.sid, s.sql_address "Address",
         s.sql_hash_value "Sql Hash", s.action
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
     AND si.sid(+) = s.sid
     AND s.username IS NOT NULL
     AND NVL (s.osuser, 'x')  'SYSTEM'
     AND s.TYPE  'BACKGROUND'
ORDER BY 3

======================================================================

ORA-01000 : 최대 열기 커서 수 초과시

0. 사용자별 세션수
select username, count(*)
from v$session
group by username;

1. DB에 접근해 있는 session과 cursor수를 확인해 보아야 한다.
V$OPEN_CURSOR라는 system view에서 sid와 해당 sid의 개수를 확인한다.

SELECT sid, count(sid) "cursor"
 FROM V$OPEN_CURSOR
 WHERE user_name = 'NWUSER'
 GROUP BY sid;

2. 사용 쿼리별로 사용하고 있는 sid를 확인한다.

select count(*), sql_text, sid
from v$open_cursor
where user_name= 'NWUSER'
group by sql_text, sid ;

3. sid의 serial#을 확인한다. (os의 process id 확인 개념)

select sid, serial#, username
from v$session
where USERNAME= 'NWUSER';

4. 문제되는 프로세스 죽이는 방법
alter system kill session 'sid.serial#';

======================================================================

-- Table Size Check
SELECT
        A.TABLESPACE_NAME,
        A.TABLE_NAME,
        ROUND(MIN(A.NEXT_EXTENT)/1048000, 1) NEXT_EXTENT,
        ROUND(SUM(BYTES)/1048000, 1) TTLSIZE,
        MIN(A.PCT_INCREASE) PCT_INCREASE
FROM
        ALL_TABLES A, USER_EXTENTS B
WHERE
        A.TABLE_NAME = B.SEGMENT_NAME       
        AND A.TABLESPACE_NAME LIKE UPPER(:AS_TABLESPACE_NAME) || '%'
        AND A.TABLE_NAME LIKE UPPER(:AS_TABLE_NAME)||'%'
        AND B.SEGMENT_TYPE = 'TABLE'
        GROUP BY A.TABLESPACE_NAME, A.TABLE_NAME
======================================================================

--테이블  사이즈 크기 (위와는 약간 다른 버전)
select DECODE( PARTITION_NAME, NULL, segment_name, PARTITION_NAME) object_name
       , segment_type object_type, round(bytes/1024/1024) Mb
       , initial_extent initial_ex
       , next_extent next_ex
       , EXTENTS
       , bytes ttlsize
       , Owner
       , max_extents
from dba_segments
Where tablespace_name='GPBILL_STAT_DAT'
======================================================================

-- Index Size Check
SELECT
        A.TABLESPACE_NAME,
        A.INDEX_NAME,
        ROUND(MIN(A.NEXT_EXTENT)/1048000, 1) NEXT_EXTENT,
        ROUND(SUM(BYTES)/1048000, 1) TTLSIZE,
        MIN(A.PCT_INCREASE) PCT_INCREASE
FROM
        ALL_INDEXES A, USER_EXTENTS B
WHERE
        A.INDEX_NAME = B.SEGMENT_NAME       
        AND A.TABLESPACE_NAME LIKE UPPER(:AS_TABLESPACE_NAME) || '%'
        AND A.INDEX_NAME LIKE UPPER(:AS_INDEX_NAME)||'%'
        AND B.SEGMENT_TYPE = 'INDEX'
        GROUP BY A.TABLESPACE_NAME, A.INDEX_NAME
======================================================================

※ Oracle SID 확인
SQL> select instance from v$thread;

※ Oracle DB_NAME 확인
SQL> select name from v$database;

※ Oracle User 확인
SQL> select * from all_users;

※ 등록된 User 목록 보기
SQL> select username, user_id from dba_users order by username;

※ User가 소유한 모든 테이블 보기
SQL> select table_name from user_tables;

※ 사용자 정보 확인
SQL> select username, default_tablespace,temporary_tablespace from dba_users;

※ 오브젝트 조회
SQL> select * from all_objects where object_name like '명';

※ 테이블 조회
SQL> select * from all_tables where table_name like '명';

※ 시퀀스 정보 보기
SQL> select * from user_sequences;

ps ) select * from all_objects where object_type = 'SEQUENCE';

※ 프로시저 정보 보기
SQL>select * from user_objects where object_type='PROCEDURE';
SQL>select text from user_source where name=upper('프로시저명');

※ 시노님 조회
SQL> select * from all_synonyms where synonym_name='명';

※ 테이블 인덱스 정보 조회
SQL> select * from all_ind_columns where table_name='테이블명';

※ 테이블의 컬럼 정보 조회
SQL> select * from all_tab_columns where table_name='테이블명';

※ table comment 쿼리
SQL> select * from all_tab_comments where table_name='테이블명';

※ column comment 쿼리
SQL> select * from all_col_comments where table_name='테이블명';

출처 : http://hidy23.tistory.com