Re: linux/unix script to dump csv files for each table in db

From: joel garry <joel-garry_at_home.com>
Date: Wed, 1 Dec 2010 15:46:20 -0800 (PST)
Message-ID: <a04b2bf5-4aa2-47dc-a8f3-97c2a2f011d5_at_t8g2000prh.googlegroups.com>



On Dec 1, 10:42 am, syd_p <sydneypue..._at_yahoo.com> wrote:
> On 1 Des, 15:52, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
> > On Nov 30, 7:45 am, syd_p <sydneypue..._at_yahoo.com> wrote:
>
> > > Hi,
>
> > > I am using a very simple script to dump out some csv files from all
> > > the tables in a mysql db.
> > > Well actually they are psv files cos they are separted by a pipe
> > > symbol.
>
> > > But anyway I want to do the same thing with oracle 10G.
> > > Any ideas on how to do this?
> > > Please help if you can!
>
> > > -Syd
>
> > > #!/bin/bash
>
> > > db=nb276
> > > user=root
> > > pass=xxxx
>
> > > for table in $(mysql -u$user -p$pass  $db -Be "SHOW tables" | sed 1d);
> > > do \
> > >  echo "exporting $table.."
> > >  mysql  $db -u$user -p$pass -e "SELECT * FROM $table" | sed 's/\t/|/g'> $db.$table.psv
>
> > > done
>
> > How do I export a database table to a flat file ?
>
> >        http://www.jlcomp.demon.co.uk/faq/flatfile.html
>
> > HTH -- Mark D Powell --
>
> Actually the question is how to export all the tables in a database to
> a flat file per table

Well of course, you simply wrap whatever technique you are using in a query of all the tables - select table_name from dba_tables; assuming you have appropriate privilege. However, this will get more than just the user tables, it will get lots of system tables too, so you more likely want to limit it to certain schemata.

Of course, the more global question is what you want to do with the data. There could be different answers, depending. For example, if you are intent on actually importing to an excel spreadsheet, you could maybe use the http://www.orafaq.com/node/508 xml db and open the xml file directly with excel (never tried it, just thinking out loud). If you are keeping the data as a backup or for cloning to another Oracle database, there could be different answers. If you are moving it to another database, there could be still different answers. You're not in toyland any more, let people know what you actually want to accomplish and you may be surprised. If you use non-  techniques in Oracle databases, you may get frustrated. If you try to use database-blind techniques, you may suffer. There's a huge beautiful world of database out there.

jg

--
_at_home.com is bogus.
http://news.cnet.com/8301-30684_3-20024325-265.html
Received on Wed Dec 01 2010 - 17:46:20 CST

Original text of this message