Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DDL & long-running query

Re: DDL & long-running query

From: <tim.kearsley_at_milton-keynes.gov.uk>
Date: 12 May 2005 07:25:47 -0700
Message-ID: <1115907947.445155.96430@f14g2000cwb.googlegroups.com>


Rauf Sarwar wrote:
> 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

Absolutely right Rauf, because the SELECT FOR UPDATE will acquire a row share lock on the table and a DDL statement will have to wait until this is released before it can acquire its own locks.

You really have to test these scenarios, as you and I have done in this case, rather than make statements which are simply incorrect, as Daniel did.

Tim Kearsley
HBS Milton Keynes Received on Thu May 12 2005 - 09:25:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US