bedbmsguru

http://m.blog.daum.net/partsofmemory/110

오라클 LinkedServer 를 생성하다가 rownum을 지정해서 DML SQL을 실행 할 경우 100건 이하는 잘 되는데 



100건이 넘어가면 에러가 발생 


검색을 하다보니 Linkedserver를 생성할때 fetchSize 지정이 가능하다(기본값이 100)


레지스트리에서 기본값 변경도 가능 (HLM--> SOFTWARE -->ORACLE --> KEY_OraClient12Home1-->OLEDB--> FetchSize 값 수정)




-- Linked Server 생성시 fetchsize 지정하는법
EXEC sp_addlinkedserver 
@server='서버이름' 
,@srvproduct='' 
,@provider='OraOLEDB.Oracle' 
,@datasrc='TNS이름'
,@location=null 
,@provstr=N'FetchSize=50000'
,@catalog=null

SELECT a.session_id,
a.wait_type,
a.wait_duration_ms,
a.blocking_session_id,
a.resource_description,
CASE
WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT)
- 1 % 8088 = 0 THEN 'Is PFS Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 2 % 511232 = 0 THEN 'Is GAM Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 3 % 511232 = 0 THEN 'Is SGAM Page'
ELSE 'Is Not PFS, GAM, or SGAM page'
END resourcetype,
c.text AS SQLText
FROM sys.dm_os_waiting_tasks a
INNER JOIN sys.sysprocesses b
ON
a.session_id = b.spid
OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c
WHERE a.wait_type LIKE 'PAGE%LATCH_%'
AND a.resource_description LIKE '2:%';

https://sqlmonitormetrics.red-gate.com/tempdb-allocation-contention/

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 아이디;

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/

--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

운영중인 서버에서 신규서버로 DB Migration 진행하기

http://yrushka.com/index.php/sql-server/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/

Service로 등록하는 방법

zabbix_agentd.exe -i -c zabbix_agentd.conf

https://sangchul.kr/542

SELECT IndexName = i.Name, ColName = c.Name FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.is_included_column = 1 ORDER BY i.Name

https://stackoverflow.com/questions/18172359/list-all-indexes-with-included-columnsnonkeys

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

https://ksmk.tistory.com/28