Why does user default tablespace not change from USER to new tablespace when import transport tablespace in oracle using IMPDP command?

Question

I imported a transport tablespace from source database to my target database using IMPDP command. After importing, my schema's default tablespace shows as USERS tablespace. But when I created that schema at my source database, on that time I mentioned its default tablespace and also its quota.

Why my imported tablespace not tagged with the schema when I perform IMPDP command with TRANSPORT_DATAFILES parameter?

For your understanding, I give my steps as following.

1. Create tablespace on my target database.
2. Create user and assign its default and quota tablespace with newly created tablespace.
3. Create some table and insert some data to the schema.
4. Alter tablespace to READ ONLY mode.
5. Perform EXPDP using TRANSPORT_TABLESPACES parameter.
6. Copy the tablespace related datafile to my target database.
7. At my target database, create same schema without mentioning the default and quota tablespace name.
8. Perform IMPDP using TRANSPORT_DATAFILES parameter.
9. Alter tablespace to READ WRITE mode.
10. Insert data and get ORA-01536: space quota exceeded for tablespace error.
11. Export command: expdp system/sys123@localhost:1521/orcl directory=TEST_BKUP_TBLSPS_DIRECTORY DUMPFILE=transport_tablespace_dumpfile.dmp logfile=transport_tablespace_exp_log_1.log transport_tablespaces=TEST_BKUP_TBLSPC_EXPIMP.
12. Import command: impdp system/sys123@localhost:1521/orcl directory=TEST_BKUP_TBLSPS_DIRECTORY DUMPFILE=transport_tablespace_dumpfile.dmp logfile=transport_tablespace_imp_log_1.log transport_datafiles='D:\app\administrator\oradata\TEST_BKUP_TBLSPC_EXPIMP_DBF1_DBF.DBF'.

Answer 1

I'll repeat and expand what I said in the comments.

A user can write to several tablespaces (he just needs to have the corresponding quota in the given tablespace, or the UNLIMITED TABLESPACE privilege).

A tablespace can be written by several users.

The user is not the owner of the tablespace. The default tablespace (for a given user) is just the tablespace where the objects (tables, indexes, partitions) of this user will be created when the user doesn't explicitly specifiy a different tablespace.

The Import operation doesn't change the default tablespace of a user. It also doesn't change the quotas.

If you want to assign a default tablespace, and quotas, for a given user, you have to do it manually (ALTER USER ... SET DEFAULT TABLESPACE ..., etc). In your scenario, you have to do it after the import.

That's it. This is intended behaviour. This is how it works.