小重山 　　-- 岳飞
　　

昨夜寒蛩不住鸣。惊回千里梦，已三更。起来独自绕阶行。人悄悄，帘外月胧明。
　　白首为功名。旧山松竹老，阻归程。欲将心事付瑶琴。知音少，弦断有谁听？

-- 摘自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 章 优化表空间==================== --