Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!r24g2000prf.googlegroups.com!not-for-mail From: joel garry Newsgroups: comp.databases.oracle.server Subject: Re: Temporary table use and the redo log Date: Wed, 9 Sep 2009 09:34:51 -0700 (PDT) Organization: http://groups.google.com Lines: 59 Message-ID: <50bc0253-6e1f-4008-a82d-05838c6e74b0@r24g2000prf.googlegroups.com> References: <4b3fdc8a-6ad1-4275-8d5a-200ecf45bdf0@a21g2000yqc.googlegroups.com> <8f7154be-0b5b-41ed-a773-0ad38cf06583@j39g2000yqh.googlegroups.com> <3e8a48c3-5386-42fc-9f5e-7296ae9e3923@m38g2000yqd.googlegroups.com> NNTP-Posting-Host: 12.232.112.189 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1252514091 18830 127.0.0.1 (9 Sep 2009 16:34:51 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 9 Sep 2009 16:34:51 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: r24g2000prf.googlegroups.com; posting-host=12.232.112.189; posting-account=tpQovAkAAABNoH5bwsZAiff2L0zxGwdv User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Sep 9, 6:53=A0am, "codefragm...@googlemail.com" wrote: > On 9 Sep, 13:28, "codefragm...@googlemail.com" > > > > wrote: > > > - This also means that you can't access an object modified by direct- > > > path inserts within the same transaction which needs to be considered > > > for your process logic. You need to commit to access the inserted the > > > rows which means a "on commit delete rows" temporary table doesn't > > > make sense with a direct-path insert operation, since after the commi= t > > > the data will be gone. > > > Thanks for the reply. > > I gather you can have Autonomous Transactions within a procedure, so I > > could: > > - start a transaction, > > - load the temporary table up, > > - do my selects > > - commit the transaction, > > > then carry on with the rest of the procedure? > > > Any views on that? > > b.t.w. this seems to work for me > - Proc1 calls Proc2 > - Proc2 does the autonomous transaction with the +append hint. > > It seems fast and theres no redo archive logs getting created :-) > > So what I'm worried about now are side effects. > (1) Proc2 is purely inserting, Proc1 is purely a select, I can't see > theres any chance of locking here > (2) Global Temporary tables, so can't lock with other sessions > (3) A session gets created, a few calls are made (but only one call to > Proc1) and is then discarded (rightly or wrongly) > (4) Any issues with writing over the high water mark and repeatedly > losing that storage to the temporary table > > I guess one issue is that I'm a complete novice and haven't seen this > technique mentioned anywhere > > If this works we get time to look at this issue properly and remove > the tables which would be nice As long as you don't try to have simultaneous transactions by the same session, you should be fine. Tom Kyte illustrates this in Expert one- on-one Oracle. He also explains how autonomous transactions work and the caveats. Of course, he thinks they are almost universally misused and should be removed from the database: http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html jg -- @home.com is bogus. http://threatpost.com/blogs/microsoft-cisco-issue-patches-tcp-dos-flaw-108