Have you played with Oracle external tables yet? They are perhaps one of Oracle's more overlooked features. I'm mostly interested in DBA/tuning uses of external tables at this point.
When they first came out in Oracle9i I wrote an article in SELECT for IOUG-A on using external tables to read your database alert log from within the database. I'm now using external tables to load the results of 10046 traces into the database (I have a presentation at OAUG on this topic). The results are interesting and fun to play with, that's for sure.
I'm really just starting to play with the 10046 traces in Oracle. I'm trying to figure out some different reports and different ways to play with the data. If you have any ideas, let me know. I'm also struggling with one part of the 10046 loads, that is getting the SQL statement to load into properly into the external table. I've tried several different ways, but so far it's not working like I think it should. I've not had time in the last couple of weeks to work with it again though...
So, I'm curious what kinds of DBA/development/tuning things have you done with external tables?
Subscribe to:
Post Comments (Atom)
2 comments:
you can check out this very preliminary "view" that is a trace file I've been playing with.
Used object types to create a variant "record" in the view. depending on the type of trace record, the right object column gets filled in.
I too sort of punted on the SQL query. I glue together upto 10 lines of the sql query in the view -- or the first 4000 characters, whichever comes first. That is usually enough to see what we see. Not perfect, but an approach.
My favorite use of external tables:
Load this really big file ASAP. In the past, setup the scripts to parallel direct path load. Coordinate the firing of said scripts. Review log files to make sure they all finished. Split up input file or use skip and load to slice the file up. In short, a pain in the butt.
Now:
create table ET parallel;
create table new_table (...) parallel
as
select * from ET;
parallel direct path load made trivial.
Thanks for the feedback Tom! I like the script a lot!
I am sure I can get the SQL loaded in, it's just a mater of finding the right way of parsing it.... For now, I just take the SQL address and join it to v$sqlarea....
Post a Comment