IMP-00013 問題及解決方法
昨天在生產上出現了一個問題,客戶的dba發現,使用臨時使用者匯出了一個dump之後,無法匯入測試環境,報錯是常見的
IMP-00013: only a DBA can import a file exported by another DBA
這個問題一般碰到的處理方法就是在給目標環境的使用者賦予dba許可權,或者細粒度一些,賦予imp_full_database的許可權,就能解決。
我檢查了一下目標環境,是11g的庫
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
SQL> show parameter insta
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string TEST02
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL> select * from dba_sys_privs where grantee = 'TESTAPP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO
7 rows selected.
--檢視使用者的role,已經有dba了。
SQL> select * from dba_role_privs where grantee ='TESTAPP';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES NO
TESTAPP TESTAPP_ALL YES NO
TESTAPP DBA NO NO
dba許可權已經存在,為了儘快解決,嘗試revoke,再賦予一次dba許可權(需要注意,revoke dba許可權可能導致quota unlimited on tablespace的許可權丟失)
再次嘗試匯入,竟然還是同樣的錯誤。
檢視其它的dba使用者,發現default欄位是YES,檢視metalink ID 949279.1,裡面也給出了類似問題的解決方法。
SQL> select * from dba_role_privs where grantee ='N1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
N1 DBA NO YES
再次嘗試。
SQL> ALTER USER TESTAPP DEFAULT ROLE ALL;
User altered.
啟用後,再次驗證,發現dba許可權已經default了。
SQL> select * from dba_sys_privs where grantee = 'TESTAPP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO
7 rows selected.
SQL> select * from dba_role_privs where grantee ='TESTAPP';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES YES
TESTAPP TESTAPP_ALL YES YES
TESTAPP DBA NO YES
再次匯入,就沒有碰到問題。
IMP-00013: only a DBA can import a file exported by another DBA
這個問題一般碰到的處理方法就是在給目標環境的使用者賦予dba許可權,或者細粒度一些,賦予imp_full_database的許可權,就能解決。
我檢查了一下目標環境,是11g的庫
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
SQL> show parameter insta
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string TEST02
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL> select * from dba_sys_privs where grantee = 'TESTAPP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO
7 rows selected.
--檢視使用者的role,已經有dba了。
SQL> select * from dba_role_privs where grantee ='TESTAPP';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES NO
TESTAPP TESTAPP_ALL YES NO
TESTAPP DBA NO NO
dba許可權已經存在,為了儘快解決,嘗試revoke,再賦予一次dba許可權(需要注意,revoke dba許可權可能導致quota unlimited on tablespace的許可權丟失)
再次嘗試匯入,竟然還是同樣的錯誤。
檢視其它的dba使用者,發現default欄位是YES,檢視metalink ID 949279.1,裡面也給出了類似問題的解決方法。
SQL> select * from dba_role_privs where grantee ='N1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
N1 DBA NO YES
CAUSE
The granted roles are not activated (DEFAULT_ROLE is NO).SOLUTION
First activate the roles with:
SQL> alter user usr001 default role all;
再次嘗試。
SQL> ALTER USER TESTAPP DEFAULT ROLE ALL;
User altered.
啟用後,再次驗證,發現dba許可權已經default了。
SQL> select * from dba_sys_privs where grantee = 'TESTAPP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO
7 rows selected.
SQL> select * from dba_role_privs where grantee ='TESTAPP';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES YES
TESTAPP TESTAPP_ALL YES YES
TESTAPP DBA NO YES
再次匯入,就沒有碰到問題。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-775832/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- IMP-00013: only a DBA can import a file exported by another DBA 問題及解決方案ImportExport
- Nacos 常見問題及解決方法
- Hadoop常見問題及解決方法Hadoop
- KMP常見問題及解決方法【Z】KMP
- Solaris 常見問題及解決方法(轉)
- 問卷調查中常見問題及解決方法
- JS中toFixed()方法的問題及解決方案JS
- 對IMP-00013問題的思考
- SERVICE問題解決方法
- vue渲染時閃爍{{}}的問題及解決方法Vue
- Python執行緒安全問題及解決方法Python執行緒
- 安裝mysql資料庫及問題解決方法MySql資料庫
- Oracle 常見的錯誤問題及解決方法Oracle
- 常見問題及解決
- Laravel5.5 升級到 5.7 問題及解決方法Laravel
- goland中npm無法使用的問題及解決方法GoLandNPM
- UG程式設計常遇到的問題及解決方法程式設計
- C++二進位制相容問題及解決方法C++
- 解決問題通用方法論
- As常見問題解決方法
- 遇到問題的解決方法
- "undefined reference to" 問題解決方法Undefined
- 問題解決方法有三
- Josephus問題解決方法一
- Josephus問題解決方法二
- Git常見問題及解決Git
- Harbor搭建及配置 問題解決
- 跨域問題及解決方案跨域
- redis安裝及問題解決Redis
- 常見問題及解決方案
- MAC 安裝 VMAF 遇到的問題及解決方法記錄Mac
- 5大常見開源軟體問題及解決方法
- 客戶服務常見問題及解決方法介紹
- windows Server 2008常見問題及解決方法WindowsServer
- css高度塌陷問題解決方法CSS
- **PHPNotice:Undefinedindex:…問題的解決方法PHPUndefinedIndex
- windows xp共享問題解決方法Windows
- MYSQL亂碼問題解決方法MySql