易截截图软件、单文件、免安装、纯绿色、仅160KB

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


相关文档:

linux oracle缩小回滚表空间大小undotbs01.dbf


http://hi.baidu.com/lu_xinzhong/blog/item/4ff92b1725dec505c83d6d09.html
回滚表空间大小
-- 创建一个新的小空间的UNDO TABLESPACE
Create UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/orcl/UNDOTBS02.DBF' SIZE 100M REUSE AUTOEXTEND ON;
-- 设置新的表空间为系统UNDO_TABLESPACE
Alter SYSTE ......

oracle与sqlserver插入数据动态字段值

记录一下以备下次快速找到。。。
       往tb_wf_privgrant表中插入一条记录,workflow_id字段值从tb_wf_workflow 表中获取workflow_name='知识审核'的所有记录中workflow_id最大值。
--oracle
declare   a NUMBER(10);
   begin
   select  max(wo ......

ORACLE函数大全


SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
        A         A      ZERO   &nbs ......

Oracle数据块损坏恢复总结(转帖)

查看数据坏块所在数据文件号及块号可以对表进行一次全表扫描,如:
  select count(*) from tablename;
  1、没有备份的情况下:
  1.1、使用exp/imp恢复
  在这种情况下肯定会造成数据的丢失,在这种情况下应采取将数据导出然后重建表再进行导入的方法,来尽量恢复损坏数据块中的数据,但是在有坏块的情况下
......

Oracle Database中DBA常用的表和视图


dba_开头
   dba_users     数据库用户信息
   dba_segments  表段信息
   dba_extents    数据区信息
   dba_objects    数据库对象信息
   dba_tablespaces   数据库表空间信息
 &nbs ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号