SoFunction
Updated on 2024-07-15

oracle database how to use exp and imp commands to import and export data

I. Exp command to export data

Export data

Export all tables under a specified user but without data rows

command: exp username/password@TEST:PORT/SID owner=username rows=n 	file=E:\dmp\ log=E:\dmp\

owner: usernameSpecify the user to export the data to
rows: n for no data rows
file: Specify the output dmp file path

II. Importing data with the Imp command

1. Create tablespace

1.1 (Default creation) Create tablespace Default size

Sql> create tablespace username datafile 'username' size 30G;

1.2 (Autogrowth creation) Create tablespace Default size Autogrowth autoextend on Initial value 50m Maximum value 30G

Sql> create tablespace username logging datafile '' size 50m  autoextend on next 50m maxsize 30720m extent management local;
  • size: file size
  • next: the size of each increase
  • maxsize: the maximum size of the file, even if there is no limit, the maximum is only 32G.

2. Create users and specify tablespaces

Sql> create user username identified by username default tablespace username;

3. User authorization

Sql> grant dba to username;

4. Importing data and executing commands

command：imp username/password@TEST:PORT/SID fromuser=username	touser=username file=E:\dmp\
  • file: Specifies the path to the dmp file
  • fromuser: username specifies the user whose objects need to be imported.
  • touser: username specifies the user to be imported, must be used together with fromuser.

III. Other orders

1. Delete users

1.1 View all user commands:

SQL>select * from dba_users;

1.2 Delete User command:

SQL>drop user username CASCADE;

2. Delete tablespace

2.1 View tablespace command:

SQL>select * from dba_tablespaces;

2.2 Multiple ways to delete a tablespace (choose one)
2.2.1. deletes an empty tablespace but does not include the physical file command:

SQL>drop tablespace username;

2.2.2. Delete Non-Empty Tablespace, But Not Containing Physical Files command:

SQL>drop tablespace username including contents;

2.2.3. Delete Empty Tablespace Containing Physical Files Command.

SQL>drop tablespace username including datafiles;

2.2.4.(Recommended) Delete Non-Empty Tablespaces Containing Physical Files command:

SQL>drop tablespace username including contents and datafiles;

2.2.5. If a table in another tablespace has constraints, such as foreign keys, associated with fields in a table in this tablespace, add the cascade command.

SQL>drop tablespace username including contents and datafiles cascade;

3. Query tablespace size

Sql>select tablespace_name, round(sum(bytes) / 1024 / 1024, 2) as size_mb       	from dba_data_files group by tablespace_name;

4. Check the tablespace situation

Sql>select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'USERNAME';

5. Query the tablespace to see if self-increment is set.

Sql>select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'USERNAME';

summarize

To this article on how to use oracle database exp and imp commands to import and export data to this article, more related oracle exp and imp commands to import and export data content, please search for my previous posts or continue to browse the following related articles I hope you will support me more in the future!