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

The rename constraint command does not make an appearance until 9.2.

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com

Author of: 'So You Want to be an Oracle DBA?'

"Sergey Adamenko" <adamenko_at_i.com.ua> wrote in message news:agu55l$2lme$1_at_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 - 05:43:08 CDT