TMP$$_ prefix in ORACLE's dbms_redefinition.copy_table_dependents

Question

ORACLE's dbms_redefinition.copy_table_dependents copies indexes/triggers/constraints and renames them to

TMP$$_[original object name]

Is it possible to change the string "TMP$$_" , so that copy_table_dependents uses a different prefix? I know I can rename the objects afterwards, but I wonder whether one can change the prefix.

score 2 · Accepted Answer · answered Jun 14 '14 at 16:52

No, you can't change the object_name.

I don't think this matters much, as the TMP$$ objects are removed once you run DBMS_REDEFINITION.FINISH_REDEF_TABLE.

If you assume the following environment

create table test_redef_source ( id number, tstamp date, primary key (id) );

create or replace trigger tr_test_redef_sources
 before insert on test_redef_source
 for each row
begin
  :new.tstamp := sysdate;
end;
/

create table test_redef_int as
select * from test_redef_source;

declare
   l_errors number;
begin
   dbms_redefinition.start_redef_table (
          uname =>  user
        , orig_table => 'TEST_REDEF_SOURCE'
        , int_table => 'TEST_REDEF_INT'
           );
   dbms_redefinition.copy_table_dependents (
          uname => user
        , orig_table => 'TEST_REDEF_SOURCE'
        , int_table => 'TEST_REDEF_INT'
        , copy_indexes => 1
        , num_errors => l_errors
          );
end;
/

You can see by querying USER_DEPENDENCIES; that the TMP$$ objects are dependent on the interim table:

select name, type, referenced_name
  from user_dependencies
 where referenced_name like '%TEST_REDEF%'
       ;

NAME                           TYPE               REFERENCED_NAME
------------------------------ ------------------ --------------------
TMP$$_TR_TEST_REDEF_SOURCES0   TRIGGER            TEST_REDEF_INT
TR_TEST_REDEF_SOURCES          TRIGGER            TEST_REDEF_SOURCE

When you've completed the redefinition; these objects are removed:

begin
   dbms_redefinition.finish_redef_table (
          uname => user
        , orig_table => 'TEST_REDEF_SOURCE'
        , int_table => 'TEST_REDEF_INT'
          );
end;
/

PL/SQL procedure successfully completed.

select name, type, referenced_name
  from user_dependencies
 where referenced_name like '%TEST_REDEF%'
       ;

NAME                           TYPE               REFERENCED_NAME
------------------------------ ------------------ --------------------
TR_TEST_REDEF_SOURCES          TRIGGER            TEST_REDEF_SOURCE

As they're only temporary objects, and the names are guaranteed to be unique; I don't see any particular problem with maintaining the standard name.

The problem starts when the original object names are longer than 24 characters and you have "important" information in the last 6 characters. ORACLE will cut the end in order to put TMP$$_ in front, and when you complete the redefinition, the important information is gone. — lxxxvi, Jun 15 '14 at 08:54

TMP$$_ prefix in ORACLE's dbms_redefinition.copy_table_dependents

1 Answers1