During Import table created in wrong tablespace.

During import table is created in other than default tablespace is happen what is reason.Suppose we have two databases.1. Orcl2. HgcNow i created one table in ORCL database in SYSTEM tablespace.
SQL> create table test_orcl ( no number) tablespace SYSTEM;Table created.Now in HGC database i import above created table in user default tablespace.
SQL> conn system/oracle@hgcConnected.SQL> alter user scott default tablespace USERS;User altered.NOTE: 1. I assign default tablespace "USERS" to scott user which import table.2. Scott user having "connect" & "resource" role privilegesQuestion: NOW TELL me during IMPORT in which tablespace "USERS" or "SYSTEM" tbs table "test_orcl" created ?NOW guess your answer..we will check later ...
SQL> conn scott/tiger@OrclConnected.SQL> host exp scott/tiger@Orcl file=c:\test_orcl.dmp tables=test_orclExport: Release 10.1.0.5.0 - Production on Wed Jan 2 11:59:18 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . exporting table TEST_ORCL 0 rows exportedExport terminated successfully without warnings.NOW import table in HGC database.
SQL> conn scott/tiger@HGCConnected.SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scottImport: Release 10.1.0.5.0 - Production on Wed Jan 2 12:02:26 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . importing table "TEST_ORCL" 0 rows importedImport terminated successfully without warnings.now we check in which tablespace table is created ?
SQL> select table_name,tablespace_name2 from user_tables3 where table_name='TEST_ORCL';TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TEST_ORCL SYSTEMofh table is created in SYSTEM tablespace instead of user default tablespace means "USERS' why it so ?Again import table but now make one changes ...1. Revoke "resource" role and grant explicit quota on tablespace to user.
SQL> conn system/oracle@hgcConnected.SQL> revoke resource from scott;Revoke succeeded.SQL> alter user scott quota 100m on users;User altered.NOW import again...
SQL> conn scott/tiger@hgcConnected.SQL> drop table test_orcl purge;Table dropped.SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scottImport: Release 10.1.0.5.0 - Production on Wed Jan 2 12:06:51 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . importing table "TEST_ORCL" 0 rows importedImport terminated successfully without warnings.SQL> select table_name,tablespace_name2 from user_tables3 where table_name='TEST_ORCL';TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TEST_ORCL USERSBut what is actual reason...?1. Resource role having "unlimited tablespace" privileges means database user have quota on all tablespace in database if user having "unlimited tablespace" privileges.2. table is created in "system" tablespace becuase tables is actually created in SYSTEM tablespace on "ORCL" database during first time creation.
Export file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set"CREATE TABLE "TEST_ORCL" ("NO" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MA""XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ""DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS". . skipping table "TEST_ORCL"