使用数据汞
Oracle数据库系统有一个默认的目录对象DATA_PUMP_DIR,可以通过查询dba_directories获得。
create directory DATA_PUMP_DIR as
'c:\data';
删除directory:drop directory
data_pump_dir
然后需要用OS命令创建文件夹并赋予使用expdp、impdp命令的OS用户对该文件夹的读写权限。
grant read ,write on directory DATA_PUMP_DIR to
scott;
要更改dumpdir目录的路径,则为如下语句:
create or replace directory dumpdir as
'/home/dumpfiles';
expdp
不同用户、表空间之间移动数据
expdp scott/tiger parfile=scott_par.txt
直接使用命令行:expdp scott/tiger@ORCL schemas=SCOTT
directory=DATA_PUMP_DIR dumpfile=ORCL.dmp logfile=ORCL.log
当参数文件与命令行参数共存时哪个在后,则哪个参数生效。
操作模式
整库模式:导出或导入整个数据库,对应impdp/expdp命令中的full参数
schema模式:导出或导入SCHEMA下的自由对象,对应impdp/expdp命令中的SCHEMAS参数
表模式:表模式导出或导入对应impdp/expdp命令中的TABLES参数
表空间模式:对应impdp/expdp命令中的TABLESAPCES参数
传输表空间模式:对应impdp/expdp命令中的TRANSPORT_TABLESPACES参数,生成的dump文件中并不包含具体的逻辑数据,只导出相关对象的元数据,逻辑数据仍然在表空间的数据文件中,导出时需要将元数据和数据文件同时复制到目标端服务器。
参数
QUERY=[schema.][table_name:]
query_clause:选择条件e.g.query=A:"where id>5",B:"where
id<30"
SAMPLE=[[schema_name.]table_name:]sample_percent:指定导出数据的百分比e.g.SAMPLE=A:50
EXCLUDE=object_type[:name_clause][,...]:指定不导出表对象e.g.EXCLUDE=INDEX:"like
'IDX_TBLA%'",CONSTRAINT:"like 'CKC_TBLB%'",grant
INCLUDE同EXCLUDE
FILESIZE和PARALLEL:配合指定导出dump时使用多个线程e.g.filesize=500m
parallel=3
跨数据库链接方式导出数据
如果要导出的数据量较大,并不推荐使用数据库链方式导出数据。因为跨数据库链方式生成的每一个字节,都必然需要占用网络带宽。
impdp
不同用户、表空间中移动数据
impdp scott/tiger parfile=scott_par.txt
参数
CONTENT:指定导入对象。共有三个参数:
ALL,导入对象定义和数据;
DATA_ONLY,只导入数据;
METADATA_ONLY,只导入对象定义e.g.content=metadata_only。由于data
pump导出时不会处理外部表对象的数据文件及其它相关文件,因此导入后原SCHEMA中的外部表访问报错。需要手动处理外部表对象。
TABLE_EXISTS_ACTION:控制若导入的表存在执行的操作。共有四个参数:
SKIP,跳过该表,继续下一个对象的处理;
APPEND,向现有表中添加数据;
TRUNCATE,TRUNCATE当前表,然后再添加记录;
REPLACE,删除并重建表对象,然后再向其中添加数据。
REMAP_SCHEMA=source_schema:target_schema[,source_schema:target_schema]:重定义对象所属SCHEMA
e.g.REMAP_SCHEMA=A:X,B:Y
REMAP_TABLESPACE用法同REMAP_SCHEMA
跨数据库链接方式导入数据
使用network_link参数,可以直接从远端数据库服务器重导入数据到本地的Oracle数据库
优化导入
PARALLEL参数可以增加线程来加快导入速度 e.g.dumpfile=ZJLPD_%U.dmp
parallel=2
命令行交互模式
只要任务没做完,就可以通过Ctrl+C组合键退出当前的输出模式,进入到命令行交互管理模式界面中。之后再执行impdp/expdp命令的同时制定attach参数可以连接到当前正在执行的导入/导出任务e.g.
expdp jss/jss
attach=sys_export_schema_01,attach的值可以在执行impdp/expdp时通过JOB_NAME参数指定,也会显示在命令日志信息的初始化部分。参数如下:
ADD_FILE=[directory_object]file_name [,...]:追加Dump文件,仅用于EXPDP
e.g.add_file=info_%U.dmp
CONTINUE_CLIENT:退出当前的命令行交互模式,进入日志记录模式。相当于又回到按Ctrl+C组合键之前的模式
EXIT_CLIENT:退出当前的交互模式,返回操作系统命令行,任务转为后台运行。
FILESIZE:指定生成的Dump文件最大容量,仅用于EXPDP。e.g.filesize=1g
KILL_JOB:中断正在执行的任务,并断开当前所有进入命令行管理界面的连接,然后返回到操作系统的命令行窗口。被kill掉得任务无法再次通过start_job命令启动,只能重新执行impdp/expdp命令。
PARALLEL:增加并行度 e.g.parallel=4
STOP_JOB:停止当前正在运行的任务
START_JOB:被停止的任务再次启动
STATUS:查看当前任务的详细状态信息
将Oracle数据传给其他软件
set line 120
set pagesize 100
set feedback off
spool f:\excels\emp
select * from emp;
spool off
保存为emp.sql,并在sqlplus下运行@emp.sql,在F:\Excels目录中就会生成一个emp.LST的正文文件,可以用excel打开并导入它。
从其他软件中导入oracle数据库
1.创建DIRECTORY
create directory data_dir as 'f:\oracle\etl'
create directory log_dir as 'f:\oracle\etl'
可通过directory_path查询是否创建成功,他们都为SYS用户所拥有,授予当前用户读写权限,之后可通过dba_tab_privs查询是否授权成功
2.创建外表
创建完成之后可通过user_tables查询,该表已存在,但没存储在任何表空间中,因为它是一个外表。
查询外表
user_external_tables
3.从数据库导入外表数据
set line 120
set pagesize 49990
set heading off
set feedback off
alter session set nls_date_language='American';
spool f:\oracle\etl\sales_delta.txt (外表所在的地址)
select * from sales where rownum<=49990;
spool off
完成以上步骤后就能使用外表像普通表一样查询了,但是表上不能进行DML操作