t0nsha's Blog

ROLLUP字面意思大概就是向上卷，用在GROUP BY 里面可起到累积求和的作用： 没有ROLLUP的情况下，以下查询按department_id和job_id进行分组求和：SELECT department_id, job_id, SUM(salary)FROM employees WHERE department_id < 60GROUP BY department

10G之前,如果要从oracle中发送email,必须借助oracle的utl_smtp和utl_tcp功能包,自己包装一个email发送程序,然后使用.自从10G后,这个流程将变得非常简单.因为oracle给我们提供了一个utl_mail包,我们可以直接调用发送email.其实这个包底层也是调用了utl_smtp、utl_tcp的相关api. 不过utl_mail只能用于无安全验证的stmp服务

Test Code:DECLAREBEGIN >-- most normal way to handle exception. DECLARE except_test0 EXCEPTION; BEGIN RAISE except_test0; EXCEPTION WHEN except_test0 THEN dbms_output.put_

在处理EXCEPTION时应特别注意WHEN OTHERS THEN的使用，因为WHEN OTHERS THEN会吃掉所有的EXCEPTION。如果在WHEN OTHERS THEN后不抛出任何信息，即：WHEN OTHERS THEN NULL; 这可能会极大地增加以后程序排错的难度，因为即使出错了，也没有任何提示。所以我们需要特别留意WHEN OTHERS THEN，除了一个一个的手工查找外，

使用PL/SQL Developer的DEBUG功能我们可以很方便的对PL/SQL代码进行逐行跟踪，但今天突然发现不好使了，检查了一下，确保一下两点即可：1. 不要用sys用户去调试；2. 编译package时需加入debug信息：alter package t01 compile debug;

NEXT_DAY(date,char)：返回指定日期（由date指定）后的第一个星期几（由char指定）所在的日期，char也可用1～7替代，1表示星期日。duzz$scott@orcl>select sysdate,to_char(sysdate,'day') weekday,next_day(sysdate,1) n_day,to_char(next_day(sysdate,1),'day')

oracle的联接分如下几种：内联接（inner join）。外联接（outer join）：全联接（full join）、左联接（left join）、右联接（right join）。交叉联接（cross join）。外联接与内联接不一样，外连接返回到查询结果中的不仅包含符合条件的行，还包括左表(左外连接),右表(右外连接)或者两个连接表(全外连接)中的所有不符合条件的数据行。 0.内联接 ([

之前在说Oracle Optimizer中的CBO时讲到，当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。 获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。 所以对于CBO，数据段的分析就非常重要。 Oracle Optimizer CBO

使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来。当GROUPING SETS里面的分组元素越多时，使用GROUPING SETS比使用UNION ALL性能更好，这可能和使用GROUPING SETS只需要访问一次表有关。如下两段查询的结果是相等的： q1（GROUPING SETS）：SELECT department_id, job

在计算月份的时候除了add_months，还可用INTERVAL：  Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpo SQL> SQL> SELECT to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') now FROM dual;

在声明VARCHAR2变量时，我们可以指定其最大长度单位为CHAR：v1 VARCHAR2(2 CHAR); 或BYTE：v2 VARCHAR2(2 BYTE); ，大多数默认什么都不指定：v3 VARCHAR2(2); 。 那么这三者有什么区别呢？ 请看例子： DECLARE v1 VARCHAR2(2 CHAR); v2 VARCHAR2(2 BYTE); v3 VARCHAR2(

测试代码：DECLARE TYPE list_of_names_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; happyfamily list_of_names_t; l_row PLS_INTEGER;BEGIN --build table data which index is not consecutive.

这个sql咋写？两个表的结构一模一样，数据大部分是重复，小部分不同；要求：1.如果a列数据相同，计算b列相减的结果test1:a    b  xxx  1yyy  2zzz  3 test2：a    bxxx  3zzz  2qqq  1查询结果结果为：a    bxxx  2zzz  -1yyy  -2qqq  1   Create Table:CREATE TABLE test1(a VAR

DECLARE l_sql VARCHAR2(2000) := 'select deptno,loc from scott.dept'; l_where VARCHAR2(200); TYPE dept_curtype IS REF CURSOR; dept_cur dept_curtype; TYPE dept_rec IS RECORD( deptno VA

NULL 不 大于/小于/等于/不等于 任何值（包括NULL本身），有且仅有：NULL IS NULLSQL> set serveroutput onSQL> SQL> BEGIN 2 IF (2 > NULL) 3 THEN 4 dbms_output.put_line('>'); 5 ELSIF (2 < NULL) 6 THEN

1.1 分区表PARTITION table在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。1.1.1 分区表的建立： 某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下： 范围分区表：CREATE TABLE sales (invoice_no NUMBER, ... s

/*intent: test the difference between "DEFAULT NULL" and "DEFAULT 0"anthor: t0nsha(liaodunxia{at}gmail.com@20081219)*/DECLARE    ln_dft_null NUMBER DEFAULT NULL;    ln_dft_zero NUMBER DEFAULT 0;BEGIN

SQLPLUS 使用的一些技巧        在ORACLE的维护过程中，目前ORACLE有很多易用的客户端工具，如plsql、delveloer、TOAD，但作为数据库管理员也需要经常和SQLPLUS 打交道，一些常用的操作往往需要在SQLPLUS完成，如果SQLPLUS不熟悉也在很大程度上降低我们的维护效率，本文主要介绍一些在管理数据库过程中比较有用的的SQLPLUS技巧。        技巧

约束的4种状态：disable novalidate 既不会约束新增数据也不会验证已有数据，等同于disableenable novalidate 约束新增数据但不会验证已有数据disable validate 约束新增数据但不会验证已有数据，启用后禁止DMLenable validate 约束新增数据并验证已有数据，等同于enable扩展解释：disable / enable 针对新增数据（对于

EBS R11通过建立带有CLIENT_INFO过滤条件的视图来实现多OU屏蔽，而R12使用的则是VDP（Virtual Private Database）来实现，关联的package是MO_GLOBAL,ORG_SECURITY。 假设我们想让员工只能查看到本部门的所有员工信息：没有应用VDP之前：select * from scott.emp;-----------------------

1. 系统权限SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT'UNION ALLSELECT * FROM dba_sys_privs WHERE grantee IN (SELECT granted_role FROM dba_role_privs

table型函数用来返回类似表结构的集合，常规的table型函数需要先填充完集合才能返回，如果集合对象太大会消耗大量内存；pipelined的table型函数能够在边填充时边返回，从而避免占用过多内存。Table FunctionsTable functions are used to return PL/SQL collections that mimic tables. They can be

1. 主要区别：NLS_LANG是环境变量，包括3部分NLS参数：NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET，需要在启动SQLPLUS等工具之前设置；NLS_LANGUAGE主要控制SESSION中提示消息的语言，可以使用ALTER SESSION在SQLPLUS里面设置；NLS_TERRITORY主要控制SESSION中的日期和货币等本地化参数的现

手动刷新share pool可导致缓存序列（cache sequence）不连续/丢失，dbms_shared_pool.keep可防止sequence cache不被flush share pool清空，但是不能防止shutdown引起sequence cache清空而导致的sequence断裂。C:\>sqlplus scott/tiger@t11aSQL*Plus: Release 11.2

题目：Provide: NAME COURSE SCORE Alex English 81 Lucy Math 79 Lucy English 82 Alex English 83 Alex Math 77 Lucy Math 75  Expect

Oracle sql语句中不支持boolean类型（decode&case）版本信息：SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Rel

在查看Apache log时，经常在url里面混杂着%3A，%2F等字符，究竟代表什么？只需通过Oracle自带函数转换下就可。根据16进制查字符:select chr(to_number('2F','xx')) from dual --/select chr(to_number('3D','xx')) from dual --=select chr(to_number('26','xx')) f

版本：Tested on R12.0.6/R12.1.1注意：请根据具体环境调整GUEST用户密码，默认情况都是ORACLE。 GUEST用户的密码可以在$CONTEXT_FILE文件中找到：view $CONTEXT_FILE     51               52             GUEST     53             ORACLE     54          用

一道关于AVG函数和NULL值的面试题某部门有10个员工,其中9个工资为1000,另一个为NULL,当使用AVG函数取该部门平均工资时,结果应该是多少?A.1000B.900我这里把题目再延伸一下,如果我们count(工资)结果是多少? A.10, B.9实践出真知SQL> select * from v$version;BANNER--------------------------------

I would like your explanation of the difference between a LEFT OUTER JOIN ON condition and a WHERE condition. Thank you.The difference is subtle, but it is a big difference. The ON condition stipulate

SET TRANSACTION READ ONLY实际上是实现数据库四大事务（ACID）中隔离性（Isolation）的一种手段，用来将数据的读一致性定在某一时间点，即不管其他事务如何更改数据（不能在当前session中再使用自治事务），在当前事务中进行查询的结果始终不变。由于Oracle的读一致性是通过undo段来实现的，所以如果在此期间DML修改的数据量很大而undo空间设置过小可能会导致OR

对象权限测试：初始状态下，user2和user3都没有user1.t1的对象权限user1下存在t1表：duzz$user1@orcl>select * from t1; C1---------- 1111Elapsed: 00:00:00.00user2不能查询user1.t1：duzz$user2@orcl>select * from user1

duzz$scott@orcl>SELECT employee_id, last_name, job_id, manager_id 2 FROM employees 3 START WITH employee_id = 101 4 CONNECT BY PRIOR employee_id=manager_id;（PRIOR：先前的，优先的；按照先前的employee_id等于本

在看Oracle约束时，看到ENABLE NOVALIDATE，意思是对自现在起及以后的数据启用约束，但是不管以前的数据怎样。不过在实验的时候却出了一点小问题：SQL> create table dept as select * from scott.dept where 1=2; Table created SQL> insert into dept select * from scot

已知：测试用户tuser1，测试角色trole1，trole1已经授权给了tuser1。在测试一段程序时需要用到延时，于是就把dbms_lock授权给了trole1，放在匿名块里测试没有问题：SQL> set serveroutput on;SQL> SQL> BEGIN 2 dbms_output.put_line(systimestamp); 3 -- dbms_ba

Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual par

我们知道 Flashback Table可以把drop掉的表从回收站里恢复回来，但是并不是关于该表的所有东西都能被Flashback回来，比如外键约束。duzz$scott@orcl>create table d(deptno number primary key, deptname varchar2(20));Table created.Elapsed: 00:00:00.28duzz

Test Code:Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpo SQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30); 2 / Type created SQL> SQ

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表. Oracle 10g中MERGE有如下一些改进： 1、UPDATE或INSERT子句是可选的 2、UPDATE和INSERT子句可以加WHERE子句 3、ON条件中使用常量过滤谓词来ins

CONTINUE is a new feature brought in Oracle 11g, but there is a trap or bug need to pay attention, see the following code scrap:DECLARE CURSOR dept_cur IS SELECT deptno ,dname