Re: System used as holiday camp!!

--0__=A0tf7DMYZFeWy6zgcC6aeBpxTv7SPAMgQb4dJSAWw4NR4D6NCsnCnZ5N Content-type: text/plain; charset=us-ascii Content-Disposition: inline

Mark,

You could try:
spool drop_user.sql
set pages 0 lines 132 feed off
select 'drop table '||owner||'.'||table_name||';' from dba_tables
where owner = '<USERNAME>'
and tablespace = 'SYSTEM'
/

spool off
set pages 20 lines 80 feed on
@drop_user.sql

By the way, any user (not only SYS) that has the 'create user' privilege can create a user within the database. The default tablespace for a new user will always be SYSTEM unless specified explicitly. The same applies for the temporary tablespace.
To list all users of this wrongly created try: select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM'
/

You can alter the user's default and temporary tablespace with:

Alter user <username>
default tablespace <def_ts_name>
temporary tablespace <temp_ts_name>
quota <size or unlimited> on def_ts_name
/

Let me know if you need more info...

Martyn

                                                                  
 (Embedded                                                        
 image moved   mleith_at_bradmark.co.uk                              
 to file:      03/05/2000 11:07                                   
 pic23388.pcx)                                                    
                                                                  

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
Subject: System used as holiday camp!!

Hey Guys & Gals

Taking a look at a system tablespace today on one of our test machines, and it seems a user was created by sys!! Thus making this little blighters default tablespace.. you guessed it.. system. Now this oh so clever user has created about 200 tables and indexes alike in the system tablespace!

What I am wondering is this, do any of you have a script that can delete all of a users objects from a tablespace? I don't want to play around to much with the system tablespace as my knowledge is limited..

Any insight is greatly appreciated!

TIA Mark

--
Author:
  INET: mleith_at_bradmark.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


----------------------------------------------------------------
The information transmitted is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material.  Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited.   If you received this in error, please
contact the sender and delete the material from any computer.

--0__=A0tf7DMYZFeWy6zgcC6aeBpxTv7SPAMgQb4dJSAWw4NR4D6NCsnCnZ5N
Content-type: text/plain; name="pic23388.txt"
Content-Disposition: attachment; filename="pic23388.txt"
Content-transfer-encoding: base64

<binary content removed -- do not send binaries to the list>