ITPub博客

oracle truncate 与 delete 的区别[@more@]truncate与delete的区别truncate操作 同没有where条件的delete操作十分相似1、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求而truncate是不产生的。2、truncate是DDL语句进行隐式提交，不能进行回滚操作3、truncate重新设定表和索引的HWM（高水标记），由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快4、truncate不触发任何delete触发器5、不能赋给某个用户truncate其它用户

oracle 游标中使用 动态 sql[@more@]declare TYPE cursor_test IS REF CURSOR; cur1 cursor_test; vsql varchar2(2000); v_table varchar2(200);begin vsql :=' SELECT outstorageformheadid FROM OUTSTORAGEHEAD'; open cur1 for vsql; loop fetch cur1 into v_table; exit when cur1%notfound; dbms_output.put_line('the result : '|| v_table); end loop;end;/

oracle sqlldr 与 外部表[@more@]自定义例外:declaretablenotexist exception;pragma exception_init(tablenotexist,-942);begin execute immediate 'drop table xxxxx';exception when tablenotexist then dbms_output.put_line('tablenotexist');end;/pragma : 为编译指示命令。

在做恢复前，先对当前数据库做畚份！[@more@]经验总结： 联机日志分为当前联机日志和非当前联机日志，非当前联机日志的损坏是比较简单的，一般通过clear命令就可以解决问题。 损坏非当前联机日志： 1、启动数据库，遇到ORA-00312 or ORA-00313错误，如: ORA-00313: open failed for members of log group 4 of thread 1 ORA-00312: online log 3 thread 1: '/opt/oracle/db04/oradata/ORCL/redo03.log' 从这里我们知道日志组1的数据文件损坏或丢失了 从报警文件可以看到更详细的信息 2、查看V$log视图: SQL>; select group#,sequence#,

今天收到了 OCP 证书！[@more@]从 2004.12 - 2006.07 所有的努力，终于开花结果了。辛辛苦苦地播种，终于得到了收获。还要继续努力！

记录自己的学习过程,不断的更新！[@more@]2006.07.20-----------------------问题：有关列名与变量名一致的情况的处理（在从 SQL SERVER 迁移到 Oracle 中处理存储过程时可能会遇到该问题 ）解决：在变量名前加限定词create or replace procedure test_update(job_id varchar, my_salary int) as begin update test set min_salary = my_salary where job_id = test_update.job_id; end;Multilevel VARRAY Example ( For Oracle )declare type t1 is varray(10) of integer; type nt1 is varray(10) of t1; -- multilevel varray type

Please refer to Oracle's Article[@more@]http://www.oracle.com/technology/obe/10gr2_db_vmware/develop/omwb/omwb.htm

本文摘自网上，在此对作者表示感谢！[@more@][Oracle] Installing Oracle 10g on RHEL AS 3 Step-by-Step整理：Fenng 日期：06-Apr-2004 　出处：http://www.dbanotes.net版本：1.01不久前,Oracle(甲骨文)公司在美国加州的总部宣布, Oracle 10g 数据库 与 Oracle RAC 在 TPC-H 基准测试中创造了新的世界纪录.这是个令人震惊的消息.因为这个测试是在 Linux ( Red Hat Enterprise Linux Advanced Server 3) 的平台上进行的. 这充分显示了 Oracle 在低成本 Linux 集群服务器上高效管理大规模数据仓库的能力, 也表明 Linux 在性能价格比上显而

oracle 中调用 store procedure[@more@]1 Create or replace procedure ex_ref(v_cur IN OUT sys_refcursor)2 as3 Begin4 open v_cur for select first_name from emp where rownum<3;5* end;SQL> /Procedure created.SQL> var c1 refcursorSQL> exec ex_ref(:c1);PL/SQL procedure successfully completed.SQL> print c1FIRST_NAME--------------------DonaldDouglas

有用的 oracle 包[@more@]utl_tcputl_smtputl_httputl_inaddr ( get_host_name , get_host_address )

摘自网上：http://www.oracle.com.cn/viewthread.php?tid=52954&highlight=sequence[@more@]ORACLE SEQUENCE的简单介绍在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。 1、Create sequence 你首先要有CREATE sequence或者CREATE ANY sequence权限， CREATE sequence emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 一旦定义了emp_seque

在 oracle 中实现 sql server 中的在 create object 时的判断与删除功能[@more@]/* Formatted on 2006/07/06 14:34 (Formatter Plus v4.8.7) */DECLARE i_count INT := 0; BEGIN SELECT COUNT (*) INTO i_count FROM user_constraints WHERE constraint_name = 'FK_ALLOTSLIPSTYLE_TO_HEAD'; IF i_count > 0 THEN EXECUTE IMMEDIATE 'alter table ALLOTSLIPSTYLE drop constraints FK_ALLOTSLIPSTYLE_TO_HEAD'; i_count := 0; END IF; SELECT COUNT (*) INTO i_count FROM user_constraint

Oracle 9I FlashBack 测试[@more@]15:08:22 SQL> execute DBMS_flashback.ENABLE_AT_TIME(to_date('2003-05-16 15:00:00')); PL/SQL procedure successfully completed. 15:09:02 SQL> select * from t; A ---------- gototop www.ncn.cn www.ncn.cn 15:16:43 SQL> execute DBMS_flashback.DISABLE; PL/SQL procedure successfully completed. -----------------------------------------------------------------------15:38:57 SQL> declare 15:39:28 2 cursor flash_recover is 15:39:28 3 select *

ODBC with Oracle Heterogeneous Services 出自网上，在此表达对作者的谢意！[@more@]ODBC with Oracle Heterogeneous Services Using DataDirect Connect® for ODBC with Oracle Heterogeneous ServicesView PDF 摘自Datadirect http://www.datadirect.com/developer/odbc/oracle_heterogeneous/index.sspIntroductionCorporate developers frequently need to integrate and consolidate data residing in diverse database systems. Oracle users make use of Oracle Heterogeneous Services to integrate non-Oracle data into

在Redhat9下安装Oracle9 [@more@]在Redhat9下安装Oracle9在安装之前有几个地方需要注意的：你的交换分区最好有一个G左右那么大，第二你的硬盘分区最好也要有足够大的空间。下面我那个转贴的中译本：1、创建用户和组：[roko@miniroko] su -[root@miniroko]# groupadd oinstall[root@miniroko]# groupadd dba[root@miniroko]# useradd -g oinstall -G dba oracle[root@miniroko]# passwd oracle2、准备文件目录：[root@miniroko]# mkdir -p /opt/ora9/product/9.2[root@miniroko]# mkdir /var/opt/oracle[root@miniroko]# chown oracle.dba

本文章源自：http://www.adp-gmbh.ch[@more@]x$bhInformation on buffer headers. Contains a record (the buffer header) for each block in the buffer cache. This select statement lists how many blocks are Available, Free and Being Used. select count(*), State from ( select decode (state, 0, 'Free', 1, decode (lrba_seq, 0, 'Available', 'Being Used'), 3, 'Being Used', state) State from x$bh ) group by state The meaning of stat

Chapter 2 : Oracle Net Architecture[@more@]Chapter 2 : Oracle Net Architecture Connectivity Concepts and Terminology:l Service Name1. The way a database is presented to clientsThe service name is a string that is the global name, that is, a name composed of the database name and domain name, entered during installation or database creation. The service name is included in the connect data part of the connect descriptor.l Location of the database2. Listener1.

LogMiner Studying[@more@]Using LogMiner :To run LogMiner , you use two PL/SQL packages:l DBMS_LOGMNRContains the procedures necessary to initialize and run LogMiner. These procedures include interfaces to specify log filenames,filter criteria, and LogMiner session characteristics.l DBMS_LOGMNR_DQueries the dictionary tables of the current database and creates a LogMiner dictionary file. LogMiner Views :V$LOGMNR_CONTENTSV$LOGMNR_DICTIONARYV$LOGMNR_LOGSV$LOGMNR_PARAMSUTL_FILE_DIR : Steps in a typi

Chapter 4 : Naming Method ConfigurationChapter 5 : Usage and Configuration of the Oracle Shared ServerChapter 6 : Backup and Recovery Overview[@more@]Chapter 4 : Naming Method Configuration Naming methods are used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service.Oracle Net provides five naming methods:Small Organizations :1 , Host naming : Enables Users in a TCP/IP environment to resolve names through their existing

Oracle 工具匯總[@more@]oradim : create / edit / delete / startup / shutdown services and instance .orapwd : create password file .sqlplus : start command line 'sqlplus' .sqlplusw : start window 'sqlplus' .tnsping : test net service name , do not connect the database actually .lsnrctl : listener setup tool .exp/imp : export from oracle database in oracle format file , import to oracle database in oracle format file .sqlloader : load data into oracle database from flat file .wrap : encrypt the pls