Oracle imports dump files

1. Create a logical directory. This command will not create a real directory in the operating system. It is better to create a directory by an administrator such as System.

create or replace directory dpdata1 as 'd:\test\dump';

Default DATA_PUMP_DIR, in the directory C: app Administrator admin orcl dpdump\

2. Check the administrator directory (and see if the operating system exists, because Oracle doesn't care if the directory exists or if it doesn't exist, there's an error)

select * from dba_directories;

3. Give scott users the right to operate in the specified directory, preferably by system administrators.

grant read,write on directory DATA_PUMP_DIR to scott;

IV. Exporting Data
1) Guided by User

expdp scott/tiger@orcl schemas=scott dumpfile=scott.dmp;

2) parallel process

expdp scott/tiger@orcl directory=DATA_PUMP_DIR dumpfile=scott3.dmp parallel=40 job_name=scott3

3) Guided by Table Name

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=DATA_PUMP_DIR;

4) Guided by Query Conditions

expdp scott/tiger@orcl directory=DATA_PUMP_DIR dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5) Table space derivation

expdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6) Import the whole database

expdp system/admin@orcl dumpfile=full.dmp full=y;

V. Restoring Data
1) Import to the specified user
impdp system/admin@orcl DUMPFILE=scott.dmp REMAP_SCHEMA=scott:scott2; (plus constant error? DIRECTORY=DATA_PUMP_DIR)

For the latter scott2, the system may or may not have, if there is no system will automatically establish this user.

2) Change the owner of the table

impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3) Import table space

impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=tablespace.dmp TABLESPACES=example;

4) Import database

impdb system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=y;

5) Additional data

impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;

6. Delete table spaces and users

drop tablespace XXXX including contents and datafiles cascade constraints;
drop user XXXX cascade;

If a program is connecting

select sid,serial# from v$session where username='XXXX';
alter system kill session '767,130';

Expanding Table Space

alter database datafile '/oracle/oradata/orcl/XXXX.dbf' autoextend on next 100m maxsize 4096M;

Create tablespaces and users

create tablespace XXXX datafile '/home/oracle/orcl/XXXX.dbf' size 4096M;
create user XXXX identified by XXXX default tablespace XXXX temporary tablespace TEMP  profile DEFAULT;

Create a table space (self-growing) (table space name XXXX)

create tablespace XXXX logging datafile '/home/oracle/orcl/XXXX.DBF' size 4096M autoextend on next 512M maxsize unlimited;

To grant authorization

-- Grant/Revoke role privileges 
grant connect to XXXX;
grant dba to XXXX;
grant resource to XXXX;
-- Grant/Revoke system privileges 
grant alter system to XXXX;
grant create any job to XXXX;
grant create any library to XXXX;
grant create any sequence to XXXX;
grant create any synonym to XXXX;
grant create any view to XXXX;
grant create database link to XXXX;
grant create external job to XXXX;
grant debug connect session to XXXX;
grant manage scheduler to XXXX;
grant unlimited tablespace to XXXX;

Import data

impdp sys/XXXX DIRECTORY=oracle DUMPFILE=XXXX.dmp SCHEMAS=XXXX
 When table spaces need to be replaced:
Impdp sys/XXXXXX DIRECTORY=oracle DUMPFILE=XXXX.dmp remap_tablespace=old table space: new table space 
username: sys as sysdba
password: XXXX
 Errors encountered in the first execution of the impdp command:
ORA-39006: internal error
ORA-39213: Metadata processing is not available
 Solution:
Log in as sysdba and execute
 exec dbms_metadata_util.load_stylesheets;