Tablespace Change

I created a user as below
create user paymaster identified by paymaster
default tablespace users
temporary tablespace temp

this created user paymaster.
however when i queried
select owner,tablespace_name from dba_tables
the output was as below:-

OWNER TABLESPACE_NAME
PAYMASTER SYSTEM

the paymaster should be in tablespace 'PAYDATA'
this tablespace is already created.
How do i create user PAYMASTER to use PAYDATA?

I had earlier dropped user paymtr from the database as the client wants paymtr to be changed to paymaster.
I created as above and granted the roles.
However when I tried to import from *.dmp file that was exported from paymaster in another machine(which was in system tablespace) and I queried from dba_tables to find all tables are in system tablespace.
How can I retify this?

Change the tablespace from SYSTEM to PAYDATA without dropping the tables.
sql > Alter user PAYMASTER default tablespace PAYDATA
temporary tablespace TEMP;

and for each table - move them to PAYDATA tablespace...try to create a script for the below for all the tables ..

sql > Alter table tablename default tablespace PAYDATA;

Also check the indexes ...if they happen to be in SYSTEM tspace do the above and move them into PAYDATA tablespace or anyother where you wish.

I have managed to move the tables from system to paydata. However there is one table with datatype long which I am unable to move.
Any suggestions on how to solve this.

In order to move the table with a LONG col datatype you can generate a table script manually and import the data into that table.

If you have problem generating table script use TOAD to generate the table script and also the INSERT statement script and run the script from sqlplus.You should be good.

No comments:

topics