인덱스 사용여부 확인하기
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 버전 확인
SQL> select banner from v$version;
※ 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='테이블명';
[출처] 오라클 다양한 SELECT (Object)|작성자 sujikang2000