Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Clean up all objects in a tablespace or clean up all objects owned by a user
In article <7kodct$j98_at_ustsu10.ust.hk>, Yat-Fan <fan.ho_at_alumni.ust.hk>
writes
>Hi all,
>
>When I try to rebuild a DB thru' full database import, I usually drop all
>the tablespaces including their
>contents, rebuild the "empty" tablespaces and then do the importing.
>
>However, the tablespaces are large, this is very time-consuming. Do anyone
>got one clean script
>which can select out all the objects of a user or a tablespace and then
>clean them up.
You can use a DESTROY=Y (I think) on the import to overwrite and re-use tablespaces.
Also, consider truncating all the tables as this also seems to speed up the process. I assume that a drop 'including contents' uses standard deletes against all the tables before actually dropping them, with a lot of rollback work going on.
Andy
>
>Using select statements, it is not difficult to do this job in multiple
>phrases, but I am very lazy
>and in case someone already get such a script :-p
>
>
>Thanks.
>
>Rgds., fan.
>ps. If possible, please also cc: a copy of your reply to me at
>fan.ho_at_alumni.ust.hk
>
>
>
>
--
Andy Hardy. PGP key available on request