Re: truncate tables in my tablespace

On Jan 17, 1:14 pm, Arun Mathur <themath..._at_gmail.com> wrote:
> On Jan 17, 1:50 pm, burrell.j..._at_yahoo.com wrote:
>
> > Hello,
> > I want to delete all the data in all the tables in my tablespace.
> > SQL> select table_name from user_tables;
> > I can get a list of the tables but I dont want to a
> > for each table type in
> > SQL> delete from tablename;
> > Is there a smart way of doing this?
>
> > TIA
> > J
>
> Assuming the tables in your tablespace are stored under one database
> account, how about:
>
> 1) Export the schema via the exp utility where rows=N
> 2) Drop the user (Ensure you have a backup)
> 3) Recreate the user
> 4) Import the .dmp file from Step 1
>
> Regards,
> Arun

Note that this might have some odd effects regarding space allocations in the tables. Test and observe. You may need to modify INITIAL before exporting. Or not, if the data distribution and amount that will be loaded will be similar to what was there. Personally, I prefer to have table creation scripts if possible, since I've sometimes found strange things carried over from times long past, and my data can vary quite a bit among a thousand tables. I've seen things quite a bit off simply from purging some data, or accidentally loading too much.

Check out the INDEXFILE option on imp, it includes the create table as a comment. Use constraints=Y.

jg

--
@home.com is bogus.
"It is generally inadvisable to eject over the area you just bombed" -
U.S. Air Force Manual