Tabelle vergleichen

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

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)

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

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

Reset Oracle Sequence to a lower value.

Deutsche Version

Setting a higher value for an already created and used sequence is simple: create loop which calls the sequences NEXTVAL method in a cursor until you reach the needed value. By default strategies it’s not that easy resetting a sequences current value to a lower value.

Of course you can drop and recreate a sequence but this will lead you to some problems like procedures become invalid and priviledge grants can be easily lost. For this reason I thought about resetting a sequences value through altering.

The overall procedure is quite simple:

1. Store all current properties of the sequence

2. Alter the sequence to cycle with the maximum value being the current and the minimum value being the desired, new value.

3. Let the sequnce cycle ;) In fact, fetch the sequnces NEXTVAL.

4. Reset the sequences properties to the original ones stored in step 1

All these steps are covered in this PL/SQL procedure:

 Procedure reset_sequence
    (p_schema varchar2,
     p_seqname varchar2,
     p_newvalue number) IS
  cid INTEGER;
  sequence_reset_exception EXCEPTION;
  cursor c_wert is
    SELECT
     SEQUENCE_OWNER,
     SEQUENCE_NAME,
     MIN_VALUE,
     MAX_VALUE,
     INCREMENT_BY,
     CYCLE_FLAG,
     ORDER_FLAG,
     CACHE_SIZE,
     LAST_NUMBER
  FROM SYS.ALL_SEQUENCES
  WHERE sequence_owner = upper(p_schema)
    and sequence_name = upper(p_seqname);
  r_wert c_wert%rowtype;
  v_wert number;
  v_cycle varchar2(100);
 BEGIN
  open c_wert;
  fetch c_wert into r_wert;
  close c_wert;
  --Aktuellen Wert der Sequence laden
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
  dbms_sql.define_column(cid, 1, v_wert);
  if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.column_value(cid, 1, v_wert);
  dbms_sql.close_cursor(cid);
  -- Sequence in einen Status setzen, indem Sie bearbeitet werden kann
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||' INCREMENT BY 1 MINVALUE '||to_char(p_newvalue - 1)||
    ' MAXVALUE '||v_wert||' CYCLE ORDER NOCACHE', dbms_sql.v7);
  if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
  --Sequence einen "hochzahlen", bewirkt neuanfang
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
  if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
  -- Sequence in alten Status setzen
  if p_newvalue -1 < r_wert.min_value then
    r_wert.min_value := p_newvalue -1 ;
  end if;
  if r_wert.cycle_flag = 'Y' then
    v_cycle := ' CYCLE ';
  else
    v_cycle := ' NOCYCLE ';
  end if;
  if r_wert.order_flag = 'Y' then
    v_cycle := v_cycle||' ORDER ';
  else
    v_cycle := v_cycle||' NOORDER ';
  end if;
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||
                        ' INCREMENT BY '||TO_CHAR(r_wert.INCREMENT_BY) ||
                        ' MINVALUE '||to_char(r_wert.min_value)||
                        ' MAXVALUE '||to_char(r_wert.max_value)||
                        v_cycle || ' CACHE '||to_char(r_wert.cache_size), dbms_sql.v7);
  if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
 EXCEPTION WHEN OTHERS THEN
   dbms_sql.close_cursor(cid);
   raise;
 END;

For sure, the procedure is not state of the art, but it works… As you see, it uses the DBMS_SQL-package with v7 compatibility for the dynamic ALTER calls. Though it also works on old systems. Namely I created the procedure on an Oracle 7.3.4 instance.

Feel free to use it but I won’t take any responsibility for corruption.

Some limitations:

- the procedure doesn’t care about ORACLE-privs, so be careful with sharing an execute-priv.

- I’m not sure if it reconstructs all properties correctly, so it’s up to you to check.

- It’s not yet possible, to set a sequence to a higher value.