Re: declare SYS.DBMS

From: Greg G <ggershSNACK_at_CAKEctc.net>
Date: Fri, 07 Nov 2003 10:13:19 -0500
Message-ID: <4OGdnTXDlK0tKzaiRVn-vA@ctc.net>

Michel Cadot wrote:
> "Greg G" <ggershSNACK_at_CAKEctc.net> a écrit dans le message de news:p5-dnViroPPaLTeiRVn-vg_at_ctc.net...
>

>>
>>Daniel Morgan wrote:
>>
>>
>>>Whoever is managing this little shop of horrors should go back to
>>>basics. Bounce the
>>>server. Does the GRANT still hang? If so I'd be running export to verify
>>>logical integrity,
>>>catalog.sql, catproc.sql, etc. Bounce the sever again. Does the GRANT
>>>still hang? If so
>>>I'd be opening an iTAR.
>>
>>    In fact, the server was bounced about a week ago.  Unfortunately, I
>>can't do that whenever I want, as it's a production box and there's a
>>boojum-load of processes that connect to the thing.
>>    Forgive my ignorance, but how will running export verify logical
>>integrity?  Or do you mean for me to export and then re-import
>>everything.  That's not really feasable as this database is gimungous.
>>
>>-Greg G
>>

>
>
> Export checks logical structure of each block. You don't have to import.
> You can also use RMAN.
> Or execute the following script as SYS:
>
> Set verify off
> Set space 0
> Set heading off
> Set feedback off
> Spool t.lst
> Select 'Analyze '||object_type||' '||object_name||' validate structure;'
> from dba_objects
> where owner='SYS' and object_type in ('INDEX','TABLE','CLUSTER')
> order by object_name, object_type;
> spool off
> Set echo on
> Set feedback on
> spool AnalyzeDict.lst
> @t.lst
> spool off

Well now. That's intersting. I get a whole pagefull of Index/Tabel/Cluster analyzed. No problems showing up at all. However, I got this from exp:

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SYS
. exporting object type definitions for user SYS

About to export SYS's objects ...

. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes

EXP-00061: unable to find the outer table name of a nested table EXP-00000: Export terminated unsuccessfully

I'm not sure how to track down exactly what the problem is, though. I looked at SYS in the OEMc and the only odd-looking thing I found was that package body DBMS_ASYNCRPC_PUSH was showing as invalid. A 'show errors' says:
Line # = 103 Column # = 49 Error Text = PLS-00302: component 'RESULT_STARTUP_SECONDS' must be declared Line # = 103 Column # = 27 Error Text = PL/SQL: Statement ignored

Additionally, DBMS_DEFER_INTERAL_SYS and DBMS_DEFER_SYS_PART1 also have errors.

I assume that this is bad, but I'm not sure how to go about fixing it. My guess is that something happened during a database migration that occurred here before I was brought in.

-Greg G Received on Fri Nov 07 2003 - 09:13:19 CST

Re: declare SYS.DBMS_PIPE???