Can not move user's table from system to data01 tablespace

[xuduro]

I have forget to assign default tablespace to a user, and the user created tables where they ended up in the SYSTEM tablespace. However, when i discovered that i did export all the user schema and then imported it. I have user's tables still in the SYSTEM tablespace.
Here is what i did:

alter user username quota 0M on system;
alter user username default tablespace data02;
alter user username temporary tablespace temp;
alter user username quota unlimited on data02;

just to make sure that user has no quota on system, i have querried :

select * from dba_ts_quotas;
and username has 0m on system, but username has -1 on data02(UNLIMITED).

then i started my export and import process.

but when i query dba_tables:

select table_name, tablespace_name,owner from dba_tables
where owner not in ('SYS','SYSTEM','OUTLN')
and tablespace ='SYSTEM';
I have found out that username has his tables still in SYSTEM
strange.
NOT TO MENTION THAT USERNAME HAS NO ---DBA--- ROLE.

PLEASE, HELP.......

[pando]

have you revoked unlimited tablespace privilege from user? (if you have granted resource role that is)

[ndisang]

Yea, but exporting the schema doesn't get rid of the tables in the system tbs. What you can do is
1/ export the empty schema.
2/ export schema plus data.
3/ edit the empty schema and change the tablespaces to waht you want and run.
4/ drop the schema
5/ import with ignore =y
hope this helps.

[sanjay92]

Why don't you use alter table move command to change the tablespace.

The method given by ndisang is also correct.


Sanjay

[supermega]

I think you may find that the tables will be in both tablespaces now (system and data2). If you find the tables in data2 have that user create a new object, if it goes into data2 then you just need to delelte the tables in system. Just because you exported them FROM system TO data2 doesnt remove them from system, it just will put a copy in data2.

SM

[BV1963]

Originally posted by supermega
I think you may find that the tables will be in both tablespaces now (system and data2). If you find the tables in data2 have that user create a new object, if it goes into data2 then you just need to delelte the tables in system. Just because you exported them FROM system TO data2 doesnt remove them from system, it just will put a copy in data2.

SM

It will not happen if original schema dropped. In case it was not than you could have 2 users with identical objects in which case you need to drop user that have tables in SYSTEM tablespace

[pando]

dont put yourself into trouble, just nciely revoke unlimited tablespace from your user and it will be fine because even you alter the user and giuve him 0MB quota in system if he has this privilege your effort of setting his quota is useless