Begin
For Cur_Rec In (select directory_path from DBA_DIRECTORIES where DIRECTORY_NAME='LDOUTPUT') Loop
Begin
Execute Immediate 'create or replace directory XMLDIR as '''|| Cur_Rec.directory_path ||'''';
Execute Immediate 'create or replace directory DOCUMENT as '''|| Cur_Rec.directory_path ||'''';
End;
End Loop;
End;
/
SYMPTOMS
When attempting to creating a unique index, the following error occurs.
Create Index BillVoucher on Faleddrcr(Firmnumber,nFinancialyear,Exchange,Booktype,Vallan,Special,Entrycode) tablespace Indx storage (initial 60M Next 30M );
Create Index BilldescVoucher on Faledgerdescription(Firmnumber,nFinancialyear,Exchange,Booktype,Vallan,Special,Entrycode) tablespace Indx storage (initial 60M Next 30M );
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.NO_VM_CREATE_PROC", line 147
ORA-06512: at line 26
CAUSE
The cause of this problem has been identified in Bug 11061801.
SOLUTION
download Patch 11061801 to resolve this issue or use below workaround.
Workaround:
To workaround the problem change the Index DDL and add a <space> between the table name "Faleddrcr" and the open parenthesis prior to the column list.
after beautify issue got resolved.
CREATE INDEX BillVoucher ON Faleddrcr
(
Firmnumber,
nFinancialyear,
Exchange,
Booktype,
Vallan,
Special,
Entrycode
)
TABLESPACE Indx STORAGE
(
INITIAL 60M NEXT 30M
);
CREATE INDEX BilldescVoucher ON Faledgerdescription
(
Firmnumber,
nFinancialyear,
Exchange,
Booktype,
Vallan,
Special,
Entrycode
)
TABLESPACE Indx STORAGE
(
INITIAL 60M NEXT 30M
);
select * from dba_profiles;
select profile, limit from DBA_PROFILES
where profile = 'DEFAULT'
and resource_name = 'IDLE_TIME';
select profile, limit from DBA_PROFILES
where profile <> 'DEFAULT'
and resource_name = 'IDLE_TIME';
alter profile BOPOLICY LIMIT IDLE_TIME 30;
alter profile DEFAULT LIMIT IDLE_TIME 30;
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;
It is changed to 30 min of idle time. The application will disconnect if it is idle for 30.
Status of session will be marked as sniped.
TO clean sniped session, need to schedule job to kill sniped session.
CREATE OR REPLACE PROCEDURE "FLUSH_SNIPED_SESSION"
AS
BEGIN
FOR X IN (
SELECT inst_id,SID, SERIAL#
FROM GV$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS='SNIPED'
) LOOP
EXECUTE IMMEDIATE 'alter system disconnect session '''|| X.SID
|| ',' || X.SERIAL# || ',@' || X.inst_id || ''' immediate';
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'FLUSH$SNIPED$SESSION',
job_type => 'STORED_PROCEDURE',
job_action => 'FLUSH_SNIPED_SESSION',
start_date => '10-MAR-13 10:00.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=minutely; interval=5;byhour=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',
end_date => NULL,
enabled => TRUE,
comments => 'Flush Sniped Session Kshitij Agarwal');
END;
/
exec dbms_scheduler.run_job('FLUSH$SNIPED$SESSION');
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29263: HTTP protocol error
following run
select utl_http.request('http://192.168.100.85/hii1111111111111111111111111111111111111111111111111111111111111111111188888888888888888888888888888888888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii7777766666iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiuu') from dual;
following give error - ORA-29273 ORA-06512 ORA-29263 HTTP protocol error because of enter line character
select utl_http.request('http://192.168.100.85/hii111111111111111111111111111111111111111111111111111111111111111111118888888888888888888888888888888888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889
9999999999999999999999999999999999999999999999999999999999999999999999999999999iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii7777766666iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiuu') from dual;
BEGIN
FOR CUR_REC IN (select * from (select table_owner,table_name,partition_name,partition_position,last_analyzed from dba_tab_partitions where table_name ='POWEROFATTORNEYSTOCKS' order by partition_position desc) where rownum=1) LOOP
BEGIN
EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats(ownname =>''LDBO'' ,Tabname =>''' || cur_rec.table_name || ''',Partname =>''' || cur_rec.partition_Name || ''',cascade => true, DEGREE=>DBMS_STATS.DEFAULT_DEGREE); end;';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR CUR_REC IN (select ss.username,ss.terminal,ss.sid,ss.serial#,ss.inst_id from gv$session ss where module='PL/SQL Developer') LOOP
BEGIN
EXECUTE IMMEDIATE 'alter system disconnect session''' || CUR_REC.sid || ',' || CUR_REC.serial# || ',@' || CUR_REC.inst_id || ''' immediate';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
ORA-12018 ORA-00979 ORA-06512
When we create Materialized report view it is successfully created and reports are coming properly.
But when we do complete refresh using below mentioned command it is showing an error
SQL> exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;
begin dbms_mview.refresh('mv_rkcapitaldashboard','C'); end;
ORA-12018: following error encountered during code generation for "LDBO"."MV_RKCAPITALDASHBOARD"
ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
Bug
Oracle Version 11.2.0.4
Solution
alter session set "_complex_view_merging"=false;
or add following in query
/*+ opt_param('_complex_view_merging','false') */
or try following in SQL
/*+ NO_QUERY_TRANSFORMATION */
exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;
