Oracle
文章平均质量分 62
wzy0623
25年的数据库、数据仓库、大数据相关工作。《Hadoop构建数据仓库实践》、《HAWQ数据仓库与数据挖掘实战》、《SQL机器学习库——MADlib技术解析》、《MySQL高可用实践》、《Kettle构建Hadoop ETL》、《Greenplum构建实时数据仓库实践》作者。
展开
-
用PLSQL解决世界最难数独(不到1毫秒)
以下两段代码分别用Oracle和PostgreSQL匿名块解“世界最难数独”,声明代码是别人写的,这里只作为兴趣记录与学习。 Oracle代码出自http://www.itpub.net/thread-1071946-2-1.html,解题用时120毫秒。DECLARE TYPE t_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t2_num IS TABLE OF t_num INDEX BY ...原创 2021-07-27 09:56:15 · 549 阅读 · 0 评论 -
Oracle sqlldr快速导入
一、需求 windows上的一个文本文件filename.txt有22008080行,有两列数字,要导入oracle的表中,时间要尽量的短。二、实现方法1. 压缩(zip)上传(scp)文件,把文件从windows传到oracle所在的linux机器上下面步骤都在oracle服务器上执行2. 解压缩unzip filename.zip3. 处理回车换行dos2unix filename.txt4原创 2016-12-27 13:26:02 · 2617 阅读 · 0 评论 -
Oracle 快速卸载数据到文本文件
一、需求 有个需求要从oracle表里导出数据,存成csv文本文件。数据量有4亿多行、25g。最普通的解决方案是在sql*plus使用spool。尽管该方案在某些情况下可行,但它的速度太慢,输出大约每秒1m字节,全部导出需要7个多小时,这是不可接受的,需要快速导出数据。二、解决方案 下面的核心代码出自adrian billington。自定义函数使用utl_file包输出数据,并且使用pipe原创 2016-12-27 13:56:06 · 4347 阅读 · 1 评论 -
GoldenGate安装、配置、实例化
一、环境两台Oracle VM VirtualBox虚拟机host:master1、master2IP:192.168.1.1、192.168.1.2OS:Linux 2.6.32 DB:Oracle 11.2.0.3,启用归档OGG:ogg112101_fbo_ggs_Linux_x64_ora11g_64bit 2.2.3二、安装OGG在master1、master2两台上执行以下步骤:1.原创 2016-12-27 14:48:37 · 3960 阅读 · 0 评论 -
使用RMAN duplicate做GoldenGate实例化
一、背景 GoldenGate从源数据库的事务日志捕获数据,并应用到目标数据库,它提供了一种灵活的、松耦合的体系结构,可以被用于实现几乎所有复制的场景。最大的挑战之一就是为复制建立一个目标库,在目标数据库初始实例化后,应用改变的数据时,确保没有数据冲突,也没有数据丢失。GoldenGateton通过“CSN”(Commit Sequence Number)解决此问题。“CSN”并不是Oracle原创 2016-12-27 15:06:01 · 802 阅读 · 0 评论 -
Oracle 当索引为DESC时获得索引列的名称
这是一个容易被忽视的问题。[sql] view plain copy -- 建表和索引 CREATE TABLE t1 ( a INT, b INT ); CREATE INDEX IDX1 ON T1 (a DESC, b DESC); CREATE INDEX IDX2 ON T1 (a, b); CREATE INDEX IDX3 ON T1 (a A原创 2016-12-28 08:32:45 · 2998 阅读 · 1 评论 -
关系数据库的优化方法论基本是一致的
最近看SQL Server的优化方法论,和Oracle的基本一致,都是以等待事件为为主要度量,采用自上到下的方法,由实例到进程再到数据库对象层层深入,找到需要优化的SQL,再进行相关的优化。SQL Server优化方法论:使用自顶向下的方法。该方法先分析实例级的等待,再通过一系列步骤将其不断细化,直到找出导致大量等待的进程/组件。一旦找出这些进程,就可以集中优化它们了。下面是该方法的主要步骤:1.原创 2016-12-28 08:40:38 · 955 阅读 · 0 评论 -
利用Oracle DBMS_SCHEDULER调用shell脚本文件
今天在itpub上看到个帖子,大意是使用shell脚本取文件列表,和数据库中一个表中记录的文件名相比较,没有的插入到表里去。地址:http://www.itpub.net/thread-1186639-1-1.html实验代码如下:#建立文件filelist.sh如下:#!/bin/bashsource ~/.bash_profilels -la --full-time | awk '/^-原创 2016-12-28 08:53:52 · 2036 阅读 · 0 评论 -
使用DBMS_LOGMNR分析归档日志应用一例
有个项目提出的需求是这样的:部署两个相同的数据库应用A、B,两个库之间没有网络连接,要定期把A库里的数据复制到B库。要求:应用程序不做修改实现增量数据更新(不能重复导入) 我提出了使用DBMS_LOGMNR分析归档日志并redo变化的方案:A库上线前数据库需要启用归档日志每次同步数据时对A库先执行一次日志切换,然后拷贝归档日志文件,拷贝后删除(最新生成的归档日志文件不要拷贝和删除)在B库上使用D原创 2016-12-28 09:58:25 · 1081 阅读 · 0 评论 -
使用Oracle with内嵌视图优化一例
需求:有分类、物资、分类/物资关系三个表,要求按树的遍历方式查询出分类ID、分类/物资名称、从根到当前节点的路径。一个分类下的物资显示在该分类下,同一级分类按序号排序,一个分类下的物资按创建时间排序。[sql] view plain copy-- 创建分类表 CREATE TABLE tab_class ( id NUMBER (8) NOT NULL原创 2016-12-28 10:10:58 · 1116 阅读 · 0 评论 -
Oracle 大数据量去重实验
[sql] view plain copy-- 环境:64位11.2G -- 一、建立测试表,生成2000万测试数据,其中200万重复 CREATE TABLE test_t ( id NUMBER (8) NOT NULL PRIMARY KEY, name VARCHAR2 (32) ); BEGIN FOR i IN 1 .. 180原创 2016-12-28 10:13:39 · 3528 阅读 · 0 评论 -
使用Oracle外部表对大文件排序
问题:对一个一列两亿行的无序的文本文件进行排序,生成一个排好序的新文本文件。1. 生成无序文件,BigFileTest.Java代码如下:[java] view plain copyimport java.io.BufferedWriter; import java.io.FileWriter; import java.util.Random; public class BigFileT原创 2016-12-28 10:16:28 · 621 阅读 · 0 评论 -
Oracle 聚合函数解决聚集连接字符串问题
需求:给定数据表:wm_testcode name1 a1 b1 c2 中2 国2 人需要的结果(分隔符可以由参数输入):code name1 a,b,c2 中,国,人分析:这个问题在可以使用Oracle的wmsys.wm_concat 函数解决:select wt.code, wm_concat(wt.name) names from wm_tes原创 2016-12-28 10:19:05 · 3266 阅读 · 0 评论 -
Oracle 行列转置
1、固定列数的行列转换如student subject grade--------- ---------- --------student1 语文 80student1 数学 70student1 英语 60student2 语文 90student2 数学 80student2 英语 100……转换为语文 数学 英语student1 80 70 60student2 90 80 100……语句如原创 2016-12-28 10:47:31 · 6131 阅读 · 0 评论 -
递归层次汇总查询
在一个数据仓库项目中,人员、组织机构、事实表是这样设计的:组织机构是一个树形结构每一个人员只属于一层组织机构,该层为叶子节点和非叶子节点均可事实表存储每个人员的数据 要求按组织机构层次汇总数据,别且可下钻。[sql] view plain copy-- 建立测试表 -- 1. 组织机构 CREATE TABLE org ( org_id NUMBER, org_原创 2016-12-28 10:53:58 · 4005 阅读 · 1 评论 -
层次树展开问题
在数据仓库的层次建模时,常用递归的方式表示一颗层次树,但有些BI工具的前端不支持递归,所以为了实现数据下钻,可以把一棵递归树进行扩展。[sql] view plain copy-- 建立原始树表,并生成数据 CREATE TABLE TREE ( C_PARENT INTEGER, C_CHILD INTEGER ); Insert into TREE (C_PARENT原创 2016-12-28 10:55:32 · 1019 阅读 · 0 评论 -
在ETL过程中对递归树的历史维护实验
-- 当前递归树 CREATE TABLE TREE_CUR ( C_CHILD VARCHAR2(32 BYTE), C_NAME VARCHAR2(100 BYTE), C_PARENT VARCHAR2(32 BYTE) ); CREATE INDEX IDX1 ON TREE_CUR (C_PARENT); CREATE UNIQUE INDEX原创 2016-12-28 11:06:23 · 719 阅读 · 0 评论 -
与时间有关的树的展开实验
-- 建立递归树历史表,并生成数据 CREATE TABLE TREE_HIS ( ID NUMBER, C_CHILD VARCHAR2(32 BYTE), C_NAME VARCHAR2(100 BYTE), P_ID NUMBER, EFF_DATE DATE, EXP_DATE DATE ); CREAT原创 2016-12-28 11:07:48 · 761 阅读 · 0 评论 -
递归树的平面化实验
/*** 已有维度表: dim_org -- 组织机构,组织为带有历史信息的递归树,其主键为SEQ_DIM_ORG_PK序列生成的代理键 dim_person -- 人员表,带历史信息,org_pk关联到dim_org的代理键 目的: 数据以平面化完整树的形式交付给OLAP工具 功能: 依照dim_org定义固定的三级组织机构,每个人员关联第三级组织机构,dim_person.原创 2016-12-28 11:09:16 · 618 阅读 · 0 评论 -
在MV上建立触发器实验
-- 建立MV测试表 CREATE TABLE tbl1 ( a NUMBER, b VARCHAR2 (20) ); CREATE UNIQUE INDEX tbl1_pk ON tbl1 (a); ALTER TABLE tbl1 ADD (CONSTRAINT tbl1_pl PRIMARY KEY(a)); -- 建立MV日志,单一表聚合视图原创 2016-12-28 11:11:16 · 570 阅读 · 0 评论 -
利用job并行执行Oracle存储过程
-- 建立测试表 CREATE TABLE t (a NUMBER); -- 建立存储过程 CREATE OR REPLACE PROCEDURE p_parallel (p_min IN NUMBER, p_max IN NUMBER) IS BEGIN INSERT INTO t SELECT TRUNC (DBMS_RANDOM.VALUE (p_min,原创 2016-12-28 11:13:26 · 4245 阅读 · 2 评论 -
Oracle生成某一用户下所有表数据的insert语句
功能:生成某一用户下所有数据表数据的insert语句,放入d:\insert.sql文件。限制:只支持number、char、varchar2、date、long、clob数据类型。提示:数据量小还可以,大了就别用这种方式了,会很慢。[sql] view plain copy/* Formatted on 2012-12-27 20:56:24 (QP5 v5.185.11230.41888) *原创 2016-12-28 13:22:03 · 2256 阅读 · 0 评论 -
Oracle中文转拼音函数
最近两个项目都提出从中文转拼音的需求。我以前的做法是:从微软拼音输入法导出字库,建立中文拼音对照表,然后写一个函数从对照表查拼音。这种方法虽然思路简单,但是返回拼音的错误率太高,而且对每个字都要去查一遍3万记录的对照表,效率也不高。 这两天重新写了一个函数来完成此功能,该函数利用了Oracle提供的NLSSORT,具有以下特点:不需要中文字库 与数据库字符集无关 支持O...原创 2016-12-28 15:16:45 · 22121 阅读 · 18 评论 -
Oracle里二进制与十进制的相互转换
Oracle里有内建函数bin_to_num可以将二进制数转换为十进制数,但这个函数的入参是个数不定的0或1:[sql] view plain copySELECT BIN_TO_NUM (1, 1, 1, 1, 0, 1, 1) FROM DUAL; 有时候入参使用一个二进制字符串会更加方便,另外Oracle本身没有提供num_to_bin这样将十进制转换成二进制的函数,于是写了两个自定义函原创 2016-12-28 15:44:35 · 8809 阅读 · 0 评论 -
ora-22992 通过DBLINK 访问远程CLOB表问题
在本地用select语句访问远程,如果远程表有CLOB字段的话则会有错:ora-22992;如果真的想看到clob字段的内容的话就得在本地建立一个表,用下面两条语句:我刚才试验insert into table select * from remote table成功remote table含有CLOB总结:在我的环境中成功(1)create table aaa select * from rem转载 2016-12-28 16:19:03 · 1492 阅读 · 0 评论 -
Oracle聚簇表使用方针
使用下面的方针决定是否使用聚簇表:经常被应用的连接语句访问的表可以使用聚簇表如果应用只是偶尔地使用表连接,或者频繁修改连接列的值,则不要使用聚簇表。修改一行的聚簇键值比修改非聚簇表的值花费更长的时间,因为Oracle可能为了维护聚簇而把被修改的行从一个块迁移到另一个块。如果应用经常对聚簇中的一个表进行全表扫描,则不要使用聚簇表。对聚簇表的全面扫描会比对非聚簇表的全表扫描花费更长的时间。因为表被存储翻译 2016-12-28 16:23:21 · 778 阅读 · 0 评论 -
利用Oracle dbms_pipe实现存储过程之间的通信
应用程序开发人员的需求是这样的:1. 根据条件给每一个国家的商品生成唯一7位随机代码,不同国家之间的商品代码可以相同2. 如果输入标准分隔符的字符串,则解析该字符串作为需要生成的商品ID,为其生成代码,否则为商品表中所有商品ID生成代码3. 代码的每一位要符合相应的规则,例如第一位的规则是[0123],则这位只能是0、1、2、3中的一个数4. 由于可能一次生成大量的代码,这个过程需要较长时间,所以原创 2016-12-28 16:29:10 · 2218 阅读 · 0 评论 -
Oracle 触发器中修改字段值
有个需求看着很简单,但也查了许多资料,还是记录下来。需求是这样的:根据一个表中的某些被修改的字段修改其它字段,用触发器实现。示例代码如下:CREATE TABLE t( ID INT, update_time DATE, other_col VARCHAR2(20));CREATE OR REPLACE TRIGGER trigger_on_t1_change原创 2016-12-28 16:32:03 · 7375 阅读 · 0 评论 -
完全停止Oracle中正在运行的JOB
Killing the Oracle DBMS_JOBJames F. Koopmann, www.dbdoctor.net源地址:http://www.quest-pipelines.com/newsletter-v4/0403_C.htmTake control of Oracle's queue with a step by step approach to getting rid of t转载 2016-12-29 09:04:19 · 3635 阅读 · 0 评论 -
Oracle数据库中的本地索引和全局索引的区别
表可以按range,hash,list分区,表分区后,其上的索引和普通表上的索引有所不同,Oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结。 局部索引local index1. 局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区说,一句话,局部索引的分区机制和表的分区机制一样。2. 如果局部索引的索引列以分区键开头,则称为前转载 2016-12-29 09:10:34 · 5835 阅读 · 0 评论 -
Oracle 10.2 流复制问题(一)—— ORA-01403: no data found
在使用Oracle 10g 流复制时出现过一些问题,现在把解决过程总结一下。 系统配置:HP-UX B.11.31 U ia64、Oracle Enterprise Edition Release 10.2.0.4.0、表级单向流复制问题现象:流复制失效,源表更新,目标表没有更新排错过程: 1) 查看应用错误信息SQL> select apply_name,LOCAL_TRANSACTION_ID原创 2016-12-29 09:13:36 · 827 阅读 · 0 评论 -
Oracle 10.2 流复制问题(二)—— C001: large txn detected
系统配置:HP-UX B.11.31 U ia64、Oracle Enterprise Edition Release 10.2.0.4.0、表级单向流复制问题现象:流复制失效,源表更新,目标表没有更新,没有apply error信息排错过程: 1) 检查源库的alert_.log,发现大量C001: large txn detectedC001: large txn detected (2457原创 2016-12-29 09:14:53 · 724 阅读 · 0 评论 -
Oracle 10.2 流复制问题(三)—— 大事务处理
以下是实践中大事务处理过程:1) 停止流-- 源数据库停止Capture进程connect strmadminBEGINDBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture1');END;/-- 目标数据库停止Apply进程connect strmadminbegindbms_apply_adm.stop_apply(apply_name =原创 2016-12-29 09:16:53 · 1030 阅读 · 0 评论 -
Oracle 10.2 流复制问题(四)—— ORA-01341: LOGMINER OUT-OF-MEMORY in Oracle Streams
系统配置:Linux CentOS 5、Oracle Enterprise Edition Release 10.2.0.1.0、表级单向流复制问题现象:流复制失效,源表更新,目标表没有更新排错过程: 1) 查看捕获进程状态select capture_name,status from dba_capture;Capture status 为ABORTED 2) 查看alert_.logkrvxe原创 2016-12-29 09:18:30 · 894 阅读 · 0 评论 -
Oracle流复制配置最佳实践
一、通用配置1. 使用Oracle 10.2.0.4版本,并且安装所有关键补订包 2. 下游捕获需要源和目标库运行在相同平台上 3. 准备源和目标数据库的redo logs(1) 配置源和目标库的archivelog模式(2) 配置本地归档目标路径,log_archive_dest_1参数,不要使用flash recovery area。 4. 建立流转用表空间:对于下游捕获,只在下游捕获数据库建翻译 2016-12-29 09:19:59 · 3250 阅读 · 0 评论 -
Oracle Dimension (Oracle 维)
在数据仓库环境中,我们通常利用物化视图强大的查询重写功能来提升统计查询的性能,但是物化视图的查询重写功能有时候无法智能地判断查询中一些相关联的条件,以至于影响性能。比如我们有一张销售表sales,用于存储订单的详细信息,包含交易日期、顾客编号和销售量。我们创建一张物化视图,按月存储累计销量信息,假如这时候我们要查询按季度或者按年度统计销量信息,Oracle是否能够智能地转换查询重写呢?我们知道交易转载 2016-12-29 09:34:45 · 1825 阅读 · 0 评论 -
从一个用户expdp导出再impdp导入到另一个用户
如果想导入的用户已经存在:1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp2. 导入用户 impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER如果想导入的用户不存在:1. 导出用户 expdp原创 2016-12-29 09:43:36 · 22916 阅读 · 2 评论 -
Oracle通过HSODBC访问mysql
一、环境OS:Linux myhostname 2.6.9-42.ELsmp #1 SMP Sat Aug 12 09:39:11 CDT 2006 i686 i686 i386 GNU/Linux CentOS release 4.4 (Final)Oracle:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr原创 2016-12-29 09:45:20 · 2732 阅读 · 0 评论 -
Inlist的绑定优化(书摘备查)
-- 建立类型CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER;CREATE OR REPLACE TYPE vartabletype AS TABLE OF VARCHAR2 (1000); -- 解析字符串CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2) R原创 2016-12-29 10:05:41 · 826 阅读 · 0 评论 -
How To Load CLOB Data from a File into a CLOB column using PL/SQL
主题:How To Load CLOB Data from a File into a CLOB column using PL/SQL 文档 ID:437432.1类型:HOWTO Modified Date:02-OCT-2007状态:MODERATEDIn this Document Goal SolutionThis document is being delivered to you转载 2016-12-29 10:14:08 · 804 阅读 · 0 评论