Oracle Import and Export data

Source: Internet
Author: User
Tags dba

Data export:

1 full export of database test, user Name System Password Manager exported to D:/daochu.dmp
Exp System/manager@test file=d:/daochu.dmp full=y

(Remote: Exp jeesite/[email protected]:1521/orcl file=d:/daochu.dmp owner= (jeesite))
2 Exporting the system user in the database to the SYS user's table
Exp system/manager@test file=d:/daochu.dmp owner= (System,sys)
3 Exporting Tables Inner_notify, Notify_staff_relat in the database
Exp Aichannel/[email protected] file= d:/data/newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)
4 Export the field filed1 in table table1 in the database with data beginning with "00"
Exp system/manager@test file=d:/daochu.dmp tables= (table1) query=/"where filed1 like ' 0% '/"
The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression.

It can also be implemented by adding compress=y to the above command.

Import of data

Create a user with a user name and password and give DBA authority

CMD command line import DMP (make sure the user has DBA authority, and the cmd command line is run as administrator, grant the User DBA Authority command: Grant CONNECT,RESOURCE,DBA to username;)


Alter user acharts (for user name) default tablespace acharts;
1 Import the data from the D:/DAOCHU.DMP into the test database.
Imp jeesite/[email protected] full=y file=d:daochu.dmp

Or


Imp jeesite/[email protected] file=d:\daochu.dmp full=y ignore=y

IMP username/password @ database name file=dmp path full= "Y"


Imp ACHARTS/ACHARTS@127.0.0.1:1521/ORCL file= "? C:\Users\wd\Desktop\acharts.dmp "full=" Y "

The user name and password should be the same as the user name and password when you export the remote database


Delete User
Drop USER username Cascade
SQL Code
--Delete empty tablespace, but no physical file
Drop Tablespace tablespace_name;
--Delete non-empty table spaces, but not physical files
Drop tablespace tablespace_name including contents;
--Delete empty table space, including physical files
Drop tablespace tablespace_name including datafiles;
--Delete a non-empty tablespace containing physical files
Drop tablespace tablespace_name including contents and datafiles;
--Add Cascade CONSTRAINTS if the table in the other tablespace is associated with a field of a table in this table space with a constraint such as a foreign key.
Drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

Plsql the correct steps for importing and exporting tables

Originally always direct tools->import talbes->oracle import results found sometimes error: Some tables can not be imported correctly, Baidu+googel solution is as follows:

Export steps:

1 Tools->export User Object Select option, Export. sql file

2 Tools->export tables-> Oracle Export Selection option exporting. dmp files

Import steps:

1 tools->import tables->sql inserts import. SQL file

2 tools->import talbes->oracle Import and then importing the DMP file

Click 1 after the import is the gray button, you need to click 2 Refresh, you can import

The import and export of the database was successful

======================= explanation =====================================================================

Tools->export User Objects exports the build table statements (including the storage structure)

Tools->export tables contains three ways to export:

Three ways can be exported table structure and data, online said three methods are different, as follows:

Oracle export,sql insert,pl/sql Developer
The first is the file format exported to. DMP, which is binary, can be cross-platform, can also contain permissions, and is also very efficient, most widely used
The second is exported as. sql files, can be viewed with a text editor, versatility is better, but less efficient than the first, suitable for small data import and export. In particular, it is important to note that there are no large fields (Blob,clob,long) in the table, and if so, you will not be able to export it (prompt below:
table contains one or more LONG columns cannot export in SQL Format,user PL/Developer format instead) can be exported in the first and third ways. The third is exported to. Pde format, the. PDE is PL/SQL Developer's own file format, can only be imported and exported by PL/SQL developer, and cannot be viewed with the editor.

Personally prefer the second method, after all, is the SQL file, I can also see, want to change also convenient

CMD command line import DMP (make sure the user has DBA authority, and the cmd command line is run as administrator, grant the User DBA Authority command: Grant CONNECT,RESOURCE,DBA to username;)

IMP username/password @ database name file=dmp path full= "Y"
Imp ACHARTS/ACHARTS@127.0.0.1:1521/ORCL file= "? C:\Users\wd\Desktop\acharts.dmp "full=" Y "

Oracle Import and Export data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.