2009/04

Block db_block_size the reasons for the operating system?

kods.net — Wed, 22 Apr 2009 21:35:02+0000

rn
I would like to ask

That all the information on
db_block_size to be configured to block the entire operating system several times to improve the processing speed,
But do not indicate the reason for that? The boss explains why ah? : em14:

rn
rn
ivhb
rn
oracle can be used bare equipment. However, the bare equipment used by the operating system to its own open / read-write device interface.
In under unix is open / read / write. For the block device, unix generally 512 bytes each read and write, and this is the block size unix.
In short, oracle can bypass the file system (used to operate the file system - that is, cooked file, then open the file, the operating system will give you the operating system level cache. Is equivalent to access the data when the disk -> pieces of the operating system file cache -> Database Cache -> Search to return to users). If the bare device, which does not have an operating system cache. Less time to copy it.

Simply put: oracle can bypass the file system, but can not bypass the system call.
As long as the operating system running on the kernel to access equipment or data, must be through the system call, switch state at the core run state before it can.

As a whole must be several times, but also like to understand.
The block size because the system is the basic unit of time to read and write. Assumed that the block size is 512 bytes, DB_BLOCK_SIZE configured as 600, then each read a database, the operating system level, the two need to read, can read a complete block to the database. Of these, only the second time to read and write 88 bytes to be effective. Equivalent to the ability to read a part of waste

rn
rn
ILoveMK
rn
: mrgreen: good

rn
rn
jayli426
rn
Understanding that the following is correct, prawns ah coach
1) for the bare equipment? db_block_size better allocation, or as much as possible with large? Whether the 8192 match, is to check each time to read 8192 instead of 512? (I feel like the wrong understanding, but I do not know a specific reference to the bare shrimp equipment is designed for that)
2) for the file system, pair 8192 and 512 in fact, can not match the number to increase speed, because the pair need to read 8192 or 16, just a few blocks back to read more, but IO is concerned, each time the word no increase in the number of, ah, how many or how much of the time. Only non-integer multiple of the match and some a waste of time?

rn
rn
doni
rn
db_block_size settings and database applications on the surface, that is to say that the database is, how and when is, how to read and write the

Under certain conditions, db_block_size a significant performance impact, I did some tests,
In Solaris 8 + oracle 7 or Solaris 10 + oracle 7 of conditions, in a number of models do imp
db_block_size set to 8192 than 2048 to increase the speed of the imp nearly 50%

rn
rn
jayli426
rn
Shrimp, you prove that this is practice.
But not the theory that, ah, just let everyone have one more experience, but no theory can in principle on the sum up to elaborate on

rn
rn
doni
rn
For example: You finished mahjong, play cards back into the box is a quick release, or release 4 4 fast?
Of course, every time the number is not the better because it depends on how big your hands.

Therefore, according to db_block_size settings to set specific conditions

rn
rn
camham
rn
block size is an interactive database and disk size, or io. If each operation requires a large amount of data to return, then the better block, and high efficiency. Conversely, if only a single record of the operation done, for example, insert, and then the same big block, but a waste of memory. Depends on the efficiency of a specific size, a simple business systems that use small block, big block with analysis system

rn
rn
jayli426
rn
Because non-科班出身boy, I would like to ask are not familiar with the operating system: disk, an interactive database and how to understand the same What is the relationship between IO

For example, a disk IO to read the size of 512bytes
8192bytes then need to read on the total of 16 times regardless of how much your db_block_size such as db_block_size = 8192, read 16 times IO such as db_block_size = 8192 / 2, but 8912 still need time to read 16 times

That is: In any case, read the number of IO will not change, oh

For example, use the "For example: You finished mahjong, play cards back into the box is a quick release, or release 4 4 fast?"
I understand the current level would not be a "4 4 release fast?" Of ah

Well, understand this, "an interactive database and disk"
Very important
Vegetables boy learn to forgive the younger brother of shallow ah forgive repeatedly, by the Rolls-ah: em14:

rn
rn
ivhb
rn
Yes, db_block_size is set up much, and finally produced the number of physical IO will also change.
This no doubt. For example, DB_BLOCK_SIZE set to 4K and 32K, so that the former occurred about 8 times read; which occurred 64 times read.

OLAP generally recommend the use of larger db_block_size, and OLTP generally recommend the use of smaller db_block_size.
The reason is that the database to read a record (note is a record or records or more. Are the same)
Database system will be in accordance with your request to positioning record, if the records in the cache, directly read from the cache. If the records are no longer in cache, from the positioning block to disk, and then read --- note that it is to read a block (db_block_size size), cache in the sga. The cache as a unit moves to the db_block_size.

In this way, you can be found, for example OLPT generally enter a customer number, an account. . . According to the information generally available only to a recorded position, and then operation of the record, is a very typical action scattered read (records are scattered). Scattered records of the visit is the result --- your memory size is fixed, the database can not always help you to cache all the records. Therefore, the frequent exchange of cache and disk (obviously, db_block_size set the greater the more random access records more dispersed, the number of exchange occurred more --- means more disk read and write. This will significantly reduce the efficiency of the system).
For OLAP, the general is to operate all of the records. Continuity of access to records. You can find more time cache, basically is the better.

db_block_size essence, is to give the user read-ahead data, the next visit from the cache read / update, rather than read from the disk each time, and bring to enhance the performance. set db_block_size greater, to a certain extent, resulting in greater waste of memory (in the random access, especially). If the memory is unlimited, I would like to be it for OLAP or OLTP, it will recommend major db_block_size --- perhaps there will be no db_block_size --- all of the database data in memory

rn

rn
This is a forum before ChinaUnix a question, chinaunix This is the point that bad, but once home to the essence of a period of time will be removed, not looking for can not find, but fortunately there was people, preservation of the post, so I copy from him to come back

rn
This should indicate the reasons for the DB_BLOCK_SIZE, according to the answer shrimp, DB_BLOCK_SIZE is several times the entire operating system will be of limited significance, the key is to DB_BLOCK_SIZE percentage in memory for the OLTP system, because of random and scattered records of the visit, so DB_BLOCK_SIZE based on a relatively small benefit

rn
What does it mean to assume that our only 32k big DB_BUFFER

rn
If we are to regularly update the record of 32, they each recorded at least 8k distance difference

rn
What does it mean, that is only a record of every 8k,

rn
If DB_BLOCK_SIZE = 8k, it is only a matter of DB_BUFFER put four of our regular update of records

rn
However, if DB_BLOCK_SIZE = 1k, then DB_BUFFER actually 32 will be able to put a regular update of our records

rn
However, OLAP, we usually need to do a lot of data statistics, data are generally continuous, so

rn
This time even bigger DB_BLOCK_SIZE please?

rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle EXP / IMP command explain

kods.net — Wed, 22 Apr 2009 20:05:14+0000

Original Address: http://www.bokee.net/bloggermodule/blog_viewblog.do?id=465310

Oracle's import utility (Import utility) allows to extract data from the database and the data written into the operating system files. imp to use the basic format: imp [username [/ password [@ service]]], the following examples of common usage imp.

1. Getting Help

 imp help=y

2. Into a complete database

 imp system/manager file=bible_db log=dible_db full=y ignore=y

3. Into one or a group of designated users of all their tables, indexes and other objects

 imp system/manager file=seapark log=seapark fromuser=seapark imp

 system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

4. To be a user to import their data from another user

 imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy

 imp system/manager file=tank log=tank fromuser=(seapark,amy)

 touser=(seapark1, amy1)

5. Into a table

 imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)

6. Files from multiple

 imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)

log=paycheck, filesize=1G full=y

7. The use of parameter files

 imp system/manager parfile=bible_tables.par

bible_tables.par parameter files:

 #Import the sample tables used for the Oracle8i Database Administrator's

Bible. fromuser=seapark touser=seapark_copy file=seapark log=seapark_import

8. Incremental import

 imp system./manager inctype= RECTORE FULL=Y FILE=A


Oracle imp/exp 

C:Documents and Settingsadministrator>exp help=y

Export: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:04:43 2004

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

 

通过输入 EXP 命令和用户名/口令，您可以
后接用户名/口令的命令:

例程: EXP SCOTT/TIGER

或者，您也可以通过输入跟有各种参数的 EXP 命令来控制“导出”
按照不同参数。要指定参数，您可以使用关键字:

格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例程: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
或 TABLES=(T1: P1,T1: P2)，如果 T1 是分区表

USERID 必须是命令行中的第一个参数。

关键字  说明(默认)        关键字      说明(默认)
--------------------------------------------------------------------------
USERID   用户名/口令           FULL         导出整个文件 (N)
BUFFER   数据缓冲区大小         OWNER     所有者用户名列表
FILE       输出文件 (EXPDAT.DMP)  TABLES       表名称列表
COMPRESS   导入到一个区 (Y) RECORDLENGTH IO 记录的长度
GRANTS  导出权限 (Y)           INCTYPE      增量导出类型
INDEXES    导出索引 (Y)         RECORD       跟踪增量导出 (Y)
DIRECT     直接路径 (N)            TRIGGERS     导出触发器 (Y)
LOG        屏幕输出的日志文件  STATISTICS   分析对象 (ESTIMATE)
ROWS    导出数据行 (Y)        PARFILE      参数文件名
CONSISTENT 交叉表的一致性 (N) CONSTRAINTS  导出的约束条件 (Y)

OBJECT_CONSISTENT    只在对象导出期间设置为读的事务处理 (N)
FEEDBACK             每 x 行的显示进度 (0)
FILESIZE             每个转储文件的最大大小
FLASHBACK_SCN        用于将会话快照设置回以前状态的 SCN
FLASHBACK_TIME       用于获取最接近指定时间的 SCN 的时间
QUERY                用于导出表的子集的 select 子句
RESUMABLE            遇到与空格相关的错误时挂起 (N)
RESUMABLE_NAME       用于标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT    RESUMABLE 的等待时间
TTS_FULL_CHECK       对 TTS 执行完整的或部分相关性检查
TABLESPACES          要导出的表空间列表
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TEMPLATE             调用 iAS 模式导出的模板名

在没有警告的情况下成功终止导出。
==================================================

C:Documents and Settingsadministrator>imp help=y

Import: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:06:54 2004

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

 

可以通过输入 IMP 命令和您的用户名/口令
后接用户名/口令的命令:

例程: IMP SCOTT/TIGER

或者, 可以通过输入 IMP 命令和各种参数来控制“导入”
按照不同参数。要指定参数，您可以使用关键字:

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,vlaueN)
例程: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
或 TABLES=(T1: P1,T1: P2)，如果 T1 是分区表

USERID 必须是命令行中的第一个参数。

关键字  说明（默认）        关键字      说明（默认）
--------------------------------------------------------------------------
USERID   用户名/口令           FULL         导入整个文件 (N)
BUFFER   数据缓冲区大小         FROMUSER     所有人用户名列表
FILE     输入文件 (EXPDAT.DMP)    TOUSER       用户名列表
SHOW     只列出文件内容 (N) TABLES      表名列表
IGNORE   忽略创建错误 (N)    RECORDLENGTH  IO 记录的长度
GRANTS  导入权限 (Y)           INCTYPE      增量导入类型
INDEXES 导入索引 (Y)          COMMIT       提交数组插入 (N)
ROWS    导入数据行 (Y)        PARFILE      参数文件名
LOG      屏幕输出的日志文件   CONSTRAINTS  导入限制 (Y)
DESTROY                覆盖表空间数据文件 (N)
INDEXFILE              将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES  跳过不可用索引的维护 (N)
FEEDBACK               每 x 行显示进度 (0)
TOID_NOVALIDATE        跳过指定类型 ID 的验证
FILESIZE               每个转储文件的最大大小
STATISTICS             始终导入预计算的统计信息
RESUMABLE            在遇到有关空间的错误时挂起 (N)
RESUMABLE_NAME         用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT      RESUMABLE 的等待时间
COMPILE                编译过程, 程序包和函数 (Y)
STREAMS_CONFIGURATION  导入 Streams 的一般元数据 (Y)
STREAMS_INSTANITATION  导入 Streams 的实例化元数据 (N)

下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户

成功终止导入，但出现警告。







oracle的imp和exp的一些用法- -

                                       



Oracle8i/9i EXP/IMP使用经验  
一、8i EXP常用选项 

1、FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如： 

exp sys file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y 

2、BUFFER和FEEDBACK，在导出比较多的数据时，我会考虑设置这两个参数。例如： 

exp new file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT 

3、FILL和LOG，这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。 

需要说明的是，EXP可以直接备份到磁带中，即使用FILE=/dev/rmt0(磁带设备名)，但是一般我们都不这么做，原因有二：一、这样做的速度会慢很多，二、现在一般都是使用磁带库的，不建议直接对磁带进行操作。至于没有使用磁带库的朋友可以考虑和UNIX的TAR结合使用。 

如果你真想使用EXP直接到磁带，你可以参考Metalink文章“EXPORTING TO TAPE ON UNIX SYSTEMS”（文档号：30428.1），该文中有详细解释。 

4、COMPRESS参数将在导出的同时合并碎块，尽量把数据压缩到initial的EXTENT里，默认是N，一般建议使用。DIRECT参数将告诉EXP直接读取数据，而不像传统的EXP那样，使用SELECT来读取表中的数据，这样就减少了SQL语句处理过程。一般也建议使用。不过有些情况下DIRECT参数是无法使用的。 

5、如何使用SYSDBA执行EXP/IMP？ 

这是一个很现实的问题，有时候我们需要使用SYSDBA来执行EXP/IMP，如进行传输表空间的EXP/IMP，以及在9i下用SYS用户来执行EXP/IMP时，都需要使用SYSDBA才可。我们可以使用下面方式连入EXP/IMP： 

exp "'sys/sys as sysdba'" file=1.dmp tables=gototop.t rows=n 

6、QUERY参数后面跟的是where条件，值得注意的是，整个where子句需要使用""括起来，where子句的写法和SELECT中相同，如果是UNIX平台所有"和'都需要使用u26469屏蔽它们的特殊含义： 

exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query="where c1=20 and c2=gototop" 

如果是windows平台，则使用下面的格式： 

exp c/c@ncn file=c.dmp log=c.log tables=t query="""where'gototop'""" 

二、8i IMP常用选项 

1、FROMUSER和TOUSER，使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。 

2、IGNORE、GRANTS和INDEXES，其中IGNORE参数将忽略表的存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。而GRANTS和INDEXES则表示是否导入授权和索引，如果想使用新的存储参数重建索引，或者为了加快到入速度，我们可以考虑将INDEXES设为N，而GRANTS一般都是Y。 

另外一个EXP/IMP都有的参数是PARFILE，它是用来定义EXP/IMP的参数文件，也就是说，上面的参数都可以写在一个参数文件中，但我们一般很少使用。 

三、Oracle9i EXP功能描述 

Oracle9i EXP在原有的基础上新增了部分新的参数，按功能主要分为以下几个部分： 

1、OBJECT_CONSISTENT - 用于设置EXP对象为只读以保持对象的一致性。默认是N。 

2、FLASHBACK_SCN和FLASHBACK_TIME - 用于支持FLASHBACK功能而新增。 

3、RESUMABLE、RESUMABLE_NAME和RESUMABLE_TIMEOUT - 用于支持RESUMABLE空间分配而新增。 

4、TTS_FULL_CHECK - 用于在传输表空间时使用依赖性检查。 

5、TEMPLATE - 用于支持iAS。 

6、TABLESPACES - 设置表空间导出模式。个人觉得对于一般用户而言，这个才是新增参数中最实用的一个，可以让用户在原来的FULL、OWNER、TABLES的基础上多了一种选择，使得EXP更加灵活。 

四、不同版本的EXP/IMP问题？ 

一般来说，从低版本导入到高版本问题不大，麻烦的是将高版本的数据导入到低版本中，在Oracle9i之前，不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决： 

1、在高版本数据库上运行底版本的catexp.sql； 

2、使用低版本的EXP来导出高版本的数据； 

3、使用低版本的IMP将数据库导入到底版本数据库中； 

4、在高版本数据库上重新运行高版本的catexp.sql脚本。 

但在9i中，上面的方法并不能解决问题。如果直接使用底版本EXP/IMP会出现如下错误： 

EXP-00008: ORACLE error %lu encountered 

ORA-00904: invalid column name 

这已经是一个公布的BUG，需要等到Oracle10.0才能解决，BUG号为2261，你可以到METALINK上去查看有关此BUG的详细信息。 

BUG归BUG，我们的工作还是要做，在没有Oracle的支持之前，我们就自己解决。在Oracle9i中执行下面的SQL重建exu81rls视图即可。 

CREATE OR REPLACE view exu81rls  

(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)  

AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,  

decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')  

|| decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')  

|| decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')  

|| decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),  

r.check_opt, r.enable_flag,  

DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)  

from user$ u, obj$ o, rls$ r  

where u.user# = o.owner#  

and r.obj# = o.obj#  

and (uid = 0 or  

uid = o.owner# or  

exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')  

)  

/  

grant select on sys.exu81rls to public;  

/  

五、其他问题 

本文只讨论了Oracle8i和9i中的EXP/IMP的一些情况，对于之前的版本，在8.0.X中，除了QUERY参数不能用外，其它差别不大。针对没有QUERY的情况，我们可以先在数据库中使用查询条件建立临时中间表，然后使用EXP导出这个中间表即可。至于Oracle7因为目前使用的人较少，gototop不打算在此做详细解释了，如果读者朋友有需求，你可以参考Metalink文档：“Overview of Export and Import in Oracle7”（文档号：61949.1）。关于EXP/IMP的详细参数信息你可以通过EXP/IMP HELP=Y来获得。 

另外关于传输表空间的更多信息可以参考下面的Metelink文档，本文不再详述。 

[NOTE:77523.1] Transportable Tablespaces -- An Example to setup and use.  

[NOTE:100698.1] Perform tablespace point-in-time recovery using Transportable Tablespace.  

在进行并行EXP/IMP的时候，如果IMP过程建索引的话不建议同时运行5个以上的IMP，如果你想加快速度

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

index_stats

kods.net — Wed, 22 Apr 2009 15:39:59+0000

I often get asked when the index need to rebuild, read a article today, recording a bit.

The INDEX_STATS view is useful for seeing how efficiently an index is using its space. Large indexes
have a tendency to become unbalanced over time if many deletions are in the table (and therefore
index) data. Your goal is to keep an eye on those large indexes with a view to keeping them balanced.
Note that the INDEX_STATS view is populated only if the table has been analyzed by using the
ANALYZE command, as follows:
SQL> ANALYZE index hr.emp_name_ix VALIDATE STRUCTURE;
Index analyzed.
The query in Listing 7-26 using the INDEX_STATS view helps determine whether you need to
rebuild the index. In the query, you should focus on the following columns in the INDEX_STATS view

to determine if your index is a candidate for a rebuild:
• HEIGHT: This column refers to the height of the B-tree index, and it's usually at the 1, 2, or 3
level. If large inserts push the index height beyond a level of 4, it's time to rebuild, which flattens
the B-tree.
• DEL_LF_ROWS: This is the number of leaf nodes deleted due to the deletion of rows. Oracle
doesn't rebuild indexes automatically and, consequently, too many deleted leaf rows can lead
to an unbalanced B-tree.
• BLK_GETS_PER_ACCESS: You can look at the BLK_GETS_PER_ACCESS column to see how
much logical I / O it takes to retrieve data from the index. If this row shows a double-digit
number, you should probably start rebuilding the index.

Using the INDEX_STATS View to Determine Whether to Rebuild an Index
SQL> SELECT height, / * Height of the B-Tree * /
2 blocks, / * Blocks in the index segment * /
3 name, / * index name * /
4 lf_rows, / * number of leaf rows in the index * /
5 lf_blks, / * number of leaf blocks in the index * /
6 del_lf_rows, / * number of deleted leaf rows
in the index * /
7 rows_per_key / * average number of rows
per distinct key * /
8 blk_gets_per_access / * consistent mode block reads (gets) * /
8 FROM INDEX_STATS
9 * WHERE name = 'EMP_NAME_IX';
HEIGHT BLOCK LF_ROWS LF_BLKS DEL_LF_ROWS ROWS_PER_KEY BLK_GETS
------ ----------- ------- -------- ----------- ------- ----- ---------
16 EMP_NAME_IX 107 1 0 1

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Summary of the partition table

kods.net — Tue, 21 Apr 2009 18:21:19+0000

Partition table

Learning through this section, you can learn the following questions

1, to understand the concept of partition table

2, clearly the use of the partition table

3, to understand the classification of the partition table

4, to understand the operation of the partition table

We use an ordinary table in the process will be particularly large amount of data the table, when a data table for more than 20 million have been or occupier 2G Space, it is recommended to establish the partition table

The classification of the partition table:

Range (range) Zoning

Hash (hash) partitions

List (list) Zoning

As well as the combination of district: Range-Hash, Range-List

Division of advantages:

Enhanced availability: If a partition table failure, table of data in other divisions are still available;

Easy maintenance: If a partition table failure, the need to repair the data, only to repair the partition;

Balanced I / O: can be mapped to a different disk partition to balance the I / O, improve the overall system performance;

Improve the query performance: The partition of the query object can be of concern to only search their own district to improve the retrieval speed.

Executive Summary

Partition function of many applications can improve the manageability, performance and availability. Through the partition function can be table, index and index-organized table is further broken down into paragraphs, which can more accurately manage and access these database objects. Orac # provides a wide variety of district programs to meet the operational requirements of each. Moreover, because the sql statement in the district is completely transparent, so the function can be applied to almost any application.

The advantages of geographical features

Partition function by improving the manageability, performance and availability of applications so as to bring all kinds of great benefits. Typically, the district can make some inquiries as well as the performance of maintenance operations greatly enhanced. In addition, the district can also greatly simplify common administrative tasks. Through the District, database designers and administrators to address the cutting-edge applications brought about by a number of challenges. District is building a gigabyte of data systems or ultra-high availability systems a key tool.

Basic knowledge of geographical features

Partition function can be table, index or index-organized table is further broken down into paragraphs. These database objects called the partition of the paragraph. Each district has its own name, you can choose its own storage characteristics.

From the database administrator's point of view, after a sub-object with multiple paragraphs, the paragraphs of collective management can also be managed separately. This allows database administrators in the management of the object after the district there is considerable flexibility. However, from the application point of view, after the partition table partition table with non-identical, so that # S # D # command to visit the table after partition, without any changes to the partition table is through the "partition key "to be achieved, area refers to the number of keys out, which included the decision of the district where a line. Orac # data # 1 # 6 technologies provided for the partition table:

# The scope of Division

Each district by the scope of a sub-key value specified (for a date as a partition key column of the Table, "January 2005" zoning district that contains keys for the " 1 January, 2005 "To" January 31, 2005 "Line).

That the scope of zoning cases:

Assuming that there is an enterprise sales data, and can be used for a range partition table

Four tables to create space for use by the partition table should be placed on a different partition on the physical device, better performance can be my test environment is a physical device with

SQL> create tablespace ts_xiaos2009q1 datafile

'/ oradata/dbnms/ts_xiaos2009q1.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q2

datafile '/ oradata/dbnms/ts_xiaos2009q2.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q3 datafile

'/ oradata/dbnms/ts_xiaos2009q3.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q4 datafile

'/ oradata/dbnms/ts_xiaos2009q4.dbf' size 50M ;

Tablespace created.

Create table

SQL> CREATE TABLE dyx_part_test1

2 (id NUMBER,

3 name varchar2 (20),

4 shuliang number,

5 danjia number,

6 xiaos_date DATE NOT NULL)

7 PARTITION BY RANGE (xiaos_date)

8 (PARTITION xiaoss2009_q1

9 VALUES LESS THAN (TO_DATE ( ' 2009-04-01 ',' YYYY-MM-DD '))

10 TABLESPACE ts_xiaos2009q1,

11 PARTITION xiaoss2009_q2

12 VALUES LESS THAN (TO_DATE ( ' 2009-07-01 ',' YYYY-MM-DD '))

13 TABLESPACE ts_xiaos2009q2,

14 PARTITION xiaoss2009_q3

15 VALUES LESS THAN (TO_DATE ( ' 2009-10-01 ',' YYYY-MM-DD '))

16 TABLESPACE ts_xiaos2009q3,

17 PARTITION xiaoss2009_q4

18 VALUES LESS THAN (TO_DATE ( ' 2010-01-01 ',' YYYY-MM-DD '))

19 TABLESPACE ts_xiaos2009q4);

Table created.

Insert data

SQL> insert into dyx_part_test1 values

(1, 'Coca-Cola', 100,3.5, to_date ( ' 2009-03-05 ',' yyyy-mm-dd '));

1 row created.

SQL> insert into dyx_part_test1 values

(2, 'Kentucky', 100,10.5, to_date ( ' 2009-06-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(3, 'Zhenglin 3A 500g ', 100,15, to_date (' 2009-09-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(4, 'Ice Tea', 100,3, to_date ( ' 2009-11-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(5, 'orange', 100,3.5, to_date ( ' 2010-04-05 ',' yyyy-mm-dd '));

insert into dyx_part_test1 values

(5, 'orange', 100,3.5, to_date ( ' 2010-04-05 ',' yyyy-mm-dd '))

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

Note: If you can not address district-wide data is not inserted, will report ORA-14400 error

Show:

SQL> select * from DYX_PART_Test1 partition (XIAOSS2009_Q3);

ID NAME SHULIANG DANJIA XIAOS_DATE

---------- -------------------- ---------- ---------- ------------

3 Zhenglin 3A 500g 100 15 05-SEP-09

# A list of district

Each district by a district designated key list (for an area out of the table as a partition key, "North America" partition may contain values of "Canada" "United States" and "Mexico").

Examples are as follows:

SQL> create table dyx_part_test2 (

2 id number (4),

3 name varchar2 (30),

4 loca varchar2 (30))

5 partition by list (loca)

6 (partition p1 values ( 'Beijing') tablespace ts_xiaos2009q1,

7 partition p2 values ( 'Shanghai', 'Tianjin', 'Chongqing') tablespace ts_xiaos2009q2,

8 partition p3 values ( 'Guangdong', 'Fujian') tablespace ts_xiaos2009q3,

9 partition p0 values (default) tablespace users

10);

Table created.

SQL> insert into dyx_part_test2 values (1, 'DAI new', 'Gansu');

1 row created.

SQL> insert into dyx_part_test2 values (2, 'Cloud neutron', 'Beijing');

1 row created.

SQL> insert into dyx_part_test2 values (3, 'Guangcheng son', 'Tianjin');

1 row created.

SQL> insert into dyx_part_test2 values (4, '南极仙翁', 'Hebei');

1 row created.

SQL> insert into dyx_part_test2 values (5, 'too has been a real person', 'Shanghai');

1 row created.

SQL> insert into dyx_part_test2 values (6, 'fear Mr Sun', 'Canton');

1 row created.

SQL> insert into dyx_part_test2 values (7, '元始s',' Chongqing ');

1 row created.

SQL> insert into dyx_part_test2 values (8, 'God of War South-class',' Beijing ');

1 row created.

SQL> insert into dyx_part_test2 values (9, 'the five God of War', 'Fujian');

1 row created.

SQL> select * from dyx_part_test2;

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

Beijing 2 the neutron cloud

8 God of War South-class Beijing

3 sub-Tianjin Guangcheng

5 live in Shanghai have been too

7元始s Chongqing

6 Liu孙广东fear

9 God of War five Fujian

1 DAI new Gansu

4南极仙翁Hebei

9 rows selected.

SQL> select * from DYX_PART_Test2 partition (p2);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

3 sub-Tianjin Guangcheng

5 live in Shanghai have been too

7元始s Chongqing

SQL> select * from DYX_PART_Test2 partition (p0);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

1 DAI new Gansu

4南极仙翁Hebei

3 sub-Tianjin Guangcheng

# Hash partition

Hash algorithm will be used to partition the specified key to determine where the district line, by specifying the partition number for a uniform geographical distribution of data types, as in I / O device hash partition,

The same size so that these zoning.

Example:

create table dyx_part_test3

(id number primary key,

class_id number (8) not null,

name varchar2 (300),

finished_date date

)

partition by hash (id)

(

partition p01 tablespace ts_xiaos2009q1,

partition p02 tablespace ts_xiaos2009q2,

partition p03 tablespace ts_xiaos2009q3

);

insert into dyx_part_test3 values

(3,12, 'quality of the skin', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(1,13, 'tiger', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(200,15, 'the middle of the tiger', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(230,19, 'tiger cat', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

SQL> select * from dyx_part_test3;

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

23,019 cat skins 30-MAY-09

SQL> select * from dyx_part_test3;

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

23,019 cat skins 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p02);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p01);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p03);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

23,019 cat skins 30-MAY-09

# Portfolio Zoning

1, the scope of hash (hash) partitions

Range and hash partition technology portfolio through the combination of the scope of the first partition on the table, and then the scope for each individual district to use technology to further breakdown of the hash partition.

Index range of organizations can only partition table.

Example:

SQL> create table dyx_part_test4 (

2 id number,

3 name varchar2 (30),

4 finished_date date)

5 partition by range (finished_date)

6 subpartition by hash (id)

7 subpartitions 2

8 (partition p1 values less than (to_date ('20090501 ',' YYYYMMDD ')) tablespace ts_xiaos2009q1,

9 partition p2 values less than (to_date ('20091001 ',' YYYYMMDD ')) tablespace ts_xiaos2009q2,

10 partition e3 values less than (maxvalue) tablespace ts_xiaos2009q3);

Table created.

SQL> insert into dyx_part_test4 values

(100, 'red black', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(101, 'the partition table test', to_date ('20090403 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(105, 'the partition table planning', to_date ('20090703 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1044, 'stream implementation', to_date ('20091103 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1400, 'oracle design', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1070, 'database planning', to_date ('20090303 ',' yyyymmdd '));

1 row created.

SQL>

SQL> commit;

Commit complete.

SQL> select * from dyx_part_test4;

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

1044 stream implementation of 03-NOV-09

6 rows selected.

SQL> select * from DYX_PART_Test4 partition (p2);

ID NAME FINISHED_DAT

---------- -------------------- ------------

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

SQL> select * from DYX_PART_Test4 partition (p1);

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

SQL> select * from DYX_PART_Test4 partition (e3);

ID NAME FINISHED_DAT

---------- -------------------- ------------

1044 stream implementation of 03-NOV-09

2, the scope of the list of district

List of geographical scope and the combination of technology, through the combination of the scope of the first partition on the table, and then the scope for each individual zoning district to use technology to further breakdown of the list.

Example:

CREATE TABLE dyx_part_test5 (

id NUMBER (6),

name VARCHAR2 (20),

quyu VARCHAR2 (30),

cretetime NUMBER (9,2))

PARTITION BY RANGE (cretetime)

SUBPARTITION BY LIST (quyu)

SUBPARTITION TEMPLATE

(SUBPARTITION east VALUES ( '

CHINA

','

JAPAN

','

INDIA

','

THAILAND

'),

SUBPARTITION west VALUES ( '

AMERICA

','

GERMANY

','

ITALY

','

SWITZERLAND

'),

SUBPARTITION other VALUES (DEFAULT))

(PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (2500),

PARTITION p3 VALUES LESS THAN (MAXVALUE));

insert into dyx_part_test5 values (1, 'DAI new', 'china', 300);

insert into dyx_part_test5 values (2, 'I am me', 'GERMANY', 2650);

insert into dyx_part_test5 values (75, 'oracle', '

JAPAN

', 6952);

insert into dyx_part_test5 values (34, 'IBM', '

SWITZERLAND

', 2100);

insert into dyx_part_test5 values (43, 'SUN', '

CHINA

', 320);

insert into dyx_part_test5 values (876, 'DELL', '

AMERICA

', 986);

insert into dyx_part_test5 values (866, 'SYSBASE', '

INDIA

', 6352);

SQL> insert into dyx_part_test4 values

(100, 'red black', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(101, 'the partition table test', to_date ('20090403 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(105, 'the partition table planning', to_date ('20090703 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1044, 'stream implementation', to_date ('20091103 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1400, 'oracle design', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1070, 'database planning', to_date ('20090303 ',' yyyymmdd '));

1 row created.

SQL>

SQL> commit;

Commit complete.

SQL> select * from dyx_part_test4;

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

1044 stream implementation of 03-NOV-09

6 rows selected.

SQL> select * from DYX_PART_Test5 partition (p1);

ID NAME FINISHED_DAT

---------- -------------------- ------------

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

SQL> select * from DYX_PART_Test4 partition (p1);

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

SQL> select * from DYX_PART_Test4 partition (e3);

ID NAME FINISHED_DAT

---------- -------------------- ------------

1044 stream implementation of 03-NOV-09

RMAN created physical standby 2009-03-30 22:49:29
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Turning optimization oracle article 2009-03-26 21:39:35

Oracle backup functions

kods.net — Tue, 21 Apr 2009 15:40:02+0000

rn
Oracle backup

rn
Include:

rn
Advanced Replication (Advanced Replication)

rn
Stream replication (Streams Replication)

rn
Preparation of the Treasury (Dataguard)

rn
A dataguard:

rn
dataguard in high availability and disaster recovery in general is a dba of choice, after all, exist in this regard dataguard overwhelming advantage, regardless of the physical standby database (physical standby database) or a logical standby database (logical standby database), they all have some common to be imposed.

rn
Configuration and management costs: dataguard than simple stream replication; security and stability in the cost of: dataguard than a stable and reliable stream replication.

rn
For a 24x7 on the system, which is very important to increase system downtime not only affects the image of the company, but will also affect the effectiveness of the company; used dataguard, data security very secure, the physical standby database can be completed in the shortest possible time failover, logical standby database in the protection of data security, but also can take a lot of statements and other business; dataguard as a result of the relatively simple configuration and management, empathy has also reduced the intensity of the work of dba;

rn
Second copy:

rn
Apply to the following:

rn
1, partial reproduction can stream or copy only certain models of certain

rn
2, heterogeneous environment full use of the existing equipment and technology

rn
3, long-range stream of network disaster recovery requirements are more stringent than the low dataguard

rn
be flexible enough to stream replication to copy the strategy, not only can be configured to copy only certain tables, but also can be configured to copy only some form of ddl or dml, compared dataguard must copy the entire database, the storage can save a considerable investment, after all, for some huge amount of data, there are a number of replication is unnecessary.

rn
If in a heterogeneous environment, that is, different operating systems, it dataguard will be at a loss, other than non-stream replication, this can make full use of the existing environment, high allocation to the program, in a heterogeneous environment, stream replication will be advanced replication of the strong opponents.

rn
stream replication is the spread of mining and packaging logmnr the logic of change records (LCRs), compared to dataguard send archived redo log, advanced replication and mview log of mview refresh the way, stream replication to reduce the demand on the network a lot in different places in the remote disaster recovery process, the rental network bandwidth is a higher cost, stream replication can reduce the cost of properly.

rn
High copy c:

rn
advanced replication in relation to dataguard, shortcomings are as follows: the more complex configuration and management, security and stability is not enough; advantages: partial replication, heterogeneous environment. advanced replication is a relatively mature technologies, in many critical systems has been successfully use in relation to 9iR2 introduced stream replication, the two parties apply to the environment may indeed be a, for example, can be partial replication, heterogeneous replication, long-range capacity disaster, such as, advanced replication and security in the stability of more tests.

rn
Stream replication compared with the bottom of advanced replication technology, stream replication in real-time performance, stability, high efficiency, low consumption (less cpu / network resources), etc. are more advantages, whenever some new features, both or or less, there are some uncertain factors.

rn
In 10gR1 in, oracle for the current stream replication weaknesses have been strengthened, not only to provide advanced replication from the stream replication to move to the script, but also provides a stream replication configuration and monitoring tools, stream replication in the configuration and management will be smart and simplistic take on competition for businesses and shareplex the important task of data replication market.

rn
Four high-level replication and copy the difference between flow

rn
Advanced Replication and Streams Replication principle is completely different, Streams Replication can go to Table, the user, the database level, but it seems that high-level copy to the table level only.

rn
Streams Replication Advanced Replication is not an upgraded version.

rn
Heterogeneous environment, oracle of the high availability and disaster recovery with high-level replication and copy the stream are the similarities and differences between the two points are as follows:

rn
1. Advanced Replication is based on the trigger (trigger) the principle of the stream is based on the principle of log mining, the stream of the source database to copy the performance of smaller, but not as good as real-time high-level replication.

rn
2. High copy replication database is based on the target object (object), such as tables, indexes and stored procedures, and the stream can copy table program (schema) and the entire database, so if the entire database for disaster recovery considerations, stream replication relatively simple configuration.

rn
3. Advanced Replication is a relatively mature technologies, in many critical systems has been successfully use in relation to the introduction of the stream copy 9iR2, senior copy the current stability and security in more tests.

rn
4. From a development point of view, the application of flow will be more and more, from oracle 10g , Oracle provided to the flow from the high-level copy tool to copy transplants, we can see, oracle will be more on the side of the new technologies based on the flow.

rn
5. As the Advanced Replication is based on the trigger, and therefore the structure of all of the copy object (ddl) changes must be adopted to provide a copy of oracle package to implement, and application of the relatively tight binding, it is more suitable to use in the development, and the stream is copied more suitable to the implementation of dba.

rn
6. Stream copy to support two-way data replication, and conflict will be high-level replication;

rn
7. Stream copy to support heterogeneous database replication, and the lack of information on the same high-level replication function;

rn
Two terms of the actual use of, streams require less bandwidth to copy, 2m Bandwidth, if not copy streams, advanced copy probably more gone, but the best use of other network streams too long break, I do not know is bug or not to consider this case oracle, if the replication halt for some time, and then returned to normal, probably Team list too much information, the team into the team very slow, non-linear increase in ah, it required a long period of time to synchronize data, the situation there is no high-level replication.

rn
bug, anyway, there are some 10,2,0,1, to see you touch the碰不到a serious team allows you to delete the list of reconstruction do imply that the essential is to rebuild the entire copy, but would like to repeat again not appeared; negative rule if there is the use of multi-site update has been encountered in large affairs becomes very slow speed, multi-site design of the first structure; will continue to apply the process in udump directory site trc file generated , although not very mature, but really good things to copy streams, will inevitably be replaced by high-level copy, it is recommended to fight 10.2.0 .3 Patch, it was said a number of amendments to the bug.

rn
stream of the system design and maintenance side of the stream have a considerable technical ability to control, and most of the department and the DBA did not experience this thing, so it is difficult to promote; dataguard wins in the maintenance of simple and reliable, can be the maintenance of general dba . stream will be very bright prospects! in particular is a two-way replication to solve a lot of practical problems.

rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Summary of the partition table (continued)

kods.net — Tue, 21 Apr 2009 14:25:09+0000

# Index-organized tables can be range or hash partition list.

Oracle Database 10g Also provides three types of zoning Index:

# Local index

Local index is the way of its geographical location and its regional approach based on the same table index. Local index of each district where the only basis of its form corresponds to a partition.

Example:

SQL> CREATE INDEX idx_dyx_part_test1 ON dyx_part_test1 (xiaos_date) LOCAL

2 (PARTITION idx_dyx_part_test1_01 TABLESPACE ts_xiaos2009q1,

3 PARTITION idx_dyx_part_test1_02 TABLESPACE ts_xiaos2009q2,

4 PARTITION idx_dyx_part_test1_03 TABLESPACE ts_xiaos2009q3,

5 PARTITION idx_dyx_part_test1_04 TABLESPACE ts_xiaos2009q4

6) TABLESPACE users;

Index created.

# Global index partition

Index is different from the district to use their keys in the table of the zoning district of the index, it can be in the table or partition table partition table. Global index partition can use

Range or hash partition to partition. For example, a table can be carried out by month the scope of zoning, it has a 12 zone, but the index on the table you can use a different partition key

To carry out the scope of zoning, which has a number of different zoning.

Zoning is the index of all the separate each district to create the index, it can automatically maintain, in the drop or truncate a partition does not affect the index of the other divisions of the use of the index, which is cable

Primers will not fail to maintain more convenient, but somewhat the impact of query performance

# Global non-partitioned index

Basically, the overall index of non-geographical and non-partition table as the index. Index structure is not partition.

Oracle provides a rich set of technologies that can be used for table, index and index-organized table partition, so can any business environment for any application to the best of the district.

Oracle also provides a complete set of SQL commands for managing the partition table. Including adding a new partition, delete partition, split partition merge partition, as well as an order.

Partition function can be used to improve the management of

Partition function through the Oracle, table and index can be divided into more manageable smaller units, so that database administrators can be "dismembered, all broken up" approach in the management of data.

The use of geographical features, the maintenance operation can be made at any particular part of the table.

For example, a database administrator can only do so on the table as part of the backup, without having to do a backup of the entire table. Database objects to the entire maintenance operation, in each district can be conducted on the basis of

Maintenance work which will be broken down into more manageable pieces.

The use of geographical features enhance the manageability of a typical data warehouse usage is to support the 'rolling window' load process. The assumption that a database administrator to the table a week to load new data. Can

The scope of the partition table so that each partition contains one week of data. This load process simply add a new partition. Add a partition to operate than the modified form of the much higher efficiency due to

DBA does not need to modify any other partition.

Improve performance by partition function

Due to restrictions on the operation of the inspection or the number of data, while supporting the implementation of parallel tasks, Oracle partition function of the realization of a performance gain. These features include:

# Division Dressing

Partition function of trimming is to improve the performance of district the simplest means of the most valuable. Zoning often repaired to improve query performance can be several orders of magnitude. For example, suppose an application includes a deposit

Reserve Orders Orders history table, and this week has been partition table. For inquiries, orders a week just to visit the orders of a sub-table. If the order contains a two-year history, the

Query only needs to visit a district rather than 104. The implementation of the speed of the query because the district may be repaired and a hundred times faster. Zoning dressing with all the other Oracle Collaboration performance characteristics.

Oracle trimming function to partition the index with any technology, connection technology or a combination of parallel access method.

The maintenance of the partition table:

Increase in district:

SQL> ALTER TABLE dyx_part_test1 ADD PARTITION xiaoss2009_q5

2 VALUES LESS THAN (TO_DATE ( ' 2010-06-01 ',' YYYY-MM-DD '))

3 TABLESPACE users;

Table altered.

If you already have maxvalue partition, can not increase the partition, can be taken to split the partition to increase partition!

Delete partition:

SQL> ALTER TABLE dyx_part_test1 DROP partition xiaoss2009_q5;

Table altered.

Truncated Zoning:

SQL> alter table dyx_part_test1 truncate partition xiaoss2009_q4;

Table truncated.

Merged district:

SQL> alter table dyx_part_test1 merge partitions xiaoss2009_q3, xiaoss2009_q4 into partition xiaoss2009_q4;

Table altered.

SQL> alter index idx_dyx_part_test1 rebuild partition idx_dyx_part_test1_01 parallel 4;

Index altered.

To split the district:

Range of examples:

SQL> ALTER TABLE dyx_part_test1

2 SPLIT PARTITION xiaoss2009_q4

3 AT (TO_DATE ( ' 2009-10-01 ',' YYYY-MM-DD '))

4 INTO (partition xiaoss2009_q3, partition xiaoss2009_q4);

Table altered.

List of examples:

SQL> select * from dyx_part_test2 partition (p2);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

3 sub-Tianjin Guangcheng

5 live in Shanghai have been too

7元始s Chongqing

SQL> alter table dyx_part_test2 split partition p2 values ( 'Tianjin', 'Chongqing') into (partition p5, partition p6);

Table altered.

SQL> select * from dyx_part_test2 partition (p2);

select * from dyx_part_test2 partition (p2)

*

ERROR at line 1:

ORA-02149: Specified partition does not exist

SQL> select * from dyx_part_test2 partition (p5);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

3 sub-Tianjin Guangcheng

7元始s Chongqing

SQL> select * from dyx_part_test2 partition (p6);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

5 live in Shanghai have been too

Note: a separate partition (on the example of the p5 and p6) can not be in the original partition table was already different from the zoning district

Exchange Zoning:

Exchange district (Exchange partition) provides a way for you in the table with the table or partition with the partition between the migration of data, instead of converting the attention of constituents or non-district basis, the relocation table is only data

(Co-migration), because of its claims is a way to change the data dictionary, so the most efficient (almost does not involve the operation io). Exchange partition format is applicable to all zoning, you can migrate data from the partition table

To non-partition table, but also from non-partition table can be moved to the partition table, or hash partition from the bar such as range partition

SQL> create table dyx_part_test7 (id number, name varchar2 (50))

2 partition by range (id)

3 (partition t_range_p1 values less than (10) tablespace ts_xiaos2009q1,

4 partition t_range_p2 values less than (20) tablespace ts_xiaos2009q2,

5 partition t_range_p3 values less than (30) tablespace ts_xiaos2009q3,

6 partition t_range_pmax values less than (maxvalue) tablespace ts_xiaos2009q4

7);

Table created.

SQL> insert into dyx_part_test7 values (11, 'a');

1 row created.

SQL> insert into dyx_part_test7 values (12, 'b');

1 row created.

SQL> insert into dyx_part_test7 values (13, 'c');

1 row created.

SQL> commit;

Commit complete.

SQL> create table dyx_part_test7_tmp (id number, name varchar2 (50));

Table created.

SQL> alter table dyx_part_test7 exchange partition t_range_p2

2 with table dyx_part_test7_tmp;

Table altered.

SQL> select * from dyx_part_test7 partition (t_range_p2);

no rows selected

SQL> select * from dyx_part_test7_tmp;

ID NAME

---------- ---------------------------------------- ----------

11 a

12 b

13 c

Record of successful exchange to non-partition table

Then the implementation of an exchange

SQL> alter table dyx_part_test7 exchange partition t_range_p2

2 with table dyx_part_test7_tmp;

Table altered.

SQL> select * from dyx_part_test7_tmp;

no rows selected

SQL> select * from dyx_part_test7 partition (t_range_p2);

ID NAME

---------- ---------------------------------------- ----------

11 a

12 b

13 c

As can be seen from the above exchange back and

SQL> insert into dyx_part_test7_tmp values (15, 'd');

1 row created.

SQL> insert into dyx_part_test7_tmp values (16, 'e');

1 row created.

SQL> insert into dyx_part_test7_tmp values (17, 'd');

1 row created.

SQL>

SQL> commit;

Commit complete.

SQL> alter table dyx_part_test7 exchange partition t_range_p2

2 with table dyx_part_test7_tmp;

Table altered.

SQL>

SQL> select * from dyx_part_test7 partition (t_range_p2);

ID NAME

---------- ---------------------------------------- ----------

15 d

16 e

17 d

SQL> select * from dyx_part_test7_tmp;

ID NAME

---------- ---------------------------------------- ----------

11 a

12 b

13 c

From the above test can be seen that the partition table or two tables in the exchange of data!

Note:

Participation in the exchange of the two tables must be consistent between the table structure, unless an additional clause with validation;

If it is from a non-partition table partition table to the exchange, non-partition table data in the partition table must be consistent with the rules specified in district, unless an additional clause without validation;

If the partition table from the partition table to the exchange, the exchange of data must comply with zoning district regulations, unless an additional clause without validation;

Global index or data related to changes in the global index for the home district will be unusable, unless an additional clause update indexes.

Tip:

Once the additional clause without validation, the data indicated that he did not verify the validity of, so be sure to specify when the clause carefully.

Access to designated districts:

SQL> select * from dyx_part_test2 partition (p1);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

Beijing 2 the neutron cloud

8 God of War South-class Beijing

Import Export:

EXPORT designated districts:

[oracle @ ora ~] $ exp dyx / dyx tables = dyx_part_test2: p1 file = dyx_part_test2_p1.dmp

Export: Release 10.2.0 .1.0 - Production on Sat Apr 25 09:42:47 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0 .1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

.. Exporting table DYX_PART_TEST2

.. Exporting partition P1 2 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

IMPORT designated districts:

[oracle @ ora ~] $ imp dyx / dyx file = dyx_part_test2_p1.dmp TABLES = (dyx_part_test2: p1) IGNORE = y

Import: Release 10.2.0 .1.0 - Production on Sat Apr 25 09:44:15 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0 .1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT: V 10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. Importing DYX's objects into DYX

. Importing DYX's objects into DYX

.. Importing partition "DYX_PART_TEST2": "P1" 2 rows imported

Import terminated successfully without warnings.

Ordinary table into a partition table

Will have the general form of the existence of data into the partition table, not by modifying the properties directly into the partition table, it is necessary to change the way the reconstruction, generally there are three ways:

Method I: surface reconstruction using the original partition table.

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

SQL> INSERT INTO T

2 SELECT ROWNUM, SYSDATE - ROWNUM FROM user_OBJECTS WHERE ROWNUM <= 5000;

57 rows created.

SQL> commit;

Commit complete.

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE ( ' 2009-01-01 ',' YYYY-MM-DD ')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE ( ' 2009-04-01 ',' YYYY-MM-DD ')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE ( ' 2009-10-01 ',' YYYY-MM-DD ')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE))

6 AS SELECT ID, TIME FROM T;

Table created.

Replacement table

SQL> RENAME T TO T_OLD;

Table renamed.

SQL> SQL> RENAME T_NEW TO T;

Table renamed.

SQL> SELECT COUNT (*) FROM T PARTITION (P2);

COUNT (*)

----------

33

SQL> SELECT COUNT (*) FROM T PARTITION (P3);

COUNT (*)

----------

24

Display the current user can access all the partitions out the partition table information: ALL_PART_KEY_COLUMNS

Displays the current partition table to all users out of the partition information: USER_PART_KEY_COLUMNS

Shows that sub-district column shows the database of all the partition table information out of the sub-district: DBA_SUBPART_KEY_COLUMNS

Display the current user can access all the partition table information out of the sub-district: ALL_SUBPART_KEY_COLUMNS

Displays the current partition table to all users of the sub-partitions out information: USER_SUBPART_KEY_COLUMNS

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle is willing to buy 7.4 billion US dollars from Sun

kods.net — Tue, 21 Apr 2009 10:20:46+0000

Oracle is willing to buy 7.4 billion U.S. dollars from Sun

Oracle has announced that would be willing to 74 million in cash and debt acquisition of Sun Microsystems, Inc., or the stock price of 9.5 U.S. dollars per share.

According to foreign media reports, the transaction price is higher than that IBM earlier this month out of 9.4 U.S. dollars per share price of the deal.

Oracle said it is estimated that the profit created by this acquisition will more than before the acquisition of BEA, PeopleSoft and Siebel to create higher profits, at the same time the acquisition is expected to be its non-GAAP operating profit more than 1.5 billion U.S. dollars brought about by the contribution, and in the sub - continue to bring in the contribution of two billion U.S. dollars.

Oracle CEO Larry Ellison (Larry Ellison), said Sun's acquisition of the purpose of integration of IT industry, and enterprise-class software and mission-critical computing systems combine to play a role.

Ellison also pointed out that as a result of the decline in the cost of system integration, and system performance, reliability and security increase, which will ultimately benefit customers.

In addition, Oracle also said, Sun's Java software, the company acquired the most important software, Oracle is set up with the software of its Fusion middleware.

DBA should have the character 2009-05-23 07:14:17
Summary of the partition table 2009-04-21 18:21:19
Partition table summary 2009-04-21 03:07:11
Zoning on the Oracle 2009-04-15 00:52:26
oracle basic concepts and terminology 2009-04-06 22:17:39

oracle technology

kods.net — Tue, 21 Apr 2009 10:00:07+0000

Oracle database today to study the Cluster, DataGuard, the logic of the physical backup, flash back, as well as technical Rman. On learning experiences:
rn
Cluster: 10g or later to use cluster operating crs; the use of VIP, ASM, OCR technology;
rn
Backup Technology:
rn
DataGuard: there are main points from the machine, the host data can be automatically synchronized to the slave, master and slave can automatically switch, trigger, the observer, sql statements may switch the status of master and slave; its way synchronization can be synchronized physics can also be is a logical synchronization;
rn
Physical backup and logical backup: Data Pump technology enables the implementation of the logic of physics to back up statements faster;
rn
Flashback: Automatic data kept for some time, if necessary, to achieve instantaneous recovery, a bit like goast
rn
RMAN: backup tool, mainly used for backup, from a library to another library guide.
rn

rn

rn
Question:
rn
1. Cluster using the vip, can not support dual-card dual-network segment running? ? Progress or regress? ?
rn
2. RMAN backup and DataGuard What is the difference between the backup?
rn
3. Application is to continue to use OCI to connect local or remote connectivity options, is located in the machine, or even on other machines?

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Partition table summary

kods.net — Tue, 21 Apr 2009 03:07:11+0000

Partition table

Learning through this section, you can learn the following questions

1, to understand the concept of partition table

2, clearly the use of the partition table

3, to understand the classification of the partition table

4, to understand the operation of the partition table

We use an ordinary table in the process will be particularly large amount of data the table, when a data table for more than 20 million have been or occupier 2G Space, it is recommended to establish the partition table

The classification of the partition table:

Range (range) Zoning

Hash (hash) partitions

List (list) Zoning

As well as the combination of district: Range-Hash, Range-List

Division of advantages:

Enhanced availability: If a partition table failure, table of data in other divisions are still available;

Easy maintenance: If a partition table failure, the need to repair the data, only to repair the partition;

Balanced I / O: can be mapped to a different disk partition to balance the I / O, improve the overall system performance;

Improve the query performance: The partition of the query object can be of concern to only search their own district to improve the retrieval speed.

Executive Summary

Partition function of many applications can improve the manageability, performance and availability. Through the partition function can be table, index and index-organized table is further broken down into paragraphs, which can more accurately manage and access these database objects. Orac # provides a wide variety of district programs to meet the operational requirements of each. Moreover, because the sql statement in the district is completely transparent, so the function can be applied to almost any application.

The advantages of geographical features

Partition function by improving the manageability, performance and availability of applications so as to bring all kinds of great benefits. Typically, the district can make some inquiries as well as the performance of maintenance operations greatly enhanced. In addition, the district can also greatly simplify common administrative tasks. Through the District, database designers and administrators to address the cutting-edge applications brought about by a number of challenges. District is building a gigabyte of data systems or ultra-high availability systems a key tool.

Basic knowledge of geographical features

Partition function can be table, index or index-organized table is further broken down into paragraphs. These database objects called the partition of the paragraph. Each district has its own name, you can choose its own storage characteristics.

From the database administrator's point of view, after a sub-object with multiple paragraphs, the paragraphs of collective management can also be managed separately. This allows database administrators in the management of the object after the district there is considerable flexibility. However, from the application point of view, after the partition table partition table with non-identical, so that # S # D # command to visit the table after partition, without any changes to the partition table is through the "partition key "to be achieved, area refers to the number of keys out, which included the decision of the district where a line. Orac # data # 1 # 6 technologies provided for the partition table:

# The scope of Division

Each district by the scope of a sub-key value specified (for a date as a partition key column of the Table, "January 2005" zoning district that contains keys for the " 1 January, 2005 "To" January 31, 2005 "Line).

That the scope of zoning cases:

Assuming that there is an enterprise sales data, and can be used for a range partition table

Four tables to create space for use by the partition table should be placed on a different partition on the physical device, better performance can be my test environment is a physical device with

SQL> create tablespace ts_xiaos2009q1 datafile

'/ oradata/dbnms/ts_xiaos2009q1.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q2

datafile '/ oradata/dbnms/ts_xiaos2009q2.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q3 datafile

'/ oradata/dbnms/ts_xiaos2009q3.dbf' size 50M ;

Tablespace created.

SQL> create tablespace ts_xiaos2009q4 datafile

'/ oradata/dbnms/ts_xiaos2009q4.dbf' size 50M ;

Tablespace created.

Create table

SQL> CREATE TABLE dyx_part_test1

2 (id NUMBER,

3 name varchar2 (20),

4 shuliang number,

5 danjia number,

6 xiaos_date DATE NOT NULL)

7 PARTITION BY RANGE (xiaos_date)

8 (PARTITION xiaoss2009_q1

9 VALUES LESS THAN (TO_DATE ( ' 2009-04-01 ',' YYYY-MM-DD '))

10 TABLESPACE ts_xiaos2009q1,

11 PARTITION xiaoss2009_q2

12 VALUES LESS THAN (TO_DATE ( ' 2009-07-01 ',' YYYY-MM-DD '))

13 TABLESPACE ts_xiaos2009q2,

14 PARTITION xiaoss2009_q3

15 VALUES LESS THAN (TO_DATE ( ' 2009-10-01 ',' YYYY-MM-DD '))

16 TABLESPACE ts_xiaos2009q3,

17 PARTITION xiaoss2009_q4

18 VALUES LESS THAN (TO_DATE ( ' 2010-01-01 ',' YYYY-MM-DD '))

19 TABLESPACE ts_xiaos2009q4);

Table created.

Insert data

SQL> insert into dyx_part_test1 values

(1, 'Coca-Cola', 100,3.5, to_date ( ' 2009-03-05 ',' yyyy-mm-dd '));

1 row created.

SQL> insert into dyx_part_test1 values

(2, 'Kentucky', 100,10.5, to_date ( ' 2009-06-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(3, 'Zhenglin 3A 500g ', 100,15, to_date (' 2009-09-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(4, 'Ice Tea', 100,3, to_date ( ' 2009-11-05 ',' yyyy-mm-dd '));

1 row created.

SQL>

SQL> insert into dyx_part_test1 values

(5, 'orange', 100,3.5, to_date ( ' 2010-04-05 ',' yyyy-mm-dd '));

insert into dyx_part_test1 values

(5, 'orange', 100,3.5, to_date ( ' 2010-04-05 ',' yyyy-mm-dd '))

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

Note: If you can not address district-wide data is not inserted, will report ORA-14400 error

Show:

SQL> select * from DYX_PART_Test1 partition (XIAOSS2009_Q3);

ID NAME SHULIANG DANJIA XIAOS_DATE

---------- -------------------- ---------- ---------- ------------

3 Zhenglin 3A 500g 100 15 05-SEP-09

# A list of district

Each district by a district designated key list (for an area out of the table as a partition key, "North America" partition may contain values of "Canada" "United States" and "Mexico").

Examples are as follows:

SQL> create table dyx_part_test2 (

2 id number (4),

3 name varchar2 (30),

4 loca varchar2 (30))

5 partition by list (loca)

6 (partition p1 values ( 'Beijing') tablespace ts_xiaos2009q1,

7 partition p2 values ( 'Shanghai', 'Tianjin', 'Chongqing') tablespace ts_xiaos2009q2,

8 partition p3 values ( 'Guangdong', 'Fujian') tablespace ts_xiaos2009q3,

9 partition p0 values (default) tablespace users

10);

Table created.

SQL> insert into dyx_part_test2 values (1, 'DAI new', 'Gansu');

1 row created.

SQL> insert into dyx_part_test2 values (2, 'Cloud neutron', 'Beijing');

1 row created.

SQL> insert into dyx_part_test2 values (3, 'Guangcheng son', 'Tianjin');

1 row created.

SQL> insert into dyx_part_test2 values (4, '南极仙翁', 'Hebei');

1 row created.

SQL> insert into dyx_part_test2 values (5, 'too has been a real person', 'Shanghai');

1 row created.

SQL> insert into dyx_part_test2 values (6, 'fear Mr Sun', 'Canton');

1 row created.

SQL> insert into dyx_part_test2 values (7, '元始s',' Chongqing ');

1 row created.

SQL> insert into dyx_part_test2 values (8, 'God of War South-class',' Beijing ');

1 row created.

SQL> insert into dyx_part_test2 values (9, 'the five God of War', 'Fujian');

1 row created.

SQL> select * from dyx_part_test2;

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

Beijing 2 the neutron cloud

8 God of War South-class Beijing

3 sub-Tianjin Guangcheng

5 has been a real person too Shanghai

7元始s Chongqing

6 Liu孙广东fear

9 God of War five Fujian

1 DAI new Gansu

4南极仙翁Hebei

9 rows selected.

SQL> select * from DYX_PART_Test2 partition (p2);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

3 sub-Tianjin Guangcheng

5 has been a real person too Shanghai

7元始s Chongqing

SQL> select * from DYX_PART_Test2 partition (p0);

ID NAME LOCA

---------- ------------------------------ ---------- --------------------

1 DAI new Gansu

4南极仙翁Hebei

3 sub-Tianjin Guangcheng

# Hash partition

Hash algorithm will be used to partition the specified key to determine where the district line, by specifying the partition number for a uniform geographical distribution of data types, as in I / O devices to hash partition,

The same size so that these zoning.

Example:

create table dyx_part_test3

(id number primary key,

class_id number (8) not null,

name varchar2 (300),

finished_date date

)

partition by hash (id)

(

partition p01 tablespace ts_xiaos2009q1,

partition p02 tablespace ts_xiaos2009q2,

partition p03 tablespace ts_xiaos2009q3

);

insert into dyx_part_test3 values

(3,12, 'quality of the skin', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(1,13, 'tiger', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(200,15, 'the middle of the tiger', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

insert into dyx_part_test3 values

(230,19, 'tiger cat', to_date ( ' 2009-05-30 ',' yyyy-mm-dd '));

SQL> select * from dyx_part_test3;

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

23,019 cat skins 30-MAY-09

SQL> select * from dyx_part_test3;

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

23,019 cat skins 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p02);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

3 12 upper skin of 30-MAY-09

1 13 tiger 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p01);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

Skin of 20,015 middle 30-MAY-09

SQL> select * from DYX_PART_Test3 partition (p03);

ID CLASS_ID NAME FINISHED_DAT

---------- ---------- -------------------- ---------- --

23,019 cat skins 30-MAY-09

# Portfolio Zoning

1, the scope of hash (hash) partitions

Range and hash partition technology portfolio through the combination of the scope of the first partition on the table, and then the scope for each individual district to use technology to further breakdown of the hash partition.

Index range of organizations can only partition table.

Example:

SQL> create table dyx_part_test4 (

2 id number,

3 name varchar2 (30),

4 finished_date date)

5 partition by range (finished_date)

6 subpartition by hash (id)

7 subpartitions 2

8 (partition p1 values less than (to_date ('20090501 ',' YYYYMMDD ')) tablespace ts_xiaos2009q1,

9 partition p2 values less than (to_date ('20091001 ',' YYYYMMDD ')) tablespace ts_xiaos2009q2,

10 partition e3 values less than (maxvalue) tablespace ts_xiaos2009q3);

Table created.

SQL> insert into dyx_part_test4 values

(100, 'red black', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(101, 'the partition table test', to_date ('20090403 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(105, 'the partition table planning', to_date ('20090703 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1044, 'stream implementation', to_date ('20091103 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1400, 'oracle design', to_date ('20090603 ',' yyyymmdd '));

1 row created.

SQL> insert into dyx_part_test4 values

(1070, 'database planning', to_date ('20090303 ',' yyyymmdd '));

1 row created.

SQL>

SQL> commit;

Commit complete.

SQL> select * from dyx_part_test4;

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

1044 stream implementation of 03-NOV-09

6 rows selected.

SQL> select * from DYX_PART_Test4 partition (p2);

ID NAME FINISHED_DAT

---------- -------------------- ------------

100 black red 03-JUN-09

105 partition table planning 03-JUL-09

1400 oracle design 03-JUN-09

SQL> select * from DYX_PART_Test4 partition (p1);

ID NAME FINISHED_DAT

---------- -------------------- ------------

101 partition table testing 03-APR-09

1070 database planning 03-MAR-09

SQL> select * from DYX_PART_Test4 partition (e3);

ID NAME FINISHED_DAT

---------- -------------------- ------------

1044 stream implementation of 03-NOV-09

2, the scope of the list of district

List of geographical scope and the combination of technology, through the combination of the scope of the first partition on the table, and then the scope for each individual zoning district to use technology to further breakdown of the list.

Example:

CREATE TABLE dyx_part_test5 (

id NUMBER (6),

name VARCHAR2 (20),

quyu VARCHAR2 (30),

cretetime NUMBER (9,2))

PARTITION BY RANGE (cretetime)

SUBPARTITION BY LIST (quyu)

SUBPARTITION TEMPLATE

(SUBPARTITION east VALUES ( '

CHINA

','

JAPAN

','

INDIA

','

THAILAND

'),

SUBPARTITION west VALUES ( '

AMERICA

','

GERMANY

','

ITALY

','

SWITZERLAND

JAPAN

', 6952);

insert into dyx_part_test5 values (34, 'IBM', '

SWITZERLAND

', 2100);

insert into dyx_part_test5 values (43, 'SUN', '

CHINA

', 320);

insert into dyx_part_test5 values (876, 'DELL', '

AMERICA

', 986);

insert into dyx_part_test5 values (866, 'SYSBASE', '

INDIA

RMAN created physical standby 2009-03-30 22:49:29
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Turning optimization oracle article 2009-03-26 21:39:35

Create table PCTUSED parameters PCTFREE and what does this mean?

kods.net — Tue, 21 Apr 2009 01:05:19+0000

Create table PCTUSED parameters PCTFREE and what does this mean?

pctfree: Used to specify the necessary reservations BLOCK in the smallest proportion of space.
pctused: as a percentage of value, when BLOCK space has been reduced to the following value, the BLOCK is available to reach or exceed this value can not be used BLOCK.
The general segment in the control structure of an independent object, the use of these two parameters to control the storage management BLOCK.
initrans: the specified operation can be complicated by the affairs of the table number.

CREATE command with PCTUSED, PCTFREE, INITTRANS, MAXTRANS, FREELIST GROUPS and FREELISTS parameters to control the table, index or cluster of data blocks in the space.

Can be used and PCTFREE parameter control data block PCTUSED when entering or leaving the table, index or list of available clusters. PCTFREE parameters used to set the maximum threshold, in order to control the line of the future update of the number of data blocks of space reservation. When a data block so that when PCTFREE filled, ORACLE available from the corresponding paragraph in the deletion of the block list. If you are expected to update only a few lines will increase the size, PCTFREE can be set to a lower value (such as 5 or 10), making each block ORACLE fill more space. However, if you will be expected to update the regular increase in the size of line will be set to a higher PCTFREE value (such as 20 or 30), have been made for the trip ORACLE update block retain more space; otherwise, will be that existing chain.

Parameter setting can be used most PCTUSED small threshold to control a data block should be returned when the corresponding list of available paragraph. For example, all paragraph PCTUSED default set to 40%, Therefore, when the transaction processing to delete the data block when, if it is filled with only 39% of, ORACLE data blocks back to the corresponding list of available paragraph . If you rarely have to delete the operation is expected to be set up for the higher value PCTUSED (eg, 60), when the accidental deletion occurs, so that the available data blocks pop-up list. However, if you PCTUSED is expected to be set to a lower value (eg 40), so do not have a ORACLE blocks of available space in the table moved into or out of the overhead.

INITRANS and MAXTRANS can be used to optimize ORACLE how parameters in the table, data, cluster or index data block of the distribution of transaction processing. INITRANS parameter for the transaction to determine the distribution of the number of data blocks in advance of the head space. When you are expected to be many complicated transaction involving a block may be related to the transaction processing for pre-allocation of more space, so as to avoid dynamic allocation of the overhead space. Parameter restrictions MAXTRANS data blocks in parallel using a number of transaction processing. When you are expected to have many affairs to deal with parallel access to a small table, then create a table, the table should be set up to deal with the affairs of the block pre-allocation of more space, higher parameter values MAXTRANS allow parallel access to a number of transaction processing The table INITRANS parameters and settings may MAXTRANS corresponding lower (eg 2 and 5, respectively). The use of such a set up, very few transaction processing for scenting reservation, so that the table data blocks of more space for data.

Can use the STORAGE clause FREELIST GROUPS section of the parameters can be used to control the amount of data a list of groups. FREELISTS parameters used in each group the number of available list.

From the Internet, thanks to the author!

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Detailed DBMS_STATS.GATHER_TABLE_STATS

kods.net — Mon, 20 Apr 2009 22:56:43+0000

Role: DBMS_STATS.GATHER_TABLE_STATS tables, columns, indexes of statistical information.

DBMS_STATS.GATHER_TABLE_STATS syntax is as follows:

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
Parameter Description:

ownname: To analyze the table owner

tabname: To analyze the table.

partname: district name, only the partition table or regional index useful.

estimate_percent: the percentage of sampling lines, range [0.000001,100], null for the entire analysis, not sampling. constants: DBMS_STATS.AUTO_SAMPLE_SIZE is the default value, the best decision taken by the oracle sample values.

block_sapmple: whether to use block sampling instead of sampling lines.

method_opt: decide what information is being histograms statistics. method_opt value as follows:

for all columns: column statistics for all histograms.

for all indexed columns: Statistics indexed list of all the histograms.

for all hidden columns: statistics you can not see out of the histograms

-points with the same value which is what we define by "there is skew in the data

degree: degree of parallelism decision. The default value is null.

granularity: Granularity of statistics to collect, only pertinent if the table is partitioned.

cascace: the index is to collect information. default falase.

stattab designated to store the table of statistical information, statid If multiple tables of statistical information is stored in the same distinction between stattab used. statown statistical information table storage owners. does not specify the above three parameters, statistical information will be directly updated to the data dictionary.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force: even if the table is locked, it will be the collection of statistical information.

Examples:

execute dbms_stats.gather_table_stats (ownname => 'owner', tabname => 'table_name', estimate_percent => null, method_opt => 'for all indexed columns', cascade => true);

SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28

Modify the ordinary user's default tablespace specified

kods.net — Mon, 20 Apr 2009 22:18:05+0000

DBA experience often is such a headache: I do not know who created an Oracle user, to create did not give the user-specified default tablespace, so the user will be using the default table space - system table space. Lead to system tables user data space quickly filled up downtime.
In 10G in, DBA there are ways to avoid this problem - the online system default tablespace specified:

ALTER DATABASE DEFAULT TABLESPACE ;

Through the implementation of the above command, you can set the default system tablespace. In that case, create a new user, If you do not specify the default table space he will be using the system specified above, the default table space used as the default table space.

SQL> conn / as sysdba

SQL> create user test1 identified by test1 default tablespace ringidx;

The user has been created.

SQL> alter database default tablespace ring;

Database has been changed.

SQL> create user test identified by test;

The user has been created.

SQL> select username, default_tablespace defspace from dba_users where

usernae = 'TEST';

USERNAME DEFSPACE

----- ------

TEST RING

However, it should be noted that, once the system modify the default table space, the original of all the ordinary user's default table space will be designated as this table space, as the example test1, created when he designated as the default tablespace ringidx the implementation of the 'alter database default tablespace ring', he's the default table space has been replaced by a ring.

SQL> select username, default_tablespace defspace from dba_users where

username = 'TEST1';

USERNAME DEFSPACE

----- ------

TEST1 RING

From the Internet, I would like to thank the author!

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

10g tips

kods.net — Mon, 20 Apr 2009 06:14:53+0000

SQL> exec dbms_scheduler.disable ( 'SYS.GATHER_STATS_JOB');
PL / SQL procedure successfully completed.

# exec dbms_scheduler.enable ( 'SYS.GATHER_STATS_JOB');

oracle pl / sql examples of practice 2009-04-06 04:35:08
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
An example of the use of TKPROF 2009-02-26 01:38:19

Modify the specified user default tablespace

kods.net — Mon, 20 Apr 2009 02:32:32+0000

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

apex3.2 steps to install oracle10

kods.net — Sun, 19 Apr 2009 10:33:11+0000

1. Installation oracle10, I installed the Release 10.1.0.2.0, in the installation database, it can be installed through dbca to create a simple database
2. Companion CD-ROM to install the third, oracle database 10g companion products 10.2.0.1.0, and databases installed in different directory
3. Download apex 3.2, extract
4. To install database httpserver after installation, the environment variable may be a problem, you can buy some environment variables
set ORACLE_HOME = D: \ oracle
set J2EE_HOME =% ORACLE_HOME% \ j2ee \ home
set ORACLE_SID = bill
set PATH =% ORACLE_HOME% \ jdk \ bin;% ORACLE_HOME% \ bin;% PATH%
set LD_LIBRARY_PATH =% ORACLE_HOME% \ lib;% ORACLE_HOME% \ ctx \ lib
Path to be installed in accordance with the practical situation
4. To enter the apex directory to sys log in sqlplus, the implementation of @ apexins SYSAUX SYSAUX TEMP / i /
SYSAUX SYSAUX TEMP is a designated space for three tables, / i / is specified by the virtual picture of the order of a previous version of the path parameter is @ apexins PASSWD SYSAUX SYSAUX TEMP / i /, 3.2 version was revised, passwd use a separate @ apxchpwd.sql amend.
5. After the installation is complete under the apex of the images directory are copied to the httpserv under the \ Apache \ Apache directory
6. Httpserv prepared under the Apache \ modplsql \ conf \ dads.conf, a typical preparation is as follows
# ================================================= ===========================

# Mod_plsql DAD Configuration File
# ================================================= ===========================

# 1. Please refer to dads.README for a description of this file

# ================================================= ===========================
Alias / i / "D: / oraclehttp / Apache / Apache / images /" # # picture of virtual path and the actual path
AddType text / xml xbl
AddType text / x-component htc

Order deny, allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_manager.process_download

PlsqlDatabaseConnectString localhost: 1521: bill ServiceNameFormat # # database sid

PlsqlNLSLanguage "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects $

PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword oracle # # database user password APEX_PUBLIC_USER
Allow from all

7. Can visit to do some (1) lstrctl start listening (2) to unlock the database user APEX_PUBLIC_USER (3) amend the APEX_PUBLIC_USER user's password and the preparation of documents in line
8. Start httpserv, can be visited
http://localhost:7777/pls/apex

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31
An example of the use of TKPROF 2009-02-26 01:38:19

Oracle installation difficulties

kods.net — Sun, 19 Apr 2009 04:08:56+0000

Recently changed computers, used to be installed directly suse11 system 32. The entertainment in order to install a virtual machine, installed in the virtual machine suse10 system, but is 64-bit.

After installation, it still felt pretty good by comparison to upgrade the computer industry under the virtual machine can also run. However, when installed oracle10g encountered a lot of trouble. Oracle software is installed when it is full of smooth, but to start dbca, netca error when, the error message is as follows:

dbca-silent-responseFile / home / billy / ftphome / up / database / response / dbca.rsp
UnsatisfiedLinkError exception loading native library: njni10
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
at oracle.net.common.NetGetEnv.get (Native Method)
at oracle.net.config.Config.getNetDir (Unknown Source)
at oracle.net.config.Config.initConfig (Unknown Source)
at oracle.net.config.Config. (Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils. (NetworkUtils.java: 222)
at oracle.sysman.assistants.util.step.StepContext. (StepContext.java: 255)
at oracle.sysman.assistants.dbca.backend.Host. (Host.java: 682)
at oracle.sysman.assistants.dbca.backend.SilentHost. (SilentHost.java: 125)
at oracle.sysman.assistants.dbca.Dbca.getHost (Dbca.java: 150)
at oracle.sysman.assistants.dbca.Dbca.execute (Dbca.java: 94)
at oracle.sysman.assistants.dbca.Dbca.main (Dbca.java: 180)

Looking online for a long time is that the lack of

glibc-32bit-8.1-9
glibc-devel-32bit-8.1-9

But still not installed.

$ ORACLE_HOME / bin / relink all the time also reported that a package can not be found.

The previous 32-bit install suse11 do not have any problems, and toss about for several days after the first and put into database manually.

To be a mark in this, to be resolved by re-recording solutions and causes.

Friends to know may also wish to point.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle Character Set

kods.net — Sat, 18 Apr 2009 22:50:37+0000

We have to discuss four areas:

1: the server character set

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2: character set client can find the registry miscellaneous NS_LANG

3: sqlplus Character Set

D: \ oracle \ product \ 10.2.0 \ client_1 \ sysman \ config \ dbappscfg.properties

Inside SQLPLUS_NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1 can be modified into

SQLPLUS_NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

ORACLE very difficult to learn

kods.net — Sat, 18 Apr 2009 08:03:43+0000

ORACLE more points the more knowledge, a look at the forgotten front of the back, not enough of a head, ah, ah

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

ora-01756

kods.net — Fri, 17 Apr 2009 23:42:30+0000

The use of character set AL32UTF8 problems

Client character set is also set to AL32UTF8, when the implementation of the problem statement:

SQL> update t_port_permission set permission_name = 'delete inbound' where permission_name ='123 ';
ERROR:
ORA-01756: quoted string not properly terminated

SQL> update t_port_permission set permission_name = 'delete administrator' where permission_name ='123 ';

1 row updated.

SQL> commit;

Commit complete.

SQL> update t_port_permission set permission_name = 'administrator management' where permission_name ='234 ';
ERROR:
ORA-01756: quoted string not properly terminated
SQL> update t_port_permission set permission_name = 'Operator' where permission_name ='234 ';

1 row updated.

SQL> commit;

SQLPlus skills 2009-04-01 09:37:29
Oracle deadlock related 2009-03-30 16:46:54
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28

ORACLE Data Recovery

kods.net — Fri, 17 Apr 2009 22:07:11+0000

To the Oracle database backup and restore a clear understanding of, first of all, it is necessary to run several databases have a full understanding of the state. Running Oracle database is divided into three kinds, they were as follows:

l Nomount (non-installed) Oracle only ini file to read configuration information and initialization SGA area.

l Mount (installed) Oracle in addition to the ini file to read but also read the control file and the database to obtain information such as physical structure.

l Open (open) database to check all the files at the same time, the error recovery rollback of unfinished matters, and ultimately allow users to access.

Backup database is divided into three categories: cold backup; hot backup; logical backup;

Database backup is not the focus of this paper, here only a brief introduction, Oracle database backup are:

l Cold Backup (cold backup) means the closure of the main database for the state of the database full backup, backup includes all data files, control files, online log files, ini files.

l Hot Backup (backup) means the database is running under the control of the data files and backup files, it is necessary to use the hot backup database must be running on (Archive Log) archive mode.

l Export (logical backup) This is the simplest backup methods, may be a database table or a user to export the entire database, and supports all of the cumulative, incremental in three ways. Using this method, the database must be open, and if the database is not in the state will not be able to restrict export of data to ensure consistency.

Database recovery can be divided into two categories: complete recovery; not fully recovered;

Fully restored back to the database refers to the failure point in time, do not lose any data. Incomplete recovery refers to the database back to before the failure of a certain point in time, this time after all the changes will be lost. If there are no special needs, we propose to make full use of fully restored.

Oracle database recovery process conducted in two steps, first of all, will be stored in the redo log files using all redo to data files, and then redo all of the matters not submitted rollback, so all data on the occurrence of return to that moment of disaster. Database only in the event of failure to repair the data files before using again, back to the failure of its time, data files can not be reverse-roll back to a time before. For example, we have a database backup 2001/1/1, 2001/5/1 When the database so that the data we found that confusion, I hope to return to the database state at the time of 2001/4/30, we can only be the restoration of 2001/1/1 database backup and then used in its redo records to at the time of pre-rolled state 2001/4/30, 2001/5/1 the database can not be rolled back to back 2001/4/30 .

In order to design a database system to resume the program, we have encountered an error that may be classified, Oracle Database error is divided into 5 broad categories:

l SQL statement failed

l thread failure

examples of the failure of l

l user operation failed

l storage device failure

If the failure of the first three, do not require human intervention, Oracle system will automatically recover. The failure of user-based (such as误删除data), the remedial measures we have taken into the main logic of the latest backup or to a certain point in time is not fully restored. Oracle 8 from a new version after the introduction of table-based restoration of space time points (TSPITR), will contain the error can be a separate operation to return to the table space for a specified period of time, rather than the entire database is not fully restored. Operator error was found when comparing the amount of data is not timely and the case can also consider the use of reverse logminer generated SQL.

Storage devices for the failure of the complexity of the situation is the focus of this paper, the failure of storage devices placed in their bound to make the document becomes unavailable, we first Oracle database of documents involved in a division can be mainly divided into as follows:

l Oracle system files, refer to the operation of Oracle documents, various applications

l databases control file

l database online redo log file

l data files

l log file archiving

To avoid the failure of the first document to rely largely on the system administrator to backup the operating system level, when the accident occurred can only rely on the operating system will restore the backup.

Control file records the entire database structure, the status of each data file, the system SCN, checkpoints and other important information counter in the database will create a user-specified control of the three locations to store files, they mirror each other, When the failure of any one of them, simply comment out from the ini file data files Fault data can be re-activated. When all control of all the failure, you can Nomount mode to re-create controlfile control file generated, but must provide a redo log, data file, the file name and address, as well as MAXLOGFILES, MAXDATAFILES, MAXINSTANCES information. If it fails to run off before the alter database backup controlfile to trace or alter database backup controlfile to 'xxx' to the control file for backup, recovery can be used to generate the script to rebuild or cover the backup file, if you use the old control file To use the recovery recover xxx using backup controlfile option for recovery, and use the option to open the database resetlogs.
If the loss is the online log files, to deal with both cases at 1, the loss of a non-event log file; 2, the loss is the activation of the log file.

If this is the first case, and the failure of the log file with multiple members of another group, you can first database shutdown, and then use the operating system to damage the command log file members of the group a good log file to document damage to a member of the coverage (in the with a log of all the members of the group the log file for the mirror), if its physical location is not available can be copied to a new drive, use the alter database rename file 'xxxx' to 'xxxx' change file location, After the restart the database, if a normal cold backup immediately. If the damaged log group members that there is only one log, to mount the database, convert it to noarchivelog model, the implementation of alter database add logfile member 'xxx' to group 'x' to the relevant group to add a member, then the implementation of alter database drop logfile member 'bad_file' damage to the log file will be deleted, as a result of changes in the structure of the database needs a backup control file, and then back to the database archivelog mode, do a cold backup.

If the loss is the current log file is activated, the database does not mirror the current log and all the members of the group becomes unavailable. First of all, the database shutdown abort, from the most recent full backup to recover all the data files, restart the database to the mount state. If the original physical location of the log file is not available, use alter database rename file 'xxx' to 'xxx' to change the file storage location. Then, use the command recover database until cancel to restore the database until the prompt use of the last log after archiving, enter cancel. After the alter database open resetlogs used to open the database, if it is a cold backup immediately. Attention! All included in the damage to the information in the redo log will be lost, that is to say the database has been submitted before the collapse of the data may be lost. This is the application of certain requirements will be very high losses and should therefore be as much as possible so that each log group members with multiple logs and placed in a different drive to prevent the occurrence of a media failure.

Failure data file is also divided into a number of situations, 1, lost in the SYSTEM tablespace contains the data files; 2, there is no loss of the non-SYSTEM rollback segment data file; 3, have lost the non-SYSTEM rollback segment data file .

If the damage is the system table space data file. The only way is from the last backup to recover damaged data files (if the original location is not available to use alter database rename command to change the location of a new document), and then mount the database under the state recover database / datafile to the database back to open the database. Note: When SYSTEM tablespace or data file offline, the database can not be opened, so the state must mount all of the restoration work is completed.

When the missing data files do not belong to system tablespace rollback segment and is not included, it is a choice of two states in the database under the --- in the database to restore the status of open or mount the database state. Eager to access the database if the user is not damaged part of the data or damaged data files on the recovery takes a long time, can damage the data file to make offline, open the database to user access, to restore damaged data files Finally, its online. Steps are as follows: first mount in the database, it will be related to the data file or table space for off-line alter database datafile xxx offline, and then the database open, so the database can not be damaged for some users, and then to recover datafile / tablespace, after the completion of use alter database datafile / tablespace 'xxx' online you can put it back in line to be visited. Of course, users can also choose to mount the database state, and recover database / datafile will return to work after all, with all data files will be open for user access.

If you have lost your data file is the last one, that contains the non-system rollback segment tablespace data files. Can also choose to open in the state database or in the mount state of the restoration. But the situation with a different rollback segment when the data files contain the damage, if the database offline so that it first opened, then all the database did not submit before the collapse of the Service relate to the table will not be able to visit, that is to say in rollback segment before the restoration of the objects involved are not allowed to visit. And when all of the data file that contains rollback segment in the offline state, the database can not be any DML operation, so in the open state of the database contains the rollback segment to restore the data file, you can create a few temporary rollback segment for the use of data create rollback segment temp1 tablespace system; alter rollback segment temp1 online;, when the data files to restore them and then delete the alter rollback segment temp1 offline; drop rollback segment temp1;. Note: When using this method to restore the data files online, all of the original offline rollback segment will be in a state, we must hand the use of alter rollback segment RBSxx online; so that they line the status of the restoration in order to be the normal use of the database. If you mount the database to restore a state of completion of all, you do not need these steps.
If you have lost your data files, users found that there was no failure of the backup data files and lost data files since the establishment of the first has not been used after the options opened resetlogs database. This means that the user's control file is damaged before the establishment of the data files created archive log data files include all the damage to redo records. Users there is a recovery method, the user can first damaged data files or offline tablespace alter database datafile / tablespace xxx offline, after the implementation of alter database create datafile 'new / xxx.dbf' as' old / xxx.dbf ' , the database will be stored in the control file based on the information in the reconstruction of an empty data file, and then the implementation recover tablespace / datafile redo record of all data files used in its fully restored to the current state, after will be able to put their the restoration of line.

If the loss is the last of a document --- archive files or archives where the physical location is not available, first shutdown the database and immediately make a cold backup. Ini file and then modify the path to the purpose of archiving log files, restart the database. Just after the disaster will be from the latest backup to restore the relevant documents, databases, when there would be no need for recover lost before the backup archive file. Oracle 8 in the new version after log_archive_duplex_dest provided and log_archive_dest_1 ... 5 and other parameters were allowed to keep the documents in the archives to a different location, even to the remote server in order to ensure the reliability of the documents in the archives.

Some say the resumption of the final database Notes:

1. This article discusses the premise of all the circumstances of the default is to run in the archive database (ARCHIVELOG) mode, and only to the common and the most basic recovery methods. Provided by the use of Oracle Recovery Manager RMAN can also accomplish the above tasks, if more complex operating environment do recommend the use of RMAN backup and recovery.

2. Once the database of a disaster, before the resumption of the best in the cold for a full backup, in order to generate errors during the recovery can also be used for remedies. A significant portion of data loss due to incorrect operation caused by the resumption.

3. When the database after the completion of the restoration, in particular the use of open database resetlogs option, it is necessary to immediately shut down the database to conduct a complete cold backup. Because, in order to prevent giving up the redo log to be restored again next time use, resetlogs option will re-create the redo log file and cleared the count, which will be done before all the backup will become unavailable (for general under).

4. To pay special attention to the database when fully recovered from the failure of the backup point in time to recover before the damaged document, must not make a backup of the redo log files covering the current redo log file, or can only incomplete and it must be part of the restoration of lost data.

Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19

2009/04

Block db_block_size the reasons for the operating system?

Related Posts

oracle EXP / IMP command explain

Oracle imp/exp

oracle的imp和exp的一些用法- -

Related Posts

index_stats

Related Posts

Summary of the partition table

Related Posts

Oracle backup functions

Related Posts

Summary of the partition table (continued)

Related Posts

Oracle is willing to buy 7.4 billion US dollars from Sun

Related Posts

oracle technology

Related Posts

Partition table summary

Related Posts

Create table PCTUSED parameters PCTFREE and what does this mean?

Create table PCTUSED parameters PCTFREE and what does this mean?

Related Posts

Detailed DBMS_STATS.GATHER_TABLE_STATS

Related Posts

Modify the ordinary user's default tablespace specified

Related Posts

10g tips

Related Posts

Modify the specified user default tablespace

Related Posts

apex3.2 steps to install oracle10

Related Posts

Oracle installation difficulties

Related Posts

Oracle Character Set

Related Posts

ORACLE very difficult to learn

Related Posts

ora-01756

Related Posts

ORACLE Data Recovery

Related Posts