Re: DDL & long-running query

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 12 May 2005 07:00:36 -0700
Message-ID: <1115906436.270600.317010@f14g2000cwb.googlegroups.com>

tim.kears..._at_milton-keynes.gov.uk wrote:
> And have you actually tried running this test? You'll find the DROP
> COLUMN on a table with 100,000,000 rows takes a *very* long time to
> complete because of the undo it generates.
>
> All the testing I've done this morning indicates that an ALTER TABLE
> does NOT wait for a query to complete.
>
> Tim Kearsley
> HBS Milton Keynes

I'll agree with you based on this test that does not require million rows.

Session 1:
SQL> create table foo (t varchar2(1));

Table created.

SQL> insert into foo values ('A');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function foo_func return number   2 is
  3 begin

  4     for i in 1..10 loop
  5        dbms_lock.sleep(1);
  6     end loop;
  7     return 0;

  8 end foo_func;
  9 /

Function created.

SQL> select * from foo where foo_func = 0;

T
-
A

While above runs for 10 seconds. Go to session 2

Session 2:
SQL> alter table foo modify (t varchar2(2));

Table altered.

Now back to session 1. Query still running... Query done!

SQL> desc foo

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 T                                                  VARCHAR2(2)

However, Alter table *will* fail for select for update query.

Regards
/Rauf Received on Thu May 12 2005 - 09:00:36 CDT