oracle中join in,partition outer join in oracle 10g

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

相关文档：

首先写好建库脚本c.sql

create database mydb

controlfile reuse

maxinstances 1

maxloghistory 1

maxlogfiles 5

maxlogmembers 5

maxdatafiles 100

datafile '$ORACLE_HOME/oradata/system01.dbf'size 325M reuse

autoextend on next 10240K maxsize unlimited

u ......

Oracle10G的EM采用了web方式，并且分成了2个产品，database control和grid control。这里主要介绍如何创建单数据的dbcontrol。Grid control需要下载单独的光盘安装。

在用DBCA建库的时候，可以选择是否启用dbcontrol，启用的话需要在

数据库

中建立一个sysman的schema，用于保存EM的一些数据，这个就是EM的资料库(reposi ......

---------------------------------------------------------------------------

---- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

---- 转载务必注明原始出处

:

http://blog.csdn.net/andkylee

--- 2010-05-31 22:56:43

---- 关键字： oracle 分页 rownum

--------------------------------- ......

Linux系统删除oracle 10g(包括软件和实例)

软件环境：Linux系统　Oracle 10.0.1.0

涉及用户：root oracle

删除oracle软件和实例步骤：

1、oracle用户登陆sqlplus停止数据库：shutdown

2、oracle用户停止监听服务：lsnrctl stop(如果监听服务启动的话)

3、root用户

删除/tmp下ora*文件

删除/opt下OR ......

Shrink space合并表的碎片

一般表里有碎片我们都采用alter table table_name move tablespace_name,或者exp,drop table table_name,imp的2种方式10G给我们其他的方法.下面我来试一吧

用Shrink Space收缩Oracle数据段

在oracle中可以使用alter table table_name shrink space收缩表,使用shrink有两个前提条件:

1� ......