February 14, 2011 at 12:32 pm
I'm working with some Oracle 9i database servers, and I need to export some fairly large tables from Oracle into textfiles.
I'm looking to get all the data at first, and export/import incrementally after that. The larger tables are between 10 and 150 million rows, but not particularly wide.
What is the fastest way to export to pipe-delimited textfiles? The only helpfile I have is for 11g.
February 14, 2011 at 6:47 pm
sqlgreg (2/14/2011)
I'm working with some Oracle 9i database servers, and I need to export some fairly large tables from Oracle into textfiles.I'm looking to get all the data at first, and export/import incrementally after that. The larger tables are between 10 and 150 million rows, but not particularly wide.
What is the fastest way to export to pipe-delimited textfiles? The only helpfile I have is for 11g.
It's been a while since I've had to do such things in Oracle but, IIRC, there's SQL*Loader for imports and, I believe, SQL*Export (could be real wrong on that name) for outputing to text files. One thing I do remember is it's pretty fast.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2011 at 4:07 pm
No direct unloader
Have a look at the faq
http://www.orafaq.com/wiki/SQL*Loader_FAQ#What_is_SQL.2ALoader_and_what_is_it_used_for.3F
or try to write to "external tables"
February 17, 2011 at 8:38 am
Thanks, guys. Jo, I ended up using the spool option to spool the results of a query with a custom delimiter (pipe) into a text file.
February 17, 2011 at 10:12 am
sqlgreg (2/17/2011)
Thanks, guys. Jo, I ended up using the spool option to spool the results of a query with a custom delimiter (pipe) into a text file.
Out of curiosity... what are you planning to do with such text files?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2011 at 10:14 am
Jo Pattyn (2/15/2011)
...or try to write to "external tables"
Oracle "external tables" are read-only - no DML activity is allowed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2011 at 1:10 pm
Apparently changed with oracle 10g
http://decipherinfosys.wordpress.com/2007/04/28/writing-data-to-a-text-file-from-oracle/
February 17, 2011 at 1:19 pm
I'm going to eat them. I find text files to be really delicious, with a bit of hot sauce.. 😛
But really, the files are older data that I'm using for a data warehouse project. I'm feeding them to SQL Server using BULK INSERT and format files.
Once I have that data, I'll be getting small and manageable daily imports.
February 18, 2011 at 4:06 pm
Jo Pattyn (2/17/2011)
http://decipherinfosys.wordpress.com/2007/04/28/writing-data-to-a-text-file-from-oracle/%5B/quote%5D
This is one of those interesting scenarios where both of us are right.
I was right when I said "Oracle external tables are read-only - no DML activity is allowed" and you are certainly right about CTAS solution - that's a creative way to do it provided the user runs on Ora10g which unfortunately is not the case.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 18, 2011 at 4:10 pm
sqlgreg (2/17/2011)
I'm going to eat them. I find text files to be really delicious, with a bit of hot sauce.. 😛
Oh yes... I know what you mean, they are delicious but they have to be fresh 😀
I'm pretty sure you already thought about the rdbms-to-rdbms options available to more the data over from Oracle, like linking the Oracle server so - I'm making no mention of them.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 18, 2011 at 7:22 pm
PaulB-TheOneAndOnly (2/18/2011)
I'm pretty sure you already thought about the rdbms-to-rdbms options available to more the data over from Oracle, like linking the Oracle server so - I'm making no mention of them.
I've been at the present post for just a few weeks now, and they told me when I started that they wanted to use SSIS. So we are using it for some jobs, but frankly I don't enjoy working with SSIS as much as scripting. I'm not thrilled with the way SSIS implements variables and parameters.
I did try setting up a linked server. It works great from my local machine, but of course we're using a server.. and it's just not working.. keep getting errors. Seems like we tried about everything, but just couldn't get it to work.
February 20, 2011 at 3:11 pm
Hello,
You'l have to check some things.
Does the basic oracle connectivity works using tnsping, sqlplus from the server?
Is the server 64-bit? In that case you might need both the 64-bit and 32-bit oracle client configured.
(search the forums)
February 21, 2011 at 8:12 am
Jo, I'm gonna start a new thread for the linked server issue.
February 21, 2011 at 8:25 am
Well, I was gonna start a new thread on the linked server issue, but whatever fiddling was done by the admins on the servers over the weekend has apparently fixed the issue!
February 21, 2011 at 12:50 pm
sqlgreg (2/21/2011)
Well, I was gonna start a new thread on the linked server issue, but whatever fiddling was done by the admins on the servers over the weekend has apparently fixed the issue!
Yahoo!... Damn!...
Usually after the initial excitement I start to feel really frustrated about those magic fixes that neither identify the root cause of the issue nor define a resolution procedure.
Do you have in place a post mortem analysis protocol that may help to document what really happened?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply