RE: System used as holiday camp!!

Thanks all for the info, worked like a charm=2E=2E

Mark

-----Original Message-----

From:	MIME :martyn=2Er=2Ebaker_at_uk=2Epwcglobal=2Ecom=20=
Sent:	Wednesday, May 03, 2000 12:51 PM
To:	ORACLE-L_at_fatcity=2Ecom
Cc:	Mark Leith
Subject:	Re: System used as holiday camp!!

Mark,

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

spool off
set pages 20 lines 80 feed on
@drop_user=2Esql

By the way, any user (not only SYS) that has the 'create user' privilege can create a user within the database=2E The default tablespace for a new user will always be SYSTEM unless specified explicitly=2E The same applies for the=20= temporary
tablespace=2E
To list all users of this wrongly created try: select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace =3D 'SYSTEM' or temporary_tablespace =3D '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=2E=2E=2E

Martyn

                                                                   (Embedded =20=
                                                       image moved  =20=
mleith_at_bradmark=2Eco=2Euk                               to file:      03/05/2000=20=
11:07                                    pic23388=2Epcx)                       =20=
                                                                             =20=
                Please respond to ORACLE-L_at_fatcity=2Ecom

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity=2Ecom> 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=20= it
seems a user was created by sys!! Thus making this little blighters default tablespace=2E=2E you guessed it=2E=2E system=2E Now this oh so clever user has cr= eated
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=20= of
a users objects from a tablespace? I don't want to play around to much with=20= the
system tablespace as my knowledge is limited=2E=2E

Any insight is greatly appreciated!

TIA Mark

--
Author:
  INET: mleith_at_bradmark=2Eco=2Euk

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=2Ecom (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)=2E  You may
also send the HELP command for other information (like subscribing)=2E


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