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
相关文档:
什么是合并多行字符串(连接字符串)呢,例如:
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
Oracle存储过程可以有无参数存储过程和带参数存储过程。
一、无参程序过程语法
1 create or replace procedure NoParPro
2 as ;
3 begin
4 ;
5 exception &nb ......
查看数据坏块所在数据文件号及块号可以对表进行一次全表扫描,如:
select count(*) from tablename;
1、没有备份的情况下:
1.1、使用exp/imp恢复
在这种情况下肯定会造成数据的丢失,在这种情况下应采取将数据导出然后重建表再进行导入的方法,来尽量恢复损坏数据块中的数据,但是在有坏块的情况下
......
dba_开头
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
&nbs ......