Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities
›
Platform as a Service (PaaS)
›
Oracle Technology
›
Oracle Database
Oracle Export and Import Utility
Oracle Export and Import Utility
Oracle Export and Import Utility
|
01-21-2011, 05:31 AM,
|
|||
|
|||
Oracle Export and Import Utility
Oracle provides Export and Import utilities for the transfer of data from one database to another. It enables writing of data i.e. Export of one database into Operating system files in binary format. These OS files in binary format can only be traced by IMPORT utility to move the data to the new owner. Efficiency of Export and Import Utilities 1. Archive data for future reference and restore database object definitions 2. Enable data movement between the databases which may be of different versions 3. Transport tablespace(s) between databases Export/Import utility prove to be useful in situations of database corruptions and user errors. They can be used for logical database recovery. They can also be used for partial exporting of objects. User can take export and import few tables or specified tablespace. 2. Export Export utility extract database objects along with their dependent and referenced objects. The export utility creates a binary format dump file at the specified location. Export Syntax Code : exp username/password file=file.dmp log=log.txt statistics=none In the syntax, username/password is the source database username and password file.dmp is the name of the file where the data gets backed up. log.txt is the log file which contains relevant information about the export status 3. Import The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects with or without data into an Oracle database. Import Syntax Code : imp username/password fromuser=User1 touser=User2 file=samepage.dmp log=implog.txt In the syntax, Username/Password is the username and password of the target Oracle Database User1 is the original DB user from where the data was exported User2 is the new DB user into which the data is being imported file.dmp is the exported database dump file log.txt is file to store the import logs 4. Utility Invoking methods Export and Import can be invoked using below methods. 1. Command-line entries 2. Interactive prompts 3. Parameter files 4. Oracle Enterprise Manager Data Management Wizard Since command line method is the most conventional and widely used method, we shall concentrate our discussion on it. 5. User Requirements •User must possess the CREATE SESSION privilege on source or target Oracle database. •To export or import tables which owned by another user, user must possess the EXP_FULL_DATABASE and IMP_FULL_DATABASE role enabled. 6. Export Parameters and Modes Below table shows the parameter which can be specified during EXPORT utility. Below are the modes of operation of EXPORT. 7. Import Parameter and Modes Below table shows the parameter which can be specified during IMPORT utility. Below are the modes of operation of IMPORT. Order of database object Import Import utility maintains a sequence of object import to avoid redundancy of trigger firing and other considerable scenarios of referential integrity. This sequence is less preferential in case of Full Database Import. 8. Examples 1. Below command line EXPORT command takes export of EMPLOYEE and EXP_ARCHIVE table from CLUB schema. Note that it also includes rows(data) from the table Code sql: exp CLUB/CLUB TABLES=EMPLOYEE,EMP_ARCHIVE rows=y file=tabexp.dmp 2. Below EXPORT command takes export only for the tablespace TBS_ORDERS. Note that transportable tablespace can be exported or imported only by SYSDBA. Code : exp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=tbs_orders file-exptbs.dmp log=exptbs.log 3. The below command line EXPORT command imports the objects owned by CLUB schema into ORCL user of SYSTEM database using the export file EXPFULL.dmp Code sql: imp system/manager FROMUSER=CLUB TOUSER=ORCL file=EXPFULL.dmp |
|||
« Next Oldest | Next Newest »
|
Possibly Related Threads… | |||||
Thread | Author | Replies | Views | Last Post | |
Oracle Architecture in less than 10 minutes | admin | 2 | 8,939 |
12-31-2019, 11:12 PM Last Post: viggora |
|
Manual Oracle Uninstall | admin | 2 | 5,327 |
12-26-2019, 11:33 PM Last Post: viggora |
|
oracle database 12c installation error | Ahmad Mujeeb | 2 | 3,863 |
12-24-2019, 06:57 AM Last Post: viggora |
|
Oracle error messages | Pryia Rai | 2 | 4,155 |
12-24-2019, 04:10 AM Last Post: viggora |
|
Oracle 11gR2 How to find user session | Sara Khan | 2 | 3,474 |
12-24-2019, 03:15 AM Last Post: viggora |
|
ORACLE HOME issue with DBCA | Richard | 3 | 5,537 |
12-24-2019, 01:29 AM Last Post: viggora |
|
SQL Performance Analyzer in Oracle Database 11g | admin | 2 | 11,337 |
12-23-2019, 07:17 PM Last Post: viggora |
|
Oracle 11g Upgrade | Sara Khan | 0 | 4,319 |
01-25-2011, 04:30 PM Last Post: Sara Khan |
Users browsing this thread: 2 Guest(s)
Current time: 10-04-2024, 12:09 AM
Powered By MyBB, © 2002-2024 MyBB Group.