insert into vit_pp_tab
select d.saledate, d.article, decode(d.saleop, 1, d.salelocationfrom, d.salelocationto) loc, NVL(d5.price_pp,0) price_pp,
NVL(d5.cliind_pp,0) cliind_pp,
decode(d.incomeclientindex,100150,100150,
100137,100137,
100182,100182,
100191,100191,
100250,100250,
100795,100795,
100885,100885,
100823,100823,
100772,100772,
100978,100978,
101032,101032,
100657,100657,
100648,100648,
100674,100674,
100784,100784,
100926,100926,
100019,100019,
100321,100321,
decode(d4.idclass,2,0,decode(d3.idclass,1,0,decode(d1.opcode,0,d.incomeclientindex,0))))
clientindex,
decode(d.saleop, 1, sum(d.saleq),0) saleq,
decode(d.saleop, 3, sum(d.saleq),0) w_in_q,
decode(d.saleop, 1, sum(d.salesum),0) salesum,
decode(d.saleop, 3, sum(d.salesum),0) w_in_sum,
decode(d.saleop, 1, sum(decode(d.saleuserop,-2,d.primecost,0)),0) primecost,
decode(d.saleop, 3, sum(decode(d.saleuserop,-2,d.primecost,0)),0) w_in_primecost
from ffmaprep_ d, smdocuments d1, smclients d3, smsuppliers d4
, (select d.article, q2.locationto, max(d.itemprice) keep (dense_rank last order by q2.createdat) price_pp,
max(q2.clientindex) keep (dense_rank last order by q2.createdat) cliind_pp
from smspec d, smdocuments q2, smclients q3, smsuppliers q4
where q2.id=d.docid
and q2.doctype=d.doctype
and q2.clientindex = q3.id(+)
and q2.clientindex = q4.id(+)
and decode(q2.clientindex,100150,1,
100137,1,
100182,1,
100191,1,
100250,1,
100795,1,
100885,1,
100823,1,
100772,1,
100978,1,
101032,1,
100657,1,
100648,1,
100674,1,
100784,1,
100926,1,
100019,1,
100321,1,
decode(q4.idclass,2,0,decode(q3.idclass,1,0,1)))=1
and q2.opcode in(0,13)
and q2.docstate=3
group by d.article, q2.locationto) d5
where d.rectype=1
and d.saleop in (1,3)
and d.incomeclientindex = d3.id(+)
and d.incomeclientindex = d4.id(+)
and d.incomeid=d1.id(+)
and d.incometype=d1.doctype(+)
and d.article=d5.article(+)
and decode(d.saleop, 1, d.salelocationfrom, d.salelocationto)=d5.locationto(+)
group by d.saledate, d.article, d.saleop, d.salelocationfrom, d.salelocationto, d5.price_pp, d5.cliind_pp,
decode(d.incomeclientindex,100150,100150,
100137,100137,
100182,100182,
100191,100191,
100250,100250,
100795,100795,
100885,100885,
100823,100823,
100772,100772,
100978,100978,
101032,101032,
100657,100657,
100648,100648,
100674,100674,
100784,100784,
100926,100926,
100019,100019,
100321,100321,
decode(d4.idclass,2,0,decode(d3.idclass,1,0,decode(d1.opcode,0,d.incomeclientindex,0))))
order by sum(decode(d.saleuserop,-2,d.primecost,0)) SELECT STATUS,TABLESPACE_NAME, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME STATUS TABLESPACE_NAME SUM(BYTES)/1024/1024 COUNT(*) --------- ------------------------------ -------------------------------------- -------------------------------------- EXPIRED UNDOTBS1 0,25 4 UNEXPIRED UNDOTBS1 94234,25 3487 ACTIVE UNDOTBS1 65 2
SELECT owner, segment_name, segment_type,tablespace_name,
ROUND(SUM (BYTES) / 1024 / 1024) "SIZE (MB)",
SUM (blocks) blocks, COUNT (*) extents
FROM dba_extents
WHERE owner = 'SUPERMAG' AND segment_name = 'VIT_PP_TAB'
and segment_type = 'TABLE' and tablespace_name='VITO_TAB'
GROUP BY owner, segment_name, segment_type, tablespace_name
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE (MB) BLOCKS EXTENTS
------------------------------ --------------------------------------------------------------------------------- ------------------ ------------------------------ -------------------------------------- -------------------------------------- --------------------------------------
SUPERMAG VIT_PP_TAB TABLE VITO_TAB 2752 352256 155 -- Start of DDL Script for Table SUPERMAG.VIT_PP_TAB
-- Generated 12-àïð-2012 13:03:16 from SUPERMAG@ILISHCO
CREATE TABLE vit_pp_tab
(saledate DATE NOT NULL,
article VARCHAR2(50 BYTE) NOT NULL,
loc NUMBER(10,0) NOT NULL,
price_pp NUMBER(19,4) NOT NULL,
cliind_pp NUMBER(10,0) NOT NULL,
clientindex NUMBER(10,0) NOT NULL,
saleq NUMBER(19,4),
w_in_q NUMBER(19,4),
salesum NUMBER(19,4),
w_in_sum NUMBER(19,4),
primecost NUMBER(19,4),
w_in_primecost NUMBER(19,4))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE vito_tab
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- Indexes for VIT_PP_TAB
CREATE INDEX vit_pp_tab_datlocart_idx ON vit_pp_tab
(
saledate ASC,
loc ASC,
article ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
CREATE INDEX vit_pp_tab_cliind_pp_idx ON vit_pp_tab
(
cliind_pp ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
CREATE INDEX vit_pp_tab_saledate_idx ON vit_pp_tab
(
saledate ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
CREATE INDEX vit_pp_tab_artloc_idx ON vit_pp_tab
(
article ASC,
loc ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
CREATE INDEX vit_pp_tab_clientindex_idx ON vit_pp_tab
(
clientindex ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
CREATE INDEX vit_pp_tab_artdat_idx ON vit_pp_tab
(
article ASC,
saledate ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE vito_idx
STORAGE (
INITIAL 1073741824
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
NOLOGGING
/
-- End of DDL Script for Table SUPERMAG.VIT_PP_TAB SQL> show parameter %undo%; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 28800 undo_tablespace string UNDOTBS1