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