블로그 이미지
bedbmsguru

Notice

Recent Post

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
  • total
  • today
  • yesterday
2020. 12. 8. 16:00 SQL SERVER

-- When will a SQL login password expire?  SELECT SL.name AS LoginName        ,LOGINPROPERTY (SL.name'PasswordLastSetTime'AS PasswordLastSetTime        ,LOGINPROPERTY (SL.name'DaysUntilExpiration'AS DaysUntilExpiration        ,DATEADD(ddCONVERT(intLOGINPROPERTY (SL.name'DaysUntilExpiration'))                   , CONVERT(datetimeLOGINPROPERTY (SL.name'PasswordLastSetTime'))) AS PasswordExpiration        ,SL.is_policy_checked AS IsPolicyChecked        ,LOGINPROPERTY (SL.name'IsExpired'AS IsExpired        ,LOGINPROPERTY (SL.name'IsMustChange'AS IsMustChange        ,LOGINPROPERTY (SL.name'IsLocked'AS IsLocked        ,LOGINPROPERTY (SL.name'LockoutTime'AS LockoutTime        ,LOGINPROPERTY (SL.name'BadPasswordCount'AS BadPasswordCount        ,LOGINPROPERTY (SL.name'BadPasswordTime'AS BadPasswordTime        ,LOGINPROPERTY (SL.name'HistoryLength'AS HistoryLength  FROM sys.sql_logins AS SL  WHERE is_expiration_checked = 1  ORDER BY LOGINPROPERTY (SL.name'PasswordLastSetTime'DESC

 

 

http://gallery.technet.microsoft.com/scriptcenter/When-will-a-SQL-login-d6fbb6df

posted by bedbmsguru
2020. 10. 13. 11:09 SQL SERVER

SELECT CAST(event_Data AS xml) AS event_data FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

 

DROP TABLE IF EXISTS #myXeData

 

SELECT CAST(event_Data AS XML) AS StatementData
INTO #myXeData
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

 

 

SELECT * FROM #myXeData

 

 

SELECT
StatementData.value('(event/@name)[1]', 'varchar(50)') AS event_name
,StatementData.value('(event/@timestamp)[1]', 'datetime2(0)') AS time_stamp
,StatementData.value('(event/action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
,StatementData.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,StatementData.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,StatementData.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,StatementData.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,StatementData.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
,StatementData.value('(event/data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
FROM #myXeData AS evts
--ORDER BY time_stamp
ORDER BY duration_ms DESC

 

 

https://dba.stackexchange.com/questions/226704/how-to-view-xevent-event-file-data

'SQL SERVER' 카테고리의 다른 글

sql server Lock정보 확인  (0) 2020.12.09
패스워드 만료(Expiration) 조회  (0) 2020.12.08
SQL Formatter  (0) 2020.06.05
Foreign key로 참조하는 테이블 확인 쿼리  (0) 2020.03.25
sql server 2012 Recommended Trace flag  (0) 2020.02.13
posted by bedbmsguru
2020. 8. 21. 15:49 maridb&mysql

1)상태확인

SHOW VARIABLES LIKE 'slow_query_%';

 

2)디렉토리 생성

mkdir /var/log/mysql

chown mysql:mysql /var/log/mysql

 

3) /etc/my.cnf설정

[mysqld]

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mariadb-slow.log

long_query_time = 5

log_slow_rate_limit = 1

log_slow_verbosity = query_plan

log_slow_admin_statements

→ slow_query_log = 1(사용), 로그파일 위치는 /var/log/mysql/mariadb-slow.log

→ 수행시간이 5초 넘는 쿼리를 수집

 

4)DB 재시작

https://zetawiki.com/wiki/MySQL_%EC%8A%AC%EB%A1%9C%EC%9A%B0_%EC%BF%BC%EB%A6%AC_%EB%A1%9C%EA%B7%B8_%EC%84%A4%EC%A0%95

 

MySQL 슬로우 쿼리 로그 설정 - 제타위키

 

zetawiki.com

 

'maridb&mysql' 카테고리의 다른 글

Online DDL  (0) 2020.12.28
mysql root 패스워드 분실 대처방법  (0) 2020.07.30
mysql status 확인하기  (0) 2020.07.30
mariabackup 을 이용한 mariadb 백업  (0) 2018.12.12
mariadb 계정, 권한 관련  (0) 2018.10.27
posted by bedbmsguru
2020. 7. 31. 10:01 카테고리 없음

declare

nlsvar varchar2(4000);
envvar raw(32);

begin

select distinct nls_env,misc_env into nlsvar,envvar 
from dba_jobs 
where  
misc_env='0102000200000000' and
nls_env like 'NLS_LANGUAGE=''KOREAN''%' and  job=582;

sys.dbms_ijob.submit

(

JOB=>700,
luser=>'유저이름', puser=>'유저이름',cuser=>'유저이름' ,
NEXT_DATE=>to_date('31-07-2020 10:10:00','dd/mm/yyyy hh24:mi:ss') ,
INTERVAL=> 'TRUNC(SYSDATE+1)+1/24+10/1440' ,
WHAT=> '--주석
--주석
--주석
OWNER.패키지.PROCEDURE(null,null);
OWNER.패키지.PROCEDURE;'
,broken=>FALSE
,nlsenv=>nlsvar
,env=>envvar

);

end;

posted by bedbmsguru
2020. 7. 30. 10:58 maridb&mysql

아래 링크 참조

 

it77.tistory.com/286

'maridb&mysql' 카테고리의 다른 글

Online DDL  (0) 2020.12.28
slow query log 설정  (0) 2020.08.21
mysql status 확인하기  (0) 2020.07.30
mariabackup 을 이용한 mariadb 백업  (0) 2018.12.12
mariadb 계정, 권한 관련  (0) 2018.10.27
posted by bedbmsguru
2020. 7. 24. 16:42 PostgreSQL

blocking session 모니터링

 

SELECT
          datname,
          usename,
          application_name,
          now()-backend_start AS "Session duration",
          pid,
          wait_event,
          wait_event_type,
          query
        FROM
          pg_stat_activity
        WHERE
          state='active'
        AND wait_event IS NOT NULL;

'PostgreSQL' 카테고리의 다른 글

DESCRIBE TABLE (  (0) 2021.06.15
pg_stat_statements  (0) 2021.06.03
pg_settings 칼럼 설명  (0) 2020.05.20
postgresql.conf 파일 설정  (0) 2020.04.27
Heidi SQL 접속시 LIBPQ.DLL 오류 해결  (0) 2020.03.24
posted by bedbmsguru
2020. 6. 30. 17:13 오라클

SELECT /*+ RULE */ D.JOB, V.SID, V.SERIAL#, STATUS, LOG_USER USERNAME, WHAT, DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') RUNNING, D.FAILURES, 'alter system kill session ' || '''' || V.SID || ', ' || V.SERIAL# || '''' || ' immediate;' KILL_SQL FROM DBA_JOBS_RUNNING D, V$SESSION V, DBA_JOBS J WHERE V.SID = D.SID AND D.JOB = J.JOB;

 

 

http://www.runningoracle.com/product_info.php?products_id=34

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

Oracle Lock 조회  (0) 2020.12.24
오랫동안 실행중인 SQL(Find Long Run SQL )  (0) 2020.04.28
Temp TableSpace 사용량 확인  (0) 2020.04.10
lock tree 형식으로 조회  (0) 2020.04.09
Undo 사용량 확인  (0) 2020.03.24
posted by bedbmsguru
2020. 6. 5. 10:45 SQL SERVER

SQL Server Management Studio에 플러그인 방식으로 설치된다.

 

가독성이 좋구만..

 

http://architectshack.com/PoorMansTSqlFormatter.ashx#License__Redistribution_17

posted by bedbmsguru
2020. 5. 20. 14:03 PostgreSQL

postgresql의 환경설정(postgresql.conf)을 저장하고 있는 시스템테이블

 

postgresql.conf내용을 Query를 이용해서 확인 할때 사용

 

내용은 아래 링크에 잘 정리되어 있음

 

http://kimchki.blogspot.com/2019/04/postgresql-pgsettings.html

 

'PostgreSQL' 카테고리의 다른 글

pg_stat_statements  (0) 2021.06.03
blocking 체크  (0) 2020.07.24
postgresql.conf 파일 설정  (0) 2020.04.27
Heidi SQL 접속시 LIBPQ.DLL 오류 해결  (0) 2020.03.24
psql 설정파일(.psqlrc) 설정  (0) 2020.03.16
posted by bedbmsguru