ORACLE

exp bemis/bemis@bemis file=E:\dbBak\BEMIS.dmp owner=bemis log=E:\dbBak\bemis.log

 

create directory dpdata1 as 'G:\orclDB';
DROP DIRECTORY directory_name

expdp 登录名/密码@orcl schemas=用户 dumpfile=pxjd.dmp DIRECTORY=dpdata2目录名;

增加文件大小

alter database datafile 'E:\dong\lzfxmis\db\LZFXMIS.ORA' autoextend on next 100m maxsize 4000M;
Expdp zftang/zftang DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=zftang,gwm logfile=expdp_test.log

impdp LZFXMIS1/LZFXMIS1@orcl directory=dplzfxmis  remap_schema=bemis:LZFXMIS1 REMAP_TABLESPACE=bemis:LZFXMIS_DB1 dumpfile=LZFXMIS_DB.DMP logfile=impdb_lzfxmis1.log

impdp bemis/bemis@bemis schemas=bemis dumpfile =IMPdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job

impdp LZFXMIS/LZFXMIS DUMPFILE=LZFXMIS_1205.DMP LOGFILE=LZFXMIS_1205.LOG DIRECTORY=LZFXMIS_DIR JOB_NAME=LZFXMIS_dir REMAP_TABLESPACE=LZFXMIS_DB1:LZFXMIS_TABLESPACE REMAP_SCHEMA=LZFXMIS1:LZFXMIS

select username,default_tablespace from user_users;--查看当前用户表空间
select file_name , tablespace_name from dba_data_files;--查看表空间位置

 

使用expdp要先在数据库中创建directory,并给相应的用户read,write权限.

SQL>create dexp和empdp的区别irectory dmpdir as ‘/u01/dmdir’;

SQL>grant read,write on directory to test;

job_name:此次导出进程使用的名称,方便跟踪查询(可选)

SCHEMA:导出某一个SCHEMA下的所有数据

 

查看目录Directory位置

select * from dba_directories;

创建目录

CONNECT system/manager 
CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump'; 
CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs'; 
GRANT read, write ON DIRECTORY my_dir TO scott; 
GRANT read, write ON DIRECTORY my_logdir TO scott;

cmd
exp bemis/bemis@orcl file=E:\BEMIS_DB\bemis.dmp owner=bemis log=E:\BEMIS_DB\exp.log

----创建表空间
CREATE TABLESPACE BEMIS
LOGGING
DATAFILE 'D:\BEMISDB\BEMISDB.ora' SIZE 100M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--删除用户
drop user BEMIS cascade;

---创建用户
CREATE USER bemis PROFILE DEFAULT
IDENTIFIED BY bemis DEFAULT TABLESPACE bemis
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED
ON bemis
ACCOUNT UNLOCK;
GRANT CONNECT TO bemis;
GRANT DBA TO bemis;

---导入数据CMD
imp bemis/bemis@orcl file=d:\bemis.dmp full=y

alter system set wallet open identified by "LZFX";
alter database open;

 

--导入导出单个表

exp   user/pw   tables=(tbname)   file=xxx.dmp

imp username/password@本地net服务名 file=xxx.dmp fromuser=xx touser=xx tables=(tablename)

 

导入导出特定表

expdp.exe c##jnxxyf/jnxxyf@orcl schemas=c##jnxxyf dumpfile=jnxxyf20180625.dmp DIRECTORY=DONG_DIR tables=base_userinfo,Base_StaffOrganize,Base_UserRole,BASE_ORGANIZATION,BASE_ROLES version=11.2.0.1.0

impdp.exe c##test/test@orcl remap_schema=c##jnxxyf:c##test dumpfile=jnxxyf20180625.dmp logfile=test directory=dong_dir job_name=my_job