HomeОбразованиеRelated VideosMore From: Ram Gupta

What is Autonomous transaction in Oracle

227 ratings | 13891 views
What is Autonomous transaction in Oracle. This is very frequently asking question in Interview. Hello Friend, in this Video You can learn how we can give the answer of Autonomous Transactions. An autonomous transaction is available from Oracle 8i. It is a very cool, useful, unique and powerful feature in oracle. An autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database. Autonomous transactions can be nested. For use Autonomous Transactions feature in program we have to use PRAGMA AUTONOMOUS_TRANSACTION Key word in Program When to use Autonomous Transactions? • Logging mechanism you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries. • Commits and rollbacks in your database triggers If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code. • Software usage meter You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application. Mutation Error Using autonomous transaction we can avoid mutation error. IF you want to share with your friend this video you can copy below block of text with link and past your friend group. ----------------------------------------------------------------------------- What is autonomous transaction in Oracle This is a very frequently ask Question in Interview Please Share this video with your friends and Oracle groups and communities. https://www.youtube.com/watch?v=gyvFajpfoWE ----------------------------------------------------------------------------- For More video's you can use blow link. https://www.youtube.com/user/rameshwargupta1/videos Join With us our Facebook Group for PLSQL Interview Q/A https://www.facebook.com/groups/146487615764170/ Join our Blogs http://oracletemple.blogspot.in/ Watch More... What is Parameterized Cursor in Oracle https://www.youtube.com/watch?v=JMKwHlVi6-A What is autonomous transaction in Oracle. https://www.youtube.com/watch?v=gyvFajpfoWE What are Constraints available in Oracle. https://www.youtube.com/watch?v=WivhdLXQklQ What is Mutation Error and How to avoid mutation. https://www.youtube.com/watch?v=CbWNCyW18Bs 5 Effective way to delete Duplicate rows https://www.youtube.com/watch?v=1wtTtnc87Oc What is Difference between Delete and Truncate https://www.youtube.com/watch?v=7b6wQ3Qumgg
Html code for embedding videos on your blog
Text Comments (22)
Dharmendra Harinkhede (6 months ago)
Good one..
rohit kumar (7 months ago)
hi sir..why you left making videos...urs are class apart ...pls continue making such great videos
mihir kar (1 year ago)
As per required I tried to perform dml in a table for auditing who perform dml where a after dml trigger also exists in the same table as per rule i tried to handel with autonomous_transaction but still cant perform. please help . 1 CREATE OR REPLACE TRIGGER autoinsert 2 AFTER INSERT OR UPDATE 3 ON triggertest 4 FOR EACH ROW 5 declare 6 pragma autonomous_transaction; 7 BEGIN 8 IF INSERTING THEN 9 insert into triggertest (i_user,i_date) values(user,sysdate); 10 elsif UPDATING then 11 insert into triggertest(u_user,u_date) values(user,sysdate); 12 end if; 13 commit; 14* END; SQL> / Trigger created. SQL> insert into triggertest(empno,ename,sal) values(1234,'JAMES',890); insert into triggertest(empno,ename,sal) values(1234,'JAMES',890) * ERROR at line 1: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "SCOTT.AUTOINSERT", line 5 ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT' ORA-06512: at "SCOTT.AUTOINSERT", line 5 ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT' ORA-06512: at "SCOTT.AUTOINSERT", line 5 ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT' ORA-06512: at "SCOTT.AUTOINSERT", line 5
mihir kar (1 year ago)
No there is no primary key on User_id column.
Ram Gupta (1 year ago)
mihir kar Hi Mihir , is any pk apply on User id Column?
Krishna Kumar (1 year ago)
give updated interview ques in oracle...
Krishna Kumar (1 year ago)
very useful for us...
Arun Natarajan (2 years ago)
hi ram, thanks for your precise video. I have question for you. Am making DML operation and am committing it. so the transaction will be saved. After this, even if we roll back, the inserted data will be available . In your video, the SP contains commit, when you are invoking it from AB with rollback, obviously the record will be available. then what's the point in having pragma autonomous transaction ??
RAHUL HAMEED (2 years ago)
How to record screen with our voice explanation
good video
Ram Gupta (2 years ago)
Thanks for Feedback F islam+
Vivek (2 years ago)
Could you pls create video on Materalized view ?
Ram Gupta (2 years ago)
Sure Vivek , Soon I will try to Create Video on Materialized view for you. Keep in touch by subscribe me https://www.youtube.com/c/ramguptaoracle?sub_confirmation=1
Srinivas Reddy Nalla (2 years ago)
Good Explanation.
Ram Gupta (2 years ago)
Thanks for feedback.
siddhartha rao (2 years ago)
As usual Good Explanation..But i would like to ask you one doubt?
Arun ROYAL (1 year ago)
hey banks are using which type of transactions methods?
Ram Gupta (2 years ago)
Yes sure, you are always welcome
siddhartha rao (2 years ago)
Actually my doubt is not regarding this video..Just I would like to ask a simple query based question?
Vivek (2 years ago)
hi siddartha, What is your doubt could you please share ?
siddhartha rao (2 years ago)
Thank You..

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.