Home » RDBMS Server » Server Administration » I've got error ORA-01422 with gather_stats_job (Oracle 10.2.0.1, Solaris 64bit)
| I've got error ORA-01422 with gather_stats_job [message #458632] |
Mon, 31 May 2010 22:42  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Good morning!
Yesterday, I've got an error ORA-01422 in alert_log file, the log file issued when Database implemented the scheduler job GATHER_TABLE_STATS:
Mon May 31 22:20:10 2010
Errors in file /u02/app/oracle/admin/VNP/bdump/vnp_j001_13464.trc:
ORA-00604: error occurred at recursive SQL level 4
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 11
The dump trace file vnp_j001_13464.trc listed the error above (GATHER_TABLE_STATS(ownname,tabname,...) when executing GATHER_JOBS_STATS).
According to those article in metalink (in-which, rare articles related), some errors as "GATHER_JOBS_STATS failed with ORA-01422 when collected stats of X$... table). In my DB, they're not x$, they are objects - heap_table. So that, I can confirm it could not cause by failed with data-dictionary, it cause by customer error. But why and what was customer's objects?
The Note in metalink
issued the namespace with objects was duplicated (solution in this note).
However, my DB's objects were not Partition, they are heap table. I re-checked if duplicated namespace:
logvnp@VNP> select count(*), o.name,u.name,o.subname,o.namespace
2 FROM
3 SYS.USER$ U, SYS.
4 OBJ$ O, SYS.PARTOBJ$ PO WHERE U.NAME like 'CCS_%'
5 AND O.NAMESPACE = NAMESPACE
6 AND U.USER# = O.OWNER#
7 AND O.OBJ# = PO.OBJ#
8 Having count(*)>1
9 group by o.name,u.name,o.subname,o.namespace;
no rows selected
logvnp@VNP>
There has not had any duplicated namespace.
Then, I tested again:
logvnp@VNP> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'CCS_HCM',
4 tabname=>'CT_NO_052010',
5 estimate_percent=>50,
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
logvnp@VNP> @lockholder
Nothing error occurs in alert_log file.
Still now, I've not understood how did the error ORA-01422 occurred, may you clarify more?
Thank you!
[Updated on: Mon, 31 May 2010 22:49] Report message to a moderator |
|
|
|
|
|
| Re: I've got error ORA-01422 with gather_stats_job [message #458793 is a reply to message #458634] |
Tue, 01 June 2010 21:01  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 01 June 2010 11:53Quote:In my DB, they're not x$,
In ALL databases these objects exist and are owned by SYS.
Please post the number of the Metalink note but do not post its content, it is illegal.
Regards
Michel
Thank you, Michel!
My opinion is not that only in my DB, the x$ did not exist, that is, the error above did not occurred to data dictionary (in Note metalink I attached), the error occurred to the customer's object (the schema user was not SYS, SYSTEM, ...).
I'll recheck the Note name, and resend to you.
|
|
|
|
|
|
| Re: I've got error ORA-01422 with gather_stats_job [message #458999 is a reply to message #458807] |
Wed, 02 June 2010 20:17 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Well, thank you very much, Michel, I wrote a bad English language. I mean that the error did not occur to the x$, they did to the other.
This is note number: ID 338845.1
Today, the error continued as following:
Unix process pid: 3616, image: oracle@database (J001)
*** 2010-06-02 22:00:21.532
*** ACTION NAME:(GATHER_STATS_JOB) 2010-06-02 22:00:21.514
*** MODULE NAME:(DBMS_SCHEDULER) 2010-06-02 22:00:21.514
*** SERVICE NAME:(SYS$USERS) 2010-06-02 22:00:21.514
*** SESSION ID:(609.4565) 2010-06-02 22:00:21.514
ORA-01422: exact fetch returns more than requested number of rows
*** 2010-06-02 22:00:21.532
GATHER_STATS_JOB: GATHER_TABLE_STATS('"CCS_TVH"','"CT_NO_042010"','""', ...)
ORA-01422: exact fetch returns more than requested number of rows
*** 2010-06-02 22:00:21.556
GATHER_STATS_JOB: GATHER_TABLE_STATS('"CCS_COMMON"','"VNPT_KM"','""', ...)
ORA-01422: exact fetch returns more than requested number of rows
*** 2010-06-02 22:00:21.955
GATHER_STATS_JOB: GATHER_TABLE_STATS('"CCS_COMMON"','"BIRTHDAY_VNP_"','""', ...)
ORA-01422: exact fetch returns more than requested number of rows
ORA-00604: error occurred at recursive SQL level 4
ORA-06512: at line 11
--More--(19%)
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6636_FABCFB5D" ("C0" NUMBER,"
C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 425495096
6 ) NOPARALLEL
Current SQL statement for this session:
WITH UNQIDX AS (SELECT /*+ index(cc) */ CD.CON#,CC.INTCOL# FROM SYS.CCOL$ CC, SY
S.CDEF$ CD WHERE CC.OBJ# = :B2 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND C
D.ENABLED IS NOT NULL AND CD.INTCOLS <= :B1 AND CD.TYPE# IN (2,3) UNION ALL SELE
CT /*+ index(i) index(ic) */ I.OBJ#,IC.INTCOL# FROM SYS.IND$ I, SYS.ICOL$ IC WHE
RE I.BO# = :B2 AND I.OBJ# = IC.OBJ# AND I.INTCOLS <= :B1 AND BITAND(PROPERTY,1)
= 1 AND BITAND(FLAGS,1025) = 0) SELECT CON# FROM UNQIDX WHERE CON# NOT IN (SELEC
T /*+ no_unnest */ CON# FROM UNQIDX WHERE INTCOL# NOT IN (SELECT /*+ no_unnest i
ndex(ic) */ INTCOL# FROM ICOL$ IC WHERE OBJ#=:B3 )) AND ROWNUM <= 1
----- PL/SQL Call Stack -----
object line object
handle number name
...skipping 1 line
5a4c17e90 9835 package body SYS.DBMS_STATS
5a4c17e90 10541 package body SYS.DBMS_STATS
5a4c17e90 13027 package body SYS.DBMS_STATS
5a4c17e90 18889 package body SYS.DBMS_STATS
ORA-00604: error occurred at recursive SQL level 4
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 11
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6637_FABCFB5D" ("C0" NUMBER,"
C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 425495096
7 ) NOPARALLEL
Current SQL statement for this session:
WITH UNQIDX AS (SELECT /*+ index(cc) */ CD.CON#,CC.INTCOL# FROM SYS.CCOL$ CC, SY
S.CDEF$ CD WHERE CC.OBJ# = :B2 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND C
D.ENABLED IS NOT NULL AND CD.INTCOLS <= :B1 AND CD.TYPE# IN (2,3) UNION ALL SELE
CT /*+ index(i) index(ic) */ I.OBJ#,IC.INTCOL# FROM SYS.IND$ I, SYS.ICOL$ IC WHE
RE I.BO# = :B2 AND I.OBJ# = IC.OBJ# AND I.INTCOLS <= :B1 AND BITAND(PROPERTY,1)
= 1 AND BITAND(FLAGS,1025) = 0) SELECT CON# FROM UNQIDX WHERE CON# NOT IN (SELEC
T /*+ no_unnest */ CON# FROM UNQIDX WHERE INTCOL# NOT IN (SELECT /*+ no_unnest i
ndex(ic) */ INTCOL# FROM ICOL$ IC WHERE OBJ#=:B3 )) AND ROWNUM <= 1
----- PL/SQL Call Stack -----
object line object
handle number name
--More--(19%)
Now, I try to view the line in dbms_stats
logvnp@VNP> select line, text
2 from dba_source
3 where owner = 'SYS'
4 and name = 'DBMS_STATS'
5 and line between 9385 and 10541
6 and type = 'PACKAGE BODY'
7 order by line asc;
no rows selected
logvnp@VNP>
However, the packaged was wrapped, then, I could not view the line. And I could not use ordebug setospid because the 3616 was not in instance, it was expiration.
According to the Note, I will re-gather the stats by manually:
logvnp@VNP> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'CCS_COMMON',
4 tabname=>'VNPT_KM',
5 estimate_percent=>100,
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
logvnp@VNP> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') analyzed
2 from dba_tables
3 where owner='CCS_COMMON'
4 and table_name='VNPT_KM';
ANALYZED
---------------------------------------------------------------------------
03/06/2010 08:35:28
logvnp@VNP>
OKie, no problem.
However, the table VNPT_KM is not partition, it is the normal table. In the note I attached above, this described the error to the partition table, not is normal table.
May you help me?
Thank you!
[Updated on: Wed, 02 June 2010 20:20] Report message to a moderator |
|
|
|
| Re: I've got error ORA-01422 with gather_stats_job [message #459025 is a reply to message #458999] |
Thu, 03 June 2010 01:11 |
 |
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The note recommends to explicitly set GRANULARITY parameter (ALL in your case), did you try it?
Other things the offendig query is:
WITH unqidx
AS (SELECT /*+ index(cc) */ cd.con#,
cc.intcol#
FROM sys.ccol$ cc,
sys.cdef$ cd
WHERE cc.obj# = :B2
AND cd.con# = cc.con#
AND cd.obj# = cc.obj#
AND cd.enabled IS NOT NULL
AND cd.intcols <= :B1
AND cd.type# IN ( 2, 3 )
UNION ALL
SELECT /*+ index(i) index(ic) */ i.obj#,
ic.intcol#
FROM sys.ind$ i,
sys.icol$ ic
WHERE i.bo# = :B2
AND i.obj# = ic.obj#
AND i.intcols <= :B1
AND Bitand(property, 1) = 1
AND Bitand(flags, 1025) = 0)
SELECT con#
FROM unqidx
WHERE con# NOT IN (SELECT /*+ no_unnest */ con#
FROM unqidx
WHERE intcol# NOT IN (SELECT /*+ no_unnest index(ic) */
intcol#
FROM icol$ ic
WHERE obj# = :B3))
AND rownum <= 1
/
Does the trace file file mentionned the value of the bind variables?
Otherwise try to execute it with (guess from my part):
- B1 = I don't know, try different values to see what happen
- B2 = object_if of your table
- B3 = object_id of the indexes of your table
Regards
Michel
|
|
|
|
| Re: I've got error ORA-01422 with gather_stats_job [message #459045 is a reply to message #459025] |
Thu, 03 June 2010 03:31 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you Michel, I'll recheck as you suggested!
logvnp@VNP> select object_id, object_name
2 from dba_objects
3 where owner='CCS_COMMON'
4 and object_type='TABLE'
5 and object_name='VNPT_KM';
OBJECT_ID OBJECT_NAME
---------- ------------
781425 VNPT_KM
logvnp@VNP> select index_name, table_name, owner
2 from dba_indexes
3 where owner='CCS_COMMON'
4 and table_name='VNPT_KM';
no rows selected
$ cd $HOME
$ sqlplus /"as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 3 15:51:13 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
sys@VNP> WITH unqidx
2 AS (SELECT /*+ index(cc) */ cd.con#,
cc.intcol#
3 4 FROM sys.ccol$ cc,
5 sys.cdef$ cd
6 WHERE cc.obj# = 781425
7 AND cd.con# = cc.con#
8 AND cd.obj# = cc.obj#
9 AND cd.enabled IS NOT NULL
10 AND cd.intcols <= 10
11 AND cd.type# IN ( 2, 3 )
12 UNION ALL
13 SELECT /*+ index(i) index(ic) */ i.obj#,
14 ic.intcol#
15 FROM sys.ind$ i,
16 sys.icol$ ic
17 WHERE i.bo# = 781425
18 AND i.obj# = ic.obj#
AND i.intcols <= 10
19 20 AND Bitand(property, 1) = 1
21 AND Bitand(flags, 1025) = 0)
22 SELECT con#
23 FROM unqidx
24 WHERE con# NOT IN (SELECT /*+ no_unnest */ con#
25 FROM unqidx
26 WHERE intcol# NOT IN (SELECT /*+ no_unnest index(ic) */
27 intcol#
28 FROM icol$ ic
29 WHERE obj# = null))
30 AND rownum <= 1
31 /
no rows selected
sys@VNP>
I pay attention very much to the error name: Recursive sql ...
Therefore, I think, I'll find the solution in metalink within approach: What caused recursive sql in job? It occurs before or after the job GATHER_STATS_JOBS begin.
Thank you very much!
|
|
|
|
|
|
| Re: I've got error ORA-01422 with gather_stats_job [message #459199 is a reply to message #459054] |
Thu, 03 June 2010 21:15 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear Michel!
I've found the root of problem. According to the position I said above - the recursive sql. Of course, I knew what is recursive sql, but I paid attention due to some reasons:
1- Oracle's error like that (Recursive SQL) did not cause to the package by itself probably, the error must (can) occurred because of coder's code.
2- What caused user's code and made it to the Oracle's job? There're many reason, however, one of reason was a trigger. What's the trigger could made error like that? Maybe a DDL trigger, a database trigger owned by sys which was created by user. Then I found:
logvnp@VNP> select object_name, object_type, owner
2 from dba_objects
3 where owner='SYS'
4 and object_type='TRIGGER';
OBJECT_NAME OBJECT_TYP OWNER
---------------------------- ---------- --------
AW_DROP_TRG TRIGGER SYS
NO_VM_DROP TRIGGER SYS
NO_VM_DROP_A TRIGGER SYS
NO_VM_CREATE TRIGGER SYS
NO_VM_ALTER TRIGGER SYS
AURORA$SERVER$STARTUP TRIGGER SYS
AURORA$SERVER$SHUTDOWN TRIGGER SYS
CDC_ALTER_CTABLE_BEFORE TRIGGER SYS
CDC_CREATE_CTABLE_AFTER TRIGGER SYS
CDC_CREATE_CTABLE_BEFORE TRIGGER SYS
CDC_DROP_CTABLE_BEFORE TRIGGER SYS
OLAPISTARTUPTRIGGER TRIGGER SYS
OLAPISHUTDOWNTRIGGER TRIGGER SYS
OBTAIN_IP TRIGGER SYS
RDS_DDL_TRIGGER$ TRIGGER SYS
15 rows selected.
Some one created a database trigger, named differently to Oracle's default trigger owned by SYS (triggername$action or triggername_action). This is RDS_DDL_TRIGGER$.
logvnp@VNP> col text format a45
logvnp@VNP> col line format 999
logvnp@VNP> select line,text
2 from dba_source
3 where owner='SYS'
4 and name='RDS_DDL_TRIGGER$'
5 and type='TRIGGER'
6 order by line asc;
LINE TEXT
---- ---------------------------------------------
1 TRIGGER rds_ddl_trigger$
2 BEFORE
3 DDL
4 ON DATABASE
5 declare
6 ip_ VARCHAR2(2000);
7 date_ VARCHAR2(100);
8 user_ VARCHAR2(100);
9 prog_ varchar2(100);
10 begin
11 ip_:=sys_context('USERENV','IP_ADDRESS');
12 date_:=TO_CHAR(SYSDATE,'dd/mm/yyyy');
13 user_:=sys_context('USERENV','SESSION_USER'
);
14
15 SELECT PROGRAM into prog_ FROM V$SESSI
ON WHERE AUDSID = USERENV('SESSIONID') and st
atus = 'ACTIVE' and type <> 'BACKGROUND';
16
17 IF USER_!='SYS' then
18 if (NOT
19 (
20 SYSDATE < TO_DATE(date_||' 06
:30:00','dd/mm/yyyy hh24:mi:ss')
21 or
22 SYSDATE > TO_DATE(date_||' 16
:30:00','dd/mm/yyyy hh24:mi:ss')
23 OR
24 (
25 SYSDATE > TO_DATE(date_||
' 11:30:00','dd/mm/yyyy hh24:mi:ss')
26 AND
27 SYSDATE < TO_DATE(date_||
' 13:50:00','dd/mm/yyyy hh24:mi:ss')
28 )
29 )
30 ) AND
31 ( ip_ NOT IN ([Edit MC: IP list removed])
35 or
36 ( prog_ not in ('w3wp.exe',
'JDBC Thin Client') and prog_ not like 'ORACL
E.EXE@'
37 and prog_ not like 'oracl
e@database%' and prog_ not like 'emagent@data
base%'
38 )
39 ) then
40 RAISE_APPLICATION_ERROR(-20999,'O
racle Windows TNS error: No Listener found no
w');
41 END IF;
42 else
43 insert into ccs_common.log_db_ddl_access(
d, osuser,current_user,host,terminal,ip,owner
,type,name,sysevent)
44 values(
45 sysdate,
46 sys_context('USERENV','OS_USER') ,
47 user_ ,
48 sys_context('USERENV','HOST') ,
49 sys_context('USERENV','TERMINAL') ,
50 ip_,
51 ora_dict_obj_owner,
52 ora_dict_obj_type,
53 ora_dict_obj_name,
54 ora_sysevent
55 );
56 end if;
57 end;
57 rows selected.
logvnp@VNP>
What's the line error? It's:
15 SELECT PROGRAM into prog_ FROM V$SESSI
ON WHERE AUDSID = USERENV('SESSIONID') and st
atus = 'ACTIVE' and type <> 'BACKGROUND';
Yesterday, I re-write these line: Select max(program) .... and the error did not occurred again.
Thank you very much!
[Updated on: Sun, 06 June 2010 10:24] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 17:51:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
