Common commands for Oracle data import

Oracle Database Full Import Export: cmd command line mode Oracle database cmdfile database server constraintsoracle data Import Export IMP/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing and one for formal use. Execution environment: can be executed in SQLPLUS.EXE or DOS (command line), DOS can be executed when the directory is installed in Oracle 8i/ora81/the bin is set to the global path, and the directory under which EXP.EXE and IMP.EXE files are used to perform import and export. Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file. SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function. The following is an example of importing and exporting. Data export:1 full export of database test, user Name System Password Manager export to d:/DAOCHU.DMP in exp system/[email protected] File=d:/daochu.dmp full=y2Export the system user in the database and the SYS user's table to the EXP system/[email protected] File=d:/daochu.dmp owner=(System,sys)3Export the tables in the database inner_notify, Notify_staff_relat exp Aichannel/[email protected] file= d:/data/newsmgnt.dmp tables=(Inner_notify,notify_staff_relat)4 filed1 the field in table Table1 in the database to "00"the beginning of the data export exp System/[email protected] 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. You can also add compress after the command above=y to achieve. Import of Data1 will d:/the data in the DAOCHU.DMP is imported into the test database. IMP System/[email protected] file=d:/daochu.dmp imp maindb/[email protected] full=y file=E:\YCSY Data 20141122\maindb20141219.dmp imp aichannel/[email protected] full=y file=file= d:/data/newsmgnt.dmp ignore=y may have a problem, because some tables already exist, and then it's an error, and the table is not imported. Add Ignore to the back .=y can do it. 2 will d:/Table table1 Import imp system in Daochu.dmp/[email protected] File=d:/daochu.dmp tables=(table1) basically the above import and export is enough. In many cases, you must first delete the table and then import it. Note: The operator must have sufficient permissions to be prompted for insufficient permissions. Database, you can connect to the. You can use tnsping test to get the database test to connect. Appendix I: Actions to increase import data permissions for users first, start the SQL*Puls Second, with system/manager Landing Sqlplus system/[email protected] as third, create user username identified by password (if you have already created a user, this step can be omitted) IV, GRANT create User,drop user,alter user   , CREATE any VIEW, DROP no view,exp_full_database,imp_full_database, dba,connect,resource,create SESSION to user name Five, run-cmd-Enter the directory where the DMP file resides, IMP userid=system/manager full=y file=*. DMP or IMP userid=system/manager full=y file=filename.dmp Execution Example: F:/work/oracle_data/backup>imp userid=test/test full=y file=inner_notify.dmpimp userid=ycsydb/ycsydb full=y file=ycsymaindb.dmpimp userid=ycsy/test full=y file=inner_notify.dmpimp userid=ycsydb/ycsydb full=y file=E:\YCSY Data 20141122\ycsy20141219.dmp screen display import:release8.1.7.0.0-production on Thu February 16 16:50:05 2006(c) Copyright2000Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release8.1.7.0.0-Productionwith The partitioning optionjserver Release8.1.7.0.0-the Production is exported by export:v08 via a regular path.01. 07 The created file has completed the ZHS16GBK character set and the import export server in the ZHS16GBK NCHAR character set uses the UTF8 NCHAR character set (possible ncharset conversions). Importing Aichannel objects into Aichannel . . Importing Tables"Inner_notify"4 rows are imported ready to enable constraints ...  The import was terminated successfully, but a warning appears. Appendix II: Oracle does not allow direct changes to the table owner, using export/import can achieve this. First establish the Import9.par, then, use the command as follows: Imp parfile=/filepath/Import9.par Example Import9.par content is as follows: Fromuser=Tgpms Touser=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different) ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y BUFFER=409600file==/backup/ctgpc_20030623.dmp Log==/backup/import_20030623.log

Import data steps in an Oracle database

============================ the Completed Import Database command starts =====================Maindbsql>Create tablespace maindb datafile' D:/PROGRAMDATA/ORACLE11G/YCSY_DATASPACE/MAINDB.DBF 'size 100M autoextend on next 32M maxsize 1024M extent management local; Create temporary tablespace maindb_temp tempfile' D:/PROGRAMDATA/ORACLE11G/YCSY_DATASPACE/MAINDB_TEMP.DBF 'size 100M autoextend on next 32m maxsize 1024M extent management local SQL>create user maindb identified by Maindbdefaulttablespace maindb temporary tablespace maindb_temp; SQL>GRANT CREATE SESSION, create any TABLE, create any VIEW, create all INDEX, create any PROCEDURE, ALTER A NY TABLE, ALTER any PROCEDURE, drop no table, drop any VIEW, drop all INDEX, drop any PROCEDURE, SELECT any tabl E, INSERT any table, UPDATE any table, DELETE any table to maindb Grant DBA to Maindb; Imp maindb/[email protected] full=y file= ' e:/ycsy/data/maindbdata20150428.dmp ' ignore=y log = ' e:/ycsy/data/maindbdata20150428.log ' ============================ Completion of Import Database command end =====================

Long-use commands for fully importing data in Oracle

User Login Database
Sqlplus System/[email protected] as Sysdba
default tablespace maintbs Delete table space    drop tablespace maintbs Delete temporary tablespace drop temporary tablespace maintbs create temporary tablespace create Temporary tablespace maintbs    ' d:/programdata/oracle11g/ycsy_dataspace/maintbs.dbf '    size 100M    Autoextend on    next 32m maxsize 1024M    ' d:/programdata/oracle11g/ycsy_dataspace/maintbs.dbf '   size 100M autoextend on  next 32M maxsize 1024M  ' d:/programdata/oracle11g/ycsy_dataspace/ Maintbs.dbf ' autoextend on NEXT 500M MAXSIZE UNLIMITED

Common commands for Oracle data import