블로그 이미지
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.30 10:14 Zabbix

Service로 등록하는 방법

 

zabbix_agentd.exe -i -c zabbix_agentd.conf

https://sangchul.kr/542

'Zabbix' 카테고리의 다른 글

Windows Zabbix Agent 설치  (0) 2019.05.30
Postgresql 모니터링  (0) 2019.02.19
Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
posted by bedbmsguru
2019.05.23 16:09 SQL SERVER

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

 

 

 

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
2019.02.19 15:27 SQL SERVER

아주 괜챃은 방법이다.


Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId




posted by bedbmsguru
2019.02.19 13:13 Zabbix

Linux Version 가능


http://cavaliercoder.com/libzbxpgsql/

'Zabbix' 카테고리의 다른 글

Windows Zabbix Agent 설치  (0) 2019.05.30
Postgresql 모니터링  (0) 2019.02.19
Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
posted by bedbmsguru