블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            
  • 35,390total
  • 16today
  • 2yesterday
2019.05.10 16:40 오라클

SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg
  FROM (SELECT segment_name table_name, owner, bytes
          FROM dba_segments
         WHERE segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name, i.owner, s.bytes
          FROM dba_indexes i, dba_segments s
         WHERE s.segment_name = i.index_name
           AND s.owner = i.owner
           AND s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.segment_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.index_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBINDEX')
 WHERE owner in UPPER('&owner')
 GROUP BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 10 /* Ignore really small tables */
 ORDER BY SUM(bytes) desc;

 

출처: http://a2zakir.blogspot.com/2012/02/script-for-getting-oracle-table-size.html

posted by bedbmsguru
2019.05.02 13:12 오라클

https://ksmk.tistory.com/28

posted by bedbmsguru
2019.03.06 15:08 오라클

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


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


posted by bedbmsguru
2018.12.31 16:12 오라클

출처: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 [안나푸르나]

'오라클' 카테고리의 다른 글

Oracle 11g 설치 오류 PRVF-0002  (0) 2019.05.02
오라클 Object DDL 스크립트 추출  (0) 2019.03.06
오라클 세션별 CPU, PGA 사용량 확인 쿼리  (0) 2018.12.31
DML쿼리 튜닝방법  (0) 2018.09.19
스케줄러 사용법  (0) 2018.08.27
특정테이블 index Size 조회  (0) 2018.08.13
posted by bedbmsguru
2018.09.19 16:31 오라클

DBMS Call을 줄인다.

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

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

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

posted by bedbmsguru
2018.08.27 10:38 오라클

http://adminid.kr/oracle/51350

posted by bedbmsguru
2018.08.13 16:52 오라클

--특정테이블 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

posted by bedbmsguru
2018.06.26 16:57 오라클

인덱스 조회용 쿼리를 필요할때마다 불러와서 실행하기가 불편해서 타이핑이 가능한 수준으로 줄였음 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;

posted by bedbmsguru
2018.06.19 11:55 오라클

한글깨짐으로 인해 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

posted by bedbmsguru