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 ......
oracle字符串分割和提取
分割
create or replace function Get_StrArrayLength
(
av_str varchar2, --要分割的字符串
av_split varchar2 --分隔符号
)
return number
is
lv_str varchar2(1000);
lv_length number;
begin
lv_str:=ltrim(rtrim(av_str));
&nb ......
‘开始’-->‘运行’--> 输入‘cmd’ 打开cmd.exe -->输入‘cd c:\’ 切换到 c:\>
启动Oracle
c:\> svrmgrl
svrmgrl> connect internal/oracle
svrmgrl> startup
svrmgrl> exit
启动监听器
c:\> lsnrctl start ......
CREATE TABLESPACE mySpace
datafile
'd:\data\data1.dbf' size 1M reuse
autoextend on next 100k maxsize 2M
'd:\data\data2.dbf' size 1M reuse
online
permanent;
其中online表联机。
联机和脱机的区别和使用
联机: ORACLE服务进程关联文件。可供oracle客户端访问.如果在系统中直接删除时会弹出异常。
脱机: ......
dba_开头
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
&nbs ......