Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, i'm really confused about what's happen exactly.

ALTER TABLE MYTABLE1 ADD MYEXPORT9 NUMBER(38,10) DEFAULT 0;

This simple query doesn't finish yet and using the following query to see if the query is blocked or blocking i found it. Query:

SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
s.logon_time,
p.spid,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s, v$process p
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND p.addr=s.paddr
AND l.TYPE IN ('TM','TX');

screenshot query's output

And looking for in the v$session table i see the following line:

29  ANC oracle  46465270    clay925p6       sqlplus@myserveur (TNS V1-V3) SQL*Plus db file sequential read  file#   5   0000000000000005    block#  1701088 000000000019F4E0    blocks  1   0000000000000001    1740759767  8   User I/O    0   0   WAITING

And it's so annoying to see the word WAITING at the end in the column named STATE.

The other option, add a column operation takes so much time, could be possible according to the number of rows of the tables, there are more than 7 million of entries. But from now the query takes more than 3 hours.

Any idea of what i'm doing wrong or what's happening ? Thanks in advance.

share|improve this question
    
Can you try and a create a new table (table b) which will be empty and contain the new column. And then can you do the inserts with selects from the old table (table a) into the table b? Then delete the the table a. And then rename the table b to table a again. And please before you try anything, create a backup just to be safe. P.S I'm not a database administrator, I described what would I do if that happened to me. I'm also curious about the possible solutions –  ant Feb 20 at 16:49
add comment

1 Answer 1

The table will need to be physically rewritten on disc to include the extra column. For this to happen the table has to be exclusively locked while this is happening. If the applications running on this database are not shutdown then you may be waiting sometime before this exclusive lock on the table can be given.

Given you mentioned that this table has several million rows the alter table statement is going to take quite some time (this also depends how much data is stored in each record. You will in all likelyhood need to stop all access to this table during this time (well it will be locked anyway).

Sorry if the answer is a little vague you did not really give many details about the size of the table and how much data is currently stored there.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.