weejar zhang

oracle ACE, A DBA, blogger.

一种select产生redo的情况

by

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

procedure调用DDL需显示授权

by

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

SQL> conn test/test

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

by

[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

rman crosscheck时RMAN-00571 ORA-19554解决

by

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:

修正ORACLE表的高水位线HWM

by

HWM 全名HIGH WATER MARK

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

恢复表设置为unused的字段

by

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

oracle impdp network从备份库导入测试库

by

以前写过一篇oracle 用imp/exp做的数据迁移方法，平时最头痛的就是让从生产库或备份库copy一份最新的数据库到开发的测试库，今天如果在测试库的同一网段有备份库，..

8, impdp system/oracle directory=impdp_dir network_link=tar31 schemas=icme remap_schema=icme:icme2;