Re: viewing all data in all tables

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 Sun Oct 20 2002 - 23:33:06 CDT