Does a DDL in Oracle affect other transactions

ddloracleoracle-11g-r2

I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction.

However, does a DDL statement in any way affect other transactions running against that table (assuming the change is compatible with the DML running against the table)?

For example, if I decide to modify a varchar2(10) columnn to varchar2(20), or add a new column altogether, or drop an obsolete, no longer used column — are other transactions going to be happy about it, or is there going to be any sort of contention or interruption of running transactions?

Best Answer

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize:

If the table cannot be locked due to another transaction having a lock on the table

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

would be thrown. If the DDL manages to acquire the lock, obviously, any other transaction will block at the point where it needs a lock on the table, until the DDL finishes.

Additionally, since 11g, it is possible to enqueue DDL statements in FIFO order with other statements to get a lock on a table:

declare
begin
      -- We really want to drop that column and so wait for 1800 seconds.
      execute immediate 'alter session set ddl_lock_timeout=1800';
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

In older versions of Oracle, it could be a challenge to perform this task on a heavily used table.

Related Solutions

Sql-server – How to use transactions with SQL Server DDL

Yes. Transactions apply to DDL and span batches.

I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error.

SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin Transaction
GO
exec sp_rename LogTable, LogTableOld;
GO
CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go
EXEC sp_recompile LogTable;
go

Commit;

Undo DDL statement on View in Oracle

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago. You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.

Best Answer

Related Solutions

Sql-server – How to use transactions with SQL Server DDL

Undo DDL statement on View in Oracle

Related Question