ITPub博客

前面已经把建库的过程给讲过了，这里对备库关闭的情况做一个补充[@more@]当我把备库的主机关掉后，发生了以下情况：alert.log文件中出现Errors in file d:oracleadmingswebbdumpgsweb_arc0_2976.trc:ORA-12535: TNS:operation timed out...Wed Apr 11 16:00:33 2007Errors in file d:oracleadmingswebbdumpgsweb_arc0_2976.trc:ORA-16058: standby database instance is not mounted当我把备库启来以后Wed Apr 11 16:06:33 2007ARC1: Begin FAL archive (thread 1 sequence 124 destination gsweb2)Wed Apr 11 16:06:33 2007ARC0: Begin F

根据TOM的提示,使用外部表来查看数据库的警告日志文件[@more@]conn system/managergrant create any directory to scott;grant drop any directory to scott;conn scott/tigercreate or replace directory data_dir as 'D:oracleadmingswebbdump';-- grant read,write on directory data_dir to scott ;CREATE TABLE alert_log( text_line varchar2(255))ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER DEFAULT DIRECTORY DATA_DIR ACCESS PARAMETERS ( records delimited by newline fields REJECT ROW

Oracle 9i Extensions to DML and DDL Statements-- 大纲：Use the following types of multitable inserts?Unconditional INSERT?Pivoting INSERT?Conditional ALL INSERT?Conditional FIRST INSERT:) Create and use external tables:) Name the index at the time of creating a primary key constraint[@more@]unconditional insert: all into_clauseThe oracle server executes each insert_into_clause once for each row returned by the subquery.在使用all关键字时，oracle将执行每个insert，不进行排除SQL> insert all 2 i

使用分级获取，生成树状报表(use hierarchical queries to create tree-structured reports)[@more@]--语法 SELECT [LEVEL], column, expr...FROM table[WHERE condition(s)][START WITH condition(s)][CONNECT BY PRIOR condition(s)];WHERE condition: expr comparison_operator expr--第一种方式：Walking the Tree: From the Bottom UpSQL> select employee_id,last_name,job_id,manager_id 2 from employees 3 start with employee_id=101 4 connect by prior manager_id=employee_id;EMPLOYEE_ID LAST_NAME JOB_ID MA

Advanced Subqueries[@more@]sql之20高级查询Advanced Subqueries--'嵌套子查询与关联子查询的区别'Nested Subquery Execution:) The inner query executes first and finds a value.:) The outer query executes once, using the value from the inner query.Correlated Subquery Execution:) Get a candidate row (fetched by the outer query).:) Execute the inner query using the value of the candidate row.:) Use the values resulting from the inner query to qualify or disqualify the candidate.:) Repeat until no candida

Advanced Subqueries'内联视图的应用'[@more@]'查询每个部门中大于本部门平均工资的员工名单及本部门的平均工资'SQL> select a.last_name,a.salary,a.department_id,b.salavg 2 from employees a,(select department_id,avg(salary) salavg 3 from employees 4 group by department_id) b 5 where a.department_id=b.department_id 6 and a.salary>b.salavg;LAST_NAME SALARY DEPARTMENT_ID SALAVG---------- ---------- ------------- ----------Hartstein 13000 20 9500Mourgo

Concatenated Groupings Example[@more@]SQL> select department_id,job_id,manager_id,sum(salary)2 from employees3 group by department_id,rollup(job_id),cube(manager_id);DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)------------- ---------- ---------- -----------SA_REP 149 700010 AD_ASST 101 440020 MK_MAN 100 1300020 MK_REP 201 600050 ST_MAN 100 580050 ST_CLERK 124 1170060 IT_PROG 102 900060 IT_PROG 103 4800080 SA_MAN 100 1050080 SA_REP 149 1960090 AD_PRES 2400090 AD_VP 100 34000110 AC_MGR 101 12000

复合列 group by rollup(department_id,(job_id,manager_id));[@more@]SQL> select department_id,job_id,manager_id,sum(salary)2 from employees3 group by rollup(department_id,(job_id,manager_id));DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)------------- ---------- ---------- -----------SA_REP 149 7000700010 AD_ASST 101 440010 440020 MK_MAN 100 1300020 MK_REP 201 600020 1900050 ST_MAN 100 580050 ST_CLERK 124 1170050 1750060 IT_PROG 102 900060 IT_PROG 103 4800060 5700080 SA_MAN 100 1050080 SA_REP 149

ROLLUP等在GROUP BY中的使用[@more@]ROLLUP在GROUP BY中的使用SELECT department_id, job_id,SUM(salary)FROM employeesWHERE department_id < 60GROUP BY ROLLUP(department_id, job_id);DEPTNO JOB AVG(SAL)------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 2916.6666720 CLERK 95020 ANALYST 300020 MANAGER 297520 1968.7530 CLERK 95030 MANAGER 285030 SALESMAN 140030 1566.6666740405050 ANALYST 300050 30002073.21429结果：除了分组外，还加上按DEPTNO的小计cube在GROUP BY中的使用SQL> /DE

因为在项目中大量地使用了视图，而在视图上的更新上产生了一点儿问题，所以抽时间对可更新视图进行了复习，英文看得多了，也就成了中文[@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),

总结 回滚段用来保存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