-- 摘自Jonathan Lewis的CBO基础
-- 在解释查询变换时，举的一个小例子,看完觉得有点意思，与大家分享

两个数学家在在同学聚会上开始谈论他们的家庭，作为数学家，他们的谈话当然要含蓄点
甲：你有孩子么？
乙：是的，有3个女儿
甲：她们多大了
乙：如果把她们的年龄相乘，结果为36
甲：不够确切
乙：如果把她们的年龄相加，结果为这个房间的人数
甲：（环视整个房间后）仍不够确切
乙：我的大女儿有一只宠物仓鼠，它有一条木头腿
甲：（此时甲已经知道其三个女儿的年龄）那么，这两个两岁大的是双胞胎吗？

如何使用SQL来推导3个女儿的年龄呢？

查看数据库缺省的临时表空间
查看数据库时区
SQL> select property_name,property_value from database_properties;

PROPERTY_NAME PROPERTY_VALUE
------------------------------ -------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP
DBTIMEZONE +08:00
NLS_LANGUAGE AMERICAN

SQL> conn wangwang/ww@orcl
Connected.
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn system/oracle@orcl
Connected.
SQL> @D:oracleora92rdbmsadminutlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
SQL> conn sys/oracle@orcl as sysdba
Connected.
SQL> @D:oracleora92sqlplusadminplustrce.sql

SQL> grant plustrace to public;

Grant succeeded.

SQL>

SQL> conn wangwang/ww@orcl
Connected.
SQL> set autotrace on statistics

tag: DBA 笔记

topic: alter system reset
重置初始化参数
语法：
ALTER SYSTEM RESET parameter_name [SCOPE = MEMORY|SPFILE|BOTH] [SID= ‘sid’|’*’]

-- XXXX库恢复一例

-- XXXX库，说昨天停电了，服务器启来后，数据库不能启动
-- 红旗LINUX， ORACLE 9I RAC， OCFS

-- 查看了一下，告警日志中提示如下：
Tue Jul 22 14:33:39 2008
ARC1: Archival stopped
Tue Jul 22 14:33:43 2008
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jul 22 14:34:55 2008
Starting ORACLE instance (normal)
Tue Jul 22 14:34:55 2008
Global Enqueue Service Resources = 10946, pool = 4
Tue Jul 22 14:34:55 2008
Global Enqueue Service Enqueues = 16733
Tue Jul 22 14:34:55 2008
WARNING: EINVAL creating segment of size 0x00000000860c5000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 1
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.6.0.
System parameters with non-default values:
processes = 500
timed_statistics = TRUE
shared_pool_size = 838860800
sga_max_size = 2232914336
large_pool_size = 16777216
java_pool_size = 0
spfile = /oradata/pdsdb/spfilepdsdb.ora
control_files = /oradata/pdsdb/control01.ctl, /oradata/pdsdb/control02.ctl,

/oradata/pdsdb/control03.ctl
db_block_size = 8192
db_cache_size = 1056964608
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=/arch1
log_archive_format = %t_%s.dbf
db_file_multiblock_read_count= 16
cluster_database = TRUE
cluster_database_instances= 2
thread = 1
fast_start_mttr_target = 300
instance_number = 1
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = pdsdb1
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/pdsdb/bdump
user_dump_dest = /opt/oracle/admin/pdsdb/udump
core_dump_dest = /opt/oracle/admin/pdsdb/cdump
sort_area_size = 16777216
db_name = pdsdb
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 524288000
aq_tm_processes = 1
Tue Jul 22 14:34:55 2008
cluster interconnect IPC version:Oracle UDP/IP
IPC Vendor 1 proto 2 Version 1.0
PMON started with pid=2
DIAG started with pid=3
LMON started with pid=4
LMD0 started with pid=5
LMS0 started with pid=6
LMS1 started with pid=7
DBW0 started with pid=8
LGWR started with pid=9
CKPT started with pid=10
SMON started with pid=11
RECO started with pid=12
CJQ0 started with pid=13
QMN0 started with pid=14
Tue Jul 22 14:34:59 2008
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=15
ARC0: Archival started
ARC1 started with pid=16
Tue Jul 22 14:34:59 2008
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Jul 22 14:34:59 2008
ARC1: Archival started
Tue Jul 22 14:34:59 2008
Tue Jul 22 14:34:59 2008
ARC1: Thread not mounted
ARC0: Thread not mounted
Tue Jul 22 14:34:59 2008
ALTER DATABASE MOUNT
Tue Jul 22 14:34:59 2008
lmon registered with NM - instance id 1 (internal mem no 0)
Tue Jul 22 14:34:59 2008
Reconfiguration started (old inc 0, new inc 1)
List of nodes:
0
Global Resource Directory frozen
one node partition
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Resources and enqueues cleaned out
Resources remastered 0
0 GCS shadows traversed, 0 cancelled, 0 closed
0 GCS resources traversed, 0 cancelled
set master node info
Submitted all remote-enqueue requests
Update rdomain variables
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
0 GCS shadows traversed, 0 replayed, 0 unopened
Submitted all GCS remote-cache requests
0 write requests issued in 0 GCS resources
0 PIs marked suspect, 0 flush PI msgs
Tue Jul 22 14:35:00 2008
Reconfiguration complete
Post SMON to start 1st pass IR
Tue Jul 22 14:35:00 2008
This instance was first to mount
LCK0 started with pid=18
***
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block -
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.000048b1 seq: 0x1 flg: 0x04
consistency value in tail: 0x48ae1501
check value in block header: 0x5a3, computed block checksum: 0x1f
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

undo的管理方式

ARCn CKPT DBWn

-- ===============第 7 章 优化表空间==================== --

一位弟兄做导出时遇到麻烦如下:

$ export ORACLE_SID=ctais
$ exp htjsweb/htjsyfzxweb file=ctais.dmp owner=htjsweb

Export: Release 9.2.0.6.0 - Production on Thu May 29 16:05:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

-- 笔记中部分内容

SQL> create table tt2 as select * from employee;

Table created.

SQL> drop table tt2;

Table dropped.

SQL> select * from tt2;
select * from tt2
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table tt2 to before drop;

Flashback complete.

SQL> select count(1) from tt2;

COUNT(1)
----------
32

如果在表删除后,又新建了这个表,恢复时可以重命名,如下:

em上的口令验证问题(Host Credentials)

http://docs.google.com/Doc?id=dgdk3pdk_123mqfgwmk

1、在原数据库上，做个视图，对保存汉字的字段，加包函数utl_raw_cast_to_raw（保存汉字的字段）
2、在新数据库上，做个数据库连接，连接指向原数据库，之后对步骤1中的视图中，使用了
utl_raw_cast_to_raw的字段，再用utl_raw_cast_to_varchar2函数转换回来，

具体测试环境和测试方法:

 查看全文

表中的数据被误DELETE,并且已经COMMIT的被救措施

做个记录,以备查询

http://docs.google.com/Doc?id=dgdk3pdk_118cbtk4ghd

日期:20080203
关键字:
ora-600 kksscl-inf-inl-loop
Connection aborted by peer: socket write error
java.sql.SQLException: 无法从套接字读取更多的数据
内容:
收到开发人员发来的邮件,告知JAVA应用程序 运行系统一段时间后，报“获取数据库连接失败”，“无法从套接字获取更多数据”,要求解决此问题

http://docs.google.com/Doc?id=dgdk3pdk_114cq8x87gw

通过一周的摸索,大概了解了TSM在WINDOWS和LINUX下的服务端与客户端的安装与配置,但还有一点遗留问题:

动态增加列

统计报表中动态增加列的解决方法之一
根据查询条件，临时生成视图

关键点： 使用DOS命令，定时批量加载文本文件入库

<<ORACLE9i实体化视图 白皮书>>

对维度、实体化视图、查询重写的理解

http://docs.google.com/Doc?id=dgdk3pdk_48fmw6pd

以前，我对实体化视图的理解仍停留在8I的快照概念上，虽然知道它可以做统计，但对维的理解仍不清楚，查询重写也是知其一不知其二，很惭愧总是在MV上做查询。
这次正好项目中的统计用到了MV，以及日报表、月报表，于是想使用MV来解决一些问题。以下是参考AKADIA的资料所做的两个测试。只看白皮书会知道个大概，还需要实际操练一下。

简单说明

简介

ORA-24315的问题
reference http://www.itpub.net/407125.html

select .... from ... where lxr is null;

this statement can't use the idnex!!!

创建强制视图可更新视图视图相关的数据字典

sys与system的区别对象权限与系统权限角色权限的区别与权限相关的数据字典

使用过程加双引号