Don't alter table in big data table

It cost a lot of time

ALTER TABLE PAPA_DC  ADD (CDI NUMBER DEFAULT 0 )

from https://stackoverflow.com/questions/2731894/what-happens-if-you-kill-a-long-running-alter-query

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.






from http://www.atmarkit.co.jp/ait/articles/0407/28/news105_3.html

ロックをかけているセッションID、ユーザー名、プログラム名、ロックしている時間を取得

SQL> SELECT a.SID sid,
             a.USERNAME username,
             a.SERIAL# serialno,
             b.TYPE type, 
            a.PROGRAM program,
             TO_CHAR(b.CTIME/60,'999990.9') lock_time,
             c.SQL_TEXT SQL
      FROM V$SESSION a,
           V$LOCK b,
           V$SQLAREA c
      WHERE a.SID = b.SID
        AND b.TYPE IN ('TX','TM')
        AND a.SQL_ADDRESS = c. ADDRESS;

SQL2
ロックのため待ちが発生しているセッションID、ユーザー名、プログラム名、待たされている時間

SQL> SELECT a.USERNAME username,
             a.PROGRAM program,
             a.SERIAL# serialno,
             a.SID sid,
             b.TYPE type,
             TO_CHAR(b.CTIME/60,'999990.9') lock_time
      FROM V$SESSION a,
           V$LOCK b
      WHERE a.SID = b.SID
        AND b.TYPE = 'TM'
        AND b.SID = (SELECT SID FROM V$LOCK c WHERE c.TYPE = 'TX' AND c.REQUEST > 0);