블로그 이미지
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 31
  • 36,498total
  • 0today
  • 9yesterday
2019.08.22 16:51 오라클

1. DBA 로그인

2. 링크 생성 권한 부여
grant create database link to 아이디;

3. 오라클 서버 TNS 등록
cd %ORACLE_HOME/admin/
cp tnsnames.ora tnsnames.ora.20181217
vi tnsnames.ora

4. 아이디 로그인

5. 링크 생성
CREATE DATABASE LINK 디비링크이름
connect to 타겟아이디 identified by “암호”
using ''tns등록이름''

6. 테스트
Select * from dual@디비링크이름;

7. 권한회수
revoke create database link from 아이디;

posted by bedbmsguru
2019.06.20 17:09 오라클

WITH sessions AS

 (SELECT /*+materialize*/

 sid, blocking_session, row_wait_obj#, sql_id

 FROM v$session) 

SELECT LPAD(' ', LEVEL ) || sid sid, object_name, 

substr(sql_text,1,40) sql_text 

FROM sessions s 

LEFT OUTER JOIN dba_objects 

ON (object_id = row_wait_obj#) 

LEFT OUTER JOIN v$sql 

USING (sql_id) 

WHERE sid IN (SELECT blocking_session FROM sessions) 

OR blocking_session IS NOT NULL 

CONNECT BY PRIOR sid = blocking_session 

START WITH blocking_session IS NULL;

 

https://oracle2amar.wordpress.com/2010/11/29/script-to-find-the-blocking-sqls-during-the-deadlock-situations/

 

Script to find the blocking SQLs during the deadlock situations

select distinct a.sid “waiting sid”, d.sql_text “waiting SQL”, a.ROW_WAIT_OBJ# “locked object”, a.BLOCKING_SESSION “blocking sid”, c.sql_text “…

oracle2amar.wordpress.com

 

--blockin 확인 SQL

 

SELECT 
       holding_session bsession_id, 
      waiting_session wsession_id, 
      b.username busername, 
      a.username wusername, 
      c.lock_type type, 
      mode_held, mode_requested,
      lock_id1, lock_id2 
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a 
WHERE
c.holding_session=b.sid and
c.waiting_session=a.sid

 

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

posted by bedbmsguru
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