Re: wrong tablespace

In article <01bca0e6$d0678a20$796631ce_at_inet-computr>, "CSS" says...
>
>Editing an Oracle export file is NOT suggested at all !!!
>However, you could export all the tables(along with the indexes) present in
>the SYSTEM tablespace. Drop these tables. Alter your users default
>tablespace to a non-SYSTEM tablespace with required quota on this
>tablespace. Revoke the resource privilege for that user and then Import the
>previously exported Oracle dmp file.
>
>Good Luck
>Arun Kumar B
>email: barunk_at_gto.net.om
>
>Sunil Jayasinghe <sunil.jayasinghe_at_probanka.si> wrote in article
><5rmqt0$102_at_news.eunet.si>...
>> How can I move some tables and indexes from system tablespace ( where
 they
>> should't be) to some other (users) tablespace?
>> I tried to export the user, then edit the export file and replace all the
 "
>> create table .... tablespace 'SYSTEM'
>> with "tablespace 'USERS' " and then import the user. That works O.K. but
 I
>> would like to find out a better way to deal with that.
>>
>> Thanx
>> sunil.jayasinghe_at_probanka.si
>>

Hi:
Well, I'm not so sure you can do what CSS says. I had a similar problem a time ago; I received an export file for 5 tables located in SYSTEM tablespace, and I had to put them in another database, in tablespace (say) USERS. I create a user with default tablespace and temporary tablespace on USERS, and with this user I run import. It _didnt't_ work, I mean, ORACLE complain about the user not having create privilege on tablespace SYSTEM.

   What I did was:
1) import the file answering YES to List contents. With this, you get the create table sentences.
2) With a "cut and paste", deleting the sentence "TABLESPACE SYSTEM", I go inside sqlplus and create the tables.
3) Then, I run again import, answering NO to List Contents and YES to Ignore Error due to table creation(or something like that). And, it works!!!, the data is inserted in the tables you created in the second step(which are of course on your default tablespace).

Of course, I would suggest that you first try what CSS says(which is not only simpler but of better style). It didn't work for me, but probably because it was not the only problem I had (I didn't know who and with which settings the export was done, it was another version of ORACLE, and some parameters were incompatible with the new version(I deleted this parameters also in step 2) ). If it doesn't work for you neither, try this. (I would suggest that you get up early if you want this done that day :-)

   Hope this helps!!! (any problem, write me: illarze_at_chasque.apc.org )

                Alvaro