 | |
Remove everything from a schema
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#41
 
| |||
| |||
|
Re: Remove everything from a schema -
11-29-2008
, 02:09 PM
|
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 |
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
#42
| |||
| |||
|
|
I want to remove all the contents of a schema (tables, indexes, sequences...) in my development box so I can checkout a fresh working copy of the web site I'm working on and generate all objects from scratch. The server runs version 9.2.0.1.0 in a separate network machine and I'd rather not ask the DBA to do it for me every time. What's the easiest way to do it? -- --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web:http://bits.demogracia.com -- Mi web de humor al baño María:http://www.demogracia.com -- |
DBA to create you a package with the drop user cascade and add user
just for you. But if there are others it may reduce his workload for
them, too. Suggestions like that presented properly can help DBA/
developer relations.
For myself, I like to work on the server, partly because I can run
stuff like this as scripts, the scripts tend to pick up most
modifications the vendors make, and the drop user followed by a
tablespace coalesce makes sure I don't leave wasted space about - be
sure you are using locally managed tablespaces, anyways. I worry when
I see things like 9.2.0.1.0.
jg
--
@home.com is bogus.
http://paulgraham.com/artistsship.html
#43
| |||
| |||
|
|
I want to remove all the contents of a schema (tables, indexes, sequences...) in my development box so I can checkout a fresh working copy of the web site I'm working on and generate all objects from scratch. The server runs version 9.2.0.1.0 in a separate network machine and I'd rather not ask the DBA to do it for me every time. What's the easiest way to do it? -- --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web:http://bits.demogracia.com -- Mi web de humor al baño María:http://www.demogracia.com -- |
DBA to create you a package with the drop user cascade and add user
just for you. But if there are others it may reduce his workload for
them, too. Suggestions like that presented properly can help DBA/
developer relations.
For myself, I like to work on the server, partly because I can run
stuff like this as scripts, the scripts tend to pick up most
modifications the vendors make, and the drop user followed by a
tablespace coalesce makes sure I don't leave wasted space about - be
sure you are using locally managed tablespaces, anyways. I worry when
I see things like 9.2.0.1.0.
jg
--
@home.com is bogus.
http://paulgraham.com/artistsship.html
#44
| |||
| |||
|
|
I want to remove all the contents of a schema (tables, indexes, sequences...) in my development box so I can checkout a fresh working copy of the web site I'm working on and generate all objects from scratch. The server runs version 9.2.0.1.0 in a separate network machine and I'd rather not ask the DBA to do it for me every time. What's the easiest way to do it? -- --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web:http://bits.demogracia.com -- Mi web de humor al baño María:http://www.demogracia.com -- |
DBA to create you a package with the drop user cascade and add user
just for you. But if there are others it may reduce his workload for
them, too. Suggestions like that presented properly can help DBA/
developer relations.
For myself, I like to work on the server, partly because I can run
stuff like this as scripts, the scripts tend to pick up most
modifications the vendors make, and the drop user followed by a
tablespace coalesce makes sure I don't leave wasted space about - be
sure you are using locally managed tablespaces, anyways. I worry when
I see things like 9.2.0.1.0.
jg
--
@home.com is bogus.
http://paulgraham.com/artistsship.html
#45
| |||
| |||
|
|
I want to remove all the contents of a schema (tables, indexes, sequences...) in my development box so I can checkout a fresh working copy of the web site I'm working on and generate all objects from scratch. The server runs version 9.2.0.1.0 in a separate network machine and I'd rather not ask the DBA to do it for me every time. What's the easiest way to do it? -- --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web:http://bits.demogracia.com -- Mi web de humor al baño María:http://www.demogracia.com -- |
DBA to create you a package with the drop user cascade and add user
just for you. But if there are others it may reduce his workload for
them, too. Suggestions like that presented properly can help DBA/
developer relations.
For myself, I like to work on the server, partly because I can run
stuff like this as scripts, the scripts tend to pick up most
modifications the vendors make, and the drop user followed by a
tablespace coalesce makes sure I don't leave wasted space about - be
sure you are using locally managed tablespaces, anyways. I worry when
I see things like 9.2.0.1.0.
jg
--
@home.com is bogus.
http://paulgraham.com/artistsship.html
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.