Simple example of Oracle data pump Import and Export

This article is suitable for UF NC, UAP series products. Other cases are for reference only.

Cut the crap and start getting to the point:

Steps:
--Create TABLE space
--data and index must be paired.
CREATE tablespace nnc_data01 datafile ' E:\ORACLE\...\nnc_data01.dbf ' SIZE 500M autoextend on NEXT 50M EXTENT MANAGEMENT LO CAL UNIFORM SIZE 256K;
CREATE tablespace nnc_data02 datafile ' E:\ORACLE\...\nnc_data02.dbf ' SIZE 300M autoextend on NEXT 50M EXTENT MANAGEMENT LO CAL UNIFORM SIZE 256K;
CREATE tablespace nnc_data03 datafile ' E:\ORACLE\...\nnc_data03.dbf ' SIZE 500M autoextend on NEXT 100M EXTENT MANAGEMENT L ocal UNIFORM SIZE 512K;

CREATE tablespace nnc_index01 datafile ' E:\ORACLE\...\nnc_index01.dbf ' SIZE 500M autoextend on NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE tablespace nnc_index02 datafile ' E:\ORACLE\...\nnc_index02.dbf ' SIZE 300M autoextend on NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE tablespace nnc_index03 datafile ' E:\ORACLE\...\nnc_index03.dbf ' SIZE 500M autoextend on NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
User:
--Create User
CREATE USER new identified by new DEFAULT tablespace Newtbs;
--User Authorized DBA
GRANT Connect,dba to new;
File storage directory:
--Build a physical location (manually create a new folder locally):
CREATE DIRECTORY dmp_dir as ' F:\dir ';--dmp_dir is the alias of the actual path
--To see if the directory was created successfully
SELECT * from Dba_directories;
--Grant users Read and Write permissions
Grant Read,write on directory Dmp_dir to new;
Export Users:
--EXPDP user/Password @ database directory= alias dumpfile= data file logfile= log;
--Example: EXPDP new/[email protected] Directory=dmp_dir dumpfile=new.dmp logfile=dmp_dir:new.log;
--Remote: EXPDP new/[email protected]:1521/orcl schemas=new directory=dmp_dir dumpfile =new.dmp logfile=new.log; (dmp_ Dir must be on a remote machine instead of local)

To import Users:
--IMPDP user/Password @ database directory= alias dumpfile= data file remap_schema= old User: New user remap_tablespace= old tablespace: New table Space full=y;
Example: IMPDP new/[email protected] Directory=dmp_dir dumpfile=new.dmp remap_schema=old:new Remap_tablespace=oldtbs:newtbs Full=y;

Then put some supplementary query code:

------------------------------------------

--Delete Directory
Drop directory Directory_name;
--View all users in the current database
Select username from dba_users
--Delete User
Drop userxxcascade;--deleted the user, just deleted the user under the schema objects, will not delete the corresponding tablespace.

--View Table space
SELECT * from Dba_tablespaces;
--View the relationship between the user and the default table space
Select Username,default_tablespace from Dba_users;
--View Tablespace file path
SELECT * from Dba_data_files order by file_id;
--Delete Table space
DROP tablespace tablespace_name including CONTENTS and datafiles;
--Change the default table space
Alter user hwpj_study default Tablespace nnc_data02;

Simple example of Oracle data pump Import and Export