Bonjour,

Je viens de mettre au point un script SQL ci-dessous mais il m'affiche une erreur d'entrée de jeu.

Je passes en paramètre un schéma Oracle.

Merci d'avance si vous avez une idée !


Message d'erreur
Connecte a :
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ancien 6 : SELECT UPPER ('&1') INTO: schema_bdd FROM dual;
nouveau 6 : SELECT UPPER ('MONSCHEMA') INTO: schema_bdd FROM dual;
SP2-0552: Variable attachee "SCHEMA_BDD" non declaree.
Deconnecte de Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
  DECLARE
    schema_bdd VARCHAR2(25);
 
BEGIN
    SELECT UPPER ('&1') INTO: schema_bdd FROM dual;
 
    FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND (table_name LIKE 'BATCH_%' OR table_name LIKE 'TMP_%'))
      LOOP
        dbms_stats.delete_table_stats(schema_bdd,cur.table_name);
        dbms_stats.lock_table_stats(schema_bdd,cur.table_name);
      END LOOP;
 
    FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND (NOT table_name LIKE 'BATCH_%' OR table_name LIKE 'TMP_%'))
      LOOP
        dbms_stats.unlock_table_stats(schema_bdd,cur.table_name);
      END LOOP;
 
    FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND table_name LIKE 'BATCH_%')
      LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_bdd || '.' || cur.table_name || 'NOLOGGING';
      END LOOP;
 
    FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd)
      LOOP
 
        dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'METHOD_OPT',pvalue=> 'FOR ALL COLUMNS SIZE SKEWONLY');
 
        dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'ESTIMATE_PERCENT',pvalue=> 'DBMS_STATS.AUTO_SAMPLE_SIZE');
 
        dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'NO_INVALIDATE',pvalue => 'FALSE');
 
        dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'CASCADE',pvalue=> 'TRUE');
      END LOOP;
 
    FOR cur IN (SELECT sequence_name FROM dba_sequences WHERE sequence_owner=schema_bdd and cache_size !='20')
      LOOP
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || schema_bdd || '.' || cur.sequence_name || ' cache 20';
      END LOOP;
 
    FOR cur IN (SELECT object_name, object_type, owner FROM sys.all_objects WHERE status = 'INVALID' )
      LOOP
        BEGIN
          IF cur.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE 'ALTER ' || cur.object_type || ' "' || schema_bdd || '"."' || cur.object_name || '" COMPILE BODY';
          ELSE
            EXECUTE IMMEDIATE 'ALTER ' || cur.object_type || ' "' || schema_bdd || '"."' || cur.object_name || '" COMPILE';
          END IF;
        EXCEPTION
          WHEN OTHERS THEN NULL;
        END;
      END LOOP;
 
  END;
  /
  EXIT;