Oracle数据泵导入导出

首先要说明一下,数据泵只可以在服务端运行,而且数据泵要用到DIRECTORY对象。

一、新建DIRECTORY并授权

#创建DIRECTORY
CREATE DIRECTORY dump_dir AS 'DIRECTORY_FULL_PATH';

#授权
GRANT READ, WRITE ON DIRECTORY dump_dir TO user_id;

二、数据泵导出数据

#导出表
expdp user_id/user_pwd directory=dump_dir dumpfile=table01.dmp tables=table01 logfile=table01.log

#导出方案
expdp user_id/user_pwd directory=dump_dir dumpfile=schema01.dmp schemas=schema01 logfile=schema01.log

#导出表空间
expdp user_id/user_pwd directory=dump_dir dumpfile=tbs01.dmp tablespaces=tbs01 logfile=tbs01.log

#导出数据库
expdp user_id/user_pwd directory=dump_dir dumpfile=db01.dmp full=y logfile=db01.log

三、数据泵导入数据

#导入表
impdp user_id/user_pwd directory=dump_dir dumpfile=table01.dmp tables=table01 

#导入方案
impdp user_id/user_pwd directory=dump_dir dumpfile=schema01.dmp schemas=schema01

#导入表空间
impdp user_id/user_pwd directory=dump_dir dumpfile=tbs01.dmp tablespaces=tbs01

#导入数据库
impdp user_id/user_pwd directory=dump_dir dumpfile=db01.dmp full=y

Oracle与SQL Server同步技术对比

方式 Oracle SQL Server 说明
备份还原 备份还原 备份还原 简单粗暴,无法实时,无法实现增量
日志备份 备库(Dataguard) 数据库镜像(Database Mirroring)
日志传输(Log Shipping)
读写操作受限
集群 RAC(Real Application Clusters) 集群(Database Cluster) RAC配置复杂,SQL Server集群只有单节点工作。实际存储只有一份。
视图 物化视图(Materialized View) 索引视图(Indexed Views) 不可改表结构,如增加字段等。
数据变更捕获 CDC(Change Data Capture) CDC(Change Data Capture) 不够灵活,无法配置只想获取部分事件,数据量很大。
订阅发布 ogg(Oracle Golden Gate)
流复制(Stream Replication)
高级复制(Oracle advanced Replication)
订阅发布(Publish and Subscribe)
数据库复制(Database Replication)
订阅发布(Publish and Subscribe)
最灵活的方式了,但也有限制。如果ogg在源加一列,或订阅发布的快照过期了,就惨了

Oracle并发查询

1. 第一张表加并发

select /*+ PARALLEL(t1, 5) */count(*) 
from table1 t1, table2 t2 
where t1.pk = t2.pk 

2. 两张表都加并发

select /*+ PARALLEL(t1, 5)  PARALLEL(t2, 5) */count(*) 
from table1 t1, table2 t2 
where t1.pk = t2.pk 

trunc函数示例

1、时间处理

select trunc(sysdate) from dual  
--2011-3-18 今天的日期为2011-3-18

select trunc(sysdate, 'mm')   from   dual  
--2011-3-1 返回当月第一天.

select trunc(sysdate,'yy') from dual  
--2011-1-1 返回当年第一天

select trunc(sysdate,'dd') from dual  
--2011-3-18 返回当前年月日

select trunc(sysdate,'yyyy') from dual  
--2011-1-1 返回当年第一天

select trunc(sysdate,'d') from dual  
--2011-3-13 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual   
--2011-3-18 14:00:00 当前时间为14:41   

select trunc(sysdate, 'mi') from dual  
--2011-3-18 14:41:00 TRUNC()函数没有秒的精确

2、数字处理

select trunc(123.458) from dual 
--123

select trunc(123.458,0) from dual 
--123

select trunc(123.458,1) from dual 
--123.4

select trunc(123.458,-1) from dual 
--120

select trunc(123.458,-4) from dual 
--0

select trunc(123.458,4) from dual  
--123.458

select trunc(123) from dual  
--123

select trunc(123,1) from dual 
--123

select trunc(123,-1) from dual 
--120

修改Oracle最大连接数

--查询最大连接数
show parameter processes; 

--最大连接数调整为500
alter system set processes=500 scope=spfile; 

not in 优化为 not exists

最近迁移数据库的时候,发现not in比not exists效率差太多了

--not in 直接卡死
insert into table1(
select * from table2 t2@oraclegate where t2.pk not in (select pk from table3 t3));

--not exist则很快处理完成
insert into table1(
select * from table2 t2@oraclegate where not exists (select pk from table3 t3 where t3.pk=t2.pk));

其实数据量并不大,不知道是不是用gateway的问题。

Oracle常用命令

--重新分析表
analyze table table1 compute statistics for table for all indexes for all indexed columns;
--解析执行计划
explain plan for select xx1,xx2,xx3,xx4,xx5 from table1 t1 where t1.xx1 = 'yyy';

关闭和打开Oralce10g自动收集COB信息

前几天,发现平台的一支程序突然运行的很慢,经分析后,发现是数据库查询变得超级慢。
用OB9分析后,发现索引正常,没办法最后重启了数据库后,速度直接飚上来了。
但好景不长,第二天早上4点后,又变成龟速,只好找公司DBA帮忙分析问题。

最后发现是Oracle的自动统计分析Job,每天自动进行统计,然后优化器就不走索引,而走统计分析的结果。
而我们的表有较多的删除操作,很快统计分析的结果就不可靠了,结果速度很快就下来了。

最后,禁用之,搞定:)

--状态查询
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

--sysdba
--关闭
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

--sysdba
--启用
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

Oracle11g导入到Oracle10g

1、导出

set PATH=D:\Oracle11g\product\11.2.0\dbhome_1\BIN;%PATH%

expdp USERID/PWD schemas=XXX VERSION=10.2 DIRECTORY=data_pump_dir DUMPFILE=XXX.dmp LOGFILE=exp.log

pause

2、导入


set PATH=D:\Oracle11g\product\11.2.0\dbhome_1\BIN;%PATH%

impdp USERID/PWD schemas=XXX DIRECTORY=data_pump_dir DUMPFILE=XXX.dmp LOGFILE=imp.log

pause

Oracle中大字容量字段

LONG
可变长的字符串数据,最长2G,LONG具有VARCHAR2列的特性,可以存储长文本一个。
表中最多一个LONG列

LONG RAW
可变长二进制数据,最长2G

CLOB
字符大对象Clob 用来存储单字节的字符数据

NCLOB
用来存储多字节的字符数据

BLOB
用于存储二进制数据

BFILE
存储在文件中的二进制数据,这个文件中的数据只能被只读访。但该文件不包含在数据库内。
bfile字段实际的文件存储在文件系统中,字段中存储的是文件定位指针。
bfile对oracle来说是只读的,也不参与事务性控制和数据恢复。
  
其中CLOB,NCLOB,BLOB都是内部的LOB(Large Object)类型,最长4G,没有LONG只能有一列的限制。