add new column takes forefer

[Eberman]

Here is problem. I am in process of adding new field to table

ALTER TABLE CUSTOMER ADD
(
CUST_FLAG NUMBER)
/
Normally to add new field to this table takes couple of seconds . In this case command never finishes. I had to kill session for couple times since it locks table and other application and users can not do updates this table.

How long whould I wait. NO erros found in log file.
Does number of existing fields matter? This table have alredy 170 fields.
Please Help.

[marist89]

Originally posted by Eberman
Here is problem. I am in process of adding new field to table

ALTER TABLE CUSTOMER ADD
(
CUST_FLAG NUMBER)
/
Normally to add new field to this table takes couple of seconds . In this case command never finishes. I had to kill session for couple times since it locks table and other application and users can not do updates this table.

How long whould I wait. NO erros found in log file.

What's it waiting on? Run a level 8 or 12 10046 trace on the session and look at the trace file.

Does number of existing fields matter?

probably not.

[tamilselvan]

Use DBMS_REDEFINITION pkg if you want to add a column online.

Tamil

[Eberman]

Could you let me know how to do it?

Thanks

[tamilselvan]

The next demo adds a col SAL in the table T1.

SQL> show user
USER is "SYS"
SQL> grant execute on dbms_redefinition to tamil ;

Grant succeeded.

SQL> grant dba to tamil ;

Grant succeeded.

SQL> conn tamil/xxxxxxxx
Connected.


SQL> create table t1 (id number primary key, name varchar2(30));

Table created.

--- create an interium table that reflect final
--- 
SQL> create table int_t1 (id number primary key, name varchar2(30), sal number);

Table created.

SQL> exec dbms_redefinition.can_redef_table('tamil','t1');

PL/SQL procedure successfully completed.


SQL>  execute SYS.DBMS_REDEFINITION.START_REDEF_TABLE('tamil','t1','int_t1');

PL/SQL procedure successfully completed.

SQL> insert into t1 values (10,'Tamil');

1 row created.

SQL> c/10/20
  1* insert into t1 values (20,'Tamil')
SQL> /

1 row created.

SQL> commit;

Commit complete.


SQL> execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE('tamil','t1','int_t1');

PL/SQL procedure successfully completed.

SQL> execute DBMS_REDEFINITION.FINISH_REDEF_TABLE('tamil','t1','int_t1');

PL/SQL procedure successfully completed.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(30)
 SAL                                                NUMBER

SQL> select * from t1;

        ID NAME                                  SAL
---------- ------------------------------ ----------
        10 Tamil
        20 Tamil

SQL> drop table int_t1 ;

Table dropped. 

Tamil

[stmontgo]

Could there be a lock on the table as you are trying to add the column? Check dba_blockers and dba_ddl_locks

[davey23uk]

and how is it different than adding a column, seems like a lot more commands than a simple one liner - what does this give you?

[Eberman]

I bounced the instance and all came back to normal.

Thanks everyone for help.