users in the wrong tablespace

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 3 Oct 2002 12:45:04 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702BD6C6B@lnewton.leeds.lfs.co.uk>

Teresa,

export the two users in the wrong tablespaces, then

alter user a
default tablespace ts_a
quota 0 on index1
quota 0 on index3
quota 0 on system
quota <whatever> on ts_a;

do the same sort of thing for user b.

Drop the contents of the user in question :

select 'drop ' || object_type || ' ' || object_name || ';' from user_objects
where object_type != 'TABLE'
union
select 'drop table ' || table_name || ' cascade constraints;' from user_tables;

(that should do everything !)

and run that in both user a and user b.

Now, if you like and have dictionary managed tablespaces, login as system and

alter tablespace index1 coalesce;
alter tablespace index3 coalesce;

(this might impact on the system so it may be left until a quiet period)

Then simply import the two errant users again.

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: tpreto7_at_sapo.pt (Teresa) [mailto:tpreto7_at_sapo.pt] Posted At: Thursday, October 03, 2002 12:35 PM Posted To: server
Conversation: users in the wrong tablespace Subject: users in the wrong tablespace

I just discovered that I have 2 users with a permanent tablespace index1 and index3, how can I move them to a tablespace of their own, I will create a tablespace for them then how to I do a import their contents into the new tablespaces ....
Thanks
Teresa Received on Thu Oct 03 2002 - 06:45:04 CDT