Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to import and export data in Oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. Export files in dmp format

-- back up some tables! Exp smsc/smsc file=/data/oracle_bak/dmp/bakup0209_2.dmp tables=\ (send_msg_his,send_msg,recv_msg_his,recv_msg\)-back up the entire database! -- 1exp smsc/smsc file=/data/oracle_bak/dmp/bakupsmmc0209_2.dmp full=y-- mode exp zop/zop@orcl file= D:\ zop_bak.dmp owner=zop log=D:\ zop_ba.log on local 2exp cop/cop@133.96.84.39:1521/coprule file=/home/oracle/cop_20160902.dmp owner=cop log=/home/oracle/cop.log--

two。 Import files in dmp format

-- data import-- 1 imports the data from D:\ daochu.dmp into the TEST database. There may be a problem with imp system/manager@TEST file=d:\ daochu.dmp imp aichannel/aichannel@TEST full=y file=d:\ datanewsmgnt.dmp ignore=y-- because some table already exists, and then it reports an error and does not import the table. -- just add ignore=y at the end. -- 2 Import the table table1 from d:daochu.dmp into imp system/manager@TEST file=d:\ daochu.dmp tables= (table1)-- basically, the above import and export is sufficient. In many cases, the table is deleted completely and then imported. Note: the operator should have sufficient permissions, and it will prompt if the permissions are not enough. You can connect to the database. You can use tnsping TEST to find out whether the database TEST can be connected.

3.sqlloader imports sql files

As shown in the figure, open the command window, @ followed by the path to the sql file

4. Import files in csv format

(1)。 Create a file in .ctl format, such as import.ctl

Load datainfile'Hcsv insert tab2.csv'-- insert_tab2.csv is the path into table "TAB_TIME2" to insert-- TAB_TIME2 is the name of the oracle table to be inserted fields terminated by','--divided by comma (CSV)-- table column name, with multiple fields sorted in data order, separated by commas

(2)。 It is required that the fields in the csv file correspond to the columns of the table, and delete the alias of csv to ensure that the first line begins with formal data.

(3) the empty table in .csv is replaced with 0 or other characters, because imported columns automatically stop when they encounter null. After import, replace the string of 0 with an empty one.

(4) .cmd Open a command prompt

C:\ Users\ luxuefeng > sqlldr userid=luffy/luffy@orcl control=H:/import.ctl log=H:/import.log after reading the above, do you have any further understanding of how to import and export data in Oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.