oracle 10g logminer笔记(一)

对于搞ORACLE数据库的人来说,LOGMINER这个装备已并不陌生,从oracle 8i开始官方公开了LOGMINER工具,可以分析REDO日志文件包括在线或已归档,因为知道数据库的操作都记录在REDO日志中,logminer和tkprof 一样,不过tkprof是分析TRACE文件,logminer是分析redo log.

What is backup retention policy?

The Recovery Manager (RMAN) provides the CONFIGURE RETENTION POLICY command to create a persistent and automatic backup retention policy for controlling how long backups and copies should be retained. When a backup retention policy is in effect, RMAN considers the backups and copies of data files and control files as obsolete…

delete obsolete和delete expired区别

delete exipired

If you run CROSSCHECK, and if RMAN cannot locate the files, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired backups and copies from the RMAN repository.

一种select产生redo的情况

SQL> select count(*) from zwz.testredo;

COUNT(*)
———-
52840

Execution Plan
———————————————————-
Plan hash value: 1457698251

———————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————–
| 0 | SELECT STATEMENT | | 1 | 158 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TESTREDO | 68430 | 158 (1)| 00:00:02 |
———————————————————————–

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
12787 consistent gets
0 physical reads
4780 redo size

How to add primary key on existing data of table(ORA-02437)(给已存在数据表增加主键)

SQL> alter table testcons add constraint pk_id primary key(id) enable novalidate;
alter table testcons add constraint pk_id primary key(id) enable novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (ANBOB.PK_ID) – primary key violated

一个面试题

起始地 目的地 距离(公里)
A B 1000
A C 1100
A D 900
A E 400
B D 300
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发,可达到的目的地(包括间接方式)。

procedure中truncate other schema’s table

SQL> create or replace procedure p_trun_t2
2 is
3 v_sql varchar2(1000);
4 begin
5 v_sql := ‘truncate table test2.t2’;
6 execute immediate v_sql;
7 end;
8 /

Procedure created.

procedure调用DDL需显示授权

存储过程中执行DDL与DML有很大的区别,比如你在存储过程中创建表,虽然你授于了建表的角色给它,即便是DBA,在调用时也是会提示ORA-01031: insufficient privileges,显然是权限问题,记住如果在存储过程中调用DDL要显示授权,通过ROLE传授的权限是被忽略的。下面做一个实验证明

SQL> conn test/test

,

未知导出(exp)用户的DMP文件导入(imp)方法及EXP-00091异常原因

[oracle@orazhang ~]$ exp test/test file=test.dbf

Export: Release 10.2.0.1.0 – Production on 星期三 4月 13 17:19:24 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST

oracle恢复误删的procedure存储过程

如果10分钟不小心刚刚误删了一个procedure,又没保存脚本,现在如何恢复? drop procedure必然delete dba_source,delete 当然会想到闪回查询

sql>conn test/test

sql>drop procedure p_user;

sql 存储过程中得到用户名

SQL> create or replace procedure p_user
2 is
3 begin
4 insert into test_pro(id,name,insertdate) values(userenv(‘SESSIONID’),user,sysdate);
5 end;
6 /

在32位操作系统下SGA支持大内存

怎么在配置ORACLE时把sga突破这1.7g的限制,涉及到另一个概念PAE全名Page Address Extensions,WINDOWs 2000以后的服务器版或linux都可以实现,现在介绍一下如果在WINDOWS 2003下做到让SGA支持大于1.7G的内存
………..

sql查出值中有特殊字符(不显示字符)

SQL> insert into testchar values(2,’anbob.com’||chr(10));

1 row created.

rman crosscheck时RMAN-00571 ORA-19554解决

RMAN> crosscheck backupset;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=118 devtype=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 04/12/2011 13:07:31
ORA-19554: error allocating device, device type: SBT_TAPE, device name:

EXP 边导出边压缩打包

exp 可以导出oracle 的数据库逻辑备份,但一般都是导出完成后再用压缩工具打包,有没有边导出连压缩的办法呢?有

下面就用exp\gzip实现

shell版本bash,在rhel 5下操作
….

ORACLE修改默认临时表空间

在9i之前,如果一个数据库用户没有被指定默认临时表空间,那么oracle就会使用system表空间作为该用户的临时表空间,这是很危险的。在9i里 面,database可以被指定一个默认临时表空间。
…………

修正ORACLE表的高水位线HWM

HWM 全名HIGH WATER MARK

在ORACLE中,执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记,则将导致查询语句的性能低下。下面的方法都可以降低高水位线标记….

恢复表设置为unused的字段

先说说unused的用处,当生产库中有一张特大的表,如果像删除一个字段drop column命令因是ddl,会给表级增加排它锁,所有用到该表的应用都无法查询,同样表很大,就会等待很长的时间,所以ORACLE推出了unuse,先把表的该字段设为unuse,等到一个闲的时间再去真正物理的删除,但如果你标为unused了,后悔了咋办?
….

安装透明网关 for sql server

环境:windows xp
oracle 实例,与透明网关在同一台机器, oracle 实例已安装好,监听名为listener 1521端口
oracle 10g\oracle2sqlserver–gateways 10.2
…….

oracle 安装透明网关的离奇经历

今儿打算在windows上安装oracle2sqlserver–gateways,没想到引起一串的连锁反应

还是从头说起,ORACLE 数据库是以前安装的,今天安装gateways时……