in0de

-- db2 -td@ -vf dropPartition.sql

-- in dropPartition.sql

CREATE OR REPLACE PROCEDURE DBA.DROP_PARTITION (IN tblSchema VARCHAR(40), IN tblName VARCHAR(60), IN partName VARCHAR(120))

SPECIFIC DROP_PARTITION

LANGUAGE SQL

BEGIN

DECLARE tblSchema VARCHAR(40) DEFAULT '___________';

DECLARE detachSQL VARCHAR(2000);

DECLARE dropSQL VARCHAR(2000);

DECLARE isTblAbsent INT;

        DECLARE dpID INT;

        DECLARE dpState CHAR;

        DECLARE isNormalState INT DEFAULT 0;

        DECLARE curDPLock CURSOR FOR

                SELECT DATAPARTITIONID, STATUS

                FROM SYSIBM.SYSDATAPARTITIONS DP

                WHERE DP.TABSCHEMA = tblSchema

                AND DP.TABNAME = tblName

                AND (STATUS = 'L' OR STATUS = 'D' OR STATUS = 'I');

        DECLARE CONTINUE HANDLER FOR NOT FOUND

                SET isNormalState = 1;

CALL DBA.chkTblExist (tblSchema, tblName, isTblAbsent);

        IF isTblAbsent = 1

        THEN

                RETURN -1;

        END IF;

SET detachSQL = 'ALTER TABLE '  || tblSchema || '.' || tblName || ' DETACH PARTITION '|| partName || ' INTO ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';

--CALL DBMS_OUTPUT.PUT_LINE(detachSQL);

EXECUTE IMMEDIATE detachSQL;

EXECUTE IMMEDIATE 'COMMIT WORK';

        WAIT_LOOP:

        LOOP

                OPEN curDPLock;

                FETCH curDPLock INTO dpID, dpState;

                CLOSE curDPLock;

                IF isNormalState = 1

                THEN

                        LEAVE WAIT_LOOP;

                ELSE

                        ITERATE WAIT_LOOP;

                END IF;

        END LOOP;

SET dropSQL = 'DROP TABLE ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';

-- CALL DBMS_OUTPUT.PUT_LINE(dropSQL);

EXECUTE IMMEDIATE dropSQL;

EXECUTE IMMEDIATE 'COMMIT WORK';

RETURN 0;

END@

--

/* Sample Table DDL

DROP TABLE INODE.TMP;

CREATE TABLE INODE.TMP  (

OPRN_DT VARCHAR(8) NOT NULL ,

RIDE_TROP_ID VARCHAR(9) NOT NULL ,

RIDE_STN_NO VARCHAR(4) NOT NULL ,

ALGH_TROP_ID VARCHAR(9) NOT NULL ,

ALGH_STN_NO VARCHAR(4) NOT NULL ,

MTAR_AREA_CD VARCHAR(2) NOT NULL ,

RIDE_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,

ALGH_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,

RIDE_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

ALGH_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

RIDE_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

ALGH_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

RIDE_VLT_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

DC_AMT BIGINT WITH DEFAULT 0 ,

GNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

TNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

CHPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

SNCN_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

DSPR_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

NTMP_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

FRGN_SNCN_PSNG_NUM BIGINT WITH DEFAULT 0 ,

FRAP_PSNG_NUM BIGINT WITH DEFAULT 0 ,

DW_LST_UPD_DTM VARCHAR(14) NOT NULL )

COMPRESS YES ADAPTIVE

DISTRIBUTE BY HASH(RIDE_STN_NO)

INDEX IN TR_MT16_I01 PARTITION BY RANGE(OPRN_DT)

(PART TMP_P201600 STARTING(MINVALUE) ENDING('20151299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201601 STARTING('20160101') ENDING('20160199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201602 STARTING('20160201') ENDING('20160299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201603 STARTING('20160301') ENDING('20160399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201604 STARTING('20160401') ENDING('20160499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201605 STARTING('20160501') ENDING('20160599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201606 STARTING('20160601') ENDING('20160699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201607 STARTING('20160701') ENDING('20160799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201608 STARTING('20160801') ENDING('20160899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201609 STARTING('20160901') ENDING('20160999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201610 STARTING('20161001') ENDING('20161099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201611 STARTING('20161101') ENDING('20161199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201612 STARTING('20161201') ENDING('20161299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201701 STARTING('20170101') ENDING('20170199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201702 STARTING('20170201') ENDING('20170299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201703 STARTING('20170301') ENDING('20170399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201704 STARTING('20170401') ENDING('20170499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201705 STARTING('20170501') ENDING('20170599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201706 STARTING('20170601') ENDING('20170699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201707 STARTING('20170701') ENDING('20170799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201708 STARTING('20170801') ENDING('20170899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201709 STARTING('20170901') ENDING('20170999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201710 STARTING('20171001') ENDING('20171099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201711 STARTING('20171101') ENDING('20171199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201712 STARTING('20171201') ENDING('20171299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201801 STARTING('20180101') ENDING('20180199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201802 STARTING('20180201') ENDING('20180299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201803 STARTING('20180301') ENDING('20180399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201804 STARTING('20180401') ENDING('20180499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201805 STARTING('20180501') ENDING('20180599') IN TR_MT16_D01 INDEX IN TR_MT16_I01);

CREATE UNIQUE INDEX INODE.PK_TMP ON KSCCDW.TMP (OPRN_DT ASC, RIDE_TROP_ID ASC, RIDE_STN_NO ASC, ALGH_TROP_ID ASC, ALGH_STN_NO ASC, MTAR_AREA_CD ASC) PARTITIONED;

ALTER TABLE INODE.TMP ADD CONSTRAINT PK_TMP PRIMARY KEY (OPRN_DT,RIDE_TROP_ID,RIDE_STN_NO,ALGH_TROP_ID,ALGH_STN_NO,MTAR_AREA_CD);

*/

-- Sample detach partition SQL

-- db2 "CALL DBA.DROP_PARTITION('INODE','TMP','TMP_P201601')"

declare

v_ret boolean;

begin

v_ret := dbms_repair.online_index_clean(nnnnn);

end;

/

function zpad(str, nb_digits)
{
    if (str.length < nb_digits)
        { return (Array(nb_digits - str.length + 1).join('0') + str); }
    else
        { return str; }
}

var tobeName = null;
var tobeExt = null;
var tobeFullName = null;

var strDTM = item.exifToolValue('DateTimeOriginal');
 
if ( item.exifToolValue('DateTimeOriginal') == '' ){
    var strDTM = item.exifToolValue('FileModifyDate');
}
 
var YY = strDTM.substring(2,4)
var MM = strDTM.substring(5,7)
var DD = strDTM.substring(8,10)
var HH = strDTM.substring(11,13)
var MI = strDTM.substring(14,16)
var SS = strDTM.substring(17,19)
strDTM = YY+MM+DD//+' '+HH+';'+MI+';'+SStobeFullName
        =
          strDTM + ' '+item.newBasename.replace(/^\d{6}\s+/gi,'')
        + item.newExt
 
return tobeFullName;

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"

with COL as

(
    select
        tabschema
        , tabname
        , colname
        , colno
        , colcard
        , partkeyseq
        , remarks
    from syscat.columns
    where 1=1
    and partkeyseq > 0
)
select TABINF.tabschema, TABINF.tabname
, max(COL.remarks) as remarks
, substr(xmlcast(xmlgroup(', '||COL.colname as colnm order by COL.partkeyseq) as varchar(128)), 2) as "파티션 키 컬럼"
, max(TBL.remarks) as "컬럼 설명"
--, max(COL.partkeyseq) "파티션 키 순번"
, max(TBL.card) as "테이블 건수"
, max(COL.colcard) as "파티션 키 컬럼 distinct 개수"
, stddev((data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)/1024) as "표준편차_MB"
, stddev(data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size) / sum(data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size) * 1024 as "사이즈 고려 표준편차지수"
, sum((data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)/1024) as size_mb
, max(decode(dbpartitionnum, 0, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node0
, max(decode(dbpartitionnum, 1, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node1
, max(decode(dbpartitionnum, 2, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node2
, max(decode(dbpartitionnum, 3, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node3
, max(decode(dbpartitionnum, 4, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node4
, max(decode(dbpartitionnum, 5, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node5
, max(decode(dbpartitionnum, 6, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node6
, max(decode(dbpartitionnum, 7, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node7
, max(decode(dbpartitionnum, 8, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node8
, max(decode(dbpartitionnum, 9, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node9
, max(decode(dbpartitionnum, 10, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node10
, max(decode(dbpartitionnum, 11, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node11
, max(decode(dbpartitionnum, 12, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node12
, max(decode(dbpartitionnum, 13, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node13
, max(decode(dbpartitionnum, 14, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node14
, max(decode(dbpartitionnum, 15, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node15
, max(decode(dbpartitionnum, 16, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node16
, max(decode(dbpartitionnum, 17, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node17
, max(decode(dbpartitionnum, 18, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node18
, max(decode(dbpartitionnum, 19, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node19
, max(decode(dbpartitionnum, 20, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node20
, max(decode(dbpartitionnum, 21, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node21
, max(decode(dbpartitionnum, 22, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node22
, max(decode(dbpartitionnum, 23, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node23
, max(decode(dbpartitionnum, 24, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node24
, max(decode(dbpartitionnum, 25, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node25
, max(decode(dbpartitionnum, 26, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node26
, max(decode(dbpartitionnum, 27, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node27
, max(decode(dbpartitionnum, 28, data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size+xml_object_p_size)) as node28
, max(TBL.lastused) as "테이블 마지막 사용일"
from SYSIBMADM.ADMINTABINFO TABINF
right join COL on TABINF.tabschema=COL.tabschema and TABINF.tabname=COL.tabname
right join syscat.tables TBL on TABINF.tabschema=TBL.tabschema and TABINF.tabname=TBL.tabname
where TABINF.tabtype='T'
group by TABINF.tabschema, TABINF.tabname
order by TABINF.tabschema, TABINF.tabname
;

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