Re: Rebuild table

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 27 Jun 2011 10:37:04 +0200

triggers should be manually created on the new table,
if necessary.

And another quite important detail: FK constraints pointing
to the table to be reorganized using CTAS and rename need
to be recreated, because after the rename they will point to
the old table.


On Fri, Jun 24, 2011 at 7:03 AM, Radoulov, Dimitre <cichomitiko@xxxxxxxxx <mailto:cichomitiko@xxxxxxxxx>> wrote:

    On 23/06/2011 23:39, Wayne Smith wrote:

        Am I missing something?

    Public and private synonyms?

    If you're using CTAS + rename you don't need to worry about them
    (unless I'm missing something):

    SQL> create table t as select * from dual;

    Table created.

    SQL> create public synonym t_pub for t;

    Synonym created.

    SQL> create synonym t_priv for t;

    Synonym created.

    SQL> create table tt as select * from t;

    Table created.

    SQL> drop table t;

    Table dropped.

    SQL> rename tt to t;

    Table renamed.

    SQL> select * from t_pub;


    SQL> select * from t_priv;


    The status of those synonyms changes after the drop
    but they become valid again the first time they are referenced
    after the rename.


Other related posts: