Re: Adding a column takes ages

Marc Eggenberger wrote:

> Hi there.
>
> I have the following environment
>
> Oracle 8.0.6.0 on Windows NT4 (yes I know, unsupported, migration to 9i
> is in progress, they are still fighting about costs ;) ....)
>
> I have a table edb_order_scope (ddl at the bottom of this posting) which
> has xxxx rows.
>
> The server is not that busy and I have some SQL (from an external
> partner) which should be executed. One is:
>
> ALTER TABLE SIMU.EDB_Order_Scope
> ADD (OHOIL_LT NUMBER DEFAULT 0 NULL);
>
> this one takes ages without completing. There is an index on this table.
> I already tried to drop the index and then recreating afterwards. That
> was 10minutes ago, its still on the alter table statement.
> What is going wrong here? How can I see whats going on?
>
> Enable trace for the session? Can this trace be read by someone not
> speaking oracle as native tongue? ;)
>
> Any other hints?
>
>
> CREATE TABLE simu.edb_order_scope
> (saveweekyear VARCHAR2(6) NOT NULL,
> order_id_edb VARCHAR2(10) NOT NULL,
> order_id VARCHAR2(10) NOT NULL,
> tbexwdate DATE,
> o1_date DATE,
> o1_remark VARCHAR2(1000),
> product_struc VARCHAR2(12),
> scopeok NUMBER(38,0),
> order_cost_edb NUMBER(11,2),
> product_typeclass VARCHAR2(12),
> order_addcost_edb NUMBER(11,2),
> ohtotal_actual NUMBER(6,0),
> starts_actual NUMBER(6,0),
> odc_outagedate DATE,
> ohtotal_start_actual_man NUMBER(3,0),
> eoh_total_actual_o NUMBER(6,0),
> thg1_actual NUMBER(6,0),
> thg2_actual NUMBER(6,0),
> spressure_tb2_actual NUMBER(7,2),
> lp_cooler_actual NUMBER(6,0),
> ohoil_actual NUMBER(6,0),
> ohgas_actual NUMBER(6,0),
> startsgas_actual NUMBER(6,0),
> startsoil_actual NUMBER(6,0),
> thg1_target NUMBER(6,0),
> thg2_target NUMBER(6,0),
> spressure_tb2_target NUMBER(7,2),
> lp_cooler_target NUMBER(6,0),
> ohoil_target NUMBER(6,0),
> ohgas_target NUMBER(6,0),
> startsgas_target NUMBER(6,0),
> startsoil_target NUMBER(6,0),
> ohgas_planned NUMBER(6,0),
> ohoil_planned NUMBER(6,0),
> startsgas_planned NUMBER(6,0),
> startsoil_planned NUMBER(6,0),
> resp_data_status VARCHAR2(20),
> commercial_date DATE,
> scope_gt_type VARCHAR2(12),
> mat_exwork_date DATE,
> mat_onsite_date DATE,
> edb_order_scope_lcd DATE,
> edb_order_scope_lcu VARCHAR2(32)
> ,
> PRIMARY KEY (saveweekyear, order_id_edb)
> USING INDEX
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> TABLESPACE simu
> STORAGE (
> INITIAL 204800
> NEXT 724992
> PCTINCREASE 10
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> ))
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> TABLESPACE simu
> STORAGE (
> INITIAL 204800
> NEXT 2291712
> PCTINCREASE 10
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> )
> /

Problem 1: A version of Oracle almost as old as you are.
Problem 2: Why PCTUSED 40 on the table?
Problem 3: Why are initial and next extents different sizes?
Problem 4: Why PCTINCREASE anything other than 0
Problem 5: Why PCTFREE 10 in a primary key? Plan on editing it?

When adding a column Oracle tries to lock the table. Is it perhaps already locked or constantly in use?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)