Perhaps this might help. Haven't tested, but both compiled successfully in
8i (I logged on as SYS).

First solution (easy but *might* have known issues):
====================================================
DECLARE
v_JOBNO NUMBER :=101;
BEGIN
DBMS_JOB.ISUBMIT(
v_JOBNO,
'BEGIN DBMS_STATS.GATHER_DATABASE_STATS(); END;',
trunc(SYSDATE),
'trunc(SYSDATE,''DD'')+1' -- Run DAILY.
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Jobno is: '||v_JOBNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error is: '||SQLERRM);
END;
/


Second solution (excludes SYS and SYSTEM schemas):
====================================================
SET SERVEROUTPUT ON SIZE 10000
CREATE OR REPLACE PROCEDURE ESTIMATE_ALL_SCHEMA_STATS
IS
v_USER VARCHAR2(50);
CURSOR C1 IS
SELECT A.USERNAME
FROM SYS.DBA_USERS A
WHERE NOT EXISTS
(SELECT 1
FROM SYS.DBA_USERS B
WHERE B.USERNAME IN ('SYS','SYSTEM')
AND B.USERNAME=A.USERNAME
)
;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO v_USER;
EXIT WHEN C1%NOTFOUND;
DBMS_STATS.GATHER_SCHEMA_STATS(v_USER);
END LOOP;
CLOSE C1;
END;
/
DECLARE
v_JOBNO NUMBER :=101;
BEGIN
DBMS_JOB.ISUBMIT(
v_JOBNO,
'BEGIN ESTIMATE_ALL_STATS(); END;',
trunc(SYSDATE),
'trunc(SYSDATE,''DD'')+1' -- Run DAILY.
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Jobno is: '||v_JOBNO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error is: '||SQLERRM);
END;
/

-----Original Message-----
From: [Email Address Removed] Thursday, October 30, 2003 12:32 PM
To: LazyDBA.com Discussion
Subject: Analyze Schema script


Hi All

Does anyone have a script that will allow a dbms_job to analyze all the
schemas in a database. I have tried unsuccessfully to run a job using a
cursor to select all the schemas that will be analyzed with the
dbms_utility_analyze_schema utility, but I cannot get the syntax right. Any
help will be appreciated.

Kevin J McClay
Systems Analyst II
Boehringer Ingelheim Pharmaceuticals
Phone: 203.791.6492
Email: <mailto:[Email Address Removed] information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
Oracle LazyDBA home page