Re: Remove everything from a schema

"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 76737d60-7547-4262-9ff7-57053d8fb248_at_r40g2000yqj.googlegroups.com... On Nov 29, 12:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> TEST> @delme
> drop TABLE LOCATIONS cascade constraints;
> drop TABLE NARROW cascade constraints;
> drop TABLE PARTS cascade constraints;
> drop TABLE PARTS_TEMP cascade constraints;
> drop TABLE PO_HEADER cascade constraints;
> drop TABLE PO_HEADER_TEMP cascade constraints;
> drop TABLE PO_LINE cascade constraints;
> drop TABLE PO_LINE_TEMP cascade constraints;
> drop TABLE UMS cascade constraints;
> drop TABLE VENDORS cascade constraints;
> drop TABLE VENDORS_TEMP cascade constraints;
> TEST> select object_name, object_type from user_objects order by 1, 2;
>
> no rows selected
>
> The only cases I see where it will not work is very unusual ones
> like you created index on tables you don't own:
>
> TEST> sho user
> USER is "TEST"
> TEST> create index t_i on michel.t (val);
>
> Index created.
>
> TEST> select object_name, object_type from user_objects order by 1, 2;
> OBJECT_NAME OBJECT_TYPE
> ------------------------------ -------------------
> T_I INDEX
>
> 1 row selected.
>
> TEST> @delme
> TEST> select object_name, object_type from user_objects order by 1, 2;
> OBJECT_NAME OBJECT_TYPE
> ------------------------------ -------------------
> T_I INDEX
>
> 1 row selected.
>
> I will modify the script to handle this case but there may be
> other things like that.
>
> Regards
> Michel

Michel,

Thanks for running through the test script and posting the results!

Did you run the full test script to the point that test data was inserted into the tables, or where there 0 rows in each table? Does that make a difference whether or not the table drop statements still work? It seems like the full test script that I pointed to required roughly 12 hours to complete, while rows are added and updated in the various tables.

If you make changes to the script, please post the final script in this thread. I have encountered a couple cases where it would have been nice to drop all objects without having to recreate the user, and I am impressed with the thoroughness of your script.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

I just executed the create statements from CREATE TABLE LOCATIONS to and including CREATE TABLE NARROW but did not fill the tables. I don't think this changes anything (but the duration of course), as I said I used this script for years without any problem, adding from to time a bit I didn't use before (and the next step will be to add the index I mentionned in my previous post).

Regards
Michel Received on Sat Nov 29 2008 - 14:09:49 CST