Re: Remove everything from a schema

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. Received on Sat Nov 29 2008 - 12:23:15 CST