partition outer join in oracle 10g
Partition outer join is a new mechanism in 10g to "invent" data to fill the gaps in non-contiguous results. In 10g there are many methods to deal with such a problem (including the awe-inspiring, but equally terrifying, MODEL clause). In older versions of Oracle, "data-densification" was not as simple and certainly less efficient than it has now become.
the problem
This article has been motivated by a response I gave to a problem raised on an Oracle developer forum. Our requirement is to produce a report that details customer spending for each month of the year. Our database only records actual spend, so for any given month, data for dormant or idle customers will have to be generated.
setup
First, we'll create a mock CUSTOMER_ORDERS table with sparse data to represent customer spending. To keep the example simple, we'll denormalise the customer name onto the orders table.
SQL> CREATE TABLE customer_orders (name, dt, amt)
2 AS
3 SELECT *
4 from (
5 SELECT owner
6 , TRUNC(created) + MOD(ROWNUM,6)
7 , TRUNC(object_id/ROWNUM)
8 from all_objects
9 WHERE created > TRUNC(SYSDATE,'YEAR')
10 AND owner IN ('ORDSYS','WKSYS')
11 ORDER BY
12 DBMS_RANDOM.RANDOM
13 &nbs
相关文档:
Oracle10G的EM采用了web方式,并且分成了2个产品,database control和grid control。这里主要介绍如何创建单数据的dbcontrol。Grid control需要下载单独的光盘安装。
在用DBCA建库的时候,可以选择是否启用dbcontrol,启用的话需要在
数据库
中建立一个sysman的schema,用于保存EM的一些数据,这个就是EM的资料库(reposi ......
CentOS 5.2下Oracle 10G 安装详解
2009-01-16 14:38
CentOS 必需的安装包
* GNOME Desktop Environment
* Editors
* Graphical Internet
* Text-based Internet
* Development Libraries
* Development Tools
* Legacy Software Development
* Server Configuration Tools
* Administration Tools
* Base
* Le ......
dba_开头
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
&nbs ......
create or replace procedure TestJiaoJi
is
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
nt2 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ......