-
I have a cronjob setup to analyze the tables daily in development and qa environment and it works fine.
The script is as following:
sqlplus internal <
col total format 9,999,999,999
alter tablespace temp coalesce;
execute DBMS_UTILITY.ANALYZE_SCHEMA('52_DEV','COMPUTE');
exit
EOF
However I didn't have a privilege to use internal and I don't want to hardcode the schema name and password in customer's production system. Any suggestions?
-
You can achieve the same with submitting a job with the dbms_job package. You don't have to hardcode U/P for that.
Ales
-
Create a user with resource , connect , analyze any , select any previlege.
login with that user and create a dynamic sql statement and execute it.
Jr.
-
Thanks Alex.
Can you show me an example how to use the dbms_job to analyze schema daily.
Blu
-
Code:
CREATE SEQUENCE SEQ_COD_ERROR_PROCESO
MINVALUE 0
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 0
NOCACHE
NOORDER
NOCYCLE
/
CREATE TABLE CTL_ERROR_PROCESO
(COD_ERROR_PROCESO NUMBER,
FEC_ERROR DATE,
DES_ERROR VARCHAR2(256))
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
LOGGING
STORAGE(INITIAL 131072
NEXT 131072
PCTINCREASE 0)
/
CREATE OR REPLACE PROCEDURE ANALIZAR_BBDD
AS
type array is table of varchar2(30);
l_users array := array('ARPA_OWN', 'BSCS_OWN', 'CM','CMGED', 'CMSAPDWH', 'CMSAPGED',
'CMSAPMDW', 'LSC', 'METADATOS_OWN', 'METRICAS', 'OPSC','PERFSTAT', 'RECHAZOS');
l_junk varchar2(30);
errmsg ctl_error_proceso.des_error%type;
BEGIN
/* asignamos calendario espanola a la sesion */
execute immediate 'alter session set nls_territory=''SPAIN''';
IF to_char(sysdate, 'D') in (2, 4, 6)
THEN
/* se asigna un sort_area_size grande para acelerar el proceso */
execute immediate 'alter session set sort_area_retained_size=65536';
execute immediate 'alter session set sort_area_size=10485760';
/* bucle para procesar los usuarios */
FOR i in 1 .. l_users.count
LOOP
BEGIN
select username into l_junk
from dba_users
where username = l_users(i);
/* deleting stats first due to 8.1.7 bug */
dbms_stats.delete_schema_stats(OWNNAME => l_users(i));
/* analyzing and generating 10 buckets */
dbms_stats.gather_schema_stats(OWNNAME => l_users(i),
ESTIMATE_PERCENT => 20,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 10',
DEGREE => 1,
CASCADE => TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
errmsg := 'Error durante analyze: no existe el usuario '||l_users(i);
insert into ctl_error_proceso values
(seq_cod_error_proceso.nextval, sysdate, errmsg);
commit;
WHEN OTHERS THEN
raise;
END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
errmsg := 'Error durante analyze: '||sqlcode||', '||sqlerrm;
insert into ctl_error_proceso values
(seq_cod_error_proceso.nextval, sysdate, errmsg);
commit;
END;
/
variable jobno number
exec dbms_job.submit(JOB => :jobno, -
WHAT => 'analizar_bbdd;',-
NEXT_DATE => trunc(SYSDATE+1,'D'),-
INTERVAL => 'trunc(SYSDATE+1, ''DD'')+1/12');
-
Originally posted by pando
/* deleting stats first due to 8.1.7 bug */
dbms_stats.delete_schema_stats(OWNNAME => l_users(i));
whats the Bug ????
Originally posted by pando
/* analyzing and generating 10 buckets */
dbms_stats.gather_schema_stats(OWNNAME => l_users(i),
ESTIMATE_PERCENT => 20,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 10',
DEGREE => 1,
CASCADE => TRUE);
Is DBMS_UTILITY.ANALYZE_SCHEMA same as dbms_stats.gather_schema_stats ??
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|