Cronjob for analyzing the tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Cronjob for analyzing the tables

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    73
    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?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    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.

  4. #4
    Join Date
    Apr 2002
    Posts
    73
    Thanks Alex.

    Can you show me an example how to use the dbms_job to analyze schema daily.



    Blu

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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');

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
  •  



Click Here to Expand Forum to Full Width