| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with DBMS_UTILITY.ANALYZE_DATABASE
You have wild imagination if after reading this note 100419.1 your conclusion is that Oracle recommend to analyze SYS objects. I include text of this document.
Alex Hillman
Doc ID:
Note:100419.1
Subject:
SCRIPT: VALIDATE.SQL to ANALYZE ..
VALIDATE objects in a Tablespace
Type:
SCRIPT
Status:
PUBLISHED
Content Type:
TEXT/PLAIN
Creation Date:
29-FEB-2000
Last Revision Date:
01-MAR-2000
Title: Script to ANALYZE .. VALIDATE objects in a Tablespace
Disclaimer:
This script is provided for educational purposes only. It is NOT supported
by
Oracle Support Services. The script has been tested and appears to work
as
intended. However, you should always test any script before relying on
it.
PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way
text
editors, email packages and operating systems handle text formatting
(spaces,
tabs and carriage returns), this script may not be in an executable state
when
you first receive it. Check over the script to ensure that errors of this
type are corrected.
Abstract: The purpose of this script is to create a package which will
check all object in a given tablespace.
Requirements: The package should be created and executed as the user
SYS.
Version Testing: This script has been tested on version 7.3.3.4.1,
8.0.6, 8.1.5 and 8.1.6.
Script:
REM ======================= Start of Script ============================REM VALIDATE.SQL
REM in a given tablespace using the REM ANALYZE TABLE .. VALIDATE STRUCTURE [CASCADE]; REM command. REM The package finds all TABLES and CLUSTERS in the REM given tablespace and issues the relevant ANALYZE REM commands.
REM 1. Install this package in the SYS schema REM Eg: connect internal REM @validate REM This should create the "ValidateStructure" package.REM
REM 1. Ensure SPOOL is enabled to catch output and enable SERVEROUT
REM Eg:
REM spool myvalidate.log
REM execute dbms_output.enable(1000000);
REM set serveroutput on
REM
REM 2. Run one of:
REM execute ValidateStructure.TS('TABLESPACE_NAME', TRUE);
REM or
REM execute ValidateStructure.TS('TABLESPACE_NAME', FALSE);
REM
REM to check objects in the named tablespace CASCADE or NOT CASCADE
REM respectively.
REM This will run until all requested items are scanned.
REM
REM 3. Errors from the ANALYZE commands are output to DBMS_OUTPUT
REM and so any failing objects are listed when all TABLES / CLUSTERS
REM have been analyzed. More detailed output from failing ANALYZE
REM commands will be written to the user trace file in
USER_DUMP_DEST
procedure ts( name varchar2 , casc boolean default true);
END;
/
CREATE OR REPLACE PACKAGE BODY ValidateStructure
AS
numbad number:=0;
--
procedure item( typ varchar2 , schema varchar2, name varchar2,
casc boolean default true) is
stmt varchar2(200);
c number;
opt varchar2(20):=null;
begin
if (casc) then
opt:=' CASCADE ';
end if;
c:=dbms_sql.open_cursor;
begin
stmt:='ANALYZE '||typ||' "'||schema||'"."'||name||'" '||
'VALIDATE STRUCTURE'||opt;
dbms_sql.parse(c,stmt,dbms_sql.native);
exception
when others then
dbms_output.put_line( 'Error analyzing '||typ||opt||
'"'||schema||'.'||name||'" '||sqlerrm);
numbad:=numbad+1;
end;
dbms_sql.close_cursor(c);
cursor c is
SELECT 'TABLE' typ,owner, table_name FROM DBA_TABLES
where tablespace_name=upper(name)
UNION ALL
SELECT 'CLUSTER',owner, cluster_name FROM DBA_CLUSTERS
where tablespace_name=upper(name)
;
n number:=0;
begin
numbad:=0;
for R in C
loop
n:=n+1;
ValidateStructure.item(R.typ,r.owner,r.table_name,casc);
end loop;
dbms_output.put_line('Analyzed '||N||' objects with '||numbad||'
errors');
if (numbad>0) then
raise_application_error(-20002,
numbad||' errors - SET SERVEROUT ON to view details');
end if;
REM ======================== End of Script ============================
-----Original Message-----
Sent: Monday, May 21, 2001 4:35 AM
To: Multiple recipients of list ORACLE-L
Oracle itself suggests to analyze "ALL SYS OBJECTS" before an upgrade to 8.1.6 from 7.x or 8.0.x versions. This is because of default DB_BLOCK_CHECKING for SYSTEM tablespace.
Just have a look at metalink note 100419.1
HTH,
Rajesh
-----Original Message-----
C.S.Venkata
Subramanian
Sent: Monday, May 21, 2001 9:15 AM
To: Multiple recipients of list ORACLE-L
Upto my knowledge, u can't analyse sys objects.
HTH
--
On Fri, 18 May 2001 08:17:08
Khedr, Waleed wrote:
>Set sql_trace on before running it and see which sys sql fails.
>
>Regards,
>
>Waleed
>
>-----Original Message-----
>Sent: Friday, May 18, 2001 10:31 AM
>To: Multiple recipients of list ORACLE-L
>
>
>unfortunately no other result with ... grant analyze any
>
>> -----Ursprüngliche Nachricht-----
>> Von: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
>> Gesendet: Freitag, 18. Mai 2001 15:36
>> An: Multiple recipients of list ORACLE-L
>> Betreff: Re: Problem with DBMS_UTILITY.ANALYZE_DATABASE
>>
>>
>> grant analyze any to sys;
>>
>> hth
>> connor
>>
>> --- "Bachmann, Henrik"
>> <Henrik.Bachmann_at_bim-consulting.de> wrote: > Hi there,
>> >
>> > I have a problem to analyze my whole database as
>> > user sys:
>> >
>> > Oracle8 Enterprise Edition Release 8.0.5.0.0 -
>> > Production
>> > PL/SQL Release 8.0.5.0.0 - Production
>> >
>> > SQL> BEGIN
>> > 2
>> >
>> DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE',NULL,NULL,NULL);
>> > 3 END;
>> > 4 /
>> > BEGIN
>> > *
>> > FEHLER in Zeile 1:
>> > ORA-20000: You have insufficient privileges for an
>> > object in this
>> > database.
>> > ORA-06512: in "SYS.DBMS_UTILITY", Zeile 282
>> > ORA-06512: in Zeile 2
>> >
>> > Anybody out there who can give me a hint?
>> >
>> > Best regards
>> >
>> > Henrik
>> >
>> > Henrik Bachmann
>> > mailto:Henrik.Bachmann_at_bim-consulting.de
>> >
>> > B.I.M.-Consulting Magdeburg
>> > http://www.bim-consulting.de/
>> > D-39108 Magdeburg
>> >
>> >
>> > --
>> > Please see the official ORACLE-L FAQ:
>> > http://www.orafaq.com
>> > --
>> > Author: Bachmann, Henrik
>> > INET: Henrik.Bachmann_at_bim-consulting.de
>> >
>> > Fat City Network Services -- (858) 538-5051 FAX:
>> > (858) 538-5051
>> > San Diego, California -- Public Internet
>> > access / Mailing Lists
>> >
>> --------------------------------------------------------------------
>> > To REMOVE yourself from this mailing list, send an
>> > E-Mail message
>> > to: ListGuru_at_fatcity.com (note EXACT spelling of
>> > 'ListGuru') and in
>> > the message BODY, include a line containing: UNSUB
>> > ORACLE-L
>> > (or the name of mailing list you want to be removed
>> > from). You may
>> > also send the HELP command for other information
>> > (like subscribing).
>>
>>
>> =====
>> Connor McDonald
>> http://www.oracledba.co.uk (mirrored at
>> http://www.oradba.freeserve.co.uk)
>>
>> "Some days you're the pigeon, some days you're the statue"
>>
>> ____________________________________________________________
>> Do You Yahoo!?
>> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
>> or your free @yahoo.ie address at http://mail.yahoo.ie
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: =?iso-8859-1?q?Connor=20McDonald?=
>> INET: hamcdc_at_yahoo.co.uk
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing
Lists
>> --------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Bachmann, Henrik
> INET: Henrik.Bachmann_at_bim-consulting.de
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: C.S.Venkata Subramanian
INET: csvenkata_at_lycos.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rajesh Dayal
INET: Rajesh_at_ohitelecom.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: Alex.Hillman_at_usmint.treas.gov
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon May 21 2001 - 11:43:46 CDT
 |
 |