in0de

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

db2 -tvm +p <<EOF
-- Copyright (c) 2014-2017, Markus Winand - NO WARRANTY
-- Modifications by Ember Crooks - NO WARRANTY
-- Info & license: http://use-the-index-luke.com/s/last_explained
--
--#SET TERMINATOR ;

CREATE OR REPLACE VIEW last_explained AS
WITH tree(operator_ID, level, path, explain_time, cycle)
AS
(
SELECT 1 operator_id
     , 0 level
     , CAST('001' AS VARCHAR(1000)) path
     , max(explain_time) explain_time
     , 0
  FROM SYSTOOLS.EXPLAIN_OPERATOR O
 WHERE O.EXPLAIN_REQUESTER = SESSION_USER

UNION ALL

SELECT s.source_id
     , level + 1
     , tree.path || '/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  path
     , tree.explain_time
     , POSITION('/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  || '/' IN path USING OCTETS)
  FROM tree
     , SYSTOOLS.EXPLAIN_STREAM S
 WHERE s.target_id    = tree.operator_id
   AND s.explain_time = tree.explain_time
   AND S.Object_Name IS NULL
   AND S.explain_requester = SESSION_USER
   AND tree.cycle = 0
   AND level < 100
)
SELECT *
  FROM (
SELECT "Explain Plan"
  FROM (
SELECT CAST(   LPAD(id,        MAX(LENGTH(id))        OVER(), ' ')
            || ' | '
            || RPAD(operation, MAX(LENGTH(operation)) OVER(), ' ')
            || ' | '
            || LPAD(rows,      MAX(LENGTH(rows))      OVER(), ' ')
            || ' | '
            -- Don't show ActualRows columns if there are no actuals available at all
            || CASE WHEN COUNT(ActualRows) OVER () > 1 -- the heading 'ActualRows' is always present, so "1" means no OTHER values
                    THEN LPAD(ActualRows, MAX(LENGTH(ActualRows)) OVER(), ' ') || ' | '
                    ELSE ''
               END
            || LPAD(cost,      MAX(LENGTH(cost))      OVER(), ' ')
         AS VARCHAR(100)) "Explain Plan"
     , path
  FROM (
SELECT 'ID' ID
     , 'Operation' Operation
     , 'Rows' Rows
     , 'ActualRows' ActualRows
     , 'Cost' Cost
     , '0' Path
  FROM SYSIBM.SYSDUMMY1
-- TODO: UNION ALL yields duplicate. where do they come from?
UNION
SELECT CAST(tree.operator_id as VARCHAR(254)) ID
     , CAST(LPAD(' ', tree.level, ' ')
       || CASE WHEN tree.cycle = 1
               THEN '(cycle) '
               ELSE ''
          END    
       || COALESCE (
             TRIM(O.Operator_Type)
          || COALESCE(' (' || argument || ')', '')
          || ' '
          || COALESCE(S.Object_Name,'')
          , ''
          )
       AS VARCHAR(254)) AS OPERATION
     , COALESCE(CAST(rows AS VARCHAR(254)), '') Rows
     , CAST(ActualRows as VARCHAR(254)) ActualRows -- note: no coalesce
     , COALESCE(CAST(CAST(O.Total_Cost AS BIGINT) AS VARCHAR(254)), '') Cost
     , path
  FROM tree
  LEFT JOIN ( SELECT i.source_id
              , i.target_id
              , CAST(CAST(ROUND(o.stream_count) AS BIGINT) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0
                         AND ROUND(o.stream_count) <= total_rows THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(o.stream_count)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE ''
                   END rows
              , CASE WHEN act.actual_value is not null then
                CAST(CAST(ROUND(act.actual_value) AS BIGINT) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0 THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(act.actual_value)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE NULL
                   END END ActualRows
              , i.object_name
              , i.explain_time
         FROM (SELECT MAX(source_id) source_id
                    , target_id
                    , MIN(CAST(ROUND(stream_count,0) AS BIGINT)) total_rows
                    , CAST(LISTAGG(object_name) AS VARCHAR(50)) object_name
                    , explain_time
                 FROM SYSTOOLS.EXPLAIN_STREAM
                WHERE explain_time = (SELECT MAX(explain_time)
                                        FROM SYSTOOLS.EXPLAIN_OPERATOR
                                       WHERE EXPLAIN_REQUESTER = SESSION_USER
                                     )
                GROUP BY target_id, explain_time
              ) I
         LEFT JOIN SYSTOOLS.EXPLAIN_STREAM O
           ON (    I.target_id=o.source_id
               AND I.explain_time = o.explain_time
               AND O.EXPLAIN_REQUESTER = SESSION_USER
              )
         LEFT JOIN SYSTOOLS.EXPLAIN_ACTUALS act
           ON (    act.operator_id  = i.target_id
               AND act.explain_time = i.explain_time
               AND act.explain_requester = SESSION_USER
               AND act.ACTUAL_TYPE  like 'CARDINALITY%'
              )
       ) s
    ON (    s.target_id    = tree.operator_id
        AND s.explain_time = tree.explain_time
       )
  LEFT JOIN SYSTOOLS.EXPLAIN_OPERATOR O
    ON (    o.operator_id  = tree.operator_id
        AND o.explain_time = tree.explain_time
        AND o.explain_requester = SESSION_USER
       )
  LEFT JOIN (SELECT LISTAGG (CASE argument_type
                             WHEN 'UNIQUE' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'UNIQUE'
                                  ELSE NULL
                                  END
                             WHEN 'TRUNCSRT' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'TOP-N'
                                  ELSE NULL
                                  END  
                             WHEN 'SCANDIR' THEN
                                  CASE WHEN argument_value != 'FORWARD'
                                       THEN argument_value
                                  ELSE NULL
                                  END                    
                             ELSE argument_value    
                             END
                           , ' ') argument
                  , operator_id
                  , explain_time
               FROM SYSTOOLS.EXPLAIN_ARGUMENT EA
              WHERE argument_type IN ('AGGMODE'   -- GRPBY
                                     , 'UNIQUE', 'TRUNCSRT' -- SORT
                                     , 'SCANDIR' -- IXSCAN, TBSCAN
                                     , 'OUTERJN' -- JOINs
                                     )
                AND explain_requester = SESSION_USER
              GROUP BY explain_time, operator_id

            ) A
    ON (    a.operator_id  = tree.operator_id
        AND a.explain_time = tree.explain_time
       )
     ) O
UNION ALL
VALUES ('Explain plan (c) 2014-2017 by Markus Winand - NO WARRANTY - V20171102','Z0')
    ,  ('Modifications by Ember Crooks - NO WARRANTY','Z1')
    ,  ('http://use-the-index-luke.com/s/last_explained','Z2')
    ,  ('', 'A')
    ,  ('', 'Y')
    ,  ('Predicate Information', 'AA')
UNION ALL
SELECT CAST (LPAD(CASE WHEN operator_id = LAG  (operator_id)
                                          OVER (PARTITION BY operator_id
                                                    ORDER BY pred_order
                                               )
                       THEN ''
                       ELSE operator_id || ' - '
                  END
                , MAX(LENGTH(operator_id )+4) OVER()
                , ' ')
             || how_applied
             || ' '
             || predicate_text
          AS VARCHAR(100)) "Predicate Information"
     , 'P' || LPAD(id_order, 5, '0') || pred_order path
  FROM (SELECT CAST(operator_id AS VARCHAR(254)) operator_id
             , LPAD(trim(how_applied)
                  ,  MAX (LENGTH(TRIM(how_applied)))
                    OVER (PARTITION BY operator_id)
                  , ' '
               ) how_applied
               -- next: capped to length 80 to avoid
               -- SQL0445W  Value "..." has been truncated.  SQLSTATE=01004
               -- error when long literal values may appear (space padded!)
             , CAST(substr(predicate_text, 1, 80) AS VARCHAR(80)) predicate_text
             , CASE how_applied WHEN 'START' THEN '1'
                                WHEN 'STOP'  THEN '2'
                                WHEN 'SARG'  THEN '3'
                                ELSE '9'
               END pred_order
             , operator_id id_order
          FROM systools.explain_predicate p
         WHERE explain_time = (SELECT MAX(explain_time)
                                 FROM systools.explain_operator)
       )
)
ORDER BY path
);
EOF

db2 "explain plan for select * from sysibm.sysdummy1"

db2 "select * from last_explained"

-- in export.sql
SET term on
SET echo off
SET UNDERLINE off
SET HEADSEP off
SET TRIMSPOOL on
SET TRIMOUT on
SET COLSEP ","
SET SQLPROMPT ''
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 999999999
SET TERMOUT OFF
SET FEEDBACK OFF

SPOOL export.pipe;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT

    col1

    ||'@@'|| col2

    ||'@@'|| col3

    ||'@@'|| col4

    ||'@@'||  col5
FROM TBL WHERE 1=1;
SPOOL OFF;
quit;
/

rm -f ./export.pipe

mkfifo ./export.pipe
chmod 770 ./export.pipe
cat ./export.pipe | gzip > ./export.csv.gz &
sqlplus -s ${DBUSER}/${password} @export.sql
rm -f ./export.pipe

LOAD DATA
INFILE import.pipe
BADFILE import.bad
APPEND INTO TABLE TBL
FIELDS TERMINATED BY "@@"
TRAILING NULLCOLS
(

col1

,col2

,col3

,col4 CHAR           -- load하는 타겟 테이블의 칼럼이 VARCHAR2 칼럼일 경우 굳이 명시하자면

,col5 INTEGER       -- load하는 타겟 테이블의 칼럼이 NUMBER일 경우

)

rm -f ./import.pipe

mkfifo ./import.pipe
chmod 770 ./import.pipe
gunzip < ./export.csv.gz > ./import.pipe &

sqlldr userid=${DBUSER}/${password} control=import.ctl errors=100 rows=5000

rm -f ./import.pipe

select * from v$fast_start_transactions where state='RECOVERING';

select ktuxesiz from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;

col msg format a80
WITH TB AS (
    SELECT TABLESPACE_NAME
          ,(SELECT ROUND(SUM(BLOCKS)*8/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=A.TABLESPACE_NAME) TOT_MB
          ,SUM(DECODE(A.STATUS,'ACTIVE'   ,SIZE_MB,0)) ACTIVE_MB
          ,SUM(DECODE(A.STATUS,'UNEXPIRED',SIZE_MB,0)) UNEXPI_MB
          ,SUM(DECODE(A.STATUS,'EXPIRED'  ,SIZE_MB,0)) EXPIRE_MB
    FROM  (
           SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES)/1024/1024) SIZE_MB
           FROM   DBA_UNDO_EXTENTS
         --WHERE  tablespace_name in ( select value from v$parameter where name='undo_tablespace')
           GROUP  BY TABLESPACE_NAME, STATUS
          ) A
    GROUP BY TABLESPACE_NAME
)
SELECT to_char(sysdate,'HH24:MI:SS')||' '||TABLESPACE_NAME||' : '||round((active_mb+unexpi_mb+expire_mb)/tot_mb*100)||
       ' % -> Act('||active_mb||') Unexp('||unexpi_mb||') Exp('||expire_mb||') TOT('||TOT_MB||')' MSG,
       round(100*(ACTIVE_MB)/TOT_MB,2),
       round(100*(ACTIVE_MB+UNEXPI_MB)/TOT_MB,2)
FROM TB;

set serveroutput on
declare
    l_start number ;
    l_end   number ;
    r_min   number ;
begin
    select ktuxesiz into l_start from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    dbms_lock.sleep(60);
    select ktuxesiz into l_end   from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    r_min := round(l_end / (l_start - l_end)  ) ;
    dbms_output.put_line('['||to_char(sysdate,'HH24:MI:SS')||'] Remain Time : '|| r_min ||'분 '|| round(r_min/60,2)||'시간 / '||(l_start-l_end)||' blocks done / '|| l_end ||' Total blocks remains' );
end ;
/

create undo tablespace undotbs2 datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_001';

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_002';

...

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_NNN';

alter system set undo_tablespace=undotbs2 ;

select a.usn, a.name, b.status, c.tablespace_name, d.addr, e.sid, e.serial#, e.username, e.program, e.machine, e.osuser
from   v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e
where  a.usn=b.usn
and    a.name = c.segment_name
and a.usn = d.xidusn
and    d.addr = e.taddr
and b.status = 'PENDING OFFLINE';

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => '1. 주문',
                                   action_name => '1.1 주문 접수');
  -- 수행할 SQL
END;
/

BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => '1.2 재고 조회');
 
  -- 수행할 SQL
END;
/

SELECT /*+  RULE */ s.inst_id AS node
   , s.username
   , CASE
   WHEN SUBSTR( s.action, 1, 4 ) = 'FRM:' THEN s.module || '(Form)'
   WHEN SUBSTR( s.action, 1, 4 ) = 'Onli' THEN s.module || '(Form)'
   WHEN SUBSTR( s.action, 1, 4 ) = 'Conc' THEN s.module || '(Conc)'
   ELSE s.module
  END AS module
   , SUBSTR( CASE WHEN SIGN( LENGTHB( s.program ) - 16 ) = 1 THEN SUBSTR( s.program, 1, 16 ) || '..' ELSE s.program END, 1, 16 ) AS program
   , s.osuser||'@'||s.machine AS who
   --, s.port
   , last_call_et
   , TO_CHAR( logon_time, 'mm-dd HH24:MI:SS' ) AS logon_time
   , s.sql_id
   , 'kill -9 ' || p.spid AS OSKILLCMD
   , 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || '''' || ';  --'||p.spid||'' AS ORACLEKILLCMD
   , s.terminal AS USER_INFO
   , SUBSTR( LPAD( TRIM( TO_CHAR( ROUND( p.pga_alloc_mem / 1024 / 1024 ), '999,999' ) ) || 'M', 5, ' ' ), 1, 6 ) AS pga
   --, TRIM( ( SELECT SUBSTR( sql_text, 1, 120 ) FROM v$sql sq WHERE sq.sql_id = s.sql_id AND s.sql_hash_value = sq.hash_value AND ROWNUM = 1 ) ) AS sql_text
   , CASE
   WHEN s.blocking_session IS NULL THEN ''
   ELSE SUBSTR( s.blocking_session_status, 1, 3 ) || '(' || s.blocking_instance || ')' || ( s.blocking_session - 1 )
  END AS BLOCKING_SID
   , s.seconds_in_wait
   , SUBSTR( s.event, 1, 32 ) AS wait_event
   , TO_SINGLE_BYTE( s.action ) AS action
   , ( SELECT  sql_text
    FROM   v$sql sq
    WHERE   sq.sql_id = s.sql_id AND s.sql_hash_value = sq.hash_value AND ROWNUM = 1 )
   AS sql_text
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
AND  s.inst_id = p.inst_id
AND  s.status = 'ACTIVE'
AND  s.event NOT IN ( 'queue messages'
        , 'pipe get'
        , 'jobq slave wait'
        , 'Streams AQ: waiting for messages in the queue'
        , 'class slave wait')
AND  s.username IS NOT NULL
AND  s.audsid <> USERENV( 'SESSIONID' )                     -- which is not my session
--order by s.inst_id,status1 desc,username1 desc , substr(s.action,1,3) desc, module1,last_call_et
ORDER BY s.inst_id, s.status DESC, last_call_et DESC

/