Working with Analytic Workflow Manager (AWM) - Part 8 Cube Metadata Analysis

Posted by Mohan Ramanuja on Oracle Blogs See other posts from Oracle Blogs or by Mohan Ramanuja
Published on Wed, 29 Aug 2012 09:02:45 +0000 Indexed on 2012/08/29 21:45 UTC
Read the original article Hit count: 289

Filed under:

CUBE SIZE


select dbal.owner||'.'||substr(dbal.table_name,4) awname, sum(dbas.bytes)/1024/1024 as mb, dbas.tablespace_name from dba_lobs dbal, dba_segments dbas where dbal.column_name = 'AWLOB' and dbal.segment_name = dbas.segment_name group by dbal.owner, dbal.table_name, dbas.tablespace_name order by dbal.owner, dbal.table_name

SESSION RESOURCES


select vses.username||':'||vsst.sid username, vstt.name, max(vsst.value) value

from v$sesstat vsst, v$statname vstt, v$session vses

where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and

VSES.USERNAME LIKE ('ATTRIBDW_OWN') AND

vstt.name in ('session pga memory', 'session pga memory max', 'session uga memory','session uga memory max', 'session cursor cache count', 'session cursor cache hits', 'session stored procedure space', 'opened cursors current', 'opened cursors cumulative') and

vses.username is not null group by vsst.sid, vses.username, vstt.name order by vsst.sid, vses.username, vstt.name

OLAP PGA USE


select 'OLAP Pages Occupying: '|| round((((select sum(nvl(pool_size,1)) from v$aw_calc)) / (select value from v$pgastat where name = 'total PGA inuse')),2)*100||'%' info from dual union select 'Total PGA Inuse Size: '||value/1024||' KB' info from v$pgastat where name = 'total PGA inuse' union select 'Total OLAP Page Size: '|| round(sum(nvl(pool_size,1))/1024,0)||' KB' info from v$aw_calc order by info desc

OLAP PGA USAGE PER USER


select vs.username, vs.sid, round(pga_used_mem/1024/1024,2)||' MB' pga_used, round(pga_max_mem/1024/1024,2)||' MB' pga_max, round(pool_size/1024/1024,2)||' MB' olap_pp, round(100*(pool_hits-pool_misses)/pool_hits,2) || '%' olap_ratio from v$process vp, v$session vs, v$aw_calc va where session_id=vs.sid and addr = paddr

CUBE LOADING SCRIPT


REM The 'set define off' statement is needed only if running this script through SQLPlus.
REM If you are using another tool to run this script, the line below may be commented out.
set define off
BEGIN
  DBMS_CUBE.BUILD(
    'VALIDATE
  ATTRIBDW_OWN.CURRENCY USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.ACCOUNT USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.DATEDIM USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.CUSIP USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.ACCOUNTRETURN',
    'CCCCC', -- refresh method
false, -- refresh after errors
    0, -- parallelism
true, -- atomic refresh
true, -- automatic order
false); -- add dimensions
END;
/

BEGIN
  DBMS_CUBE.BUILD(
    '
  ATTRIBDW_OWN.CURRENCY USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.ACCOUNT USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.DATEDIM USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.CUSIP USING
  (
    LOAD NO SYNCH,
    COMPILE SORT
  ),
  ATTRIBDW_OWN.ACCOUNTRETURN',
    'CCCCC', -- refresh method
false, -- refresh after errors
    0, -- parallelism
true, -- atomic refresh
true, -- automatic order
false); -- add dimensions
END;
/

VISUALIZATION OBJECT - AW$ATTRIBDW_OWN


 CREATE TABLE "ATTRIBDW_OWN"."AW$ATTRIBDW_OWN"
        (
            "PS#"    NUMBER(10,0),
            "GEN#"   NUMBER(10,0),
            "EXTNUM" NUMBER(8,0),
            "AWLOB" BLOB,
            "OBJNAME"  VARCHAR2(256 BYTE),
            "PARTNAME" VARCHAR2(256 BYTE)
        )
        PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255 STORAGE
        (
            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE "ATTRIBDW_DATA" LOB
        (
            "AWLOB"
        )
        STORE AS SECUREFILE
        (
            TABLESPACE "ATTRIBDW_DATA" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION MIN 1 CACHE NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        )
        PARTITION BY RANGE
        (
            "GEN#"
        )
        SUBPARTITION BY HASH
        (
            "PS#",
            "EXTNUM"
        )
        SUBPARTITIONS 8
        (
            PARTITION "PTN1" VALUES LESS THAN (1) PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" LOB ("AWLOB") STORE AS SECUREFILE ( TABLESPACE "ATTRIBDW_DATA" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION MIN 1 CACHE READS LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ( SUBPARTITION "SYS_SUBP661" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP662" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP663" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP664" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP665" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION
            "SYS_SUBP666" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP667" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP668" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" ) ,
            PARTITION "PTNN" VALUES LESS THAN (MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" LOB ("AWLOB") STORE AS SECUREFILE ( TABLESPACE "ATTRIBDW_DATA" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION MIN 1 CACHE NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ( SUBPARTITION "SYS_SUBP669" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP670" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP671" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP672" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP673" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION
            "SYS_SUBP674" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP675" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_SUBP676" LOB ("AWLOB") STORE AS ( TABLESPACE "ATTRIBDW_DATA" ) TABLESPACE "ATTRIBDW_DATA" )
        ) ;
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."ATTRIBDW_OWN_I$" ON "ATTRIBDW_OWN"."AW$ATTRIBDW_OWN"
    (
        "PS#", "GEN#", "EXTNUM"
    )
    PCTFREE 10 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS STORAGE
    (
        INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
    )
    TABLESPACE "ATTRIBDW_DATA" ;
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000406980C00004$$" ON "ATTRIBDW_OWN"."AW$ATTRIBDW_OWN"
    (
        PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" LOCAL (PARTITION "SYS_IL_P711" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ( SUBPARTITION "SYS_IL_SUBP695" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP696" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP697" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP698" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP699" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP700" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP701" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP702" TABLESPACE "ATTRIBDW_DATA" ) , PARTITION "SYS_IL_P712" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ( SUBPARTITION "SYS_IL_SUBP703" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP704" TABLESPACE
        "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP705" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP706" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP707" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP708" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP709" TABLESPACE "ATTRIBDW_DATA" , SUBPARTITION "SYS_IL_SUBP710" TABLESPACE "ATTRIBDW_DATA" ) ) PARALLEL (DEGREE 0 INSTANCES 0) ;

CUBE BUILD LOG


 CREATE TABLE "ATTRIBDW_OWN"."CUBE_BUILD_LOG"
        (
            "BUILD_ID"          NUMBER,
            "SLAVE_NUMBER"      NUMBER,
            "STATUS"            VARCHAR2(10 BYTE),
            "COMMAND"           VARCHAR2(25 BYTE),
            "BUILD_OBJECT"      VARCHAR2(30 BYTE),
            "BUILD_OBJECT_TYPE" VARCHAR2(10 BYTE),
            "OUTPUT" CLOB,
            "AW"            VARCHAR2(30 BYTE),
            "OWNER"         VARCHAR2(30 BYTE),
            "PARTITION"     VARCHAR2(50 BYTE),
            "SCHEDULER_JOB" VARCHAR2(100 BYTE),
            "TIME" TIMESTAMP (6)
WITH TIME ZONE,
        "BUILD_SCRIPT" CLOB,
        "BUILD_TYPE"            VARCHAR2(22 BYTE),
        "COMMAND_DEPTH"         NUMBER(2,0),
        "BUILD_SUB_OBJECT"      VARCHAR2(30 BYTE),
        "REFRESH_METHOD"        VARCHAR2(1 BYTE),
        "SEQ_NUMBER"            NUMBER,
        "COMMAND_NUMBER"        NUMBER,
        "IN_BRANCH"             NUMBER(1,0),
        "COMMAND_STATUS_NUMBER" NUMBER,
        "BUILD_NAME"            VARCHAR2(100 BYTE)
        )
        SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
        (
            INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE "ATTRIBDW_DATA" LOB
        (
            "OUTPUT"
        )
        STORE AS BASICFILE
        (
            TABLESPACE "ATTRIBDW_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        )
        LOB
        (
            "BUILD_SCRIPT"
        )
        STORE AS BASICFILE
        (
            TABLESPACE "ATTRIBDW_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        ) ;
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000407294C00013$$" ON "ATTRIBDW_OWN"."CUBE_BUILD_LOG"
    (
        PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000407294C00007$$" ON "ATTRIBDW_OWN"."CUBE_BUILD_LOG" ( PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" PARALLEL (DEGREE 0 INSTANCES 0) ;

CUBE DIMENSION COMPILE


 CREATE TABLE "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"
        (
            "ID"               NUMBER,
            "SEQ_NUMBER"       NUMBER,
            "ERROR#"           NUMBER(8,0) NOT NULL ENABLE,
            "ERROR_MESSAGE"    VARCHAR2(2000 BYTE),
            "DIMENSION"        VARCHAR2(100 BYTE),
            "DIMENSION_MEMBER" VARCHAR2(100 BYTE),
            "MEMBER_ANCESTOR"  VARCHAR2(100 BYTE),
            "HIERARCHY1"       VARCHAR2(100 BYTE),
            "HIERARCHY2"       VARCHAR2(100 BYTE),
            "ERROR_CONTEXT" CLOB
        )
        SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "ATTRIBDW_DATA" LOB
        (
            "ERROR_CONTEXT"
        )
        STORE AS BASICFILE
        (
            TABLESPACE "ATTRIBDW_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING
        ) ;
COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."ID"
IS
    'Current operation ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."SEQ_NUMBER"
IS
    'Cube build log sequence number';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."ERROR#"
IS
    'Error number being reported';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."ERROR_MESSAGE"
IS
    'Error text being reported';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."DIMENSION"
IS
    'Name of dimension being compiled';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."DIMENSION_MEMBER"
IS
    'Problem dimension member';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."MEMBER_ANCESTOR"
IS
    'Problem dimension member''s parent';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."HIERARCHY1"
IS
    'First hierarchy involved in error';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."HIERARCHY2"
IS
    'Second hierarchy involved in error';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"."ERROR_CONTEXT"
IS
    'Extra information for error';
    COMMENT ON TABLE "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"
IS
    'Cube dimension compile log';
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000407307C00010$$" ON "ATTRIBDW_OWN"."CUBE_DIMENSION_COMPILE"
    (
        PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE( INITIAL 1048576 NEXT 1048576 MAXEXTENTS 2147483645) TABLESPACE "ATTRIBDW_DATA" PARALLEL (DEGREE 0 INSTANCES 0) ;

CUBE OPERATING LOG


 CREATE TABLE "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"
        (
            "INST_ID"    NUMBER NOT NULL ENABLE,
            "SID"        NUMBER NOT NULL ENABLE,
            "SERIAL#"    NUMBER NOT NULL ENABLE,
            "USER#"      NUMBER NOT NULL ENABLE,
            "SQL_ID"     VARCHAR2(13 BYTE),
            "JOB"        NUMBER,
            "ID"         NUMBER,
            "PARENT_ID"  NUMBER,
            "SEQ_NUMBER" NUMBER,
            "TIME" TIMESTAMP (6)
WITH TIME ZONE NOT NULL ENABLE,
        "LOG_LEVEL"    NUMBER(4,0) NOT NULL ENABLE,
        "DEPTH"        NUMBER(4,0),
        "OPERATION"    VARCHAR2(15 BYTE) NOT NULL ENABLE,
        "SUBOPERATION" VARCHAR2(20 BYTE),
        "STATUS"       VARCHAR2(10 BYTE) NOT NULL ENABLE,
        "NAME"         VARCHAR2(20 BYTE) NOT NULL ENABLE,
        "VALUE"        VARCHAR2(4000 BYTE),
        "DETAILS" CLOB
        )
        SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
        (
            INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE "ATTRIBDW_DATA" LOB
        (
            "DETAILS"
        )
        STORE AS BASICFILE
        (
            TABLESPACE "ATTRIBDW_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        ) ;
COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."INST_ID"
IS
    'Instance ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."SID"
IS
    'Session ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."SERIAL#"
IS
    'Session serial#';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."USER#"
IS
    'User ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."SQL_ID"
IS
    'Executing SQL statement ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."JOB"
IS
    'Identifier of job';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."ID"
IS
    'Current operation ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."PARENT_ID"
IS
    'Parent operation ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."SEQ_NUMBER"
IS
    'Cube build log sequence number';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."TIME"
IS
    'Time of record';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."LOG_LEVEL"
IS
    'Verbosity level of record';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."DEPTH"
IS
    'Nesting depth of record';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."OPERATION"
IS
    'Current operation';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."SUBOPERATION"
IS
    'Current suboperation';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."STATUS"
IS
    'Status of current operation';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."NAME"
IS
    'Name of record';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."VALUE"
IS
    'Value of record';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"."DETAILS"
IS
    'Extra information for record';
    COMMENT ON TABLE "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"
IS
    'Cube operations log';
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000407301C00018$$" ON "ATTRIBDW_OWN"."CUBE_OPERATIONS_LOG"
    (
        PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" PARALLEL (DEGREE 0 INSTANCES 0) ;

CUBE REJECTED RECORDS


CREATE TABLE "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"
        (
            "ID"            NUMBER,
            "SEQ_NUMBER"    NUMBER,
            "ERROR#"        NUMBER(8,0) NOT NULL ENABLE,
            "ERROR_MESSAGE" VARCHAR2(2000 BYTE),
            "RECORD#"       NUMBER(38,0),
            "SOURCE_ROW" ROWID,
            "REJECTED_RECORD" CLOB
        )
        SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
        (
            INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE "ATTRIBDW_DATA" LOB
        (
            "REJECTED_RECORD"
        )
        STORE AS BASICFILE
        (
            TABLESPACE "ATTRIBDW_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        ) ;
COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."ID"
IS
    'Current operation ID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."SEQ_NUMBER"
IS
    'Cube build log sequence number';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."ERROR#"
IS
    'Error number being reported';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."ERROR_MESSAGE"
IS
    'Error text being reported';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."RECORD#"
IS
    'Rejected record number';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."SOURCE_ROW"
IS
    'Rejected record''s ROWID';
    COMMENT ON COLUMN "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"."REJECTED_RECORD"
IS
    'Rejected record copy';
    COMMENT ON TABLE "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"
IS
    'Cube rejected records log';
CREATE UNIQUE INDEX "ATTRIBDW_OWN"."SYS_IL0000407304C00007$$" ON "ATTRIBDW_OWN"."CUBE_REJECTED_RECORDS"
    (
        PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ATTRIBDW_DATA" PARALLEL (DEGREE 0 INSTANCES 0) ;

© Oracle Blogs or respective owner

Related posts about /Oracle/AWM