- 博客(156)
- 收藏
- 关注
原创 oracle 主键和外键的一比较细…
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#SQLRF01111 Foreign KeyConstraint Example Thefollowing statement creates the dept_20 table anddefines and enables a foreig
2013-07-08 23:00:14 1281
原创 关于NOT IN运算符的一些特殊之处
文档地址:http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref502IN OperatorThe IN operator tests set membership.It means "equal to any member of." The set can contain nulls, bu
2013-07-08 23:00:12 813
原创 GRANT使用
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#i2062455grant_object_privileges::=Description of the illustrationgrant_object_privileges.gif --grant的语法:在授予对象权限的时
2013-07-08 23:00:10 661
原创 ORA-03137: TTC protocol internal error : [12333]错误一例
环境说明:IBM-AIX6服务器oracle11g11.2.0.1.0数据库出现错误症状:Java代码 TTC protocol internal error : [12333]错误一例(来源网上)" /> TTC protocol internal error : [12333]错误一例(来源网上)" />Dump file /cnbbs01/app/oracle/diag
2013-07-08 23:00:05 3600
原创 NVL2函数
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm#SQLRF00685 NVL2SyntaxDescription of the illustrationnvl2.gifPurpose NVL2 lets you determine the value returnedby
2013-07-08 23:00:03 1377
原创 SYSDATE函数
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm#SQLRF06124 SYSDATE SyntaxDescription of the illustrationsysdate.gifPurposeSYSDATE returns the current date and ti
2013-07-08 23:00:01 807
原创 ORDER BY的一些小知识点
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079 order_by_clause::=order_by_clause Use the ORDER BY clause to order rowsreturned by the statement. Witho
2013-07-08 22:59:59 740
原创 Interval的知识点
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#SQLRF00221 IntervalLiterals An interval literal specifies a period of time. You can specifythese differences in ter
2013-07-08 22:59:57 1063
原创 oracle连接叙述
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2080416 subquery::=Description of the illustrationsubquery.gif join_clause::=Description of the illustrat
2013-07-08 22:59:55 516
原创 甲骨文语句级别的回滚
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm#CNCPT117 Statement-Level RollbackIf at anytime during execution a SQL statement causes an error, all effectsof the statemen
2013-07-08 22:59:52 926
原创 TO_CHAR函数详解
文档地址:http://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_x_stddev018.htm#i78638 Return ValueTEXTSyntaxTO_CHAR(datetime-exp, [datetime-fmt,] [optionsetting])orTO_CHAR(num-exp,[num-fmt,] [n
2013-07-08 22:59:50 2972
原创 alter table语法
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#CIHCFDDJ ALTER TABLE PurposeUse the ALTER TABLE statement to alterthe definition of a nonpartitioned table, a partit
2013-07-08 22:59:48 701
原创 update的一些注意的语法
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm#i2073065 update_set_clause::=Description of the illustrationupdate_set_clause.gif update_set_clauseThe update_s
2013-07-08 22:59:46 960
原创 转义字符和引号
稳当地址:http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams003.htm#i1124422 Using the Escape Character--\As described in "Rules Governing Initialization Parameter Files", the backslash(
2013-07-08 22:59:44 776
原创 关于设置会话时区
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006728 Setting the Session Time ZoneYou can set the default session timezone with the ORA_SDTZ environmentvariable. Wh
2013-07-08 22:59:42 850
原创 子查询的知识点
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm#sthref2680 Scalar Subquery ExpressionsA scalar subquery expression is a subquery that returns exactlyone column value
2013-07-08 22:59:39 756
原创 单行函数的一些知识点
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref914 Single-Row Functions Single-rowfunctions return a single result row for every row of a queriedtable or vie
2013-07-08 22:59:37 453
原创 函数索引的使用
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14211/data_acc.htm#i994615.2 Using Function-based Indexes forPerformance A function-based index includes columns thatare either transformed
2013-07-08 22:59:34 570
原创 再说WITH子句
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1670 Computation Using the WITH Clause The WITH clause (formally known assubquery_factoring_clause) enables you to
2013-07-08 22:59:32 488
原创 Oracle双机/RAC/Dataguard的区别---来源于网上
DataGuard是Oracle的远程复制技术,它有物理和逻辑之分,但是总的来说,它需要在异地有一套独立的系统,这是两套硬件配置可以不同的系统,但是这两套系统的软件结构保持一致,包括软件的版本,目录存储结构,以及数据的同步(其实也不是实时同步的),这两套系统之间只要网络是通的就可以了,是一种异地容灾的解决方案。而对于RAC,则是本地的高可用集群,每个节点用来分担不用或相同的应用,以解决运算效率
2013-07-08 22:59:30 1789
原创 约束的一些知识积累
结论:有外键约束的字段可以为空。如果不为空的话,则一定要满足外键的约束关系, 组合外键中有列为空,Oracle不再检查其他列是否满足外键约束的条件,而使得这条记录直接插入到子表中。SQL> create table t_p (id number, name varchar2(30),constraint pk_t_p primary key (id, name));SQL> crea
2013-07-08 22:59:27 454
原创 update语句的语法
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm#SQLRF01708 update_set_clause::=Description of the illustrationupdate_set_clause.gif ---update语句中SETXXX=DEFAUL
2013-07-08 22:59:25 1551
原创 create table .... as注意事项
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2128916AS subquerySpecify a subquery to determine the contents of the table. Therows returned by the subquery are
2013-07-08 22:59:23 607
原创 NATURAL JOIN--自然连接的一些注意事项
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2080416 NATURAL JOIN The NATURALkeyword indicates that a natural join is being performed. A naturaljoin is based on
2013-07-08 22:59:21 3797
原创 Insert语句的语法
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2064185(DML_table_expression_clause::=)values_clause::=Description of the illustrationvalues_clause.gif--可见values
2013-07-08 22:59:19 1286
原创 关于外部表----external_table_clause
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2159541 (external_data_properties::=)external_data_properties::=Description of the illustrationexternal_data_pro
2013-07-08 22:59:17 680
原创 关于序列
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14231/views.htm#i1106548 Creating Sequences To create a sequence in your schema, you must have the CREATESEQUENCE system privilege. To c
2013-07-08 22:59:14 554
原创 WITH引出的子查询扇出子句
官方文档:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2077142 语法:subquery_factoring_clause::=Description of the illustrationsubquery_factoring_clause.gif subquery_f
2013-07-08 22:59:12 1149
原创 关于运算符IN、ANY、ALL、NOT、SOME
找出员工中,只要比部门号为10的员工中的任何一个员工的工资高的员工的姓名个工资。也就是说只要比部门号为10的员工中的那个工资最少的员工的工资高就满足条件。select ename,sal From emp Where sal > any(select salfrom emp where deptno = 10);下面的SQL语句的意义与前面的就完全不一样了,其意义是找到比部门号为20的员
2013-07-08 22:59:10 1406
原创 MERGE的使用一些注意事项
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606 MERGE Purpose Use the MERGE statement to select rows from one ormore sources for update or insertion i
2013-07-08 22:59:08 1042
原创 详解GROUPING函数
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions062.htm#SQLRF00647 GROUPING SyntaxDescription of the illustrationgrouping.gifPurpose GROUPING distinguishes superaggre
2013-07-08 22:59:06 3636
原创 oracle 迁移数据方案(定制数据库模板)
注意:迁移数据库的方法很多,但下例子是比较简便的方法。但必须满足有条件,才能迁移。迁移的条件(经试验):1、版本号必须相同,例如:10.2.0.3,那么新服务器的版本号也必须是10.2.0.3。如果是10.1.0.3的话,经测试是不行的。2、跨平台的情况没测试。如何定制个性化的模板 (1)启动【数据库配置助手】,一直到出现如图6.49所示的【操作】界面。 (2)出现如图6
2013-07-08 22:59:04 1813
原创 Oracle的时区问题
Oracle 9i 开始多了 3 个关于时间的数据类型:TIMESTAMP [(precision)] TIMESTAMP[(precision)] WITH TIME ZONE TIMESTAMP [(precision)] WITH LOCALTIME ZONE,其中 TIMESTAMP [(precision)] WITH TIME ZONE保存了时区信息。-----(同时可以参考书籍
2013-07-08 22:59:02 2109
原创 interval day to second和interval year to month数据类型
文档地址:http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref816 INTERVAL YEAR TO MONTH DatatypeYou use the datatype INTERVAL YEARTO MONTH to store and manipulateintervals of y
2013-07-08 22:58:59 1263
原创 External Tables
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2201774external_table_clause::=Description of the illustrationexternal_table_clause.gif (external_data_properti
2013-07-08 22:58:57 937
原创 包含子查询的一条insert语句
例子:INSERT INTO (SELECT order_id,order_date,customer_idFROM ORDERSWHERE order_total = 1000WITH CHECK OPTION)VALUES (13, SYSDATE, 101); insert into ( WITHCHECK OPTION) values (...)1:这样的语法看起来很特殊
2013-07-08 22:58:55 1826
原创 oracle 约束:延迟 与 立即
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1002273 constraint_state::=Description of the illustrationconstraint_state.gif constraint_state Youcan use the constr
2013-07-08 22:58:53 949
原创 一道Oracle面试年薪70W的DBA题目
最近在网上看到一道Oracle面试年薪70W的DBA题目,其实是一道SQL题目。蛮有意思的。于是拿来分析了下,题目如下: 有表order_list数据如下源数据:(order_list) user food Jack HamburgerJack HamburgerTom Hamburger Tom Sausage
2013-07-08 22:58:51 1031 1
原创 oracle中rollup和cube的用法
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1616 ROLLUP Extension to GROUP BYROLLUP enables a SELECTstatement to calculate multiple levels of subtotals across as
2013-07-08 22:58:48 2295 1
原创 oracle 下WITH CHECK OPTION用法
如果视图定义包括条件(譬如WHERE 子句)并且其意图是确保任何引用该视图的 INSERT 或UPDATE 语句都应用 WHERE 子句,则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件,则返回 SQL 错误。下面是一个使用了 WITH CHECK OPTION 的视图
2013-07-08 22:58:46 4262 1
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人