建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
IMP Import Operation: Successfully Importing Database Data into Sys User
Introduction:
Oracle’s Import (IMP) utility is a powerful tool that allows users to import data from an export file. When importing data, IMP maps the file contents to an existing database schema, creating tables and inserting data according to the file contents. In this article, we will discuss the steps involved in importing database data using IMP, with specific focus on importing data into the sys user.
Step 1: Exporting Data
Before importing data, a user must first export the data from the source database. An export file is created using Oracle’s Export (EXP) utility. The export file contns the data to be imported into the destination database. An export file can be created by executing a command similar to the following:
exp userID/password@[database alias] file=[export file name] tables=[table name]
The above command exports a specific table to the export file. Users can export multiple tables by specifying multiple table names in the command.
Step 2: Preparing for the Import
Before importing data, several steps must be taken to prepare the destination database. Primarily, the schema to map the data must be created.
Using SQL*Plus, connect to the destination database as sys and execute the following commands:
CREATE USER [user name] IDENTIFIED BY [password];
GRANT CONNECT, RESOURCE TO [user name];
The above creates a new database schema that has the necessary rights to import data. Following this, the user must create a directory object that points to the directory where the export file is located. This can be done using the following command:
CREATE DIRECTORY [directory name] AS ‘full path of the directory’;
Step 3: Importing Data
Having prepared the destination database, the user can now execute the IMP utility to import the data. The following command can be used to import data into the sys user schema:
imp sys/[password]@[database alias] file=[export file name] fromuser=[source user] touser=[destination user] directory=[directory name] log=[log file name] full=y
Explanation of command:
– sys/[password]@[database alias]: This is the connection string for connecting to the destination database as sys. Users can substitute the correct values for username and password as necessary.
– file: This is the name of the export file to import data from.
– fromuser: This specifies the name of the user in the export file from which data is to be imported.
– touser: This specifies the name of the schema in the destination database to map the data to.
– directory: This specifies the directory object created earlier.
– log: This is the name of the log file that documents the import process.
– full=y: This specifies that all data in the export file is to be imported.
Step 4: Verifying the Import
After the import process is complete, users must verify that the data has been successfully imported. This can be done by executing SQL queries on the destination database. Ensure that the data imported matches the data exported.
Conclusion:
This article has discussed the steps involved in importing database data using Oracle’s IMP utility, with specific focus on importing data into the sys user. Users must first export the data from the source database, prepare the destination database, and execute the IMP utility to import data. Subsequently, users can verify that the data imported matches the data exported. With the steps outlined in this article, users will be able to import large amounts of data efficiently and effectively.
相關(guān)問(wèn)題拓展閱讀：
- 如何用sys as sysdba權(quán)限連接數(shù)據(jù)庫(kù)進(jìn)行Exp/Imp
- oracle的imp使用方法
如何用sys as sysdba權(quán)限連接數(shù)據(jù)庫(kù)進(jìn)行Exp/Imp
如何用sys as sysdba權(quán)限連接數(shù)據(jù)庫(kù)進(jìn)行Exp/Imp
Windows:
exp ‘sys/change_on_install@instance as sysdba’ tables=scott.emp
Unix or Linux (you need to ‘escape’ the single quote):
exp /’sys/change_on_install@instance as sysdba/’ tables=scott.emp
VMS (use …):
exp “‘sys/change_on_install@instance as sysdba'” tables=scott.emp
小結(jié)：
1、USERID 必須是命令行中的之一個(gè)參數(shù)。（如imp help=y里顯示的內(nèi)容）
所以如exp ‘ as sysdba’等價(jià)于exp USERID=‘a(chǎn)s sysdba’，即可以省略USERID不寫(xiě)。
2、imp/exp命令里參數(shù)與參數(shù)間的間隔是用空格來(lái)區(qū)分的（等號(hào)兩邊的空格不算），于是像如下語(yǔ)句：exp USERID= sys/as sysdba就不能被imp/exp工具所理解（參數(shù)USERID= sys/123456可以解析出來(lái)，但是as sysdba不知道如何理解了，as或sysdba又不屬于設(shè)定的參數(shù)名）。而oracle公司設(shè)計(jì)的軟件里一般用單引號(hào)將一字符串常量包括起來(lái)。將上面語(yǔ)句改為exp USERID= ’sys/as sysdba‘的話(huà)，imp/exp工具就認(rèn)為sys/as sysdba整體是一個(gè)字符串，故而就是參數(shù)USERID的一個(gè)值。
3.
如果是寫(xiě)在參數(shù)文件中,則連接字符串需要用雙引號(hào)了:
USERID=” as sysdba”
Parameter file.
You can also specify the username in the parameter file. In this situation, you have to enclose the connect string with a double quote character. However, to prevent possible security breaches we advice you to stop using the USERID parameter in a parameter file.
Contents of file exp.par:
USERID=”sys/change_on_install@instance as sysdba”
TABLES=scott.emp
Run export with:
exp parfile=exp.par
注釋?zhuān)篿mp/exp（
impdp/expdp
）默認(rèn)目錄是什么，即parfile=exp.par里的文件exp.par在什么目錄下？
附加：
impdp/expdp
）默認(rèn)目錄是什么
(5)、數(shù)據(jù)泵如何決定文件的路徑
5.1 如果目錄對(duì)象是文件標(biāo)示符的一部分，那么目錄對(duì)象指定的路徑就需要使用。在目跡虧旦錄MY_DIR創(chuàng)建dump文件的示例：
> expdp scott/tiger DUMPFILE=my_dir:expdp_s.dmp NOLOGFILE=Y
5.2 如果目錄對(duì)象不代表一個(gè)文件，那么就需要使用DIRECTORY變量命名的目錄對(duì)象。目錄MY_DIR中創(chuàng)建dump文件，目錄MY_DIR_LOG中創(chuàng)建日志文件的示姿擾例：
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=my_logdir:expdp_s.log
5.3 如果沒(méi)有明確目錄對(duì)象，也沒(méi)有以DIRECTORY變量命名的目錄對(duì)象，那么環(huán)境變量DATA_PUMP_DIR將會(huì)使用。環(huán)境變量是在在運(yùn)行導(dǎo)出和導(dǎo)入數(shù)據(jù)泵應(yīng)用的客戶(hù)端系統(tǒng)中使用操作系空磨統(tǒng)命令定義的，分配給基于客戶(hù)端環(huán)境變量的取值必須和基于服務(wù)端的目錄對(duì)象一致，且必須首先在服務(wù)器端建立。
目錄MY_DIR中創(chuàng)建dump文件和MY_DIR_LOG中創(chuàng)建日志文件的示例：
在使用expdp的客戶(hù)端機(jī)器上，設(shè)定環(huán)境變量：
— On windows, place all expdp parameters on one single line:
C:\> set DATA_PUMP_DIR=MY_DIR
C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp
LOGFILE=my_logdir:expdp_s.log
注意環(huán)境變量DATA_DUMP_DIR對(duì)應(yīng)的目錄名稱(chēng)是大小寫(xiě)敏感的。設(shè)定錯(cuò)誤的DATA_PUMP_DIR環(huán)境變量會(huì)報(bào)錯(cuò)，例如：DATA_PUMP_DIR=My_Dir：
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name My_Dir is invalid
5.4 如果之前三種情況都沒(méi)有創(chuàng)建目錄對(duì)象，作為一個(gè)具有權(quán)限的用戶(hù)(例如具有EXP_FULL_DATABASE或IMP_FULL_DATABASE角色)，那么數(shù)據(jù)泵試圖使用默認(rèn)的基于服務(wù)器端的目錄對(duì)象，DATA_PUMP_DIR。理解數(shù)據(jù)泵不會(huì)創(chuàng)建DATA_PUMP_DIR目錄對(duì)象是非常重要的。僅當(dāng)授權(quán)用戶(hù)未使用任何之前提到的機(jī)制創(chuàng)建的目錄對(duì)象時(shí)，才會(huì)嘗試使用DATA_PUMP_DIR。這個(gè)默認(rèn)的目錄對(duì)象必須首先由DBA創(chuàng)建。不要將這個(gè)和同名的基于客戶(hù)端的環(huán)境變量相混淆。
首先，清空DATA_PUMP_DIR環(huán)境變量：
C:\> set DATA_PUMP_DIR=
創(chuàng)建DATA_PUMP_DIR的目錄：
CONNECT SYSTEM/MANAGER
CREATE OR REPLACE DIRECTORY data_pump_dir AS ‘D:\DataPump’;
GRANT read, write ON DIRECTORY data_pump_dir TO scott;
— On windows, place all expdp parameters on one single line:
C:\> expdp system/manager@my_db_alias DUMPFILE=expdp_s.dmp
LOGFILE=expdp_s.log SCHEMAS=scott
如果SCOTT用戶(hù)不是授權(quán)用戶(hù)，不能使用默認(rèn)的DATA_PUMP_DIR。
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null
用戶(hù)SCOTT的解決方法：如上面5.3，SCOTT可以設(shè)置環(huán)境變量DATA_PUMP_DIR為MY_DIR：
— On windows, place all expdp parameters on one single line:
C:\> set DATA_PUMP_DIR=MY_DIR
C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp
LOGFILE=expdp_s.log SCHEMAS=scott
或者這種特定場(chǎng)景下，用戶(hù)SCOTT也可以有目錄DATA_PUMP_DIR的讀和寫(xiě)權(quán)限：
— On windows, place all expdp parameters on one single line:
C:\> set DATA_PUMP_DIR=DATA_PUMP_DIR
C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp
oracle的imp使用方法
你如果舊庫(kù)的dmp里有多個(gè)用戶(hù)的話(huà)，你是絕對(duì)不能用full yes的方法導(dǎo)入的，否則你后悔。
oracle的imp使用方法具有三種模式（完全、用戶(hù)、表）
1、旁歷完全：
IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
2、用戶(hù)模式：
IMP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC
這樣用戶(hù)SONIC的所有對(duì)象被導(dǎo)入到文件中。必須指定FROMUSER、TOUSER參數(shù)，這樣才能導(dǎo)入數(shù)據(jù)。
3、表模式：
EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)
這樣用戶(hù)SONIC的表SONIC就被導(dǎo)入。
擴(kuò)展資料
ORACLE數(shù)據(jù)庫(kù)有兩類(lèi)備份方法。之一類(lèi)為物理備賀啟槐份，該方法實(shí)現(xiàn)數(shù)據(jù)庫(kù)的完整恢復(fù)，但數(shù)據(jù)庫(kù)必須運(yùn)行在歸擋模式下（業(yè)務(wù)數(shù)據(jù)庫(kù)在非歸擋模式下運(yùn)行），且需要極大的外部存儲(chǔ)設(shè)備，例如磁帶庫(kù)。
第二類(lèi)備份方式為邏輯備份，業(yè)務(wù)數(shù)據(jù)庫(kù)采用此種方式，此方法不需要數(shù)據(jù)庫(kù)運(yùn)行在歸擋模式下，不但備份簡(jiǎn)單，而且可以不需要外部存儲(chǔ)設(shè)備。
IMP常用選項(xiàng)
1、FROMUSER和TOUSER，使用它們實(shí)現(xiàn)將數(shù)據(jù)從一個(gè)SCHEMA中導(dǎo)入到另外一個(gè)SCHEMA中。例如：假設(shè)禪友做exp時(shí)導(dǎo)出的為test的對(duì)象,現(xiàn)在想把對(duì)象導(dǎo)入用戶(hù)：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1
2、IGNORE、GRANTS和INDEXES，其中IGNORE參數(shù)將忽略表的存在，繼續(xù)導(dǎo)入，這個(gè)對(duì)于需要調(diào)整表的存儲(chǔ)參數(shù)時(shí)很有用，可以先根據(jù)實(shí)際情況用合理的存儲(chǔ)參數(shù)建好表，然后直接導(dǎo)入數(shù)據(jù)。
而GRANTS和INDEXES則表示是否導(dǎo)入授權(quán)和索引，如果想使用新的存儲(chǔ)參數(shù)重建索引，或者為了加快到入速度，可以考慮將INDEXES設(shè)為N，而GRANTS一般都是Y。例如：impuserid=test1/test1file=expdat.dmpfromuser=test1touser=test1indexes=N
imp system/manager file=bible_db log=dible_db full=y ignore=y
system/告枯manager是用戶(hù)名和密碼
file=bible_db 是要導(dǎo)入賣(mài)迅的文件
log=dible_db是生成日志的襪配洞名稱(chēng)
imp system/manager file=bible_db log=dible_db full=y ignore=y
system/manager是用戶(hù)名和密碼
file=bible_db 是要導(dǎo)入的文件
log=dible_db是生成日志的名稱(chēng)
full=y是導(dǎo)入整個(gè)文件
ignore=y是忽略創(chuàng)建錯(cuò)誤臘咐
數(shù)據(jù)庫(kù)是a.dmp我想導(dǎo)入的耐局液時(shí)候，數(shù)據(jù)庫(kù)是b
imp system/manager@服務(wù)SID file=bible_db.dmp log=dible_db full=y ignore=y fromuser=a touser=b
詳細(xì)用法可在“運(yùn)行”中輸入cmd進(jìn)入命令窗口鍵入昌物
‘IMP HELP=Y’ 獲取幫助信息提示
imp -help就可以查看相應(yīng)的參數(shù)信息，相信你也會(huì)寫(xiě)了
關(guān)于imp數(shù)據(jù)庫(kù)導(dǎo)入sys用戶(hù)的介紹到此就結(jié)束了，不知道你從中找到你需要的信息了嗎 ？如果你還想了解更多這方面的信息，記得收藏關(guān)注本站。
成都創(chuàng)新互聯(lián)科技有限公司，是一家專(zhuān)注于互聯(lián)網(wǎng)、IDC服務(wù)、應(yīng)用軟件開(kāi)發(fā)、網(wǎng)站建設(shè)推廣的公司，為客戶(hù)提供互聯(lián)網(wǎng)基礎(chǔ)服務(wù)！
創(chuàng)新互聯(lián)（www.cdcxhl.com）提供簡(jiǎn)單好用，價(jià)格厚道的香港/美國(guó)云服務(wù)器和獨(dú)立服務(wù)器。創(chuàng)新互聯(lián)——四川成都IDC機(jī)房服務(wù)器托管/機(jī)柜租用。為您精選優(yōu)質(zhì)idc數(shù)據(jù)中心機(jī)房租用、服務(wù)器托管、機(jī)柜租賃、大帶寬租用，高電服務(wù)器托管，算力服務(wù)器租用，可選線(xiàn)路電信、移動(dòng)、聯(lián)通機(jī)房等。
分享文章：IMP導(dǎo)入操作：將數(shù)據(jù)庫(kù)數(shù)據(jù)成功導(dǎo)入sys用戶(hù)(imp數(shù)據(jù)庫(kù)導(dǎo)入sys用戶(hù))
文章轉(zhuǎn)載：http://www.dlmjj.cn/article/dpdgppi.html
