Shrinking Tables Once More

Commenting on my post from last week, “an oracle dba” said:

“”shrink” would down the HWM of object, but may not for datafile. and it would invalidate the dependent plsql/trigger definitely.”

I did not believe that! Surely something as innocent as shrinking has no reason to invalidate the triggers. Maybe in 10g, but 11g has Fine Grained Dependencies, surely merely shrinking can’t invalidate a trigger in 11g!

So I tried it:

SQL> create table test2 (ID number, c_date date);

Table created.

SQL> create trigger trig1
  2  after insert on test
  3  referencing new as newrow
  4  for each row
  5  begin
  6  insert into test2 values (:newrow.id,sysdate);
  7  end trig1;
  8  /

Trigger created.

SQL> select object_name,object_type,status from user_objects where object_name='TRIG1';

OBJECT_NAME         OBJECT_TYPE          STATUS
-------------       -------------------  -------
TRIG1               TRIGGER             VALID

SQL> delete from test where mod(id,5)=0;

1000 rows deleted.

SQL> alter table test enable row movement ;

Table altered.

SQL>  alter table test shrink space cascade;

Table altered.

SQL> alter table test disable row movement ;

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TRIG1';

OBJECT_NAME         OBJECT_TYPE          STATUS
-------------       -------------------  -------
TRIG1                TRIGGER             INVALID

Learn something new every day 🙂
Thanks “an oracle dba”!

Advertisements

3 Comments on “Shrinking Tables Once More”

  1. Gregory says:

    That’s the enable/disable row movement that invalidates the trigger; The “alter table … shrink space cascade;” doesn’t invalidate the trigger.

  2. joel garry says:

    Yes, it is mentioned in Note: 787011.1almost as an afterthought. I guess people were unsure if enabling row movement is a change in table structure 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s