Monthly Archive for April, 2009

Tabelle vergleichen

Published on 2009-04-15 in Oracle. 0 Comments

Mit folgendem SQL kann man die Definition zweier Tabellen in der Datenbank vergleichen:

 select * from
 (SELECT  a.column_name, a.data_type,
        a.data_length
   FROM sys.dba_tab_columns a
   where a.owner = 'SCHEMA_1'
     and a.table_name = 'TABELLE_1') a,
 (SELECT  a.column_name, a.data_type,
        a.data_length
   FROM sys.dba_tab_columns a
   where a.owner = 'SCHEMA_2'
     and a.table_name = 'TABELLE_2') b
 where a.column_name = b.column_name (+)
   and (a.data_type <> nvl(b.data_type,'$$$')
        or a.data_length <> nvl(b.data_length,0))
 /

Recompile Invalid Objects Script

Published on 2009-04-15 in Oracle. 0 Comments

Mit folgendem SQL alle invaliden PL/SQL-Objekte in der Datenbank gesucht und es wird versucht, sie neu zu compilieren:

 BEGIN
   FOR cur_rec IN (SELECT owner,
                          object_name,
                          object_type,
                          DECODE(object_type, 'PACKAGE', 1,
                                              'PACKAGE BODY', 2, 2) AS sort
                   FROM   dba_objects
                   WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE',
                                          'TRIGGER', 'VIEW')
                   AND    status != 'VALID'
                   ORDER BY 4)
   LOOP
     BEGIN
       IF cur_rec.object_type != 'PACKAGE BODY' THEN
         EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
             ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
         DBMS_OUTPUT.put_line('Compiled: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
       ElSE
         EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
             '"."' || cur_rec.object_name || '" COMPILE BODY';
         DBMS_OUTPUT.put_line('Compiled: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
       END IF;
     EXCEPTION
       WHEN OTHERS THEN
         DBMS_OUTPUT.put_line('ERROR: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
     END;
   END LOOP;
 END;
 /

Oracle Procedures (SQL)

Published on 2009-04-15 in Oracle. 0 Comments

Mit folgendem SQL kann man den Quelltext eines PL/SQL-Objekts in der Datenbank selektieren:

 select
   substr(text,1,length(text)-1)
 from dba_source
 where
   owner = 'SCHEMA'
   and name = 'OBJECT_NAME'
   and TYPE = 'TYPE' --z.B. PROCDURE
 order by line
 /

Column Name Prefix

Published on 2009-04-15 in Oracle. 0 Comments

Mit diesem SQL-Statement kann man sich in Oracle Datenbanken alle Column Name Prefixe (3-Stellig) anzeigen, die mit einem “_” abgetrennt sind. Ganz nützlich zum eindeutig halten.

 select distinct substr(column_name,1,instr(column_name,'_')-1)
 from dba_tab_columns
 where owner in('SCHEMA1', 'SCHEMA2')
 and length(substr(column_name,1,instr(column_name,'_')-1))=3