-
Oracle provides two tools imp.exe and exp.exe for importing and exporting data respectively. These two tools are located in the Oracle_home/bin directory.
Export dataexp
1 Completely export the database ATSTestDB with the username system and password 123456 to c:export.dmp
exp system/123456@ATSTestDB file=c:export.dmp full=y
Among them, ATSTestDB is the database name, system is the account in the database, and 123456 is the password.
2 Export all related resources of the system user and sys user in the database (tables, stored procedures, methods, views, etc.)
exp system/123456@ATSTestDB file= c:export.dmp owner=(system,sys)
3 Export the tables sys.table1 and owbsys.table2 in the database
exp system/123456@ATSTestDB file= c:export.dmp tables=(sys.table1, owbsys.table2)
Note that you need to add the schema name of the table. If not, it will default to the table of the currently connected user. Of course, the account you connect to must have permissions on the corresponding table.
4 Export the data whose field Title starts with "GangGe" in table1 in the database
exp system/123456@ATSTestDB file= c:export.dmp tables=(table1) query=" where Title like 'GangGe%'"
The slash "" followed by a colon is used to escape the character colon " " ", because it is followed by a conditional query statement. Only one Query parameter can be specified. If the Query is to be for multiple tables, so similarly, there are only There can be one table, or multiple tables, and the conditions in the query can be run on these tables. Otherwise, you have to write a few more exp statements.
After exporting, you may find that the data is relatively large. We can use some compression tools to re-compress the data, such as winzip, winrar, 7zip and other third-party tools. Similarly, exp supports a parameter for users to directly compress data: compress = y. This parameter can be added directly to the end of the command to compress the data while exporting.
Import dataimp
We know how to extract data, so when restoring, we need to use the imp command to load the exported data.
1 Load c:export.dmp data into ATSTestDB
imp system/123456@ATSTestDB file=c:export.dmp
Okay, when importing data, an error may be reported. Why? There are two main reasons:
A. The imported objects (tables, views, methods, etc.) do not originally belong to the currently connected user.
B. The imported object already exists under the specified user of the database
C. The original user of the imported object is no longer in this database.
All objects are imported into the specified account:
Imp system/123456@ATSTestDB file=c:export.dmp fromuser=sys touser=system
Among them, fromuser=sys is the original owner of the object in the .dmp file, and touser=system is the new Owner of the imported object.
Ignore/insert data
Imp system/123456@ATSTestDB file=c:export.dmp ignore=y
Among them, ignore=y tells imp.exe to insert data directly into the corresponding object (and if there are other objects in the imported object, such as constraints, indexes, etc., they will be created after the data is inserted).
2 Load the specified tables table1, table2
imp system/123456@ATSTestDB file=C:export.dmp tables=(table1,table2)
3 Ignore loading constraints
Sometimes when importing data, we don’t need to import its constraints, such as some foreign key constraints, etc., we can add the parameter constraints=N
imp system/123456@ATSTestDB file=C:export.dmp tables=(table1,table2) constraints=N
4 Do not load indexes (such as unique indexes)
imp system/123456@ATSTestDB file=C:export.dmp tables=(table1,table2) indexs=N
5 Only load the structure, not the data
If you only want to define the structure of the table (constraints, triggers), and do not need the data inside, you can add the parameter rows=N
imp system/123456@ATSTestDB file=C:export.dmp tables=(table1,table2) rows=N
For the above operation, the object system of the login operation is the administrator. If it is not an administrator, but an ordinary user, then this user must have the right to create and delete objects. The objects may include common objects such as tables, views, methods, stored procedures, etc. Why does "might" include? Because this depends on whether related types of objects are involved when importing and exporting.
At this point, have you mastered the above simple Oracle data import and export commands? I believe it's enough.