bedbmsguru

https://ksmk.tistory.com/28

Object 종류 등등 여러가지로 변경 응용 가능

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'procedure이름', '스키마이름') FROM DUAL

출처:http://oracle.tistory.com/430

1. 세션별 CPU Time, Memory 사용량
 
select s.sid, s.serial#, p.spid as "os pid", s.username, s.module, s.sql_id, event, seconds_in_wait,
st.value/100 as "cpu sec",
round(pga_used_mem/1024/1024) "pga_tot(mb)",
round(pga_used_mem/1024/1024) "pga_per_sess(mb)"
from v$sesstat st, v$statname sn, v$session s, v$process p
where sn.name = 'CPU used by this session' -- cpu
and st.statistic# = sn.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and s.last_call_et < 1800 -- active within last 1/2 hour
and s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
order by st.value

 
 
 
 
2. Client별 Memory 사용량
 
select machine,status,count(*) cnt, 
       round(sum(pga_used_mem)/1024/1024) "pga_tot(mb)",
       round(sum(pga_used_mem)/count(*)/1024/1024) "pga_per_sess(mb)"
from v$session s, v$process p
where 1=1
--and s.status='active'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1

출처: http://oracle.tistory.com/430 [안나푸르나]

DBMS Call을 줄인다.

 (1) 절차적 Loop를 One SQL로 변경한다.

 (2) Array Processing 활용 Array Size를 크게 늘려서 DBMS Call을 줄인다.

 (3) Index 및 제약조건 해제를 통하여 대량 DML 성능 개선

http://adminid.kr/oracle/51350

--특정테이블 index Size 조회

SELECT 
T1.owner, T1.segment_name, T1.segment_type, T1.bytes/1024/1024 SIZE_MB, T2.PRIMARY_KEY, T2.UNIQUENESS, INDEX_POS1, INDEX_POS2, INDEX_POS3, INDEX_POS4, INDEX_POS5, INDEX_POS6, INDEX_POS7, INDEX_POS8, INDEX_POS9, INDEX_POS10
FROM DBA_SEGMENTS T1
INNER JOIN 
(
SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블이름'))
) T2
ON T1.SEGMENT_NAME = T2.INDEX_NAME

--PKG_DBA_REPOSITORY.GET_INDEX_INFO 함수 생성

http://bedbmsguru.tistory.com/101?category=793983

https://community.oracle.com/thread/648581?tstart=0

인덱스 조회용 쿼리를 필요할때마다 불러와서 실행하기가 불편해서 타이핑이 가능한 수준으로 줄였음 Package와 Function 활용

--실행

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));

ORACLE INDEX 정보조회 FUNCTION 만들기

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));

CREATE OR REPLACE PACKAGE PKG_DBA_REPOSITORY IS
TYPE INDEX_INFO_RECORD IS RECORD
(
INDEX_NAME VARCHAR2(100),
INDEX_TYPE VARCHAR2(100),
PRIMARY_KEY VARCHAR2(100),
UNIQUENESS VARCHAR2(100),
INDEX_POS1 VARCHAR2(100),
INDEX_POS2 VARCHAR2(100),
INDEX_POS3 VARCHAR2(100),
INDEX_POS4 VARCHAR2(100),
INDEX_POS5 VARCHAR2(100),
INDEX_POS6 VARCHAR2(100),
INDEX_POS7 VARCHAR2(100),
INDEX_POS8 VARCHAR2(100),
INDEX_POS9 VARCHAR2(100),
INDEX_POS10 VARCHAR2(100)

);
--Return 하게될 Row type Array
TYPE INDEX_INFO_TABLE IS TABLE OF INDEX_INFO_RECORD;


FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2) RETURN INDEX_INFO_TABLE PIPELINED;
END PKG_DBA_REPOSITORY;


CREATE OR REPLACE PACKAGE BODY PKG_DBA_REPOSITORY IS

FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2)
RETURN INDEX_INFO_TABLE PIPELINED
IS
v_menu INDEX_INFO_RECORD;
BEGIN
FOR func_cur IN
(
select index_name
,index_type
,decode(constraint_name, null, ' ', 'pk') primary_key
,uniqueness
,nvl(max(a),' ') index_pos1
,nvl(max(b),' ') index_pos2
,nvl(max(c),' ') index_pos3
,nvl(max(d),' ') index_pos4
,nvl(max(e),' ') index_pos5
,nvl(max(f),' ') index_pos6
,nvl(max(g),' ') index_pos7
,nvl(max(h),' ') index_pos8
,nvl(max(i),' ') index_pos9
,nvl(max(j),' ') index_pos10
from
(
select a.index_name
,a.index_type
,a.uniqueness
,c.constraint_name
,DECODE(column_position, 1, column_name, '') a
,DECODE(column_position, 2, column_name, '') b
,DECODE(column_position, 3, column_name, '') c
,DECODE(column_position, 4, column_name, '') d
,DECODE(column_position, 5, column_name, '') e
,DECODE(column_position, 6, column_name, '') f
,DECODE(column_position, 7, column_name, '') g
,DECODE(column_position, 8, column_name, '') h
,DECODE(column_position, 9, column_name, '') i
,DECODE(column_position, 10, column_name, '') j
from all_indexes a, all_ind_columns b, all_constraints c
where a.table_name = IN_TABLE_NAME
and a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name = c.table_name(+)
and a.index_name = c.constraint_name(+)
and c.constraint_type(+) = 'P'
order by index_name, column_position
)
group by index_name, index_type,constraint_name, uniqueness
order by primary_key desc, index_name
)
LOOP
v_menu.INDEX_NAME := func_cur.index_name;
v_menu.INDEX_TYPE := func_cur.index_type;
v_menu.PRIMARY_KEY := func_cur.primary_key;
v_menu.UNIQUENESS := func_cur.uniqueness;
v_menu.INDEX_POS1 := func_cur.index_pos1;
v_menu.INDEX_POS2 := func_cur.index_pos2;
v_menu.INDEX_POS3 := func_cur.index_pos3;
v_menu.INDEX_POS4 := func_cur.index_pos4;
v_menu.INDEX_POS5 := func_cur.index_pos5;
v_menu.INDEX_POS6 := func_cur.index_pos6;
v_menu.INDEX_POS7 := func_cur.index_pos7;
v_menu.INDEX_POS8 := func_cur.index_pos8;
v_menu.INDEX_POS9 := func_cur.index_pos9;
v_menu.INDEX_POS10 :=func_cur.index_pos10;
PIPE ROW (v_menu);
END LOOP;
END GET_INDEX_INFO;
END PKG_DBA_REPOSITORY;

한글깨짐으로 인해 DB 캐릭터셋을 변경하고 impdb 작업하다가  

ORA-00600 internal error code, arguments:[kokle_lob2lob13:input mismatch] 

ORA-29213 두가지 에러가 발생

아래 두개의 블로그로 해결 가능

http://atin.tistory.com/463

http://www.dba-oracle.com/t_ora_39213_metadata_processing.htm