Re: code to load tracefile into CLOB?

1. Next time I'll mention that I'm very familiar with and fond of MR's fantastic offerings. :) In fact this is exactly what made me ask the question in the first place... they've really been getting excited about MR Trace here. It's the whole reason I want developers to have their trace files - so they can run it through MR Trace. Unfortunately the development team in this shop uses PL/SQL Developer from Allround Automations (http://www.allroundautomations.com/plsqldev.html) and the MR Trace plugin is not available for this environment. Seems like a bit of a stretch for me to try to convince their entire development team to change to a new development environment... hence asking about a snippit of code to pull the tracefiles for them.

[Any MR folks on the list... do you have a solution for this that you give to MR Trace customers? I did ask around at Hotsos and got no answer... but I probably should have shot an email over to you before I hit the list here!!]

2. Setting up sftp is a nice idea - and if this was a smaller shop then I'd skip it and just try for full shell access. Unfortunately this is a very large and security-conscious place (for very good reasons). Trying to convince them to give the developers any kind of access to the production boxes besides the listener port will involve a lot of paperwork and argument and politics... much easier to just write a snippit of code that lets them do it through the database, which they already have access to.

Whatever I come up with, I'll try to remember to post an update back to the list.

-Jeremy

On 8/16/2011 9:43 PM, Kerry Osborne wrote:
> I've created external tables on trace files before so I could query
> some of the data. Works fine for a single file as long as you're a DBA
> with access to the right directories, but when you start trying to
> figure out how to deal with multiple files being created on the fly
> and giving developers access, it becomes a more complicated problem.
> And by the way, not providing developers with the tools they really
> need is, in my opinion, one of the biggest problems we have in the
> Oracle community. MR Trace provides a pretty straight forward way to
> obtain that information without having to find a DBA and communicate
> to him which trace file you're looking for. Off my soapbox and back
> into my hole now.
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com <http://kerryosborne.oracle-guy.com>
>
>
> On Aug 16, 2011, at 9:26 PM, John Piwowar wrote:
>
>> If the developers are usually generating these trace files themselves
>> from a client session, and not dumping them from within an
>> application, you might want to look at Method R's MR Trace tool,
>> which bolts on to SQL Developer and generates trace files locally.
>>
>> http://method-r.com/software/mrtrace
>>
>>
>> Regards,
>>
>> John P.
>>
>> On Tue, Aug 16, 2011 at 6:56 PM, Jeremy Schneider
>> <jeremy.schneider_at_ardentperf.com
>> <mailto:jeremy.schneider_at_ardentperf.com>> wrote:
>>
>> Hm... I could definitely see some perks to using an external
>> table. It would be a lot easier to export this to a file on
>> their own machine... simpler all-around...
>>
>> Only additional wrinkle is that this is not just one tracefile,
>> but lots and lots of tracefiles. Anytime they enable 10046 on a
>> session, I want them to get their tracefile. A block of code
>> that dynamically creates a bunch of external tables would do it,
>> one for each trace file... but we have a lot of files and I'd
>> rather not create so many objects. Maybe if there was just one
>> external table, and I had a block of code that just redefined
>> that external table to point to whichever trace file they
>> need... the many developers here would just have to coordinate.
>> Or perhaps I could make it like DBMS_STATS where they can point
>> it to any table, create one in their own schema that they just
>> re-use, like the PLAN_TABLE.
>>
>> But to my original question, I guess nobody already has this
>> block of code already written? Suppose I'll have to actually go
>> and write it? Oh well. :)
>>
>> -Jeremy
>>
>>
>> On 8/16/2011 6:08 PM, Tim Gorman wrote:
>>> Jeremy,
>>>
>>> Given all the complexities involving LOBs in general, why not
>>> load the tracefile line-by-line into a table with each line is a
>>> row, thus using a VARCHAR2 column for the text of the line?
>>> Easier to load, easier to manage, easier to index, easier to
>>> retrieve?
>>>
>>> Just my $0.02?
>>>
>>> -Tim
>>>
>>>
>>> -----Original Message-----
>>> *From:* Jeremy Schneider
>>> [mailto:jeremy.schneider_at_ardentperf.com]
>>> *Sent:* Tuesday, August 16, 2011 04:39 PM
>>> *To:* Oracle-L_at_freelists.org <mailto:Oracle-L_at_freelists.org>
>>> *Subject:* code to load tracefile into CLOB?
>>>
>>> Just wondering... does anyone out there have a snippit of
>>> code that will load a 10046 trace file from bdump or udump
>>> into a LOB? Just looking for a quick and dirty way to give
>>> some developers access to tracefiles (without requiring unix
>>> logins). Didn't see any code samples with a quick google
>>> search, so I'm about to code it myself - just thought I'd
>>> ask first.
>>>
>>

-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago


--
http://www.freelists.org/webpage/oracle-l