Job to import system statistics

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Job to import system statistics - errors

Job to import system statistics - errors

2006-06-13 - By Smith, Steven K - MSHA

Reply: 1 2 3 4 5 6

That's already done.. Didn't help.

GRANTEE OWNER TABLE_NAME
-- ---- ---- ---- ---- ---- -- -- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- --
GRANTOR PRIVILEGE
GRA HIE
-- ---- ---- ---- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- --
--- ---
SSMITH SYS DBMS_STATS
SYS EXECUTE
NO NO

Steve Smith

Envision Technology Partners / MSHA MSIS Team

Desk: 303-231-5499

-- --Original Message-- --
From: Fuad Arshad [mailto:fuadar@(protected)]
Sent: Tuesday, June 13, 2006 12:45 PM
To: Smith, Steven K - MSHA; oracle-l
Subject: Re: Job to import system statistics - errors

grant your id execute priviliege directly to dbms_stats and it should
work.
had the same issue

-- -- Original Message ----
From: "Smith, Steven K - MSHA" <Smith.Steven@(protected)>
To: oracle-l <oracle-l@(protected)>
Sent: Tuesday, June 13, 2006 1:29:54 PM
Subject: Job to import system statistics - errors

I'm stuck. Pulling my hair out.

I have collected system statistics for day, night and weekend
processing.

I can interactively import system statistics with no problems:

SQL->execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>
'SYSTEM_STAT_TABLE', statid => 'DAYTIME', statown => 'SYSTEM');

PL/SQL procedure successfully completed.

When I put that in an oracle job, It errors and the trace file is:

ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job 2219
ORA-20000 (See ORA-20000.ora-code.com): Unable to import system statistics stats from user stat table
SYSTEM.
SYSTEM_STAT_TABLE: does not exist or insufficient privileges
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 5575
ORA-06512 (See ORA-06512.ora-code.com): at line 1
*** 2006-06-13 09:55:33.659
ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job 2219
ORA-20000 (See ORA-20000.ora-code.com): Unable to import system statistics stats from user stat table
SYSTEM.
SYSTEM_STAT_TABLE: does not exist or insufficient privileges
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 5575
ORA-06512 (See ORA-06512.ora-code.com): at line 1

I have given myself explicit privileges to the system.system_stat_table:

1 select grantee, owner, table_name, privilege
2 from dba_tab_privs
3* where table_name = 'SYSTEM_STAT_TABLE'

GRANTEE OWNER TABLE_NAME
PRIVILEGE
-- ---- ---- ---- ---- ---- -- -- ---- ---- -- -- ---- ---- ---- ---- --
-- ---- ---- ---- --
SSMITH SYSTEM SYSTEM_STAT_TABLE
ALTER
SSMITH SYSTEM SYSTEM_STAT_TABLE
DELETE
SSMITH SYSTEM SYSTEM_STAT_TABLE
INDEX
SSMITH SYSTEM SYSTEM_STAT_TABLE
INSERT
SSMITH SYSTEM SYSTEM_STAT_TABLE
SELECT
SSMITH SYSTEM SYSTEM_STAT_TABLE
UPDATE
SSMITH SYSTEM SYSTEM_STAT_TABLE
REFERENCES

II also have granted ssmith GATHER_SYSTEM_STATISTICS role.

Any ideas?

Steve

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>

<STYLE type=text/css>DIV {
MARGIN: 0px
}
</STYLE>

<META content="MSHTML 6.00.2800.1528" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=706184718-13062006><FONT face=Arial color=#0000ff size=2>That
's
already done..  Didn't help.</FONT></SPAN></DIV>
<DIV><SPAN class=706184718-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=706184718-13062006><FONT face=Courier color=#0000ff
size=2>GRANTEE          &nbsp
;            
OWNER            
             
TABLE_NAME<BR>-- ---- ---- ---- ---- ---- -- -- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- --<BR>GRANTOR      
             
    
PRIVILEGE           
             
       
GRA HIE<BR>-- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- -- --- ---</FONT></SPAN></DIV>
<DIV><SPAN class=706184718-13062006><FONT face=Courier color=#0000ff
size=2>SSMITH           
             
SYS            
             
  
DBMS_STATS<BR>SYS          
             
    
EXECUTE            
             
        
NO  NO</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV class=Section1>
<P class=MsoNormal style="mso-layout-grid-align: none"><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Comic Sans MS'; mso-bidi-font-family:
'Courier New'">Steve
Smith<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office"
/><o:p></o:p></SPAN></P>
<P class=MsoNormal style="mso-layout-grid-align: none"><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Comic Sans MS'; mso-bidi-font-family:
'Courier New'">Envision
Technology Partners / MSHA MSIS Team<o:p></o:p></SPAN></P>
<P class=MsoNormal style="mso-layout-grid-align: none"><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Comic Sans MS'; mso-bidi-font-family:
'Courier New'">Desk:
303-231-5499<o:p></o:p></SPAN></P>
<P class=MsoNormal style="mso-layout-grid-align: none"><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN
style="FONT-FAMILY: 'Comic Sans MS'"><o:p> </o:p></SPAN></P></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-- --Original Message-- --<BR><B>From:</B> Fuad Arshad
[mailto:fuadar@(protected)] <BR><B>Sent:</B> Tuesday, June 13, 2006 12:45
PM<BR><B>To:</B> Smith, Steven K - MSHA; oracle-l<BR><B>Subject:</B> Re: Job
to import system statistics - errors<BR><BR></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">
<DIV
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">
<DIV>grant your id  execute priviliege directly to dbms_stats and it
should work.</DIV>
<DIV>had the same issue <BR><BR>-- -- Original Message ----<BR>From: "Smith,
Steven K - MSHA" <Smith.Steven@(protected)><BR>To: oracle-l
<oracle-l@(protected)><BR>Sent: Tuesday, June 13, 2006 1:29:54
PM<BR>Subject: Job to import system statistics - errors<BR><BR></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff size=2>I'm
stuck.  Pulling my hair out.</FONT></SPAN></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff size=2>I
have collected system statistics for day, night and weekend processing. 
</FONT></SPAN></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff size=2>I
can interactively import system statistics with no
problems:</FONT></SPAN></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2>SQL->execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>
'SYSTEM_STAT_TABLE', statid => 'DAYTIME', statown =>
'SYSTEM');</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2>PL/SQL procedure successfully completed.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff size=2
>When
I put that in an oracle job, It errors and the trace file
is:</FONT></SPAN></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2>ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job 2219<BR>ORA-20000 (See ORA-20000.ora-code.com): Unable to
import system statistics stats from user stat table
SYSTEM.<BR>SYSTEM_STAT_TABLE: does not exist or insufficient
privileges<BR>ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 5575<BR>ORA-06512 (See ORA-06512.ora-code.com): at line
1<BR>*** 2006-06-13 09:55:33.659<BR>ORA-12012 (See ORA-12012.ora-code.com): error on auto execute of job
2219<BR>ORA-20000 (See ORA-20000.ora-code.com): Unable to import system statistics stats from user stat
table SYSTEM.<BR>SYSTEM_STAT_TABLE: does not exist or insufficient
privileges<BR>ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 5575<BR>ORA-06512 (See ORA-06512.ora-code.com): at line
1</FONT></SPAN></DIV>
<DIV><SPAN class=789061918-13062006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=789061918-13062006> </DIV>
<DIV><SPAN class=789061918-13062006></SPAN><FONT face=Arial color=#0000ff
size=2>I<SPAN class=789061918-13062006> have given myself explicit privileges
to the system.system_stat_table:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=789061918-13062006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=789061918-13062006>  1  select grantee, owner, table_name,
privilege<BR>  2  from dba_tab_privs<BR>  3* where table_name
=
'SYSTEM_STAT_TABLE'</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial><SPAN class=789061918-13062006><FONT face=Courier
color=#0000ff
size=2>GRANTEE          
             
OWNER          
TABLE_NAME           
    
PRIVILEGE<BR>-- ---- ---- ---- ---- ---- -- -- ---- ---- --
-- ---- ---- ---- ---- --
-- ---- ---- ---- --<BR>SSMITH       &nbsp
;             
   
SYSTEM         
SYSTEM_STAT_TABLE        
ALTER<BR>SSMITH          
             
 
SYSTEM          SYSTEM_STAT
_TABLE         DELETE<BR>SSMITH
             
           
SYSTEM          SYSTEM_STAT
_TABLE         INDEX<BR>SSMITH&nbsp
;             
          
SYSTEM          SYSTEM_STAT
_TABLE         INSERT<BR>SSMITH
             
           
SYSTEM          SYSTEM_STAT
_TABLE         SELECT<BR>SSMITH
             
           
SYSTEM          SYSTEM_STAT
_TABLE         UPDATE<BR>SSMITH
             
           
SYSTEM          SYSTEM_STAT
_TABLE         REFERENCES<BR></FONT
></SPAN></DIV>
<DIV><SPAN class=789061918-13062006></SPAN><FONT color=#0000ff><FONT
size=2>I<SPAN class=789061918-13062006>I also have </SPAN>g<SPAN
class=789061918-13062006>ranted ssmith GATHER_SYSTEM_STATISTICS
role.</SPAN></FONT></FONT></DIV>
<DIV><FONT color=#0000ff><FONT size=2><SPAN
class=789061918-13062006></SPAN></FONT></FONT> </DIV>
<DIV><FONT color=#0000ff><FONT size=2><SPAN class=789061918-13062006>Any
ideas?</SPAN></FONT></FONT></DIV>
<DIV><FONT color=#0000ff><FONT size=2><SPAN
class=789061918-13062006></SPAN></FONT></FONT> </DIV>
<DIV><FONT color=#0000ff><FONT size=2><SPAN
class=789061918-13062006>Steve</SPAN></FONT></FONT></FONT></SPAN></DIV></DIV>
</DIV></BLOCKQUOTE></BODY></HTML>