resouce是角色,unlimited tablespace是權限。
很多人在進行數據遷移時,希望把數據導入不同于原系統的表空間,在導入之后卻往往發現,數據被導入了原表空間。
本例舉例說明解決這個問題:
1.如果缺省的用戶具有DBA權限
那么導入時會按照原來的位置導入數據,即導入到原表空間
$?imp?bjbbs/passwd?file=bj_bbs.dmp?fromuser=jive?touser=bjbbs?grants=n
Import:?Release?8.1.7.4.0?-?Production?on?Mon?Sep?22?11:49:41?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
Export?file?created?by?EXPORT:V08.01.07?via?conventional?path
Warning:?the?objects?were?exported?by?JIVE,?not?by?you
import?done?in?ZHS16GBK?character?set?and?ZHS16GBK?NCHAR?character?set
.?.?importing?table????????????????"HS_ALBUMINBOX"?????????12?rows?imported
.?.?importing?table????????????????"HS_ALBUM_INFO"?????????47?rows?imported
.?.?importing?table???????????????????"HS_CATALOG"?????????13?rows?imported
.?.?importing?table??????????"HS_CATALOGAUTHORITY"??????????5?rows?imported
.?.?importing?table?????????"HS_CATEGORYAUTHORITY"??????????0?rows?imported
.
.?.?importing?table?????????????????"JIVEUSERPROP"??????????4?rows?imported
.?.?importing?table????????????????????"JIVEWATCH"??????????0?rows?imported
.?.?importing?table???????????????????"PLAN_TABLE"??????????0?rows?imported
.?.?importing?table???????????????????"TMZOLDUSER"??????????3?rows?imported
.?.?importing?table??????????????????"TMZOLDUSER2"??????????3?rows?imported
About?to?enable?constraints
Import?terminated?successfully?without?warnings.
Import:?Release?8.1.7.4.0?-?Production?on?Mon?Sep?22?11:49:41?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
Export?file?created?by?EXPORT:V08.01.07?via?conventional?path
Warning:?the?objects?were?exported?by?JIVE,?not?by?you
import?done?in?ZHS16GBK?character?set?and?ZHS16GBK?NCHAR?character?set
.?.?importing?table????????????????"HS_ALBUMINBOX"?????????12?rows?imported
.?.?importing?table????????????????"HS_ALBUM_INFO"?????????47?rows?imported
.?.?importing?table???????????????????"HS_CATALOG"?????????13?rows?imported
.?.?importing?table??????????"HS_CATALOGAUTHORITY"??????????5?rows?imported
.?.?importing?table?????????"HS_CATEGORYAUTHORITY"??????????0?rows?imported

.?.?importing?table?????????????????"JIVEUSERPROP"??????????4?rows?imported
.?.?importing?table????????????????????"JIVEWATCH"??????????0?rows?imported
.?.?importing?table???????????????????"PLAN_TABLE"??????????0?rows?imported
.?.?importing?table???????????????????"TMZOLDUSER"??????????3?rows?imported
.?.?importing?table??????????????????"TMZOLDUSER2"??????????3?rows?imported
About?to?enable?constraints

Import?terminated?successfully?without?warnings.
查詢發現仍然導入了USER表空間
$?sqlplus?bjbbs/passwd
SQL*Plus:?Release?8.1.7.0.0?-?Production?on?Mon?Sep?22?11:50:03?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:
Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
SQL>?select?table_name,tablespace_name?from?user_tables;
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
HS_ALBUMINBOX??????????????????USERS
HS_ALBUM_INFO??????????????????USERS
HS_CATALOG?????????????????????USERS
HS_CATALOGAUTHORITY????????????USERS
HS_CATEGORYAUTHORITY???????????USERS
HS_CATEGORYINFO????????????????USERS
HS_DLF_DOWNLOG?????????????????USERS

JIVEWATCH??????????????????????USERS
PLAN_TABLE?????????????????????USERS
TMZOLDUSER?????????????????????USERS
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
TMZOLDUSER2????????????????????USERS
45?rows?selected.
2.回收用戶unlimited tablespace權限SQL*Plus:?Release?8.1.7.0.0?-?Production?on?Mon?Sep?22?11:50:03?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:
Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
SQL>?select?table_name,tablespace_name?from?user_tables;
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
HS_ALBUMINBOX??????????????????USERS
HS_ALBUM_INFO??????????????????USERS
HS_CATALOG?????????????????????USERS
HS_CATALOGAUTHORITY????????????USERS
HS_CATEGORYAUTHORITY???????????USERS
HS_CATEGORYINFO????????????????USERS
HS_DLF_DOWNLOG?????????????????USERS

JIVEWATCH??????????????????????USERS
PLAN_TABLE?????????????????????USERS
TMZOLDUSER?????????????????????USERS
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
TMZOLDUSER2????????????????????USERS
45?rows?selected.
這樣就可以導入到用戶缺省表空間
SQL>?create?user?bjbbs?identified?by?passwd
??2??default?tablespace?bjbbs
??3??temporary?tablespace?temp
??4??/
??2??default?tablespace?bjbbs
??3??temporary?tablespace?temp
??4??/
SQL>?grant?connect,resource?to?bjbbs;
Grant?succeeded.
SQL>?grant?dba?to?bjbbs;
Grant?succeeded.
SQL>?revoke?unlimited?tablespace?from?bjbbs;
Revoke?succeeded.
SQL>?alter?user?bjbbs?quota?0?on?users;
User?altered.
SQL>?alter?user?bjbbs?quota?unlimited?on?bjbbs;
User?altered.
SQL>?exit
Disconnected?from?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
重新導入數據Grant?succeeded.
SQL>?grant?dba?to?bjbbs;
Grant?succeeded.
SQL>?revoke?unlimited?tablespace?from?bjbbs;
Revoke?succeeded.
SQL>?alter?user?bjbbs?quota?0?on?users;
User?altered.
SQL>?alter?user?bjbbs?quota?unlimited?on?bjbbs;
User?altered.
SQL>?exit
Disconnected?from?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
$?imp?bjbbs/passwd?file=bj_bbs.dmp?fromuser=jive?touser=bjbbs?grants=n
Import:?Release?8.1.7.4.0?-?Production?on?Mon?Sep?22?12:00:51?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
Export?file?created?by?EXPORT:V08.01.07?via?conventional?path
Warning:?the?objects?were?exported?by?JIVE,?not?by?you
import?done?in?ZHS16GBK?character?set?and?ZHS16GBK?NCHAR?character?set
.?.?importing?table????????????????"HS_ALBUMINBOX"?????????12?rows?imported
.?.?importing?table????????????????"HS_ALBUM_INFO"?????????47?rows?imported
.?.?importing?table???????????????????"HS_CATALOG"?????????13?rows?imported
.?.?importing?table??????????"HS_CATALOGAUTHORITY"??????????5?rows?imported
.?.?importing?table?????????"HS_CATEGORYAUTHORITY"??????????0?rows?imported
.?.?importing?table??????????????"HS_CATEGORYINFO"??????????9?rows?imported
.?.?importing?table???????????????"HS_DLF_DOWNLOG"??????????0?rows?imported
.
.?.?importing?table?????????????????????"JIVEUSER"????????102?rows?imported
.?.?importing?table?????????????????"JIVEUSERPERM"?????????81?rows?imported
.?.?importing?table?????????????????"JIVEUSERPROP"??????????4?rows?imported
.?.?importing?table????????????????????"JIVEWATCH"??????????0?rows?imported
.?.?importing?table???????????????????"PLAN_TABLE"??????????0?rows?imported
.?.?importing?table???????????????????"TMZOLDUSER"??????????3?rows?imported
.?.?importing?table??????????????????"TMZOLDUSER2"??????????3?rows?imported
About?to?enable?constraints
Import?terminated?successfully?without?warnings.
SQL>?select?table_name,tablespace_name?from?user_tables;
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
HS_ALBUMINBOX??????????????????BJBBS
HS_ALBUM_INFO??????????????????BJBBS
HS_CATALOG?????????????????????BJBBS
HS_CATALOGAUTHORITY????????????BJBBS
.
JIVETHREAD?????????????????????BJBBS
JIVETHREADPROP?????????????????BJBBS
JIVEUSER???????????????????????BJBBS
JIVEUSERPERM???????????????????BJBBS
JIVEUSERPROP???????????????????BJBBS
JIVEWATCH??????????????????????BJBBS
PLAN_TABLE?????????????????????BJBBS
TMZOLDUSER?????????????????????BJBBS
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
TMZOLDUSER2????????????????????BJBBS
45?rows?selected.
Import:?Release?8.1.7.4.0?-?Production?on?Mon?Sep?22?12:00:51?2003
(c)?Copyright?2000?Oracle?Corporation.??All?rights?reserved.
Connected?to:?Oracle8i?Enterprise?Edition?Release?8.1.7.4.0?-?64bit?Production
With?the?Partitioning?option
JServer?Release?8.1.7.4.0?-?64bit?Production
Export?file?created?by?EXPORT:V08.01.07?via?conventional?path
Warning:?the?objects?were?exported?by?JIVE,?not?by?you
import?done?in?ZHS16GBK?character?set?and?ZHS16GBK?NCHAR?character?set
.?.?importing?table????????????????"HS_ALBUMINBOX"?????????12?rows?imported
.?.?importing?table????????????????"HS_ALBUM_INFO"?????????47?rows?imported
.?.?importing?table???????????????????"HS_CATALOG"?????????13?rows?imported
.?.?importing?table??????????"HS_CATALOGAUTHORITY"??????????5?rows?imported
.?.?importing?table?????????"HS_CATEGORYAUTHORITY"??????????0?rows?imported
.?.?importing?table??????????????"HS_CATEGORYINFO"??????????9?rows?imported
.?.?importing?table???????????????"HS_DLF_DOWNLOG"??????????0?rows?imported

.?.?importing?table?????????????????????"JIVEUSER"????????102?rows?imported
.?.?importing?table?????????????????"JIVEUSERPERM"?????????81?rows?imported
.?.?importing?table?????????????????"JIVEUSERPROP"??????????4?rows?imported
.?.?importing?table????????????????????"JIVEWATCH"??????????0?rows?imported
.?.?importing?table???????????????????"PLAN_TABLE"??????????0?rows?imported
.?.?importing?table???????????????????"TMZOLDUSER"??????????3?rows?imported
.?.?importing?table??????????????????"TMZOLDUSER2"??????????3?rows?imported
About?to?enable?constraints

Import?terminated?successfully?without?warnings.
SQL>?select?table_name,tablespace_name?from?user_tables;
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
HS_ALBUMINBOX??????????????????BJBBS
HS_ALBUM_INFO??????????????????BJBBS
HS_CATALOG?????????????????????BJBBS
HS_CATALOGAUTHORITY????????????BJBBS

JIVETHREAD?????????????????????BJBBS
JIVETHREADPROP?????????????????BJBBS
JIVEUSER???????????????????????BJBBS
JIVEUSERPERM???????????????????BJBBS
JIVEUSERPROP???????????????????BJBBS
JIVEWATCH??????????????????????BJBBS
PLAN_TABLE?????????????????????BJBBS
TMZOLDUSER?????????????????????BJBBS
TABLE_NAME?????????????????????TABLESPACE_NAME
------------------------------?------------------------------
TMZOLDUSER2????????????????????BJBBS
45?rows?selected.