Re: viewing all data in all tables

Thanks. That's exactly what I needed.

JR

Martin Doherty <martin.doherty_at_oracle.comX> wrote in message news:<axLs9.1$fl.147_at_news.oracle.com>...
> How about trying some SQL-from-SQL:
>
> set pagesize 0
> spool listall.sql
> select 'REM ' || table_name || chr(10) || 'SELECT * FROM ' || table_name
> || ';'
> from user_tables
> /
> spool off
> set termout off -- not sure about this syntax, but intention is to
> suppress terminal output
> spool listall.txt
> @listall.sql
> spool off
> set termout on
>
> (not tested so sorry if not 100%, but should get you pretty damn close)
>
> Martin
>
> John wrote:
>
> >Yeah, I'm only interested in a small bit of the data in the system,
> >the rest I can reimport. I was hoping for some way to open SQLPlus,
> >have it loop thru the user's tables, and output the data to the
> >screen. Then save it. The data is minimal, but there are twenty+
> >tables to select from individually.
> >
> >JR
> >
> >Karsten Farrell <kfarrell_at_medimpact.com> wrote in message news:<8KZr9.5957$Iu7.374935810_at_newssvr21.news.prodigy.com>...
> >
> >
> >>John wrote:
> >>
> >>
> >>>I have an account setup on an Oracle box and I was wondering the best
> >>>way to do something. I need to drop the user cascading, and recreate
> >>>all the tables. There are about 30 tables, with half of them having
> >>>some data. The amount is minor. I do not want to completely lose the
> >>>data so was looking for the best way to do it and exactly how to
> >>>implement. I'm right now looking at either a way to list all table
> >>>names, and the content of them recursively, like a select * from all
> >>>tables or something. Or if there is a way to do an exp of that user
> >>>(I know how to do that part), and then after recreating the tables,
> >>>just import the data and do not effect the table layout. Any help on
> >>>exactly how to go about this would be much appreciated. I'd
> >>>definitely be interested in the first method just so I can have a
> >>>"paper" copy of the data in case I have to key any data back in
> >>>because of changing field types.
> >>>
> >>>Thanks.
> >>>
> >>>JR
> >>>
> >>>
> >>I assume you plan to change the order of some columns or to change the
> >>datatype of some columns. In that case, exp/imp may not work (unless all
> >>you're doing is making columns larger - eg, VARCHAR2(30) to VARCHAR2(60)
> >>or some such). Your best bet might be to output the data into a
> >>comma-delimited file and reload it with SQL*Loader (which allows you to
> >>specify some changes).
> >>
> >>
Received on Mon Oct 21 2002 - 15:59:18 CDT