Waseen, Dr. Lui is using the given query as a complete transaction and there is data present before as we are using the database table from chap04.sql. And we perfrom the given discussion to the that file data table. I hope this helps.
Thank you.
BIkash
------------------------------------------------------------ Message no. 6574[Branch from no. 6544] Posted by Antoinette Lockett (arlockett) on Monday, February 20, 2006 2:32am Subject: Re: confusion regarding question Wasseem, I also agree that Dr. Liu is using the query as a comple transaction. ------------------------------------------------------------ Message no. 6592[Branch from no. 6574] Posted by Abhash Bhandary (abhandary) on Monday, February 20, 2006 10:24pm Subject: Re: confusion regarding question Waseem Run the chap04.sql provided by Dr. Liu and it should help Abhash ------------------------------------------------------------ Message no. 6604[Branch from no. 6544] Posted by Kelli Lemon (kmlemon) on Tuesday, February 21, 2006 10:00pm Subject: Re: confusion regarding question As everyone else has mentioned, you must run the chap04 script and as Ash mentioned, it would be a good idea to use the Select command to check the tables and see if any changes were made. ------------------------------------------------------------ Message no. 6607 Posted by Gwendolyn Mobley (gdmobley) on Tuesday, February 21, 2006 11:53pm Subject: SQL article from Oracle magazine (jan/feb 2006) includes attachment I ran across this article in Oracle's magazine...it may be of interest. The attached article is in .PDF format or use the url http://www.oracle.com/technology/oramag/oracle/06-jan/o16sql.html Gwen ------------------------------------------------------------ Message no. 6609[Branch from no. 6604] Posted by Harold Harris (hjharris2) on Wednesday, February 22, 2006 5:52am Subject: Re: confusion regarding question I've also found that running the command "select constraint_name, table_name from user_constaints;" to be helpful. HH ------------------------------------------------------------ Message no. 6612[Branch from no. 6607] Posted by Waseem Manzoor (wmanzoor) on Wednesday, February 22, 2006 12:53pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Thank you Gwen for the file and link. It is helpful source. Waseem Manzoor ------------------------------------------------------------ Message no. 6617[Branch from no. 6607] Posted by Venkat Munagala (vrmunagala) on Wednesday, February 22, 2006 1:36pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) The link was really interesting and informative Gwen. ------------------------------------------------------------ Message no. 6618[Branch from no. 6607] Posted by Sagun Piya (srpiya2) on Wednesday, February 22, 2006 1:36pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Hey Rhonda, Thanks for informing us about that article ------------------------------------------------------------ Message no. 6619[Branch from no. 6618] Posted by Sagun Piya (srpiya2) on Wednesday, February 22, 2006 1:37pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Hey Gwen thanks for informing about that article ------------------------------------------------------------ Message no. 6620[Branch from no. 6618] Posted by Rhonda Nichols (renichols2) on Wednesday, February 22, 2006 2:27pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) It wasn't me...we have Gwen to thank! -Rhonda ------------------------------------------------------------ Message no. 6621[Branch from no. 6607] Posted by Gnaneshwar Bukka (gbukka) on Wednesday, February 22, 2006 2:58pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) good stuff gwen. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 6623[Branch from no. 6621] Posted by Muhammad Latif (mlatif) on Wednesday, February 22, 2006 3:49pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Hey Gwen: Thanks for sharing! The scripts, notes and chapters provided by Dr. Liu are very informative and useful as well. I am learning a lot from this class. Arsal ------------------------------------------------------------ Message no. 6624[Branch from no. 6607] Posted by Bikash Adhikari (badhikari) on Wednesday, February 22, 2006 4:18pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Seems like some of the tools that have been mentioned in the article are very useful. Thank you Gwen. Bikash ------------------------------------------------------------ Message no. 6626[Branch from no. 6624] Posted by Abhash Bhandary (abhandary) on Wednesday, February 22, 2006 9:05pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Thankyou Gwen for sharing this useful article with all of us. Abhash ------------------------------------------------------------ Message no. 6627[Branch from no. 6552] Posted by Alex Auffenorde (aaauffenorde) on Wednesday, February 22, 2006 10:45pm Subject: Re: confusion regarding question Thanks for the clarification of Waseem's question Sagun. ------------------------------------------------------------ Message no. 6632 Posted by Rhonda Nichols (renichols2) on Thursday, February 23, 2006 10:39am Subject: Trick Question Can a select statement create a lock? Setup: As a dba, you want to set up read only access to your developers on the production database. So you set up a role with select on every application table and grant it to the developers. A couple weeks later a developer locks up a key table in production and holds up a batch operation. So how did it happen? -Rhonda ------------------------------------------------------------ Message no. 6637[Branch from no. 6632] Posted by Muhammad Latif (mlatif) on Thursday, February 23, 2006 4:34pm Subject: Re: Trick Question Hey Rhonda: From what I have read, if a developer issues a select statement which takes a long time to retrieve data, it would temporarily lock the table for alter or update. Secondly if a developer issues a select statement with FOR UPDATE clause, this also locks the table from any kind of alteration. Arsal ------------------------------------------------------------ Message no. 6638[Branch from no. 6637] Posted by Krishnamurth Ashwini (kashwini) on Thursday, February 23, 2006 5:26pm Subject: Re: Trick Question Arsal I don't think there is permission to issue a 'FOR UPDATE' command with the SELECT statement, when the users have 'read-only' access. With read-only access, users will only have the privilege of searching/viewing the database. They will not be able to edit , import or change the content in tables. I assume, an update command in the such a mode may generate error. Ash ------------------------------------------------------------ Message no. 6639[Branch from no. 6638] Posted by Alex Auffenorde (aaauffenorde) on Thursday, February 23, 2006 5:33pm Subject: Re: Trick Question I would agree with you Ash ------------------------------------------------------------ Message no. 6641[Branch from no. 6607] Posted by Krupa Kandavalli (kkandavalli) on Thursday, February 23, 2006 7:09pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Hey gwen, thank you for the informative site. ------------------------------------------------------------ Message no. 6646[Branch from no. 6639] Posted by Rhonda Nichols (renichols2) on Friday, February 24, 2006 8:13am Subject: Re: Trick Question Any one else have an opinion? ------------------------------------------------------------ Message no. 6647[Branch from no. 6549] Posted by Krishnamurth Ashwini (kashwini) on Friday, February 24, 2006 1:28pm Subject: Discussion:Week 8 This is one of the discussion questions for week-8 1. There are three types of major loop controls in PL/SQL including BASIC loop (Exercise 5.3, example 1, page 106), WHILE loop (Exercise 5.3, example 2, page 107) and FOR loop (Exercise 5.3, example 3, page 107). Does anyone know which excersice and page number Dr.Liu is refering in this question?? I did not find it the Oracle text book. Please let me know. Ash ------------------------------------------------------------ Message no. 6648[Branch from no. 6544] Posted by Phanindra Maddhi (pmaddhi) on Friday, February 24, 2006 2:07pm Subject: Re: confusion regarding question hi waseem i have have the same doubt ....phanindra ------------------------------------------------------------ Message no. 6649[Branch from no. 6607] Posted by Phanindra Maddhi (pmaddhi) on Friday, February 24, 2006 2:08pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Thank you it is realy interesting ------------------------------------------------------------ Message no. 6650[Branch from no. 6632] Posted by Phanindra Maddhi (pmaddhi) on Friday, February 24, 2006 2:09pm Subject: Re: Trick Question i would agree with ash ------------------------------------------------------------ Message no. 6653[Branch from no. 6650] Posted by Rhonda Nichols (renichols2) on Friday, February 24, 2006 3:41pm Subject: Re: Trick Question Here's the answer according to Oracle Magazine: http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html I hope you find it interesting as I did.... SELECT Allows Locks Our developers (or at least our production support developers) need READ ONLY access to our production databases, so we have a role in every production database with SELECT on every application table, and this role is granted to developers. A few weeks ago, one of our new developers locked up a key table in the production database and held up a batch operation. All the DBAs swore that one cannot acquire locks with just SELECT access, and we lost a lot of time figuring out how the developer had locked the table. We opened a TAR with Oracle, and the response was, "Yes, you can lock rows with just SELECT. In fact, you can lock the entire table by issuing LOCK TABLE." My reason for posting this here is to find out how many DBAs are not aware of this and to understand what is the reasoning behind allowing locking when you cannot modify data. This locking is "known" and expected, and I agree that few people are aware of this feature (which is the primary reason I've decided to print this question—to raise awareness). The documentation for the LOCK TABLE command (Oracle Database SQL Reference), for example, includes the following: "Prerequisites: The table or view must be in your own schema or you must have the LOCK ANY TABLE system privilege, or you must have any object privilege on the table or view." (emphasis mine) Note that these prerequisites do not necessarily convey the ability to modify data, but the ability to read data does convey the ability to lock—stabilize, freeze, disallow modification to—that data. SELECT ... FOR UPDATE works the same way. So why is this true? Well, the following is one scenario that shows why. Suppose I have read access to the conference room table and I have read/write access to the conference schedule table. I would like to schedule conference room X. For this to happen safely (to avoid overlaps), no one else can schedule conference room X. Therefore, I need to lock conference room X (in the conference room table). The transaction that schedules rooms is a stored procedure, as it should be—all attempts to modify the conference schedule table are via this procedure. The stored procedure does a SELECT ... FOR UPDATE on the conference room (in the conference room table), locking it—and making it safe to peek at the conference schedule table to see if anyone else has it reserved. Another scenario that shows why reading can lock a table is that I would like to delete from table P. P is the parent of C. I have read/write access to P. I have only read access to C. I attempt to delete from P, but before I can do that, I need to lock a bit of C (if indexed foreign keys exist) or all of C (if there is no index on a foreign key). ------------------------------------------------------------ Message no. 6655[Branch from no. 6653] Posted by Bikash Adhikari (badhikari) on Friday, February 24, 2006 6:36pm Subject: Re: Trick Question Hi Rhonda, Very informative indeed. Bikash ------------------------------------------------------------ Message no. 6660[Branch from no. 6653] Posted by Gwendolyn Mobley (gdmobley) on Friday, February 24, 2006 11:54pm Subject: Re: Trick Question this was an interesting article...thanks Rhonda. Gwen ------------------------------------------------------------ Message no. 6663[Branch from no. 6544] Posted by Imran Pathan (ipathan) on Saturday, February 25, 2006 2:06pm Subject: Re: confusion regarding question Hey waseem, I know I am late at answering your question but try out running the chap04.sql and then when the SQl prompt appears then type some commands and you should be able to see the rest of the information. Imran ------------------------------------------------------------ Message no. 6664[Branch from no. 6607] Posted by Imran Pathan (ipathan) on Saturday, February 25, 2006 2:07pm Subject: Re: SQL article from Oracle magazine (jan/feb 2006) Hi Gwen, Thank you for your link it was really interesting and informative. Imran ------------------------------------------------------------ Message no. 6665[Branch from no. 6632] Posted by Venkat Munagala (vrmunagala) on Saturday, February 25, 2006 2:12pm Subject: Re: Trick Question Hi Rhonda, The article is very interesting. Rohit ------------------------------------------------------------ Message no. 6666[Branch from no. 6632] Posted by Imran Pathan (ipathan) on Saturday, February 25, 2006 2:13pm Subject: Re: Trick Question Hi, As far as I know 'Read-only' access, users will have only the option of going through and viewing the database, but they will not be able to make any changes to it. hence with a select statement we cannot issue a for update command. Imran ------------------------------------------------------------ Message no. 6668[Branch from no. 6653] Posted by Imran Pathan (ipathan) on Saturday, February 25, 2006 2:17pm Subject: Re: Trick Question Hey Rhonda, The article is really good. Thank you. Imran ------------------------------------------------------------ Message no. 6671[Branch from no. 6666] Posted by Muhammad Latif (mlatif) on Saturday, February 25, 2006 4:07pm Subject: Re: Trick Question Hey Rhonda: Thanks for sharing. Arsal ------------------------------------------------------------ Message no. 6672[Branch from no. 6671] Posted by Waseem Manzoor (wmanzoor) on Saturday, February 25, 2006 4:16pm Subject: Re: Trick Question Hey Rhonda thank you for helping it out. By the way I could not understand your question. Your atrticle helped it out. Waseem ------------------------------------------------------------ Message no. 6673[Branch from no. 6653] Posted by Krupa Kandavalli (kkandavalli) on Saturday, February 25, 2006 6:29pm Subject: Re: Trick Question Hi Rhonda, Thank you for sharing the article, its very intresting. ------------------------------------------------------------ Message no. 6678[Branch from no. 6647] Posted by Kelsey Pooley (kjpooley) on Saturday, February 25, 2006 10:43pm Subject: Re: Discussion:Week 8 Ash, I see your point. I do not find these examples on these pages either. I looked in the index in the back of the book and I can see the types of loops he mentions listed with page numbers. It starts on page 225 in the text. There are examples of each. ------------------------------------------------------------ Message no. 6679[Branch from no. 6632] Posted by Abhash Bhandary (abhandary) on Sunday, February 26, 2006 1:22pm Subject: Re: Trick Question Thankx for the information Rhonda abhash ------------------------------------------------------------