CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gddl
user1008697
user1008697

Reputation: 1151

Oracle - Transaction with a series of DDL statements

I have a series of rename table ddl statements that I would like to run within a transaction. During this period, there will also be other sessions that will be running as well which might hijack the tables used for the rename above and cause a resource contention/deadlock.

Is it possible to achieve that in Oracle? Understand that each ddl statements will commit after each execution which will free up the tables for other sessions to hijack. How can I ensure that the current session that is executing the DDL statments complete successfully before other sessions can access the tables?

--LOCK TABLE a  
RENAME tbl a to b
--possible contention as commit release the lock on tbl a 

RENAME tbl b to c 

RENAME tbl c to d

--commit

Upvotes: 0

Views: 298

Answers (1)

Guido Leenders
Guido Leenders

Reputation: 4262

DDL statements in Oracle are each a transaction. Each DDL statement causes few or many changes in the data dictionary, like obj$. I am not sure, but looking at the major work Oracle has gone through to ensure that locking is not an issue with even the early versions of their platform, I think they found it easier to commit per DDL statement to keep the locks short in time and avoiding dead locks within a session or between sessions doing DDL. Under some circumstances, you can still feel that the Oracle kernel doesn't lock dropping and creating to many objects during production use with ORA-600 thrown at your head.

As a workaround, you can either use the datamodel versioning introduced a few years ago. I have no working experience with it since it is too restricted for my work, but you can find more on it by googling on 'Edition-based redefinition' or going to Oracle manual. It might not be available in the licensed edition of Oracle you are working on.

As a workaround, you can execute the statements during uptime. But this will generally break sessions unless the code your users are executing automatically recovers easily. Remember that each object has an ID and a name. Changing the name might not change the ID, so many pointers to the object will need to be refreshed, leading to ORA-4063 or alike. Oracle has no pause/suspend for sessions as far as I know.

Upvotes: 2

Related Questions