ITPub博客

看深入浅出的一些疑问，希望以后能弄清楚。[@more@]我的测试库的db_cache_size为16MB,此时的_db_block_hash_buckets为4096,正好为block buffer的2倍(16*1024/8)*2.在oracle8i之前,该参数是db_block_buffers/4,而之后该参数缺省为db_block_buffers*2.SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProdPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4

http://tolywang.itpub.net/post/48/455900这里只谈物理data guard的原理[@more@]一般来说，我们在谈到data guard的时候，都会用很简单的语言来描述整个data guard的运做流程。通常是这样的: primary database通过arch 进程，或者lgwr进程把archive log 送到standby database的archive_dest，然后standby database 通过RFS来接收这个archive log，然后MRP进程运用此log，也就是redo apply，使物理特征和primary database一模一样。这样讲清楚了吗？ 表面看起来很清楚，其实非常不清楚，歧义很多。歧义在哪里？ 那么我来问几个问题：1.arch

How to release space from database这篇文章比较详细，包括回收到HWM，以及如何降低HWM后再继续回收。[@more@]How to release space from database(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)当然就是resize datafile 了，下面稍微写了一下步骤 How to release space from database(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)1. Find which tablespace have much fr

通过试验对freelist,freelist group 以及手动管理段的管理有了初步的了解。[@more@]建立手动管理的表空间SQL> CREATE TABLESPACE eric DATAFILE '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1MBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT MANUAL; Tablespace created.分别建立不同的freelist和freelist group的三个表SQL> CREATE TABLE t_manualTABLESPACE ERICSTORAGE ( FREELISTS 1 FREELIST GROUPS 1 )as select * from dba_objec

一直以来以ASSM的概念很模糊，今天做了个实验，总算有点收获。[@more@]SQL> create tablespace eric 2 datafile '/u01/app/oradata/orcl/eric01.dbf' size 10m 3 extent management local uniform size 128k 4 segment space management auto;Tablespace created.SQL> select tablespace_name,extent_management,allocation_type,segment_space_management 2 from dba_tablespaces 3 where tablespace_name='ERIC';TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN------------------------------ ----------

看循序渐进和数据库性能优化时进行的测试，记录一下。[@more@]UNIFORM SIZE的测试SQL> CREATE TABLESPACE eric DATAFILE 2 '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFF 3 LOGGING 4 PERMANENT 5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 6 BLOCKSIZE 8K 7 SEGMENT SPACE MANAGEMENT MANUAL 8 FLASHBACK ON;Tablespace created.SQL> select tablespace_name,extent_management,allocation_type 2 from dba_tablespaces where tablespace_name='ERIC';TABLESPACE_NAME EXTENT_MAN

从9.2.0.1升级到10.2.0.4的过程，同事写的，记录一下。[@more@]1. Environment Setting：Solaris 10 + Oracle 9.2.0.1/9.2.0.4(SPARC 64-bit)2. DB Version：SQL>select version from v$instance;>=9.2.0.4 direct upgrade to 10.2.0.4.0；<9.2.0.4 of 9iR2, upgrade to 9.2.0.8 first, then upgrade to 10.2.0.4.03. Upgrade to 9.2.0.8：A). Preparation:a).Check pool size of SHARED_POOL_SIZE & JAVA_POOL_SIZE to minimum size 150M each;If not, verify it pfile;b). Shutdown DB and Take a full cold backupc). Shu

目的：将linux的数据库迁移到solaris上同时升级数据库原系统情况：OS：RHEL4 U4oracle:10.2.0.1IP：172.17.61.131 目标系统情况：OS：solaris 10oracle:10.2.0.2IP：172.17.61.130linux平台的版本是10.2.0.1,sun...

安装完成之后，首先配置ftp方便文件传输：RED HAT AS4 U4下gssftp服务的配置 1、在操作系统里，使用ntsysv命令查看是否已经开启gssftp服务；2、在/etc/xinetd.d的目录中，修改gssftp配置文件如下：service ftp{dis...

[oracle@rhel131 ~]$ expdp l5m/l5m directory=dpdata dumpfile=full.dmp full=y job_name=expfullExport: Release 10.2.0.1.0 - Production on Wednesday, 15 October, 2008 2:23:47Copyright (c) 2003, 2005, Or...

top 的%CPU总数大于100是为什么呢。top - 11:34:07 up 10 days,  2:35,  2 users,  load average: 2.35, 2.11, 1.59Tasks: 171 total,   4 running, 167 sleeping,  &...

$ exp mtrx/mtrx tables=BRAND_SPENDING_MM_MP file=mtrx.dmp log=mtrx.log statistics=none buffer=4096000 direct=y recordlength=65535Export: Release 10.2.0.4.0 - Production on Mon Oct 13 22:40:23 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setAbout t

目的：将linux的测试表空间传到到solaris[@more@]源系统情况：OS：RHEL4 U4oracle:10.2.0.1IP：172.17.61.131目标系统情况：OS：solaris 10oracle:10.2.0.2IP：172.17.61.130linux平台下[oracle@rhel131 ~]$ sqlplus '/ as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 08:50:31 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> col platform_name for

在10.2.0.1+rhel4上碰到一个bug,导致在shutdown数据库时时间较长。[@more@]To implement the solution, execute the following steps::1. In $ORACLE_HOME/network/admin/listener.ora, of the database where the Metadata Repository is to be loaded, add the line:INBOUND_CONNECT_TIMEOUT_LISTENER = 02. In the Oracle Server 10g sqlnet.ora, add the line:SQLNET.INBOUND_CONNECT_TIMEOUT = 03. Stop and restart the database and the listener for the changes to take effect这是metalink上的：Applies to:Oracle Server - Ent

ITL和Freelist的区别，记录一下。[@more@]1.地点不同： ITL：影响在block层面的 concurrency的item.Freelist：影响在segment层面上的concurrency2.事情不同：ITL:影响的是并发事务Freelist：影响的是并发insert3.时间不同：ITL：一个block上的事务是在整个事务期占用一个ITL Slot.Freelist：只是在 insert之前寻找free block时占用，找到后释放。

表空间offline,数据文件offline 的区别[@more@]在对表空间进行offline时,oracle需要针对这个表空间生成检查点,刷新表空间中数据文件的相关事务,更新数据文件头SCN,数据文件SCN,文件终止SCN,达到一致状态,当表空间被再次ONLINE时,oracle会捕捉online时刻的SCN,更新上面各个位置的SCN（当然文件结束SCN号会置为无穷大）,将数据文件都打开在对单个数据文件做offline时,是立即offline,此时不会针对数据文件生成检查点,所以数据文件的终止SCN为无穷大,处于不一致状态,数据文件也需要recover,在v$recover_file视图也可以看到这个文件的信息,要想

做过实验,不过没有记录下过程,转一下别人的过程吧![@more@]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 21 09:31:32 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: TESTDB (DBID=2437320461)RMAN> list backup;using target database control file instead of recovery catalogRMAN> exitRecovery Manager complete.-----以上表明该数据库并没有rman备份---------$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 21 09:34:

转自:http://www.heysky.net/archives/2006/08/optimizer_operations_notes.html[@more@]Optimizer OperationsHow the Optimizer Performs Operations How the CBO Evaluates IN-List Iterators 当 IN clause 指定了特定的值，并且在该列上有索引，优化器选择 IN-list iterator。如果有多个 OR clauses 使用相同的索引，那么优化器选择更高效的 IN-list iterator，而不使用 CONCATENATION or UNION ALL。 How the CBO Evaluates Concatenation 当不同的条件使用 OR clause 连接起来，并且不同的条件都可以通过不同

转自:http://www.heysky.net/archives/2006/08/9iperformancetuningguide.html[@more@]Introduction to the OptimizerOverview of SQL Processing 1. parser 进行语意和语法分析 2. Optimizer 利用RBO,CBO等方法决定产生查询结果的最有效路径 3. Row Source Generator 从2中接受优化后的方案，并产生SQL的Execution Plan 4. SQL Execution Engine运行该执行计划，并产生查询结果Features that Require the CBO 1. Partitioned tables and indexes 2. Index-organized tables 3. Reverse key indexes 4. Function-based indexes

问个问题：数据库是archivelog的，表是nologing的情况下，sqlldr加载会产生redo吗？