Remove everything from a schema

#1

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
--

#2

On Nov 27, 7:33*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

#3

On Nov 27, 7:33*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

#4

On Nov 27, 7:33*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

#5

On Nov 27, 7:33*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

#6

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> a écrit dans le message de news:
d952204e-7625-48c6-b80a-0a19549e6f07...oglegroups.com...
On Nov 27, 7:33 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

------------------------------------------

In this case, you will also need to regrant all privileges.
I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'OPERATOR', ' force',
'TABLE',' cascade constraints',
'TYPE', ' force',
'VIEW',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

Regards
Michel

#7

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> a écrit dans le message de news:
d952204e-7625-48c6-b80a-0a19549e6f07...oglegroups.com...
On Nov 27, 7:33 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

------------------------------------------

In this case, you will also need to regrant all privileges.
I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'OPERATOR', ' force',
'TABLE',' cascade constraints',
'TYPE', ' force',
'VIEW',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

Regards
Michel

#8

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> a écrit dans le message de news:
d952204e-7625-48c6-b80a-0a19549e6f07...oglegroups.com...
On Nov 27, 7:33 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

------------------------------------------

In this case, you will also need to regrant all privileges.
I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'OPERATOR', ' force',
'TABLE',' cascade constraints',
'TYPE', ' force',
'VIEW',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

Regards
Michel

#9

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> a écrit dans le message de news:
d952204e-7625-48c6-b80a-0a19549e6f07...oglegroups.com...
On Nov 27, 7:33 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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

One method is to drop the user that owns the schema using the CASCADE
option. You would then need to recreate the schema owner, which will
be done automatically if you import the user schema using the imp
utility. In the following, replace schema with the owner of the
schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take
additional steps to recreate user. I suggest querying DBA_USERS for
the information related to the schema user. Make note of the password
hash, default tablespace, etc. The password hash may be used to
recreate the user with the same password "CREATE USER ______
IDENTIFIED BY VALUES ____"...

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

------------------------------------------

In this case, you will also need to regrant all privileges.
I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'OPERATOR', ' force',
'TABLE',' cascade constraints',
'TYPE', ' force',
'VIEW',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

Regards
Michel

#10

Michel Cadot escribió:

Quote:

I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'OPERATOR', ' force',
'TABLE',' cascade constraints',
'TYPE', ' force',
'VIEW',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

This is not a complement to Charles' solution but an alternative, isn't
it? (Running "DROP USER schema CASCADE" would be an option but it
implies contacting the DBA.)

The production box will be using a bare Instant Client so I prefer not
to rely on SQL*Plus commands but I get the idea. I've run the commands
generated by your SELECT query and everything is happily gone. I can
easily stuff it in a PHP script (or just run it manually).

Thank you!

--
-- 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
--