Dear all,
While creating materialized view I received the following error;
ORA-01114: IO error writing block to file 4 (block # 183042)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 183053)"
Then I got some Information of BUG:1668488.
I saw that mt tempfile reached to 12gb, because the is autoextend on.
Then I drop that temporaru tablespace and created new as follows;
create temporary tablespace mytemp
tempfile 'e:\rbis\datafiles\mytemp_01.dbf' size 3072m reuse;
But I got following errors;
ORA-01652: unable to extend temp segment by 256 in tablespace MYTEMP
Then I add another tempfile of size 3072m.
but still I am fetching the same problem.
ORA-01652: unable to extend temp segment by 256 in tablespace MYTEMP
My parameter and their sizes are follows;
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 4096
db_cache_size big integer 33554432
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
global_context_pool_size string
hash_area_size integer 314572800
java_max_sessionspace_size integer 0
java_pool_size string 25165824
large_pool_size string 26214400
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
oracle_trace_collection_size integer 5242880
parallel_execution_message_size integer 2148
sga_max_size big integer 386691384
shared_pool_reserved_size big integer 13421772
shared_pool_size big integer 293601280
sort_area_retained_size integer 0
sort_area_size integer 157286400
workarea_size_policy string MANUAL
syntax for creating materialized view;
CREATE MATERIALIZED VIEW report_view
ENABLE QUERY REWRITE
AS
select distinct trim(wrk.bd_alcd) as ALCD,
wrk.bd_typecd as TypeCD,
wrk.bd_forcd as FORCD,
wrk.bd_curcd as CURCD,
wrk.bd_councd as COUNCD,
wrk.bd_sectcd as SECCD,
wrk.bd_matcd as MATCD,
wrk.bd_c_u_cd as C_U_CD,
wrk.bd_s_u_cd as S_U_CD,
0 as Org_FCBal,
0 as ORG_Bal,
case
when wrk.bd_type='O' then wrk.bd_fc_bal
else 0
end as Main_FCBal,
case
when wrk.bd_type='O' then (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1))
else 0
end as main_Bal,
wrk.bd_rs_int,
wrk.bd_rs_bal,
wrk.bd_fc_int,
wrk.bd_fc_bal,
' ' as TrackChangs
from ibs_work_bankdata wrk
inner join ibs_org_bankdata org
ON org.bd_yrqtr = wrk.bd_yrqtr
and org.bd_bkcode=wrk.bd_bkcode
and org.bd_forcd = wrk.bd_forcd
left join ibs_currencymaster curmst
on curmst.cur_code = wrk.bd_curcd
left join ibs_currencyexchangerate exchg
on exchg.cer_currencyid = curmst.cur_id;
please give the solution.
Thanks In advance,
Prathamesh.
] 
Blog
Search
Help
Members
Register
Login
Home
