ITPub博客

因为在项目中大量地使用了视图，而在视图上的更新上产生了一点儿问题，所以抽时间对可更新视图进行了复习，英文看得多了，也就成了中文[@more@]测试用表CREATE TABLE Dept_tab ( Deptno NUMBER(4) PRIMARY KEY, Dname VARCHAR2(14), Loc VARCHAR2(13));CREATE TABLE Emp_tab ( Empno NUMBER(4) PRIMARY KEY, Ename VARCHAR2(10), Job varchar2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2),

fighting[@more@]坚持目标不动摇,实现一个目标后再去选择第二个目标,否则你一个目标都不会实现.要做行业的领头羊,必定先有积累,积累的过程就是包含容忍吸收的过程.

为什么将资源插入到日志中,不能显示?[@more@]

总结 回滚段用来保存DML时的前映像，给查询用户提供一致读，回退发生时，前映像用来写回原数据。[@more@]When an insert, update, or delete operation is made to the database, the Oracle Server takes a copy of the data before it is changed and writes it to a undo segment.All readers, except the one who issued the change, still see the database as it existed before the changes started; they view the undo segment’s snapshot of the data.When a DML statement is committed, the change made to the database bec

事务何时开始、何时结束？显式的事务控制? 隐式的事务发生[@more@]-- 事务何时开始、何时结束？When Does a Transaction Start and End?A transaction begins when the first DML statement is encountered and ends when one of the following occurs:A COMMIT or ROLLBACK statement is issuedA DDL statement, such as CREATE, is issuedA DCL statement is issued,such as grant,is issuedThe user exits iSQL*PlusA machine fails or the system crashes-- 显式的事务控制Explicit Transaction Control StatementsCOMMIT, SAVEPOINT,

语法 + 一个例子[@more@]-- 语法MERGE INTO table_name AS table_aliasUSING (table|view|sub_query) AS aliasON (join condition)WHEN MATCHED THENUPDATE SETcol1 = col_val1,col2 = col2_valWHEN NOT MATCHED THENINSERT (column_list)VALUES (column_values);-- 一个例子merge into tj_rb_wlzx using (select v_tjrq as dt, v_webid as web_id , nvl(cc.flag_stat,0) service_type, --受理分类 service_type nvl(cc.type_id,0) taxtype, --税种 taxtype cc.swjg swjg, sum(nvl(aa.slsl,0)) slsl, sum

在DML 时显式使用default示例[@more@]Specify DEFAULT to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, Oracle sets the column to null.-- 示例UPDATE departmentsSET manager_id = DEFAULT WHERE department_id = 10;INSERT INTO departments(department_id, department_name, manager_id)VALUES (300, 'Engineering', DEFAULT);

insert into 中使用内联视图[@more@]-- A subquery is used to identify the table and columns of the DML statement.SQL> insert into (select empno,ename from emp 2 where deptno=20 3 with check option) 4 values (333,'wangwang');insert into (select empno,ename from emp *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violationSQL> 1 1* insert into (select empno,ename from empSQL> edWrote file afiedt.buf 1 insert into (select empno,en

锁的类型,产生的情况[@more@]准备知识： 0：none 1：null 空 2：Row-S 行共享(RS)：共享表锁 3：Row-X 行专用(RX)：用于行的修改 4：Share 共享锁(S)：阻止其他DML操作 5：S/Row-X 共享行专用(SRX)：阻止其他事务操作 6：exclusive 专用(X)：独立访问使用1级锁有：Select，有时会在v$locked_object出现。2级锁有：Select for update,Lock For Update,Lock Row Shareselect for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或

替换变量与格式化[@more@]-- 第一种方式，临时输入select * from emp where ename=&vname;-- 第二种方式，预定义def vname='SMITH'select * from emp where ename='&vname';-- 注意：在使用char类型的变量时，要加上'',数值型不加''-- 查看及解除已定义的变量def vnamedefundefine vname-- &&定义一次，全session可用；&一次使用-- 不显示新旧值的提示 set verify off-- 设置与显示session中的变量SET system_variable valueSHOW ECHOshow all-- sql*plus 中的Format CommandsCOLUMN [column option]TTITLE [text | O

几种约束[@more@]-- 添加主键 alter table orderitems add constraint orderitems_pk primary key (order#,item#);-- 添加外键 alter table orders add constraint orders_customer#_fk foreign key (customer#) references customers(customer#);-- 父表记录删除后，级联删除子表记录 alter table orders add constraint orders_customer#_fk foreign key (customer#) references customers(customer#) on delete cascade;-- 使用unique约束alter table booksadd constraint books_title_uk unique (title);-- 使用check约束，在check中不

SQL入门之4 group by 与子查询[@more@]使用group by的注意点：Any column or expression in the SELECT list that isnot an aggregate function must be in the GROUP BYclause. -- 说明：select 中的列必须在group by 子句中，在group by 中可以不在select中?You cannot use the WHERE clause to restrict groups. ?You use the HAVING clause to restrict groups. ?You cannot use group functions in the WHERE clause. 使用子查询的注意点：ORA-01427: single-row subquery returns more than one row原因是使用单行子查询时，子查

-- 单行函数及几个测试题,是否全面系统学习过ORACLE的函数,一试便知[@more@]-- decode函数select customer#,state,decode(state,'CA',.08,'FL',.07,0) sales_trfrom customers;-- 使用DECODE比对字符串SELECT last_name, job_id, salary,DECODE(job_id, 'IT_PROG', 1.10*salary,'ST_CLERK', 1.15*salary,'SA_REP', 1.20*salary,salary)REVISED_SALARYFROM employees;-- 使用DECODE测定数值范围SELECT last_name, salary,DECODE (TRUNC(salary/2000, 0),0, 0.00,1, 0.09,2, 0.20,3, 0.30,4, 0.40,5, 0.42,6, 0.44,0.45) TAX_RATEFROM emplo

字符串函数 数字函数[@more@]-- 常规的多表联接select title,firstname,lastnamefrom customers,orders,orderitems,bookswhere customers.customer#=orders.customer#and orders.order#=orderitems.order#and orderitems.isbn=books.isbnorder by title;-- 使用join进行多表联接select title,firstname,lastnamefrom customers join orders on customers.customer#=orders.customer#join orderitems on orders.order#=orderitems.order#join books on orderitems.isbn=books.isbnorder by title;-- 在外部联接时不能使用OR 或 IN-- 单行函

内联接外联接全联接join的用法[@more@]-- 笛卡尔连接 交叉连接select title,name from books,publisher;select title,name from books cross join publisher;-- 相等连接 内连接-- 常规用法select title,name from books a,publisher b where a.pubid=b.pubid;-- 有公共列，使用natural join 或者usingselect title,name from books natural join publisher;select title,name from books join publisher using (pubid);-- 公共列名称不同，使用onselect title,name from books b join publisher p on b.pubid=p.putid;-- 不等连接-- 按照

出差回来,开发人员给我说,35数据库不能启动了[@more@]redo log file 损坏的处理过程一 查看日志Thu Mar 29 09:15:21 2007Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAGS12366REDO01.LOG'ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 2) 系统找不到指定的文件。Thu Mar 29 09:15:21 2007Errors in file d:oracleadmings12366bdumpgs12366_lgwr_2052.trc:ORA-00313

已有的环境准备机器一：IP: 10.0.10.85 已有普通的数据库gsweb，准备做为主库机器二：IP: 10.0.10.55 准备新增一个备库[@more@]已有的环境准备机器一：IP: 10.0.10.85 已有普通的数据库gsweb，准备做为主库机器二：IP: 10.0.10.55 准备新增一个备库/** 预处理：将机器二上的原来的一个数据库的数据文件目录和ADMIN目录改名为gsweb2 保留了机器二的口令文件。否则需要从主库机器上复制一个口令文件 */ --3.1.1 Enable Forced Logging 在主库上启用force logging模式--Place the primary database in FORCE LOGGING mode ALTER DATABASE FOR

如何在IBM小型机上安装VirusScan防病毒软件,文件为vaix414l.tar.z,现在已经拷贝到光盘的根目录下,请各位大虾说明一下具体的安装步骤,本人没用过UNIX系统,例如如何加载光驱,都需要什么命令.[@more@]1、#uncompress vaix4141.tar.z 2、#tar -xvf vaix4141.tar 3、#smitty install_latest 加载光驱 #mount -v cdrfs -o ro /dev/cd0 /cdrom----------------------------------------------------------------你可以到smitty中建一个cdrfs文件系统(mount点在/cdrom），以后就直接mount /cdrom，就ok了，不用写那么多参数 我一直这样做 那些参数

配置个人的环境变量[@more@]用户的登录SHELL依次执行/etc/profile,/etc/environment,$HOME/.profile或者$HOME/.cshrc $HOME/.env 如果是从CDE登录系统,则使用.dtprofile文件设置环境

在狼行天下的 blog中发现以下内容,可以排序与分页row_number() over(order by create_time desc) rk[@more@]在开发中应用慢select B.* from (select A.*, ROWNUM ROWNO from (select A.TITLE, TO_CHAR(A.CREATE_TIME, 'YYYY.MM.DD') AS CREATE_TIME, A.AUTHOR_ID, A.DOC_ID, A.ABSTRACT, A.PIC_ID AS IMAGE_ID, B.TYPE_ID from CT_DOCUMENT_L A,