SESSION 1DELETE FROM EMP; SESSION 2 SELECT * FROM EMP;What will be the result?

  • Interview Candidate
  • 17
  • 4372

Showing Answers 1 - 17 of 17 Answers

kiran

  • Feb 8th, 2006
 

no rows found

  Was this answer useful?  Yes

l_p_nedunuri

  • Feb 13th, 2006
 

Hi

When we execute Query "DELETE FROM EMP"  -  is going to delete all the rows(DATA). But Structure Remains Same(Structure will not be effacted).

when we execute Query "SELECT * FROM EMP"    -is going to Show the structure.or "  0 row(s) affected ".

Thank you.

  Was this answer useful?  Yes

dhiraj

  • Feb 27th, 2006
 

in 1st session

if we fire delete query and in 2nd session if we fire select query then its going to show all the records bcoz after 1st we havent fired commit statement may be i m wrong

  Was this answer useful?  Yes

Mainak Aich

  • Mar 1st, 2006
 

If we delete the contents of a table and log out then there is an autocommit performed.So although we are not performing commit.A commit is performed automatically by oracle server.So in the next session if we try to select the datas of that table we won't be able to see anything.Thank you.

  Was this answer useful?  Yes

kamal

  • Mar 3rd, 2006
 

Delete is a DML statement so there is no auto commit for delete.

TO answer the question.

Session 1

DELETE FROM EMP;

session 2

SELECT * FROM EMP;

No rows found.

Session 1

Rollback;

Session 2

SELECT * FROM EMP;

12 rows selected.

dwhprofessional

  • Mar 25th, 2006
 

WHEN WE SAY "DELETE FROM EMP; (say)" ALL ROWS WILL BE DELETED AND THEN IF WE SAY "SELECT * FROM EMP " THEN WE GET NO ROWS SELECTED AS THE TABLE DOESN'T CONTAIN ANY DATA.

AFTER ENTERING THE DELETE COMMAND IF WE SAY "ROLLBACK " THEN ROLLBACK IS DONE AND NOW IF WE SAY "SELECT * FROM EMP" NOW ALL ROWS WILL BE DISPLAYED . HOPE U GOT THE ANSWER .

ANYWAY PLZ USERS IF U ARE CONFIDENT OF ANSWERS THEN ONLY POST THE SAME AS "LITTLE KNOWLEDGE IS DANGEROUS AS UR JEOPARDING THE LIFE OF THE PEOPLE WHO ARE NEW TO ORACLE OR U HAVE CHANGED THEIR BACKGROUND TO IT" PLZ CONFIRM IT OR IF UR SURE THEN POST UR ANSWERS.

THANK YOU  

  Was this answer useful?  Yes

Nanditha

  • Mar 28th, 2006
 

HI dwh Professional,

    In the question...they have given 2 Statements.Thtz it, then din't asked any if statements or allowed to use any other Statement.

They simply asked the result...So this result of those two Queries is "We are not going to find any data Except the Sturcture".,

Don't comment on others View.

Thank you,

-Nanditha

  Was this answer useful?  Yes

nileshsingh

  • May 17th, 2006
 

Delete is a DML statement.If all the Data in a table is deleted in 1 session ,it will not be refelected in Session 2.if it is to be refeleted on 2nd Session,we have to COMMIT it 1st in the 1st Session.

  Was this answer useful?  Yes

Abhishek

  • Jul 6th, 2006
 

Well here we are talking about two different sessions :-If one Session1 deletes all the rows of any table and dosen't fire commit then on the other Session2 user will see all the rows. This is the right answer...

Mahantesh

  • Jul 18th, 2007
 

Delete is not a autocommit. If we delete and select in other session it will select all rows because we have not given commit explicitly.

  Was this answer useful?  Yes

shiiva

  • Sep 9th, 2008
 

Yes, I agree with abhishek. He's right. If a same user executes --'DELETE FROM EMP' , and then loggs off, then again logs in and exicutes -- 'SELECT * FROM EMP' , he cannot see any records.

  Was this answer useful?  Yes

mfasiur

  • Oct 13th, 2009
 

Yes,Abhishek is right.
I already opened 2 sessions & did this exercise.
In the 1st window,i issued delete from emp & went to 2nd window.
I was able to see all the rows in the 2nd session.
THis is because user dint commit in the 1st session & so changes are not being reflected.
I tried to drop table emp from 2nd session but i got error as ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
So i needed to kill that session by using v$session & v$process.
So the final conclusion is you will be not be able to see the table changes in different sessions until the user commits in either of them.

So please verify before writing your answers on the forum.
People might be confused at the end especially freshers like me :-)

Regards,
Fasi

  Was this answer useful?  Yes

giriraj gupta

  • Apr 9th, 2012
 

Only data will be deleted for first session and for second session data will be available because there is no commit in first session.

Hany

  • Jun 7th, 2012
 

With No Commit after delete , you will be able to see all record in session 2

  Was this answer useful?  Yes

mfasiur

  • Jun 7th, 2012
 

Also, the undo blocks will store all the data until the transaction is either committed or rolled back.So, if someone issues DML statements(insert,update & delete)The before image of the transaction is stored in undo blocks so that other users can read that data.This is done to maintain read consistency between multiple transactions.There is a saying in oracle world."Readers do not wait for writers & writers do not wait for readers"

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions