Suppose we have two databases.
1. Orcl
2. Hgc
Now 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@hgc
Connected.
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 privileges
Question: 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@Orcl
Connected.
SQL> host exp scott/tiger@Orcl file=c:\test_orcl.dmp tables=test_orcl
Export: Release 10.1.0.5.0 - Production on Wed Jan 2 11:59:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . exporting table TEST_ORCL 0 rows exported
Export terminated successfully without warnings.
NOW import table in HGC database.
SQL> conn scott/tiger@HGC
Connected.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:02:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.
now we check in which tablespace table is created ?
SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL SYSTEM
ofh 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@hgc
Connected.
SQL> revoke resource from scott;
Revoke succeeded.
SQL> alter user scott quota 100m on users;
User altered.
NOW import again...
SQL> conn scott/tiger@hgc
Connected.
SQL> drop table test_orcl purge;
Table dropped.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:06:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.
SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL USERS
But 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 path
import 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"
