Ways to export all tables for a user under Oracle

Ways to export all tables for a user under Oracle

Scott/tiger is the user name and password, HandsOn is the exported instance name

Export data by user (user name is written in owner) exp scott/[email protected] File=scott_back Owner=scott

Export data as a table (the name of all tables is written in Talbes) exp Scott/[email protected] tables= (EMP, dept) file=scott_back_tab

Export data by Tablespace (Tablespaces is written in Tablespace name) exp System/[email protected] tablespaces= (users) file=tbs_users

Exporting data using a parameter file exp System/handson parfile= ' C:\parameters.txt

1, Cmd->exp->username/[email Protected]> All the way back---> Input export the name of the library to all the tables, views, stored procedures, functions, operations, such as all the mess are exported;

2, pl/sql--> tool---) export user objects You can find the things you want to guide.

Both of them can be very convenient!

Export TABLE: Exp scott/[email protected] tables= (dept,emp) file=tab1.dmp

Export User: Exp system/[email protected] Owner=scott file=usr1.dmp

To export a database:

1. Fully export exp System/[email protected] full=y inctype=complete file=full1.dmp

2.2. Incremental export exp system/[email protected] full=y inctype=incremental file=inc1.dmp

3.3. Cumulative export exp System/[email protected] full=y inctype=cumulative file=cum1.dmp

4. Import Table: Imp system/[email protected] file=c:\tab1.dmp tables= (dept,emp) Touser=scott

5. Import User: Imp System/[email protected] file=usr1.dmp Fromuser=scott Touser=scott

Import Database:

1. Full Library import imp system/[email protected] file=full1.dmp full=y

2.2. Incremental Import

1) Import Database latest information imp system/[email protected] Inctype=system full=y file=inc7.dmp

2) 2) Import the most recent full export file imp system/[email protected] Inctype=restore full=y file=full1.dmp 3) Import all cumulative export files Imp system/[email Protected] Inctype=restore full=y file=cum1.dmp 4) Import files for the most recent incremental export imp System/[email protected] Inctype=restore full=y file =inc1.dmp

Common commands

EXP User/[email protected] file=file.dmp rows=y log=log_name where Username/password for export user's user name and password instance for exported instance name

rows Specifies whether rows in the table are to be imported

Export the table structure under a user as a SQL file: Use the command as if exported are DMP files can use Plsql software in Tools--àexport user object if you do not go around table space, do not tick the include storage Select the exported path file name, export can be exported directly

Example 1: (This method is limited to oracle9i version above)

1. set pagesize 0
2. set long 90000
3. set Span style= "FONT-SIZE:9PT;" > feedback off
4. set echo off
5. spool get_allddl.sql
6. connect username/[email protected];
7. select Dbms_metadata. Get_ddl ('

Example 2: First log in to the Oracle database under Sqlplus, then paste the following into the Sqlplus: set feedback off; Set pagesize 0; Set heading off; Set verify off; Set Linesize 200; Set Trimspool on; Spool c: \ Database backup. bat; Select ' Exp username/[email protected] tables= ' | | table_name| | ' File= ' | | table_name| | '. DMP Triggers=n ' from User_tables; Spool off; Set feedback on; Set pagesize 9999; Set heading on; Set verify on; When exit is complete, the. bat file is found in the C: \ database, the batch file is edited, the first and last lines are deleted, and all the tables under that user are exported after saving the batch file.

Methods for importing all tables from a user under Oracle

Import the entire file into the database imp System/[email protected] file=item_back.dmp ignore=y full=y Imports the Scott User's table into the Martin user imp System/[email Protected] File=scott_back Fromuser=scott touser=martin tables= (emp,dept)

Importing data using a parameter file imp system/oracle parfile= ' C:\parameters.txt

Common commands Imp username/password file=file.dmp fromuser=user1 touser=user2 rows=y

Where Username/password is the user name and password that is imported into the database Fromuser the user touser the database when the database is backed up, general and username

Ways to export all tables for a user under Oracle