ORACLE 9i-23ai – Page 57

DB_nk_CACHE_SIZE中的granule

2011-05-032011-05-03 by weejar zhang

….
这里涉及到了SGA中内存分配的粒度问题，粒度是连续虚拟内存分配的单位,在9I版本引入了一个粒度（granule）的概念，如果SGA小于128M，则粒组大小为4M，否则为16M；在10G版本中，如果SGA小于1G，则粒组为4M，否则大于4M。粒度大小受内部隐含参数_ksmg_granule_size的控制
…….

linux 命令从redo日志中找sql

2011-04-28 by weejar zhang

用linux的命令是可以读出redo日志的

[oracle@orazhang windb]$ strings redo1.log |sed -n ‘/create procedure/,/end;/p’
create procedure ptest
begin
dbms_output.put_line(‘nothing’);
end;

copy 数据文件数据库移植从windows到linux

2011-04-28 by weejar zhang

今天闲来无事，做了个极其无聊的实验，把windows上的orcl数据库的数据文件（dbf）复制到linux上会不会也能用呢？我觉的工作中除了测试没人这么搞，下面把步骤分享一下，今天试了一天，一步错步步错呀！

pc1:winxp 32bit oracle10.2.0.1 dbname:orcl
pc2:rehl 5 linux 32bit oracle 10.2.0.1 dbname:orcl

oracle 10g logminer笔记(三)

2020-04-242011-04-27 by weejar zhang

logminer实战篇

1，session 1 ，建立几个对象，并做更新
2，session 2, 进行logmnr分析
3, session 3,等session2分析出来后,能不能查看他的结果?
4，先不启用supplemental log,进行分析
5, 用其中两种数据字典分析online data dictionary、flat file dictionary
6，启用supplemental log,进行分析

oracle 10g logminer笔记(二)

2024-08-062011-04-27 by weejar zhang

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

启动最小附加日志才可以LOGMNR中看到DML，在做DG中也会提到这个设置，这个参数在ORACLE 9I的9.1版本是个界限，以前默认打开，新版本默认关闭。但SUPPLEMENTAL LOG会给系统带来什么影响呢？redo 增长速度会加快么？会

oracle 10g logminer笔记(一)

2025-01-072011-04-26 by weejar zhang

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

What is backup retention policy?

2011-04-25 by weejar zhang

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区别

2011-04-25 by weejar zhang

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的情况

2011-04-22 by weejar zhang

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)(给已存在数据表增加主键)

2013-12-062011-04-21 by weejar zhang

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

一个面试题

2011-04-19 by weejar zhang

起始地目的地距离（公里）
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

2011-04-18 by weejar zhang

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需显示授权

2011-04-18 by weejar zhang

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

SQL> conn test/test

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

2011-04-13 by weejar zhang

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

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

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存储过程

2011-04-13 by weejar zhang

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

sql>conn test/test

sql>drop procedure p_user;

sql 存储过程中得到用户名

2011-04-13 by weejar zhang

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支持大内存

2011-04-12 by weejar zhang

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

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

2011-04-12 by weejar zhang

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

1 row created.

rman crosscheck时RMAN-00571 ORA-19554解决

2011-04-12 by weejar zhang

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 边导出边压缩打包

2011-04-11 by weejar zhang

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

下面就用exp\gzip实现

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