3zecisi3: November 2016

Tuesday, November 22, 2016

Oracle - Rotate/backup listener.log file

Stop log generation into listener logfile

[host11:oracle:/home/oracle]lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 22-NOV-2016 15:45:39

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

In different window, change listener log filename

[host111:oracle:/home/oracle]mv listener.log listener_1.log

Return to previous window and start log generation

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL>

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

Oracle - Rotate/backup listener.log file

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

Thursday, November 17, 2016

SQL Server - Execute dynamic SQL with parameters

Example of procedure running an dynamic SQL based on procedure's parameter:

CREATE PROCEDURE [dbo].[SP_DYNAMIC_SQL_TEST]
(@V_PARAMETER INT)
AS
BEGIN

SET NOCOUNT ON

DECLARE @delete_sql nvarchar(1000)
DECLARE @ParmDefinitionDel NVARCHAR(500)

SET @delete_sql = 'DELETE TB_DYNAMIC_SQL'

IF (@V_PARAMETER IS NOT NULL)
SET @delete_sql = @delete_sql + ' WHERE COLUMN_NAME = @V_PARAMETER_DYN'

SET @ParmDefinitionDel = N'@V_PARAMETER_DYN INT'

PRINT @delete_sql

EXEC sp_executesql @delete_sql, @ParmDefinitionDel, @V_PARAMETER_DYN = @V_PARAMETER

END

Saturday, November 12, 2016

Oracle - Sessions details using UNDO

SELECT r.NAME "Undo Segment Name", dba_seg.size_mb,
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') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn AND 
l.TYPE(+) = 'TX' AND 
l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

Oracle - List all my tablespaces free space (autoextend on)

WITH tmp AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   tmp.tablespace_name,
         ROUND (SUM (tmp.mysize) / (1024 * 1024)) total_size,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) datafile_free_space,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) total_free_space,
         ROUND ( (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (tmp.mysize)
                 * 100
               ) percent_free
    FROM tmp, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE tmp.file_id = dfs.file_id(+)
GROUP BY tmp.tablespace_name
ORDER BY 6 DESC