Changing Database Default Tablespace for Users

At the time of user[Schema] creation, we define the tablespace for that user. But for some case, we may need to change the tablespace for a user. We can easily perform that following the below steps:

1. Create new Tablespace for schema
create tablespace datafile '/u01/app/oracle/oradata/orclpaul/_DBF.DBF' size 5120m autoextend on;
2. Take Export of schema on Seperate drive.
EXP SYSTEM/SYSMAN@pauldb-uat OWNER= FILE=/home/paul/dbdump/_2501016.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= /home/paul/dbdump/_2501016.log
3. Now create new schema or if you have already existing schema then drop and recreate it.
drop OLD_SCHEMA_NAME CASCADE;

CREATE USER
  IDENTIFIED BY VALUES
  DEFAULT TABLESPACE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

  --  Role for
  GRANT DBA TO WITH ADMIN OPTION;
  ALTER USER DEFAULT ROLE ALL;
  --  System Privilege for
  GRANT UNLIMITED TABLESPACE TO WITH ADMIN OPTION;
  -- Tablespace Quota for
  ALTER USER QUOTA UNLIMITED ON ;

alter user quota 0 on quota unlimited on ;


In the case while changing the default tablespace for the user's in the same database, don't forget to assign quota unlimited on new tablespace and revoke unlimited quota on old tablespace. This might not required while changing the tablespace of user's of different database.

revoke unlimited tablespace from ;

4. Now try to import the dump you have taken. Make sure dump is properly copied on the drive.

IMP SYSTEM/sysman@pauldb-uat FILE=/home/paul/dbdump/_2501016.DMP FROMUSER= TOUSER= LOG= /home/paul/dbdump/_2501016.log

Don't forget to assign back quota unlimited on tablespace.
alter user DEFAULT tablespace QUOTA UNLIMITED ON ;