Oracle Import and Export

1. Import and export Oracle databases
Commands and PL/SQL tools can be used for import and export.
 
1. Command Method
Database Export:
1) completely export the database TEST. The username "system Password manager" is exported to "D: \ daochu. dmp". exp system/manager @ TEST file = d: \ daochu. dmp full = y
2) Export the tables bookinfor and dept in the database
Exp doris/doris @ TESTDB file = d: \ data \ daochu. dmp tables = (bookinfor, dept)
3) export data based on conditions
Exp doris/doris @ doris file = d: \ daochu1.dmp tables = (dept) query = 'where dept_id = 1'
-- Query clause
4) only export tables without exporting data
Exp doris/doris @ doris file = D: \ daochu2.dmp rows = N tables = (bookinfor)
-- Rows = N indicates that only tables are exported and data is not exported.
5) export data by user
Exp scott/tiger @ doris file = e: \ scott_back owner = scott;
6) export data by tablespace
Exp sys/doris @ doris tablespaces = (users) file = e: \ user_tab; -- only DBA can export the complete database or tablespace.
 
Database Import:
1) import data from D: \ daochu. dmp to the TEST database.
Imp system/manager @ TEST file = d: \ daochu. dmp
Imp doris/doris @ testdb full = y file = d: \ data \ daochu. dmp ignore = y
-- Ignore = y will skip the error
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
2) Import table 1 in d: \ daochu. dmp
Imp system/manager @ TEST file = d: \ daochu. dmp tables = (table1)
3) import the dept table of user user1 in database TEST to user user2 and password user2.
Imp user2/user2 @ TEST file = d: \ daochu. dmp fromuser = user1 touser = user2 tables = (dept)
Ii. Batch import of data to oracle (take excel as an example)
After exploration, we solved the problem of importing an excel table into an oracle database and passed the experiment. I mainly use two methods. These methods can also be used to import data in other formats to oracle. Here we only use excel as an example.
Method 1: use SQL * Loader
This is a lot of methods, as long as the target table already exists in oracle Data.
The general steps are as follows:
Use sqlloader: (if the table does not exist, you must create the table first)
1. Open MicroSoft Excel 2007
2. File (F) → new (N) → workbook → enter the data to be imported
For example, the data format in Excel is as follows:
Id
Username
Password
2
Bb
342
3
Cc
234
4
Dd
433
4. File (F) → save as (A) →
The storage type is: delimiter, the name is text.txt, and saved to C :\
5. Connect to SQL * Plus or PL/SQL
Log on to and create a table structure.
SQL> create table test
(
Id number, -- serial number
Username varchar2 (10), -- User Name
Password varchar2 (10), -- password
);
6. Create the files required for SQL * Loader input data and save them to C: \. Edit them in Notepad:
Control File: input. ctl. The content is as follows:
Load data -- 1. Control File ID
Infile 'C: \ text.txt '-the data file to be input is text.txt
Append into table test -- 3. append records to table test
Fields terminated by X '09' -- 4. The field is terminated on X '09' and is a TAB)
(Id, username, password) -- defines the sequence of Columns
A. insert, which is the default mode. The table is required to be empty when data loading starts.
B. append: Add a new record to the table
C. replace: delete old records and replace them with newly loaded records.
D. truncate, same as above
7. Use the SQL * Loader command in the DOS window to input data.
C: \> sqlldr userid = system/manager control = input. ctl
The default log file name is input. log.
The default bad record file is input. bad.
Method 2 Use PLSQL Developer
Use PLSQL Developer, a common tool.
When a single file is small (less than 100000 rows) and the target table structure already exists-for excel, it will certainly not exceed, because the maximum behavior of an excel file is 65536-you can select all the data Ctrl + V, and then use the PLSQL Developer tool.
The data in the Excel table is as follows. Copy all selected data (do not select the column header)
Id
Username
Password
2
Bb
342
3
Cc
234
4
Dd
433
1. Enter select * from test for update in the SQL window of PLSQL Developer. (The keyword for update must be added)
 
2. Press F8 to execute
3. open the lock and move the cursor to the last row. Place the cursor over the column header of the first column to make the entire column selected, then press Ctrl + C, and then click √ à Close Lock à commit to submit.
Make the first column fully selected
Then, Ctrl + C, and the result is as follows:
Close the lock and then commit it.


Author: neola