Renaming system-genarated constraint names to human-readable via SQL or PL/SQL

From: Sergey Adamenko <adamenko_at_i.com.ua>
Date: Mon, 15 Jul 2002 12:37:48 +0300
Message-ID: <agu55l$2lme$1@news.dg.net.ua>

Hi, Everyone,

Delevoping a database structure I used to give human-readable names to indexes or constraints I create. Recently I stated with project whose database is a copy of old one, so I decided to remane all indexes and constraints in the way I like.
I'm using Oracle8i EE 8.1.7.0.0 and for indexes I wrote a script which genarates
ALTER INDEX <OLD_INDEX_NAME> RENAME TO <NEW_INDEX_NAME> commands.This script is at the end of this message. For renaming constraint name there is nothing in Oracle8i SQL Reference Release 3 (8.1.7). Recreating constraints or exporting schema, genarating schema script and editing with hands is a solution of other kind: But may be there exist some undocumented feature or way to do this?

Thanks,
Sergey Adamenko

---

set linesize 255;
set newpage 0
set space 0
set pages 0
set echo off
set feed off
set head off
set autocommit on
set termout off

spool ~rename_system.sql
set serveroutput on size 500000;

declare
  index_name varchar2(4000);
  index_length pls_integer;
begin
  for rec in

     (select index_name, table_name from user_indexes where index_name not like
'IDX\_%' escape '\'

      and Index_type <> 'LOB'
      order by index_name)
  loop
       dbms_output.put_line('-- ************************************');
       dbms_output.put_line('-- Index to change: ' ||rec.index_name);
       dbms_output.put_line('-- Indexed table: ' ||rec.table_name);
       index_name := null;
       for col_rec in
          (select  column_name
           from user_ind_columns
           where index_name = rec.index_name order by column_position)
       loop
             dbms_output.put_line('-- Indexed column: ' ||col_rec.column_name);
             index_name :=index_name||'_'||col_rec.column_name;
       end loop;
       if index_name is not null then
           index_name :='idx_'||rec.table_name ||index_name;
           index_length := length(index_name);
           if index_length>30 then
               dbms_output.put_line('-- !!! New index name is too long: '
||index_name);
               dbms_output.put_line('-- Cutting off column names');
               index_name := null;
               for col_rec in
                 (select  substr(column_name,1,4) column_name
                    from user_ind_columns
                    where index_name = rec.index_name order by column_position)
               loop
                   index_name :=index_name||'_'||col_rec.column_name;
              end loop;
              index_name :='idx_'||rec.table_name ||index_name;
              index_length := length(index_name);
              if index_length>30 then
                  index_name :='idx_'||rec.table_name;
              end if;
           end if;
           index_name :='alter index '||rec.index_name||' rename to


'||index_name||';';

           dbms_output.put_line(index_name);
       end if;

  end loop;
end;
/

spool off

set echo on
set termout on
set autocommit on

@~rename_system.sql Received on Mon Jul 15 2002 - 04:37:48 CDT