在对于一个表作相关的结构变化后，所依赖她的对象都将失效，
这里包括视图，过程，函数或包处于无效状态，但其定义仍被保存于数据字典内，
相关的权限信息，引用此视图的同义词，对象，及其他视图也 都会被保留。
当重新访问这些对象的时候，oracle会尝试自动编译这些对象。注意，表，序列，和同义词总是处于有效状态的。
Oracle 能够自动地跟踪数据库中发生的特定变化，并在数据字典中记录相关的方案对象的最新状态。
状态记录是一个递归的过程。引用对象的状态变化不仅会导致其直接依赖对象的状态变化，
同时会影响其间接依赖对象的状态。

下面来看一个简单的例子：

 

SQL> alter table test drop column z;

Table altered.

SQL> select status,object_name from user_objects;

 

STATUS
-------
OBJECT_NAME
--------------------------------------------------------------------------------
VALID
TMP_SES

VALID
BIN$SCF8qbynKMfgQAB/AQAMxw==$0

VALID
TEST

STATUS
-------
OBJECT_NAME
--------------------------------------------------------------------------------
INVALID
V_TEST

VALID
I_X

14 rows selected.

SQL> alter session set events '10046 trace name context forever,level 8';

Session altered.

SQL> select * from v_test;

     COUNT
----------
    100000

 

********************************************************************************

ALTER VIEW "TEST"."V_TEST" COMPILE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.03          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56     (recursive depth: 1)
********************************************************************************

oracle中的临时表

临时表通常用来保存一个事务或者会话期间的数据.
临时表中保存的数据是具有独立性的,只对各自会话可见,并且每个会话
都只能查询和修改属于此会话的数据,在对temporary table作dml操作时,
不需要申请锁资源,因此lock语句对于临时表来说是没有作用的.

在空间方面,在创建永久性表时通常是需要为表分配initial extent,但是对于
临时表是不需要的,临时表只是在使用的时候,根据数据来分配创建临时段.

对临时表的 DML 操作不会产生数据修改的重做日志,但是将产生被修改数据的撤销记录，

及撤销记录的重做日志.

我们来看一下临时表所产生的redo size情况.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       159        134     929956

SQL> create global temporary table temp_ses on     
  2  commit preserve rows
  3  as
  4  select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       159        134     948420

再来看一下创建一个同样大小数据量的永久性表:
SQL> select * from v$sesstat where sid=142 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        134       1432

SQL> create table pert as select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=142 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        134    5724360

SQL> select 5724360-1432 from dual;

5724360-1432
------------
     5722928

SQL>

可以看到两者的差距已经不仅仅是一个数据级了.

 

临时表一共有两种:
会话级别和事务级别的.

先来看一个会话级别的.

SQL> create global temporary table tmp_ses on commit preserve rows
  2  as  select * from dba_objects;

Table created.

SQL>

on commit preserve rows表明这一个基于会话的临时表,在会话断开以后.
所有数据都将被抹去.

SQL> create global temporary table tmp_trans on commit delete rows
  2  as select * from dba_objects;

Table created.

SQL>

on commit delete rows表示这是一个基于事务的临时表，在会话提交的时候，数据

就会被自动清除掉。

SQL> insert into tmp_trans select * from dba_objects;

49772 rows created.

SQL> select count(*) from tmp_trans;

  COUNT(*)
----------
     49772

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_trans;

  COUNT(*)
----------
         0

这里可以看到在事务commit以后，临时表中的数据被全部清空。而这个清空的过程

是几乎不存在开销，oracle完成的仅仅是把临时段回收的一个动作。

临时段的分配

临时表使用临时段来分配数据，因此在创建临时表的时候，oracle并不会为其分配段，

而是在使用的时候才分配。我们可以使用v$sort_usage来观察某个临时表所占用的

空间大小。

SQL> select * from v$sort_usage;

no rows selected

SQL> insert into tmp_ses select * from dba_objects;

49772 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

  COUNT(*)
----------
     49772

SQL> select distinct sid from v$mystat;

       SID
----------
       142

SQL> select sid,serial# from v$session where sid=142;

       SID    SERIAL#
---------- ----------
       142        168

SQL>  select username,user,session_num,
  2  tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

这里可以看到，通过与v$session视图中的serial#相关联，可以得出某个会话的

所拥有的临时表中的数据的大小，当然，这里与永久表一样，在delete的时候

是不会释放出空间的：

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

SQL> delete tmp_ses;

19772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

  COUNT(*)
----------
         0

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

SQL>

SQL> truncate table tmp_ses;

Table truncated.

SQL>  select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

no rows selected

可以看到，在删除数据时，oracle采用了节省成本的方式，减少了不必要的开销。

关于临时表的事务，与事务相关的临时表中的数据可以被用户的事务及子事务访问。

但是这些数据不能被同一会话里的两个事务同时访问。不同会话中的事务可以同时

使用同一个事务相关的临时表。如果用户事务对临时表执行了 INSERT 操作，

在此之后此事务的子事务将不能使用这个临时表。

如果在子事务中对临时表执行了 INSERT 操作，临时表中已有的数据将被清除。

子事务结束后，父事务及其他子事务对此临时表访问权利将被恢复。
　

tom写的一个用来生成为sqlldr准备的格式.

其中有unix和windows两个版本.

在itpub中有人已经上传.

具体可以到这里下载:

http://www.itpub.net/thread-927857-1-1.html

关于这个脚本,需要注意的是,由于tom只是输出一个例子.因此并未把数据写入至文件,

其实稍把sqlldr_exp.sql脚本作一修改即可实现, 如下:

 

set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off

spool ytmpy.sql

prompt set head off;
prompt set feedback off;
prompt spool aa.txt
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select  'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from    user_tab_columns
where   table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA

prompt  select
select  lower(column_name)||'||chr(124)||'
from    user_tab_columns
where   table_name = upper('&1') and
    column_id != (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
    order by column_id
/
select  lower(column_name)
from    user_tab_columns
where   table_name = upper('&1') and
    column_id = (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
    order by column_id
/
prompt  from    &1
prompt  /
prompt spool off
prompt set feedback on
prompt set head on
spool off
set termout on
@ytmpy.sql
exit

 

 

SQL> declare
  2           l_rows  number;
  3        begin
  4           l_rows := dump_csv( 'select *
  5                                            from all_users
  6                                          where rownum < 5',
  7                                        ' , ' , 'TMP', 'test.dat' );
  8        end;
  9  /

 

PL/SQL procedure successfully completed.

 

[oracle@rac2 tmp]$ pwd
/tmp
[oracle@rac2 tmp]$ cat test.dat
SYS , 0 , 03-AUG-07
SYSTEM , 5 , 03-AUG-07
OUTLN , 9 , 03-AUG-07
DIP , 13 , 03-AUG-07

 

 

 

 

 

 

see  http://asktom.oracle.com

spfile在11G的一点改进

在11G中，oracle为保护spfile也做了一些小小的改进，

例如，在运行时，spfile无法中被删除时，可以从memory

中直接读出所有参数的值

模拟spfile被删除
[oracle@rac2 dbs]$ rm spfileorcl.ora


尝试修改参数，oracle会报错
SQL> alter system set sga_max_size=317M scope=spfile;
alter system set sga_max_size=317M scope=spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '/opt/u01/app/oracle/11g/dbs/spfile.ora'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


使用以下命令创建一个新的spfile,但由于spfile正被oracle使用，而运行时中oracle并不实时
去监控spfile是否存在，因此需要为新的spfile指定一个新的名字。如spfile.ora
SQL> create spfile from memory;
create spfile from memory
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance  




SQL> create spfile='/opt/u01/app/oracle/11g/dbs/spfile.ora' from memory;

File created.

需要注意的是，这个命令读出的是当前内存中的值，当然也可以使用这个方式来
备份当前的spfile.可以看出oracle越来越方便

-

在整理我收藏过的一些文章,放在这里,供所有需要的人一起学习,

出自:http://www.itpub.net/371230.html

ORACLE PL/SQL 例外处理
1) 基本结构
BEGIN
... --语句
EXCEPTION -- 例外处理
WHEN ...
...
WHEN OTHERS
...
END;

2) 常用预定义例外
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN -- ORA-06511 SQLCODE = -6511 游标已经打开
...
WHEN DUP_VAL_ON_INDEX THEN -- ORA-00001 SQLCODE = -1 向唯一索引中插入重复数据
...
WHEN INVALID_CURSOR THEN -- ORA-01001 SQLCODE = -1001 非法游标操作
...
WHEN INVALID_NUMBER THEN -- ORA-01722 SQLCODE = -1722 字符向数字转换失败
...
WHEN LOGIN_DENIED THEN -- ORA-01017 SQLCODE = -1017
...
WHEN NO_DATA_FOUND THEN -- ORA-01403 SQLCODE = +100 没有找到数据
...
WHEN NOT_LOGGED_ON THEN -- ORA-01012 SQLCODE = -1012
...
WHEN PROGRAM_ERROR THEN -- ORA-06501 SQLCODE = -6501 程序错误
...
WHEN STORAGE_ERROR THEN -- ORA-06500 SQLCODE = -6500
...
WHEN TIMEOUT_ON_RESOURCE THEN -- ORA-00051 SQLCODE = -51
...
WHEN TOO_MANY_ROWS THEN -- ORA-01422 SQLCODE = -1422 返回多行
...
WHEN TRANSACTION_BACKED_OUT THEN -- ORA-00061 SQLCODE = -61
...
WHEN VALUE_ERROR THEN -- ORA-06502 SQLCODE = -6502 数值转换错误
...
WHEN ZERO_DIVIDE THEN -- ORA-01476 SQLCODE = -1476 被零除
...
WHEN OTHERS THEN -- 其它任何错误的处理
...
END;

3) 用户定义的例外
DECLARE
FIND_DATA_EMP EXCEPTION;
BEGIN
IF ... THEN
RAISE FIND_DATA_EMP;
END IF;
EXCEPTION
WHEN LOB_NO_LOCKED THEN
...
END;
4) EXCEPTION_INIT的使用
PRAGMA EXCEPTION_INIT(例外名, ORACLE错误号);
注：PRAGMA 是一个编译器命令，可以认为是对编译器的一个注释。
例：
DECLARE
ZERO_DIVIDE1 EXCEPTION;
PRAGMA EXCEPTION_INIT(ZERO_DIVIDE1, -1476);
BEGIN
...
EXCEPTION
WHEN ZERO_DIVIDE1 THEN
...
END;

DML Error Logging in Oracle 10g

主要在于使用DBMS_ERRLOG.create_error_log 这个包来跟踪dml错误信息

SQL> CREATE TABLE source (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10),
4 description VARCHAR2(50),
5 CONSTRAINT source_pk PRIMARY KEY (id)
6 );

表已创建。

SQL> DECLARE
2 TYPE t_tab IS TABLE OF source%ROWTYPE;
3 l_tab t_tab := t_tab();
4 BEGIN
5 FOR i IN 1 .. 100000 LOOP
6 l_tab.extend;
7 l_tab(l_tab.last).id := i;
8 l_tab(l_tab.last).code := TO_CHAR(i);
9 l_tab(l_tab.last).description := 'Description for ' || TO_CHAR(i);
10 END LOOP;
11
12 -- For a possible error condition.
13 l_tab(1000).code := NULL;
14 l_tab(10000).code := NULL;
15
16 FORALL i IN l_tab.first .. l_tab.last
17 INSERT INTO source VALUES l_tab(i);
18
19 COMMIT;
20 END;
21 /

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE dest (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10) NOT NULL,
4 description VARCHAR2(50),
5 CONSTRAINT dest_pk PRIMARY KEY (id)
6 );

表已创建。

SQL> CREATE TABLE dest_child (
2 id NUMBER,
3 dest_id NUMBER,
4 CONSTRAINT child_pk PRIMARY KEY (id),
5 CONSTRAINT dest_child_dest_fk FOREIGN KEY (dest_id) REFERENCES dest(id)
6 );

表已创建。

SQL> BEGIN
2 DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
3 END;
4 /

PL/SQL 过程已成功完成。

SQL> SELECT owner, table_name, tablespace_name
2 FROM all_tables
3 WHERE owner = 'TEST';

OWNER TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
TEST TEST
USERS

SQL> DESC err$_dest
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
ID VARCHAR2(4000)
CODE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)

SQL> INSERT INTO dest
2 SELECT *
3 FROM source;
SELECT *
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> INSERT INTO dest
2 SELECT *
3 FROM source
4 LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

已创建99998行。

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'INSERT';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10;
SET code = DECODE(id, 9, NULL, 10, NULL, code)
*
第 2 行出现错误:
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10
4 LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

已更新8行。

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'UPDATE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1407
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

1407
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

SQL> DELETE FROM dest
2 WHERE id > 50000;

已删除50000行。

SQL> MERGE INTO dest a
2 USING source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE SET a.code = b.code,
6 a.description = b.description
7 WHEN NOT MATCHED THEN
8 INSERT (id, code, description)
9 VALUES (b.id, b.code, b.description);
VALUES (b.id, b.code, b.description)
*
第 9 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> MERGE INTO dest a
2 USING source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE SET a.code = b.code,
6 a.description = b.description
7 WHEN NOT MATCHED THEN
8 INSERT (id, code, description)
9 VALUES (b.id, b.code, b.description)
10 LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 行已合并。

SQL> MERGE INTO dest a
2 USING source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE SET a.code = b.code,
6 a.description = b.description
7 WHEN NOT MATCHED THEN
8 INSERT (id, code, description)
9 VALUES (b.id, b.code, b.description)
10 LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 行已合并。

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'MERGE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> INSERT INTO dest_child (id, dest_id) VALUES (1, 100);

已创建 1 行。

SQL> INSERT INTO dest_child (id, dest_id) VALUES (2, 101);

已创建 1 行。

SQL> DELETE FROM dest;
DELETE FROM dest
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录

SQL> DELETE FROM dest
2 LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;

已删除99996行。

SQL> COLUMN ora_err_mesg$ FORMAT A69
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'DELETE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
---------------------------------------------------------------------
2292
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录

2292
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录

参考oraclebase

DBA们经常需要监控数据库中一些花费大量时间的操作，如备份恢复，收集统计信息，排序都会记录在这个视图当中。
这个视图主要是显示运行时间超过6秒的数据库操作的状态.所以对于数据库监控一些耗时的操作是非常有意义的。
并且可以看到某个进程的执行进度。
来看看这个视图的结构：
SQL> desc v$session_longops
名称 是否为空? 类型
----------------------------------------- -------- ----------------

SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64)
TARGET VARCHAR2(64)
TARGET_DESC VARCHAR2(32)
SOFAR NUMBER
TOTALWORK NUMBER
UNITS VARCHAR2(32)
START_TIME DATE
LAST_UPDATE_TIME DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
QCSID NUMBER


其中SID和SERIAL#是与v$session中的匹配的，
OPNAME：指长时间执行的操作名.如：Table Scan
TARGET：被操作的object_name. 如：tableA
TARGET_DESC：描述target的内容
SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。
TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。
UNITS：
START_TIME：进程的开始时间
LAST_UPDATE_TIM：最后一次调用set_session_longops的时间
TIME_REMAINING： 估计还需要多少时间完成，单位为秒
ELAPSED_SECONDS：指从开始操作时间到最后更新时间
CONTEXT：
MESSAGE：对于操作的完整描述，包括进度和操作内容。
USERNAME：与v$session中的一样。
SQL_ADDRESS：关联v$sql
SQL_HASH_VALUE：关联v$sql
QCSID：主要是并行查询一起使用。

SQL> conn aa/admin
已连接。
SQL> insert into test select * from test ;

已创建898432行

打开另一个会话,可使用以下脚本来观察之前那个会话所作的操作的执行状态.

SQL> select b.*
2 from v$session a, v$session_longops b
3 where a.sid=b.sid
4 and a.serial#=b.serial#
5 /

SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS CONTEXT MESSAGE
---- ---------- -------------- --------- ------ ---------- -------- ----------- ---------------- -------------- --------------- ---------- -----------------------------------------------------
10 74 Table Scan AA.TEST 6623 12389 Blocks 2007-5-7 2: 2007-5-7 2:52:03 30 34 0 Table Scan: AA.TEST: 6623 out of 12389 Blocks done

SQL>

可以看到现在是在对aa.test作表扫描。预计有12389个blocks,已然完成了6623个,已经使用了34秒，大概还需要30秒可以完成。
可以看出大概195块/秒的速度来扫描表。

DOCID :169706.1

最新的安装文档.各种平台都有.保留一下.

一个用户说要为数据仓库选型.

制定了一套基准测试方案.sybase由原厂的工程师做.ORACLE这这我负责.

发现加载数据ORACLE快得多.

5000万的数量量.sqlldr 5分钟搞定.不过sybase可是用了将近20分钟啊.在检索方面ORACLE当然也虽强于SYBASE啦.

上周去巡检.结果一个用户问我如何使用sql语句来查看alert日志中的内容. 当时想着用存储过程来做.回来后想了想,用external测试一下看看:

SQL> !pwd
/u01/app/oracle/admin/ORALINUX/bdump

create directory BDUMP as '/u01/app/oracle/admin/ORALINUX/bdump';

SQL> !ls
ALERT_LOG_3220.bad alert_ORALINUX.log oralinux_lgwr_3202.trc oralinux_lgwr_7056.trc
ALERT_LOG_3220.log oralinux_lgwr_3123.trc oralinux_lgwr_6993.trc oralinux_pmon_7048.trc

create table alert_log ( text varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_ORALINUX.log')
)
reject limit 1000;

SQL> select * from alert_log where text like '%ORA-%';

TEXT
--------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/ORALINUX/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u02/oradata/ORALINUX/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/oradata/ORALINUX/redo03.log'
ORA-27037: unable to obtain file status

删除一个JOB需要提交

一般来说，执行一个存储过程是不需要COMMIT的。但是DBMS_JOB.REMOVE有例外：

按文档中的描述是：You must issue a COMMIT statement immediately after the statement.

版本：10.2.0.1
windows
今天在做一个测试的时候发现的，
记录一下：

会话1:
SQL> variable job number;
SQL> begin
2 dbms_job.submit(:job,'ab;',sysdate,'sysdate+1');
3 commit;
4 end;
5 /
SQL> col what format a10;
SQL> select job,what from user_jobs;

JOB WHAT
---------- ----------
2 ab;

SQL> exec dbms_job.remove(2);

PL/SQL 过程已成功完成。

SQL> rollback;

回退已完成。

SQL> select job,what from user_jobs;

JOB WHAT
---------- ----------
2 ab;

SQL> exec dbms_job.remove(2);

PL/SQL 过程已成功完成。

SQL> select job,what from user_jobs;

未选定行

SQL>

会话2：
SQL> select job from dba_jobs;

JOB
----------
2

SQL>

会话1:
SQL> commit;

提交完成。

SQL> select job,what from user_jobs;

未选定行

会话2:
SQL> select job from dba_jobs;

JOB
----------

SQL>

试一下回滚：

SQL> variable job number;
SQL> begin
2 dbms_job.submit(:job,'ab;',sysdate,'sysdate+1');
3 commit;
4 end;
5 /

PL/SQL 过程已成功完成。

SQL> select job from dba_jobs;

JOB
----------
21

SQL> exec dbms_job.remove(21);

PL/SQL 过程已成功完成。

SQL> select job from dba_jobs;

未选定行

SQL> rollback;

回退已完成。

SQL> select job from dba_jobs;

JOB
----------
21

SQL>

default pool:所有段块一般都缓存在这个池中,
keep pool:对于访问相对频繁的段会放在这个池中,因为如果把这些段放在default pool中,尽管会频繁访问,
但也可以因为其他段需要空间而使其age out.
recyle pool:访问不频繁,也就是比较随机的大段可以放在这个池中,这个块会导致过量的缓冲区刷新输出,而且不会带来任何好处,
因为等你想再用这个块时,它可以已经被age out.要把这些段与default pool 和 recyle pool 中的段分开,这样就不会导致default pool
和recyle pool的块被age out.

一些初学者对于sqlplus的使用很是不方便,其实用得好,这个工具当然是首选.下面帖出一段login.sql的脚本以方便参照:

以下是我机器上的glogin.sql

[oracle@rac1 admin]$ vi glogin.sql

COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
COLUMN units_col_plus_show_sga FORMAT a15
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 200
set pagesize 9999
column plan_plus_exp format a30
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname>'
set termout on

这是昨天在论坛上回答问题:

原问题为:

如：列出2006.7所有的日期：
2006.7.1
2006.7.2
2006.7.3
……
2006.7.31
要求，只能是一个SQL语句，可以嵌套。

想了想. 尽量不用ALL_OBJECTS.

且不嵌大.觉得只能是用CONNECT BY 来递归了:

这是我的回答,主要是CONNECT BY配合ROWNUM来使用.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> select to_date('2006-07-' || rownum, 'yyyy-mm-dd')
2 from dual
3 connect by rownum <= 31
4 /

TO_DATE('2006-07-'||ROWNUM,'YY
------------------------------
2006-7-1
2006-7-2
2006-7-3
2006-7-4
2006-7-5
2006-7-6
2006-7-7
2006-7-8
2006-7-9
2006-7-10
2006-7-11
2006-7-12
2006-7-13
2006-7-14
2006-7-15
2006-7-16
2006-7-17
2006-7-18
2006-7-19
2006-7-20

TO_DATE('2006-07-'||ROWNUM,'YY
------------------------------
2006-7-21
2006-7-22
2006-7-23
2006-7-24
2006-7-25
2006-7-26
2006-7-27
2006-7-28
2006-7-29
2006-7-30
2006-7-31

31 rows selected

SQL>

dd

from dba-oracle.com

The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain. The following discussion only applies if you are not using Automatic Segment Space Management (ASS Management).

The ASS management tablespace is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

The setting for PCTFREE sets the value for the percent of a block to reserve for updates. A block will remain on a FREELIST until it reaches blocksize * (1-(ptcfree/100)) full or greater. Here are the main issues with incorrect settings:

• High migrated/chained rows - If PCTFREE is to small, adequate space may not be reserved in the block for update of variable sized rows in the block, or, may not have enough space for a complete row insert. In this case a block chaining will occur where the data is migrated to a new block and a pointer will be established from the old block to a new block.

• High I/O - This will result in doubling the IO required to retrieve this data. For new data blocks, the space available for inserts is equal to the block size minus the sum of the block overhead (84-107 bytes) and free space (PCTFREE/100 * blocksize). When you update existing data Oracle uses any available space in the block. So, updates will eventually reduce the available space in a block to less than PCTFREE, the space reserved for updates but not accessible to inserts. This removes the block form the freelist on which it resides.

The un-link process

Blocks with total filled volume less than BLOCKSIZE – overhead – (blocksize*(1-(PCTFREE/100)) are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in the selected block is not large enough to accommodate the data in the INSERT statement, and the block is at least filled to the value PCTUSED, then Oracle will remove the block from the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

The Re-link Process

After processing a DELETE or UPDATE statement, Oracle checks to see if the space being used in the block is now less than (BLOCKSIZE – overhead) * PCTUSED/100. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. However, it is only when the transaction commits, that the free space in the block becomes available for other transactions.

For tables with high levels of updates, setting PCTFREE to a high value is suggested, for blocks which are never updated, then set this to a low value. For example, for a high update table a setting of 40-50 is acceptable, while for a low or no update table (such as in a data warehouse) a setting of 5 or less is acceptable. PCTUSED must be set to less than 100-PCTFREE and should be set such that PCTFREE+PCTUSED is less than 100. If PCTFREE+PCTUSED=100 this can result in see-sawing of the block on and off the free lists which can be a performance issue.

在调优过程中,经常需要了解某张表,特别是大表的一些情况.

启动表监控是常用手段之一.

环境：Linux as 3/Oracle10.2.0.1

测试过程如下：

16:52:14 test>create table test(a number);

表已创建。

已用时间: 00: 00: 00.00

16:52:24 test>alter table test monitoring;--开始监控

表已更改。

已用时间: 00: 00: 00.00

16:52:35 test>insert into test values(1);

已创建 1 行。

已用时间: 00: 00: 00.03

16:52:46 test>insert into test values(2);

已创建 1 行。

已用时间: 00: 00: 00.00

16:52:49 test>insert into test values(3);

已创建 1 行。

已用时间: 00: 00: 00.00

16:52:51 test>insert into test values(4);

已创建 1 行。

已用时间: 00: 00: 00.00

16:52:54 test>insert into test values(5);

已创建 1 行。

已用时间: 00: 00: 00.00

16:52:56 test>commit;

提交完成。

已用时间: 00: 00: 00.00

16:53:00 test>update test set a = 8 where a = 4;

已更新 1 行。

已用时间: 00: 00: 00.00

16:53:22 test>update test set a = 10 where a = 5;

已更新 1 行。

已用时间: 00: 00: 00.00

16:53:28 test>commit;

提交完成。

已用时间: 00: 00: 00.00

16:53:30 test>delete test where a < 5;

已删除3行。

已用时间: 00: 00: 00.00

16:53:44 test>commit;

提交完成。

已用时间: 00: 00: 00.00

16:53:46 test>truncate table test;

表已截掉。

已用时间: 00: 00: 00.00

16:53:51 test>exec dbms_stats.flush_database_monitoring_info;--通过包来刷新数据库监控信息

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.00

16:54:12 test>select table_name,inserts,updates,deletes,truncated from sys.dba_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU

============================== ======= ======= ======= ===

TEST 5 2 3 YES

已用时间: 00: 00: 00.02

16:54:24 test>alter table test nomonitoring;--取消监控，这个命令会把sys.dba_tab_modifications表中的监控信息清空。

表已更改。

已用时间: 00: 00: 00.00

16:54:58 test>select table_name,inserts,updates,deletes,truncated from sys.dba_tab_modifications;

未选定行

已用时间: 00: 00: 00.02

16:55:00 test>

总结：

通过这个测试可以看出，我们可以在实际工作中对表进行监控。

监控的步骤如下：

1) 开始监控命令：alter table test monitoring

2) 实际对表的一些操作

3) 通过包来刷新数据库监控信息，dbms_stats.flush_database_monitoring_info

4) 查询对表的操作信息，sys.dba_tab_modifications

5) 取消监控命令alter table test nomonitoring，这个命令会把sys.dba_tab_modifications表中的监控信息清空。

ALTER SYSTEM SWITCH LOGFILE ;

SWITCH LOGFILE Clause

The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

ALTER SYSTEM ARCHIVE LOG CURRENT ;

CURRENT Clause

Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, then Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify CURRENT only when the database is open.

ALTER SYSTEM ARCHIVE LOG CURRENT NOSWITCH;

NOSWITCH

Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.

You can use the NOSWITCH clause only when your instance has the database mounted but not open. If the database is open, then this operation closes the database automatically. You must then manually shut down the database before you can reopen it.

创建两个表.其实test为我们在操作的表.而script为记录对test表进行dml操作的script:

SQL> create table SCRIPT
2 (
3 SCRIPT VARCHAR2(4000),
4 TABLENAME VARCHAR2(20),
5 RUNDATE DATE
6 )
7 /

Table created

SQL>
SQL> create table TEST
2 (
3 ID NUMBER not null,
4 NAME VARCHAR2(20)
5 )
6 /

Table created

--创建触发器

SQL> Create Or Replace Trigger Trg_Test
2 After Insert Or Update Or Delete On Test
3 For Each Row
4 Declare
5 l_Dmlsql Varchar2(4000);
6 Begin
7 If Inserting Then
8 l_Dmlsql := 'insert into test(id,name) values(' || :New.Id || ',' || :New.Name || ');';
9 Elsif Updating Then
10 l_Dmlsql := 'UPDATE TEST SET name=' || :New.Name || ' where id=' || :Old.Id || ' ;';
11 Elsif Deleting Then
12 l_Dmlsql := 'DELETE test WHERE id=' || :Old.Id || ' ;';
13 End If;
14 Insert Into script (script, tablename, rundate) Values (l_Dmlsql, 'TEST', Sysdate);
15 End;
16 /

Trigger created

SQL>

--测试

SQL> insert into test values(1,'aa');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into test values(2,'bb');

1 row inserted

SQL> update test set name='xy' where id=2;

1 row updated

SQL> delete test;

2 rows deleted

SQL> commit;

Commit complete

SQL> select * from script;

SCRIPT TABLENAME RUNDATE
-------------------------------------------------------------------------------- -------------------- -----------
insert into test(id,name) values(1,aa); TEST 2006-6-15 1
insert into test(id,name) values(2,bb); TEST 2006-6-15 1
UPDATE TEST SET name=xy where id=2 ; TEST 2006-6-15 1
DELETE test WHERE id=1 ; TEST 2006-6-15 1
DELETE test WHERE id=2 ; TEST 2006-6-15 1

SQL>

注意:这里没有对具体的数据类型作处理.可以另写函数实现.

因为10G安装过程中是不带Gateway

因为10G安装过程中是不带Gateway

需要到otn上下载安装组件.

安装过程这里不再敖述:

说明:

我的oracle和sqlserver安装在同一台主机上:

IP:192.168.100.102

sqlserver database:pub

第一步 修改配置文件.

在ORACLE_HOMEtg4msqladmininittg4msql.ora
目录下:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=192.168.100.102;database=pub"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

第二步:修改listener.ora.如下内容:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = tg4msql)
(ORACLE_HOME = F:oracleproductdb_1)
(PROGRAM = tg4msql)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.102)(PORT = 1521))
)
)

第三步:修改tnsname.ora 添加以下内容

hs_sql=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.100.102)(PORT=1521))
(CONNECT_DATA=(SID=tg4msql))
(HS=OK))

注:这里的sid与listener.ora里面的sid要求一样:

以上过程完成以后重启监听:

在命令行下:

lsnrctl stop

lsnrctl start

第四步.

创建dblink:

注意.在创建dblink的过程中有以下几点需要注意.

如果global_names 为true
那么在创建dblink时需要带oracle默认的域名:

否则的话将会报类似于以下的错误:
ORA-02085 "database link HO.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to HO.WORLD

解决方法是:
把dblink name创建为ho.world(当然你那里可能会有不同的名称)

如果global_names为false
那么按照你自己的名称来创建.

我这里的环境:
SQL> show parameter global_names;

NAME TYPE VALUE
------------------------------------ ----------- -------------------
global_names boolean true
SQL> create PUBLIC database link pub connect to sa identified by "123" using 'hs_sql';

SQL> select * from test@pub;
select * from test@pub
*
第 1 行出现错误:
ORA-02085: 数据库链接 PUB.REGRESS.RDBMS.DEV.US.ORACLE.COM 连接到 HO.WORLD

SQL> create public database link HO.WORLD connect to sa identified by "123" using 'hs_sql';

数据库链接已创建。

SQL> SELECT * FROM TEST@HO.WORLD;

未选定行

SQL> alter system set global_names=false;

系统已更改。

SQL>

SQL> create public database link HO connect to sa identified by "123" using 'hs_sql';

数据库链接已创建。

SQL> desc test@ho;
名称
--------------------------------------------------------------------------------------
id
name