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 happening 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');
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
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.
What am I doing wrong? What's happening?