程序人生

dynamic SQL introductionhttp://camden-www.rutgers.edu/HELP/Documentation/Oracle/server.815/a68022/dynsql.htm

create table lck (a number, b number);insert into lck values (1,2);insert into lck values (2,4);insert into lck values (3,6);insert into lck values (4,8);insert into lck values (5,3);i

When creating large tables (large referring to the amount of data), you may want to consider creating them with the nologging option. If the data is of a transient nature, that is, the table is used f

These work good on 9i .free.sql-amount of freespace in a tablespaceselect tablespace_name, round(sum(bytes)/(1024*1024),0) MB from dba_free_space group by tablespace_name/-----------------------------

使用一条简单的SQL语句，把表或者数据库恢复到过去的某个时间点。 新年的圣诞夜前夕，顶点银行的DBA约翰正在和他的朋友们一起狂欢，为新年的到来倒计时。正当午夜的时钟敲响，大家一起欢呼时，他的手机忽然响了起来。银行数据中心里，年终计帐程序正忙于计算利息，但是发生了一些问题，所有的利息都被计算错了。幸运的是，开发团队找到了问题所在并且开发了一个应急方案，但是这个方案不能撤消已经造成的损害。运算

From the docs we see that the ORA-01555 error relates to insufficient undo storage:ORA-01555:snapshot too old: rollback segment number string with name "string" too smallCaus

By Steve CallanProbably the number one database administration headache in pre-9i versions of Oracle concerns the "ORA-01555 snapshot too old" error. Lets move forward to Oracle 10g and configure

 Oracle Tips by Burleson ConsultingThis is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.Undo AdvisorThe Undo Advisor helps you perform the following tasks:

Oracle Security Tips by Burleson ConsultingThis is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.

 Oracle Tips by Burleson ConsultingMarch 26, 2002 - Updated June 28, 2007For complete details on Oracle indexing for high performance, see my book "Oracle Tuning: The Definitive Reference".Oracl

The proper use of the SQL optimizers can have a huge impact on the speed of SQL execution.  Both the rule-based and cost-based optimizers have shortcomings, and it is up to you to tune each SQL query

http://www.gplivna.eu/papers/sql_join_types.htm

What seems like ages ago, I listed 8 things you may not have known about indexes. Although I’ve since written about many of the 8 items, I’ve yet to address the last item listed:8. An index can pote

注：low-cardinality是指该列或者列的组合具有的不同值的个数较少，即该列有很多重复值。high-cardinality是指该列或者列的组合具有不同的值的个数较多，即该列有很少的重复值。理解每种索引的适用场合将对性能产生重大影响。 传统观念认为位图索引最适用于拥有很少不同值的列 ---- 例如GENDER, MARITAL_STATUS,和RELATION。但是，这种假设是不

What circumstances we use ALL_ROWS and what circumstances we use FIRST_ROWS optimizer mode? This article is written in oracle9i.First_rows attempts to optimize the query to get the very first row back

每当一个语句被执行的时候，Oracle要遵循一套方法来检查语句的语法、其中引用对象的有效性以及用户的权限。除此之外，Oracle还会检查是否已经执行了相同的语句，目的是减少处理负担。所有这些都在几分之一秒甚至更短的时间内完成，发出语句的用户毫不知晓。这个过程被称为解析。 解析的类型所有的语句，DDL或者DML在执行的时候都要被解析。关键是这个解析是软解析（语句已经被解析并且在内存中可用

PGA(程序/进程全局区)是用于保存单个进程的数据和控制信息的内存区域。例如，它一般包括一个排序区、散列区、会话游标缓存等。PGA自动调整通过设置hash_area_size、sort_area_size等参数可以手动管理PGA。为了使oracle自动管理PGA，可以设置workarea_size_policy参数为AUTO并且把pga_aggregate_target设置为PGA可以

The Oracle server maintains the redo Oracle log files to minimize the loss of data in the Database in case of an uncontrolled shutdown. Online redo Oracle log files are filled with redo records. A r

   数据库程序，一般都要使用计算机的内存和持久存储设备（例如磁盘）进行操作。磁盘提供了持久存储和保存大量信息的空间。但是，从磁盘中获得信息要比从内存中获取信息的速度慢很多，因此，很多数据库引擎利用内存来缓存信息，从而加快数据的访问速度。     信息如何存储和从什么地方获取对于终端查询用户来说是透明的，但是，如果你打算管理oracle，你就需要熟悉oracle的处理方式。本文讨论两个基础但是

      对于一个DBA来说，启动和关闭oracle数据库是常规的、基础的操作，有时候Linux管理员或者程序员也需要在开发数据库中进行一些基本的DBA操作，所以对于非DBA人员来说了解一些基础的数据库管理活动是很重要的。      本文将讨论oracle数据库的启动和关闭过程。 怎样启动oracle数据库1.用oracle用户登陆系统典型的oracle安装使用oracle作

      对于DBA来说，启动和关闭oracle监听器是很基础的任务，但是Linux系统管理员或者程序员有时也需要在开发数据库中做一些基本的DBA操作，因此了解一些基本的管理操作对他们来说很重要。      本文将讨论用LSNRCTL命令启动、关闭和查看监听器的状态的方法。 怎样启动、关闭和重新启动oracle监听器在启动、关闭或者重启oracle监听器之前确保使用lsnrctl

Oracle Tips by Burleson ConsultingOracle SGA ConceptsThe System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance?(an instance is your database program

Oracle Tips by Burleson ConsultingMost DBAs know all about the Oracle System Global Area (SGA).  The SGA is Oracles structural memory area that facilitates the transfer of data and information be

1.spfile简介spfile是oracle实例启动时的参数文件，该文件的默认位置是:$ORACLE_HOME/dbs，默认文件名为spfile$ORACLE_SID.ora。相对于老的init.ora参数文件，spfile具有如下优点：可以杜绝参数文件的繁殖：spfile总是存储在数据库服务器上，必须存在于服务器主机本身，不能放在客户机上。可以通过ALTER SYSTEM命令自

db_name - 当你执行“create database”命令时被创建，可以从v$database视图中查询。SQL> CREATE DATABASE mysid DATAFILE SIZE 400M2> DEFAULT TEMPORARY TABLESPACE temp_ts 3> TEMPFILE SIZE 10M4> UNDO TABLESPACE undo_

 临时表在oracle8i以前已经有大量的应用程序使用临时表存储处理过程中结构复杂的数据，这些表以数据库表或者PL/SQL表的形式出现。从oracle8i开始，临时表的维护和管理由服务器负责。 创建临时表可以通过create global temporary语句创建临时表，根据ON COMMIT语句的设置临时表有两种类型ON COMMIT DELETE ROWS 指定临时表

A useful feature for any type of programming is the ability to store and use temporary data. Oracle provides us this ability with temporary tables. These temporary tables are created just like any oth

Oracle9i introduces a new feature that simplifies tablespace creation. This new feature, Oracle Managed Files (OMF), makes life easier for Oracle DBAs by removing the tedium from creating and ma

Here are several ways to manually issue the "create database" syntax for Oracle:EXTREMELY minimal manual database creation OMF minimal manual create database syntax Standard Oracle c

Oracle 10g Tutorials : Oracle Net ServicesIn this tutorial you will learn Oracle Net Services - An Overview, Using Oracle Net Manager, Creating Listeners, Choosing General Parameters , enable the tr

Provided By: John Kazerooni A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these

URL: http://www.frontiernet.net/~rhode/cluster.html Abstract Introduction Obstacles to Using Oracle Clustered Tables Dynamics of Oracle Clustered Tables Evaluation of Clustered T

  Oracle Locking Survival GuideOverviewIn multi-user systems, many users may update the same information at the same time. Locking allows only one user to update a part

By Steven Feuerstein Best practices for knowing your LIMIT and kicking %NOTFOUNDI have started using BULK COLLECT whenever Ineed to fetch large volumes of data. This has caused me some