Adding a column takes ages

From: Marc Eggenberger <marc.eggenberger_at__REMOVE_itc.alstom.com>
Date: Tue, 17 Aug 2004 14:22:05 +0200
Message-ID: <MPG.1b8c16d48d2d4d2c989682@iww.cacti.ch.alstom.com>

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
  )
/

-- 
mfg
Marc Eggenberger

Received on Tue Aug 17 2004 - 07:22:05 CDT