2009/06

Disk Management Part asm

kods.net — Tue, 30 Jun 2009 22:55:03+0000

asm add linux on the disk into the disk method
[root @ localhost init.d] # fdisk / dev / sdd

rn
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1): 130
Last cylinder or + size or + sizeM or + sizeK (130-261, default 261):
Using default value 261

rn
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or + size or + sizeM or + sizeK (1-129, default 129):
Using default value 129

rn
Command (m for help): w
The partition table has been altered!

rn
Calling ioctl () to re-read partition table.
Syncing disks.
[root @ localhost init.d] # fdisk-l
skiping ................
Disk / dev / sdd: 2147 MB, 2147483648 bytes
255 heads, 63 sectors / track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

rn
Device Boot Start End Blocks Id System
/ dev/sdd1 130 261 1060 290 83 Linux
/ dev/sdd2 1 129 1036161 83 Linux

rn
Partition table entries are not in disk order
[root @ localhost init.d] #
Create asm disk

rn
[root @ localhost init.d] #. / oracleasm createdisk VOL5 / dev/sdd1
Marking disk "VOL5" as an ASM disk: [OK]
[root @ localhost init.d] #. / oracleasm createdisk VOL6 / dev/sdd2
Marking disk "VOL6" as an ASM disk: [OK]

rn
Create a disk group
[root @ localhost init.d] # su - oracle
[oracle @ localhost ~] $ export ORACLE_SID = + ASM
[oracle @ localhost ~] $ sqlplus "/ as sysdba"

rn
SQL * Plus: Release 10.2.0.1.0 - Production on Tue Jun 30 10:31:42 2009

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

rn

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

rn
SQL> create diskgroup disk_group_3 normal redundancy
2 failgroup VOL6 disk 'ORCL: VOL6' name VOL6
3 failgroup VOL5 disk 'ORCL: VOL5' name vol5;

rn
Diskgroup created.

rn
SQL> drop diskgroup disk_group_3 including contents; to delete the disk group

rn
Diskgroup dropped.

rn
SQL>

rn
- Add to asm disk into the disk group already exists, the

rn
SQL> l
1 * alter diskgroup disk_group_1 add disk 'ORCL: VOL5'
SQL> /

rn
Diskgroup altered.

rn
Add to delete the disk

rn
SQL> l
1 select group_number, disk_number, name, label, path from v $ asm_disk
2 *
SQL> /

rn
GROUP_NUMBER DISK_NUMBER NAME LABEL PATH
------------ ----------- ----- ----- ----------
0 0 VOL6 ORCL: VOL6
1 0 VOL1 VOL1 ORCL: VOL1
1 1 VOL2 VOL2 ORCL: VOL2
2 0 VOL3 VOL3 ORCL: VOL3
2 1 VOL4 VOL4 ORCL: VOL4
1 2 VOL5 VOL5 ORCL: VOL5
SQL> alter diskgroup disk_group_1 drop disk VOL5;

rn
Diskgroup altered.

rn
select group_number, disk_number, bytes_written/1024/1024 from v $ asm_disk_stat
1 0 79.74072265625
1 1 77.85791015625
2 0 41.99072265625
2 1 41.99072265625
1 2 3.8515625
Disk2 in vol5 which can be reduced to 4MB;
Can also write a resize, if small, will be the following error

rn
SQL> alter diskgroup disk_group_1 resize disk VOL5 size 3M;
alter diskgroup disk_group_1 resize disk VOL5 size 3M
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15037: disk 'ORCL: VOL5' is smaller than mimimum of 4 MBs

rn

SQL> c / 3 / 4
1 * alter diskgroup disk_group_1 resize disk VOL5 size 4M
SQL> /

rn
Diskgroup altered.

rn
The second group are 41MB, so the first set to 50MB;

rn
SQL> alter diskgroup disk_group_2 resize all size 50M;

rn
Diskgroup altered.

rn

rn

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

Change apps, the statements appear to run error, pl / sql to the normal landing

kods.net — Tue, 30 Jun 2009 21:04:42+0000

Today at the Forum to help users to solve a problem of a complicated, more or less as follows:
To change the apps, the statements appear to run error, pl / sql to the normal landing

erp version 11.5.10.2
1, to change the apps password, pl / sql can use the new password, but an error running statements;
2, change the method of FNDCPASS tools, change the file wdbsvr.app, CGIcmd.dat;
3, error as follows:
REP-0501: Unable to connect to the specified database
ORA-01017: invalid username / password; logon denied
4, I would like to ask is not where there is no change to it? Expert advice, please, thank you!

Here are some replies to other users, but never answered the question on Recommendation 1: Kin intended to run after you restart autoconfig.sh
Recommendation 2: The running time is FNDCPASS concurrent manager need to stop

See the problem first impression is that wonder where the dat file, based on experience, which generally does not require altering the document, but he had clearly told him here revised!
So he replies as follows:
Paste your files to see dat

Reply to post # 4 remen

dat file is as follows:
[applprod @ dba server] $ more CGIcmd.dat
#
# $ Header: CGIcmd.dat 115.6 2003/07/10 08:41:16 skghosh ship $
#
# ################################################# ##############
#
# This file is automatically generated by AutoConfig. It will be read and
# Overwritten. If you were instructed to edit this file, or if you are not
# Able to use the settings created by AutoConfig, refer to Metalink document
# 165195.1 for assistance.
#
# ################################################# ##############

#
PROD_APPS: server = REP60_PROD userid = APPS/ps07r618 @ PROD destype = cache desformat = h
tml% *

PROD_APPS_NO_DESFORMAT: server = REP60_PROD userid = APPS/ps07r618 @ PROD destype = cach
e% *

Thanks!

CGIcmd.dat you modify it? CGIcmd.dat it useful to you?
Do not have a backup? So, go back and try to restore!

There are not FNDCPASS.log error?
Dat file which is only in Oracle Reports integrated with Portal when used to amend, it is estimated that this step is superfluous, you had!

QUOTE:

With the original 2009-6-30 14:08 by remen published in screen.width * 0.7) (this.resized = true; this.width = screen.width * 0.7; this.alt = 'Click here to open new window \ nCTRL + Mouse wheel to zoom in / out';}"" if (this.width> screen.width * 0.7) (this.resized = true; this.width = screen.width * 0.7; this.style.cursor = 'hand '; this.alt =' Click here to open new window \ nCTRL + Mouse wheel to zoom in / out ';) "onmousewheel =" return imgzoom (this); "alt =" "border =" 0 ">
There are not FNDCPASS.log error?

normal log file, dat file is in the process of change over!

QUOTE:

With the original 2009-6-30 14:13 by remen published in screen.width * 0.7) (this.resized = true; this.width = screen.width * 0.7; this.alt = 'Click here to open new window \ nCTRL + Mouse wheel to zoom in / out';}"" if (this.width> screen.width * 0.7) (this.resized = true; this.width = screen.width * 0.7; this.style.cursor = 'hand '; this.alt =' Click here to open new window \ nCTRL + Mouse wheel to zoom in / out ';) "onmousewheel =" return imgzoom (this); "alt =" "border =" 0 ">
Dat file which is only in Oracle Reports integrated with Portal when used to amend, it is estimated that this step is superfluous, you had!

Positive Solutions, knot front, very grateful, it is proposed that a version should be remem book on oracle ERP-specific optimization of the book!

Oh, like to solve the problem! As for the book, the lack of personal feeling too much, we all learn from each other to!

Thus, the issue is resolved

The original with the following address:
http://www.itpub.net/viewthread.php?tid=1183856&extra=&page=2

Oracle's role in the password file and notes 2009-04-08 23:33:46
Oracle structure 2009-04-07 10:22:13
oracle basic concepts and terminology 2009-04-06 22:17:39
oracle pl / sql examples of practice 2009-04-06 04:35:08
An example of the use of TKPROF 2009-02-26 01:38:19

Oracle Certification skillfully taking outside designated records associated with the alternative in / not in query

kods.net — Tue, 30 Jun 2009 17:52:58+0000

Oracle Certification skillfully taking outside designated records associated with the alternative in / not in query

Table 1 to obtain the first 6 to Article 10 of the value of records

1.1 The first method, the use of minus statement

Ddl statement assumptions are as follows:

CREATE TABLE T (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)

The first method is then removed before the 5, and then removed before 10, and then using the method of computing a collection of the former 10 minus 5 on the OK before the, SQL statement is as follows

The following is a quoted fragment:
SELECT * FROM T WHERE ROWNUM <= 10
MINUS
SELECT * FROM T WHERE ROWNUM <= 5;

1.2 In addition a method of using subquery

This kind of subquery approach is relatively complex, but a collection of performance than just subtract better. This method first subquery to be 10 before the data has also been made way before the 10 data rownum, and then once again just made inquiries when rownum query the data of more than 5. SQL statement is as follows

The following is a quoted fragment:
SELECT ID, VALUE FROM
(SELECT ID, VALUE, ROWNUM R FROM T WHERE R <= 10)
WHERE
R> 5;

Through the above statement, it has been 6 to Article 10 of the data.

2 the use of an alternative not in outer join statement

in statements are not in the statement of the efficiency is very poor, because the database in the face of these two statements, when data is to a more than a right, if in or not in the amount of data on both sides in the last million of the time, to compare the number of the million is very likely a simple sql statement to be performed for more than half an hour. This efficiency can not be sure that customers are acceptable. Then we can consider two alternative methods, the first statement is based on the exist and not exist statement, which we should be more familiar with. Another is outside the associated statement Using this method may not be familiar with everyone, I came to talk about a little. Hypothetical data table DDL statements to build tables for the

CREATE TABLE T1 (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)

And in the table or not in the building table for DDL statements

CREATE TABLE T2 (VALUE INT)

Oracle is used by Chinese and foreign association (+) symbol outside the association, that is to say a logo (+) symbols of value can not find the time corresponding to NULL. The following is a statement of when to replace in the SQL statement

The following is a quoted fragment:
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE (+)
AND T2.VALUE IS NOT NULL;

And similar. Alternative not in the SQL statement of the statement when compared with

The following is a quoted fragment:
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE (+)
AND T2.VALUE IS NULL;

We can test, in the amount of data and more time than the use of external linkages in or not in the implementation of the efficiency of many, many higher.

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

Database training a sense of

kods.net — Tue, 30 Jun 2009 16:40:02+0000

rn
Recently had the honor of the Ministry of Information Industry took part in a training organized by oracle, the teacher lectures, Yuan Baohua, to 30 years old, more than ten years in database industry, although much older, but the teaching can be a vivid, lively way. After listening to the entire five-day course, I did not even play a doze, a record since the record since I go to school, so, regardless of the final database of knowledge to learn how many aspects of teaching that he is powerful enough.

rn
Said the lively lecture because he likes to play when for instance, using the database permissions for the chapter, he would go to the bank by a person, such as money, etc. so for example, because the process is similar to one it is easy to understand than simply teaching more lively.

rn
Speaking of lively because he is very humorous style of speaking, for example, to teach rman would like to introduce before the merits of rman, he would jokingly said, "I would like to introduce one of the advantages of the rman, you want to listen to, I said next, does not want to listen Even if "; introduction he asked us," You felt you rman, ah want to continue to listen to. "We see no response, he went on to say that" such a good thing, you are not tempted?? that One of the advantages I have to say, look at your heart at every turn "; If so, he introduced the rman of 5, 6 advantages. In fact, the content of a professor teaching with almost ordinary, but because of his lecture on the gimmicks and more people can always find new ideas. This lecture can be attractive, it is not surprising.

rn
In addition, I found a way to improve teaching effectiveness, that is, story-telling. Comrade Yuan special love story (do not know this story is true or false), his work will take place in the middle of things, for example, who encountered谁谁what kind of things, and finally found the reasons for that, and so on and so on. These stories have taken place in the well-known inside the company, but behind each story there is a specific database knowledge, and so he finished the story, well, not only to remember the story, the story behind it to remember the knowledge. This seems to simply point a professor of knowledge more effective. From, I also found a reason, a regular person who engaged in technology, it should be from time to time onto the platform can not only sort out their own knowledge into the system, it can happen to them to educate other people's things.

rn
Five-day course is over, even if the databases do not have much technical progress, but at least be considered in regard to lectures to learn a lot.袁老师wish our visit to Hangzhou.

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

Oracle10g - The regular expression

kods.net — Tue, 30 Jun 2009 16:31:54+0000

On the basis of regular expressions that can refer to "layman's language of regular expressions", Oracle10g achieved with the regular expression is basically the same, the details can refer to "10g in regular expressions." Oracle10g provides four regular expressions can use the function:

regexp_like
regexp_substr
regexp_instr
regexp_replace

Sys.standard in the package can be seen from these functions (see appendix). Here the main function of these four to do some small tests.

1. Regexp_like

REGEXP_LIKE (srcstr, pattern, modifier)

__srcstr: Search String

__pattern: matching model

__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)

__return type: boolean

eg:

SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8 }$');

1

----------

SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8) $', 'c');

1

----------

SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8) $', 'i');

1

----------

1

2. Regexp_substr

REGEXP_SUBSTR (srcstr, pattern, position, occurrence, modifier)

__srcstr: Search String

__pattern: matching model

__position: Search srcstr starting position (default is 1)

__occurrence: search the first matching pattern appears several times in the string (defaults to 1)

__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)

eg:

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9] +') from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

30637

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9] +', 1) from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

30637

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9] +', 35) from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

1009927

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9] +', 1, 2) from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

1009927

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[a-z_0-9] +', 35) from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

showart_1009927

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35) from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

howart_1009927

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35, 1, 'c') from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

howart_1009927

SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35, 1, 'i') from dual;

REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI

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

Showart_1009927

3. Regexp_instr

REGEXP_INSTR (srcstr, pattern, position, occurrence, returnparam, modifier)

__srcstr: Search String

__pattern: matching model

__position: Search srcstr starting position (default is 1)

__occurrence: search the first matching pattern appears several times in the string (defaults to 1)

__returnparam: return to the substring position in srcstr (0 that the first position, a position that the end of +1, the default is 0.)

__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +') from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

12

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 21) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

52

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

52

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 1, 0) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

12

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 1, 1) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

20

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2, 0) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

52

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2, 1) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

60

SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

8

SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0) from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

9

SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0, 'i') from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

8

SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0, 'c') from dual;

REGEXP_INSTR ( 'HTTP: / / SHOP35741

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

9

4. Regexp_replace

function REGEXP_REPLACE (srcstr, pattern, replacestr, position, occurrence, modifier)

__srcstr: Search String

__pattern: matching model

__replacestr: new substring (default is NULL)

__position: srcstr retrieval start position (default is 1)

__occurrence: there several times to replace the first string match pattern (default is 0)

__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT') from dual;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://SYCT.taobao.com/shop/xshop/wui_page-YCT.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 20) from dual;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 1, 2) from dual ;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 1, 1) from dual ;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://SYCT.taobao.com/shop/xshop/wui_page-16115021.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 1, 1, 'i ') from dual;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://YCT.taobao.com/shop/xshop/wui_page-16115021.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * ([0-9 ])+',' \ 1 ', 1, 1 , 'i') from dual;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://5.taobao.com/shop/xshop/wui_page-16115021.htm

SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * ([0-9 ]+)',' \ 1 ', 1, 1 , 'i') from dual;

REGEXP_REPLACE ( 'HTTP: / / SHOP357

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

http://35741645.taobao.com/shop/xshop/wui_page-16115021.htm

5. Appendix

- REGEXP_LIKE --

function REGEXP_LIKE (srcstr VARCHAR2 CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

modifier VARCHAR2 DEFAULT NULL)

return BOOLEAN;

pragma FIPSFLAG ( 'REGEXP_LIKE', 1452);

function REGEXP_LIKE (srcstr CLOB CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

modifier VARCHAR2 DEFAULT NULL)

return BOOLEAN;

pragma FIPSFLAG ( 'REGEXP_LIKE', 1452);

- REGEXP_INSTR --

function REGEXP_INSTR (srcstr VARCHAR2 CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

position PLS_INTEGER: = 1,

occurrence PLS_INTEGER: = 1,

returnparam PLS_INTEGER: = 0,

modifier VARCHAR2 DEFAULT NULL)

return PLS_INTEGER;

pragma FIPSFLAG ( 'REGEXP_INSTR', 1452);

function REGEXP_INSTR (srcstr CLOB CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

position INTEGER: = 1,

occurrence INTEGER: = 1,

returnparam PLS_INTEGER: = 0,

modifier VARCHAR2 DEFAULT NULL)

return INTEGER;

pragma FIPSFLAG ( 'REGEXP_INSTR', 1452);

- REGEXP_SUBSTR --

function REGEXP_SUBSTR (srcstr VARCHAR2 CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

position PLS_INTEGER: = 1,

occurrence PLS_INTEGER: = 1,

modifier VARCHAR2 DEFAULT NULL)

return VARCHAR2 CHARACTER SET srcstr% CHARSET;

pragma FIPSFLAG ( 'REGEXP_SUBSTR', 1452);

function REGEXP_SUBSTR (srcstr CLOB CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

position INTEGER: = 1,

occurrence INTEGER: = 1,

modifier VARCHAR2 DEFAULT NULL)

return CLOB CHARACTER SET srcstr% CHARSET;

pragma FIPSFLAG ( 'REGEXP_SUBSTR', 1452);

- REGEXP_REPLACE --

function REGEXP_REPLACE (srcstr VARCHAR2 CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

replacestr VARCHAR2 CHARACTER SET srcstr% CHARSET

DEFAULT NULL,

position PLS_INTEGER: = 1,

occurrence PLS_INTEGER: = 0,

modifier VARCHAR2 DEFAULT NULL)

return VARCHAR2 CHARACTER SET srcstr% CHARSET;

pragma FIPSFLAG ( 'REGEXP_REPLACE', 1452);

function REGEXP_REPLACE (srcstr CLOB CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

replacestr CLOB CHARACTER SET srcstr% CHARSET

DEFAULT NULL,

position INTEGER: = 1,

occurrence INTEGER: = 0,

modifier VARCHAR2 DEFAULT NULL)

return CLOB CHARACTER SET srcstr% CHARSET;

pragma FIPSFLAG ( 'REGEXP_REPLACE', 1452);

function REGEXP_REPLACE (srcstr CLOB CHARACTER SET ANY_CS,

pattern VARCHAR2 CHARACTER SET srcstr% CHARSET,

replacestr VARCHAR2 CHARACTER SET srcstr% CHARSET

DEFAULT NULL,

position INTEGER: = 1,

occurrence INTEGER: = 0,

modifier VARCHAR2 DEFAULT NULL)

return CLOB CHARACTER SET srcstr% CHARSET;

pragma FIPSFLAG ( 'REGEXP_REPLACE', 1452);

- End REGEXP Support --

Oracle Sysdba certification authority management 2009-04-06 23:37:06
Oracle Database exp imp Import Export by user examples 2009-04-06 18:58:52
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Merge the use of (1) 2009-03-31 09:48:55
Dbms_obfuscation_toolkit use of Oracle's encryption and decryption of data (to) 2009-03-13 18:19:14

How do I enable AutoTrace features of sqlplus

kods.net — Tue, 30 Jun 2009 13:46:27+0000

Through the following methods can Autotrace granted permission to Everyone,

Autotrace If you need to restrict the authority of the public can be re-authorized user on a specific authorization.

D: / oracle/ora92> sqlplus / nolog

SQL * Plus: Release 9.2.0.1.0 - Production on Tuesday, June 3 15:16:03 2003

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

SQL> connect sys as sysdba
Please enter password:
Is connected.
SQL> @? / Rdbms / admin / utlxplan

Table has been created.

SQL> create public synonym plan_table for plan_table;

Synonyms have been created.

SQL> grant all on plan_table to public;

Successful authorization.

SQL> @? / Sqlplus / admin / plustrce
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: the role of 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role has been created
SQL>
SQL> grant select on v_ $ sesstat to plustrace;

Successful authorization.

SQL> grant select on v_ $ statname to plustrace;

Successful authorization.

SQL> grant select on v_ $ session to plustrace;

Successful authorization.

SQL> grant plustrace to dba with admin option;

Successful authorization.

SQL>
SQL> set echo off

DBA user plustrace the first to be awarded the role, and then we can plustrace given public

So that all users will have the authority plustrace role.

SQL> grant plustrace to public;

Successful authorization.

Then we can use the functions of the AutoTrace.

SQL> connect eqsp / eqsp
Is connected.
SQL> set autotrace on
SQL> set timing on
SQL>

On several commonly used Autotrace options:

SET AUTOTRACE OFF ---------------- not AUTOTRACE report generation, which is the default mode
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE optimizer only shows the report of the implementation of the path
SET AUTOTRACE ON STATISTICS - displays only the implementation of statistical information
SET AUTOTRACE ON ----------------- includes the implementation of plans and statistical information
SET AUTOTRACE TRACEONLY ------ with set autotrace on, but it does not show the query output

SQL> set autotrace traceonly
SQL> select table_name from user_tables;

98 firms have chosen.

Has spent time: 00: 00: 00.04

Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS (OUTER)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ $'
8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 6 TABLE ACCESS (CLUSTER) OF 'TAB $'
10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ #' (NON-UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ $'
12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
14 3 TABLE ACCESS (CLUSTER) OF 'USER $'
15 14 INDEX (UNIQUE SCAN) OF 'I_USER #' (NON-UNIQUE)
16 2 TABLE ACCESS (CLUSTER) OF 'SEG $'
17 16 INDEX (UNIQUE SCAN) OF 'I_FILE # _BLOCK #' (NON-UNIQUE)
18 1 TABLE ACCESS (CLUSTER) OF 'TS $'
19 18 INDEX (UNIQUE SCAN) OF 'I_TS #' (NON-UNIQUE)

Statistics
-------------------------------------------------- --------
0 recursive calls
0 db block gets
1389 consistent gets
0 physical reads
0 redo size
2528 bytes sent via SQL * Net to client
569 bytes received via SQL * Net from client
8 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts (disk)
98 rows processed

SQL>

== THE END

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

SQL interview a subject of transnational corporations

kods.net — Tue, 30 Jun 2009 12:36:41+0000

inner join
left outer join
right outer join
full outer join
A difference between the four
is a cross join is a Cartesian product of the number of rows in table another table multiplied by the number of rows
a left join b: to return to a full line - "b in the line to meet and b in the line does not meet (with null instead of)
a right join b: ibid change what is ab
to return to full join two lines in the table left join + right join
inner join table to connect only to return two out of the match

a table
1, 'x'
2, 'y'
3, 'z'

b Table
1, 'a'
4, 'b'
5, 'c'

connect with inner join:
select a.id, a.name, b.id, b.name from a, b
where a.id = b.id

1, 'x', 1, 'a'

left outer join left connected
select a.id, a.name, b.id, b.name from a
left outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null

right outer join right connections
select a.id, a.name, b.id, b.name from a
right outer join b on a.id = b.id
1, 'x', 1, 'a'
null, null, 4, 'b'
null, null, 5, 'c'

outer join full outer join
select a.id, a.name, b.id, b.name from a
full outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null
null, null, 4, 'b'
null, null, 5, 'c'

ORACLE PL / SQL Management Command 1 2009-12-28 11:10:45
SQL Connection to connect the left and right connections, fully connected, internal connections, cross-connect, self-connection 2009-10-09 22:30:29
MERGE 2009-07-09 01:04:58
Joins 2009-07-06 02:12:24
Block db_block_size the reasons for the operating system? 2009-04-22 21:35:02

Son of discrete pieces of work resulted in a single stock is negative

kods.net — Tue, 30 Jun 2009 08:52:14+0000

Reasons:

BOM in the definition of recent users, we had pieces of the parent / child ratio of 1:1 parts consumption may be due to user error, the results of the parent items / sub-items for the consumption of more than 1:1000, single work in the completion of discrete sub-pieces lead to a negative value.

Measures: Move Transaction Form the job done for the return interface operation, which used to solve the problem of negative inventory. BOM table and then modify the new discrete work orders, re-completion of work order

Analysis: why in the analysis until the parameters of the Organization "Allow negative Stock" has not been checked, why such matters can lead to negative stock it?

Check information provided by: INV: Override Neg for Backflush for Yes

INV: Override Neg for Backflush

Indicates whether backflush transactions can drive the inventory negative even if the inventory organization parameter Allow Negative Balances is unchecked. Inventory predefines a value of Yes for this profile option upon installation. A value of No does not allow backflush transactions to drive on-hand inventory negative even when the inventory
organization parameter Allow Negative Balances is unchecked.

This profile is updateable at all levels.

Database tuning problem (parameter binding, Histogram Analysis) 2009-04-03 23:53:10
setting to discuss filing oracle10g 2009-04-02 05:19:08
LISTENER and EZCONNECT 2009-04-01 21:42:20
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
An example of the use of TKPROF 2009-02-26 01:38:19

Profiles Used in Form Personalization

kods.net — Tue, 30 Jun 2009 07:14:03+0000

The profile controlling this follows

1 Utilities: Diagnostics = Yes / No
This profile option controls whether users can use the examine utility.

2 Hide Diagnostics Menu entry = Yes / No
This profile option controls the availability of Diagnostics utility.

oracle user profile is used 2009-08-05 23:45:04
Database users to create and modify user information 2009-06-23 04:52:17

Oracle memory structure of-PGA

kods.net — Tue, 30 Jun 2009 05:49:14+0000

I. Overview

SGA, PGA, UGA is Oracle's memory management.

SGA (System Global Area), that is, the overall situation of the district system, Oracle the most important area of memory.

PGA (Process Global Area), that is, the overall district program, a process-specific memory area.

UGA (User Global Area), and the user global areas, with a particular session is associated.

Dedicated server connection mode, UGA allocated in the PGA.

Shared server connection mode, UGA in the SGA in the distribution of Large Pool.

If the dedicated server connection mode, PGA contain UGA, is used to sort other regions, the combined hash and bitmap.

Simply put, PGA = UGA + sort + area + hash area bitmap merge area.

Second, PGA management mode.

PGA of two management models:

1) manually PGA memory management, user-specified sort and hash area of memory used by the district, each connected to use the same memory.

2) automatic PGA memory management, Oracle can be used to tell the total amount of PGA by Oraclce decision in accordance with the specific distribution of the system load.

l 9iR1 when PGA defaults to manual memory management, 9iR2 after the default for the automatic management of PGA memory.

l PGA memory can be expanded and recovery dynamic.

PGA memory management mode control by WORKAREA_SIZE_POLICY.

1) is set to MANUAL, manual memory management is enabled.

2) is set to AUTO, and 0:00 for PGA_AGGREGATE_TARGET not enable automatic memory management.

Third, manual memory management PGA

There are three most influential parameters on the PGA.

SORT_AREA_SIZE: sort of information the total memory used

SORT_AREA_RETAINED_SIZE: Sort in memory after the sort of information to preserve the total amount of memory.

HASH_AREA_SIZE: store hash list the amount of memory used.

The following description of these three parameters:

1) SORT_AREA_SIZE:

1) PGA_AGGREGATE_TARGET is a target. When connecting a few of the actual allocation of PGA memory than it is to small. A few more times to connect the actual allocation of PGA memory than it is to big, but Oracle will work hard to keep the total PGA keep PGA_AGGREGATE_TARGET value.

For example, PGA_AGGREGATE_TARGET set to 300MB. 5 users connected, each user may be allocated 10MB of PGA memory, a total of 50MB of PGA memory allocation. 300 user connections per user at the PGA may be allocation of 1.3MB of memory, a total of 390MB of PGA memory allocation. When a user connects a long time, Oracle will reduce the per-user usage of PGA memory.

2) a serial query (non-parallel query) may include a number of sort / hash operation, each sort / hash operation using up to 5% of the PGA memory.

3) a parallel query can be used up to 30% of the PGA memory, no matter how many parallel processes.

5, manual memory management and automatic PGA memory management PGA

Automatic PGA memory management PGA compared with the manual memory management there are many advantages

1, when a user is connected is low

a) manually PGA memory management can be used regardless of the number of memory are allocated in accordance with the default value. For example, the current free memory to 300MB, connections need to be sorted 10MB of memory, and we set the sort area size 5MB, lead free although there is sufficient memory is not allocated to the current connection, resulting in inefficient implementation.

b) automatic PGA memory management will be based on the current distribution of free memory. When free memory to 300MB, the current user needs 10MB memory sort, Oracle will be allocated 10MB of memory to the current user.

2, when a user is connected for a long time

a) manually PGA memory management will be fully in accordance with the default memory allocation. If the total amount of physical memory 1G, sort area as 5MB, when there are 300 users connected, Oracle will be the allocation of 1.5G of memory, which has more than our actual physical memory!

b) automatic PGA memory management will be conducted in accordance with the distribution of the current connection. If the total amount of physical memory 1G, PGA_AGGREGATE_TARGET to 300MB, when the number of users rose to 300 from 10, each user connects the memory will gradually meet the needs of 10MB reduced to 1.3MB, while the final total will be more than PGA_AGGREGATE_TARGET, PGA compared with manual memory management a lot better.

When using automatic PGA memory management? PGA when using manual memory management?

During the day when the normal operation of the system suitable for the use of automatic PGA memory management, so that Oracle automatically based on the current load management, distribution of PGA memory.

The night the number of users less time to maintain the current session can be set manually using PGA memory management, so that the maintenance of the current operation of the memory access to as much as possible to speed up the execution.

Such as: server usually runs on automatic PGA memory management mode, there is a task of the night, to a large table to update sort connected, you can operate in the interim to change the session in the PGA memory management manually, and then the distribution of large SORT_AREA_SIZE and HASH_AREA_SIZE ( 50% or even 80% of memory, it is necessary to ensure that no other users), this will greatly accelerate the speed of the system without affecting the peak period during the day and the impact on the system.

6, the operation command

System-level changes:

ALTER SYSTEM SET WORKAREA_SIZE_POLICY = (AUTO | MANAUL);

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 100000000;

ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE;

ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE;

Conversation-level changes

ALTER SESSION SET WORKAREA_SIZE_POLICY = (AUTO | MANAUL);

ALTER SESSION SET SORT_AREA_SIZE = 65536;

ALTER SESSION SET HASH_AREA_SIZE = 65536;

7, to apply their knowledge

1, sorting areas:

pga_aggregate_target for 100MB, a single query can be used to 5% when the order is the time required for 5MB

SQL> create table sorttable as select * from all_objects;

Table has been created.

SQL> insert into sorttable (select * from sorttable);

49735 line has been created.

SQL> insert into sorttable (select * from sorttable);

99470 line has been created.

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select * from sorttable order by object_id;

Have chosen to 198,940 lines.

Has spent time: 00: 00: 50.49

Session class to sort the district to amend the time needed for the 30mb

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

Conversation has changed.

Has spent time: 00: 00: 00.02

SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> select * from sorttable order by object_id;

Have chosen to 198,940 lines.

Has spent time: 00: 00: 10.76

Time to see reduced from 50.49 seconds to 10.31 seconds, the speed upgrade is obvious.

2, hash District:

pga_aggregate_target for 100MB, a single query can be used to 5% is 5MB time when the table join

SQL> select / * + use_hash (tb1 tb2) * / * from sorttable tb1, sorttable tb2 where tb1.object_id = tb2.object_id;

Have chosen to 49,735 lines.

Has spent time: 00: 00: 40.50

Session class modify hash 30mb Time zone

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> select / * + use_hash (tb1 tb2) * / * from sorttable tb1, sorttable tb2 where tb1.object_id = tb2.object_id;

Have chosen to 49,735 lines.

Has spent time: 00: 00: 04.47

Time from 40.50 seconds to 4.47 seconds, the same effect is obvious.

Note: The above experiments are to ensure the implementation of full-table scan read the relevant table in the buffer zone to avoid data read into the cache did not cause the error.

Conclusion: 9iR2 version in the future, PGA no longer a DBA problem, Oracle will help us do a good job in the distribution of PGA. However, this does not mean that the DBA does not need a deeper understanding of the PGA, PGA master and will work in accordance with the proper use of a menace.

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

How to design a good database

kods.net — Tue, 30 Jun 2009 04:14:26+0000

Database design (Database Design) means that for a given application environment, the optimal structure of the database schema, the establishment of databases and applications, to enable them to effectively store data to meet various application needs of users (information requirements and processing requirements).

The field in the database, often the use of various types of database systems are collectively referred to as database applications.

First, databases and information systems (1) database is the core of information systems and infrastructure, information systems to large amounts of data in the model according to a certain organization, providing storage, maintenance, data retrieval, so that information systems can be convenient, timely, accurately from the database to obtain the necessary information.
(2) Information System database is whether the various parts together closely, as well as how to combine the key.
(3) database design and development of information systems is an important part of the building.
(4) database design should have the skills and knowledge:
Basic knowledge of databases and database design techniques of the basic knowledge of computer science and programming methods and techniques of software engineering principles and methods of application of knowledge in the field of

Second, the characteristics of database design database construction is the hardware, software and the combination of stem pieces of two-thirds of technology, seven management, basic data十二分technology and management interface called the "dry matter"
Database design and application of system design should be a combination of structural (data) design: the design of the database or the database structure the framework of behavior (processing) design: the design of applications, transaction processing, such as the separation of the structure and behavior of software engineering design of the traditional neglect of data on the application of semantic analysis and abstraction, as long as possible the design of data structures as much as possible to defer the decision-making early commitment to data model database design and modeling studies, ignoring the acts of the design of the figure:

Third, the database design manual test on the quality of design together with the design and level of experience is directly related to the lack of scientific theory and engineering methods to support, engineering, it is difficult to guarantee the quality of the database is running again after a period of time often find a variety of different levels problem and increase the cost of maintaining manual design specification to design the basic idea and step by step process of iterative refinement design specification (continued)
Typical methods:
(1) New Orleans (New Orleans) Methods: The database design is divided into four stages SBYao Methods: The database design is divided into five steps IRPalmer methods: the database design as a process step by step (2) computer-aided design ORACLE Designer 2000
SYBASE PowerDesigner

Fourth, the basic steps of database design database design
process (six phase)
1. Needs analysis phase
Accurate understanding and analysis of user needs (including data processing)
Is the basis of the entire design process is the most difficult and time-consuming step 2. The concept of the structure of the design phase
Design is the key to the entire database of user needs through a comprehensive, summarized and abstract form a specific DBMS independent of the concept of model 3. The logic of the structure of the design phase
The concept of the structure will be converted to a DBMS data model supported by their optimized 4. Database physical design stage
Logical Data Model to select the most suitable environment for the physical structure (including storage structures and access methods)
5. Database implementation phase
DBMS data provided by the use of language, tools, and the host language, according to the logic design and physical design of the outcome of the establishment of a database, developing and debugging applications, organizations, data warehousing, and 6 trials. Database operation and maintenance phase
Database application system is ready for trial operation in the formal run.
Process in the database system must be continually evaluated, adjusted and modified design features:
During the design process, the database design and data processing of the database design to closely combine the two aspects of the needs analysis, abstraction, design, implementation at all stages at the same time, cross-reference and complement each other in order to improve both design

Various stages of the design process to describe the design:
Figure:

Fifth, the database model of the formation of levels 1. Needs analysis phase: the application of the comprehensive needs of individual users
The need to belong to specific categories or with a specific role in the definition of things to do, the role of entities can be used to create a specific time relationship, which can be self-documenting.
For example: PERSON entities and used to describe the staff PERSON_TYPE entities. For example, when John Smith, Engineer upgraded to John Smith, Director and even the final climb John Smith, CIO of the high, and all you have to do is change the two tables PERSON_TYPE the relationship between the PERSON and the keys, while an additional Date / Time field to know when change is occurring. In this way, your PERSON_TYPE table PERSON includes all possible types, such as Associate, Engineer, Director, CIO or CEO and so on. There is also an alternative way is to change the PERSON title records to reflect the new changes, but so that in time the individual can not track the location of a specific time.
7) Select the number of types and text types in SQL as much as possible the use of adequate smallint and tinyint type to be especially careful. For example, if would like to see total sales on a total field type is smallint, then, if the total amount of more than $ 32,767 can not be calculated on the operation.
And the ID type of the text field, such as customer ID or order number, etc. should be set greater than generally imagined. Assuming a 10-digit customer ID long. Then you should be the length of the database table field is set to 12 or 13 characters long. However, this additional space occupied has no future reconstruction of the entire database can be achieved on the scale of the growth of the database.
8) increase in the deletion of fields in the table tag contains a "Delete Marked" field, so that line can be marked for deletion. Relations not in a separate database to delete a line; the best procedures used to clear the data but also to carefully safeguard the overall index.

3. Selection keys and indexes (logical database design)
Button to select the principles:
1) 4 key design principles to create a foreign key related fields. •
All keys must be unique. •
Avoid the use of composite keys. •
Foreign key is always associated key fields only. •
2) the use of system-generated primary key when designing the database using system-generated key as the primary key, then the actual control of the index of the integrity of the database. In this way, databases and非人工effective control mechanism to store data in each line of the visit. The use of system-generated key as a primary key advantages: when the keys have the same structure, can easily find a logical flaw.
3) Do not use the user's key (not primary key can be updated with the of)
In determining what the key field as a table when the user may have to be careful to edit the field. Usually do not choose the circumstances of the user can edit the fields as a key.
4) an optional master key can sometimes be key to bond further with an optional master key, can have the ability to build a strong index.

Index of use of principles:
Index is the data from the database to obtain one of the most efficient manner. 95% of database performance problems can be solved using indexing technology.
1) the logic of the primary key to use the only group index, keys on the system (as stored procedures) using only non-group index, foreign key column for any non-index group. Consider how much space a database table to visit, and these visits are mainly used for reading and writing.
2) the majority of databases are created automatically index the primary key field, but do not forget the foreign key index, which is frequently used keys, such as running queries show the main table and all associated records in a table on the useful.
3) Do not index memo / note field, not to index a large field (there are many characters), so the index will be taking up too much storage space.
4) Do not index small tables commonly used for small data tables not set any keys, if they often have to insert and delete operations do not even made. Of these insertion and deletion may be more than the maintenance of the index table space scan consume more time.

4. Data integrity design (logical database design)
1) the integrity of the realization of mechanisms:
Entity integrity: the primary key referential integrity:
Father of the table to delete data: cascade delete; restricted deleted; home space to insert the value of the parent table data: Insert restricted; recursive insert the parent table to update data: update cascade; limited update; home DBMS null value of the light There are two ways can the integrity of the realization: the realization of foreign key mechanism (bound by the rules) and the trigger mechanism to achieve the integrity of user-defined:
NOT NULL; CHECK; Trigger 2) business rules rather than mandatory constraints data integrity using the database system data integrity. This will not only be achieved through standardization of data integrity but also functional. When writing data can also trigger an increase to ensure the accuracy of the data. Do not rely on the business layer to ensure data integrity; it can not guarantee that forms between the (foreign keys) can not be imposed on the integrity of the integrity of the rules on the other.
3) the integrity of mandatory instructions
Harmful data in the database before entering removed. The instructions to activate the integrity of the database system characteristics. This data can be maintained clean and will force developers to devote more time to deal with error conditions.
4) Find a control data integrity data integrity control is the best way to limit user choice. Whenever possible should be made available to the user a clear choice for the value of the list. This type of code will reduce errors and misunderstandings while providing data consistency. Particularly suitable for certain public data to find: country code, such as a status code.
5) In order to view the use of databases and applications with another layer of abstraction between the code, the application can view the establishment of specialized applications without having to go direct access to data sheets. It could also mean changes in dealing with the database to provide you with more freedom.

5. Other design techniques 1) to avoid the use of the functions of flip-flop flip-flop can usually achieve by other means. Flip-flop in the debugging process could be interference. If you do need the use of flip-flop, you'd better concentrate on its documentation.
2) the use of common English (or any other language) and do not use code to create drop-down menu, a list of the best statements in accordance with the sort of English. If the need for coding, coding can be attached next to the user to know English.
3) commonly used to preserve information stored so that a table devoted to the general database information very useful. Exterior storage in the current version of the database, the latest inspection / repair (for Access), the name associated design documents, customer and other information. This simple mechanism to achieve a tracking database, when a customer complained that they hope that the database does not meet the requirements of contact with you when to do so for non-client / server environment is particularly useful.
4) contains the version of the mechanism introduced in the database version control mechanism to determine the version of the database. Over time, the needs of the users will always be change. Ultimately may be asked to modify the database structure. The version information stored directly to the database more convenient.
5) the preparation of documents for all the shortcuts, named norms, constraints and functions should be the preparation of the document.
Used to the table, columns, triggers and other database tools plus notes. Development, support and follow-up very useful modification.
Documentation on the database, or in the database's own internal documents, or separate. In this way, when after more than a year after the back to do the first two versions of the opportunity to make mistakes will be significantly reduced.
6) Testing, testing, repeated testing to establish or amend the database, users must use the new data entered field test data. The most important thing is to allow users to test and to work together to ensure that users choose the same type of data to meet business requirements. Tests need to invest in the new database prior to the completion of the actual services.
7) check the design in the development of the database design during the inspection of the commonly used technology is supported through the application of its prototype to check the database. In other words, for each of the final prototype of the application of expression data to ensure that you check the data model and how to remove the data view.

The third part (20) Advanced Configuration of the single-source multi-directional replication environment (1) 2009-04-09 16:38:20
char, varchar2 difference 2009-04-06 22:10:03
Oracle Database Exp / Imp tools for performance tuning 2009-04-06 11:12:24
Detailed: database name. Instance name. ORACLE_SID. Database name 2009-04-06 07:22:20
High Availability Oracle Flashback 2009-02-26 17:23:02

oracle notes

kods.net — Tue, 30 Jun 2009 02:47:12+0000

EM services start and stop (10g)

emctl start dbconsole

emctl stop dbconsole

emctl status dbconsole

Oracle10g EM garbled the quick fixes

Show only English.

To the IE select "Tools "==>" "==>" internet options to add "==>" English language "==>"" ==> and then moved up in English.

http://www.eygle.com/archives/2006/03/oracle10g_em_font_change.html

Had not been how to use 10g of EM tools, take a look at today's open and found that there are the garbled word problem for a long time. Online spread the N kinds of solutions, look carefully, in fact, this problem solved.

If the installation, our system environment variable is the Chinese environment, then there would not have this problem. The root causes of this problem is to install, JDK / JRE character set automatically.

In the $ ORACLE_HOME / jdk / jre / lib and $ ORACLE_HOME / jre / 1.4.2 / lib / directory will have a variety of character sets the font configuration file:

[oracle@danaly ~]$ cd $ORACLE_HOME/jdk/jre/lib
[oracle@danaly lib]$ ls font*zh_CN*
font.properties.zh_CN.Redhat  font.properties.zh_CN.Redhat2.1  
font.properties.zh_CN.Sun  font.properties.zh_CN_UTF8.Sun

[oracle@danaly lib]$ cd $ORACLE_HOME/jre/1.4.2/lib/
[oracle@danaly lib]$ ls font*zh_CN*
font.properties.zh_CN.Redhat  font.properties.zh_CN.Redhat2.1  
font.properties.zh_CN.Sun  font.properties.zh_CN_UTF8.Sun

As long as we use the appropriate file to replace the Chinese character set to the default file, I choose to use to replace the default font font.properties.zh_CN.Redhat definition files:

[oracle@danaly lib]$ cp font.properties.zh_CN.Redhat font.properties

Need to be cleaned up after you replace the Cache, you can restart EM.

Cache is usually located in the path:

$ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zhs

Gif files you can remove all. And then restart the EM:

[oracle@danaly zhs]$ emctl stop dbconsole
TZ set to PRC
oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0  
Copyright (c) 1996, 2005 oracle Corporation.  All rights reserved.
http://danaly.hurrray.com.cn:1158/em/console/aboutApplication
Stopping oracle Enterprise Manager 10g Database Control ... 
 ...  Stopped. 
[oracle@danaly zhs]$ emctl start dbconsole
TZ set to PRC
oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0  
Copyright (c) 1996, 2005 oracle Corporation.  All rights reserved.
http://danaly.hurrray.com.cn:1158/em/console/aboutApplication
Starting oracle Enterprise Manager 10g Database Control .............. started. 
------------------------------------------------------------------
Logs are generated in directory 
/opt/oracle/product/10.2.0/danaly.hurrray.com.cn_danaly/sysman/log

Now landing Web page, EM display can display Chinese.

Note: If, after more than deal with the Chinese still can not display correctly, please refer to the following description:

Open just a good replacement font.properties, or view the original Chinese text documents:

[oracle @ smsdbrac1 lib] $ tail -2 font.properties.zh_CN.Redhat
filename.-misc-zysong18030-medium-r-normal--*-% d-*-*- c-*- iso10646-1 = / usr / share / fonts / zh_CN / TrueType / zysong.ttf

Includes attention to the Chinese font file filename, make sure that the file in your server if there is, if does not exist, there is a change of a font file to the Chinese, but also can download and install a new font file:

[oracle @ smsdbrac1 lib] $ ls-al / usr / share / fonts / zh_CN / TrueType / zysong.ttf
-rw-r - r - 1 root root 9249332 Mar 29 2002 / usr / share / fonts / zh_CN / TrueType / zysong.ttf

Starter Edition

oracle em in garbled button

2009-06-12 06:53

oracle em in garbled button solution:
1. Jdk modify the fonts below.
[oracle @ rac2 db_1] $ cd $ ORACLE_HOME / jdk / jre / lib /
[oracle @ rac2 lib] $ mv font.properties font.properties_bak
[oracle @ rac2 lib] $ cp font.properties.zh_CN.Redhat font.properties

2. Jre to amend the following fonts
[oracle @ rac2 lib] $ cd $ ORACLE_HOME/jre/1.4.2/lib
[oracle @ rac2 lib] $ mv font.properties font.properties_bak
[oracle @ rac2 lib] $ cp font.properties.zh_CN.Redhat font.properties

3. The cache to clear em
[oracle @ rac2 lib] $ cd $ ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zhs
[oracle @ rac2 zhs] $ rm *. gif

4. Restart em
[oracle @ rac2 zhs] $ emctl stop dbconsole
[oracle @ rac2 zhs] $ emctl start dbconsole

In addition, the need to pay attention to the last line of the set font.properties font file, if there is no need to specify the font file already exists, but also pay attention to font file must have the authority to operate

Such as:
[oracle @ localhost lib] $ tail-n 3 font.properties
filename.-misc-zysong18030-medium-r-normal--*-% d-*-*- c-*- iso10646-1 = / usr / share / fonts / chinese / TrueType / uming.ttf

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
Oracle XDB relax resolve port conflict of 8080 2009-03-13 16:47:42
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31

Oracle memory structure-SGA

kods.net — Tue, 30 Jun 2009 00:10:47+0000

First, an overview of the "Oracle memory structure of-PGA Chapter" mentioned in the article, PGA is a dedicated server process private memory area, while the SGA is the shared memory area.

SGA by a number of parts:
1, the fixed SGA (Fixed SGA)
2, block buffer (Db cache)
3, redo log buffer (Redo log buffer)
4, Java pool (Java pool)
5, large pool (Large pool)
6, shared pool (Shared pool)
7, stream pool (Stream pool)

The following parameters to control the size of shared pool of related components:
1, JAVA_POOL_SIZE: Control Java pool size.
2, SHARED_POOL_SIZE: 9i shared pool to control the largest part of the occupier, 10g shared pool size over the control.
3, LARGE_POOL_SIZE: control of large pool size.
4, DB_ * K_CACHE_SIZE: control the size of the different pieces of the buffer size.
5, LOG_BUFFER: redo log buffer size control.
6, SGA_TARGET: 10g or more automatic SGA memory management control of the total memory size.
7, SGA_MAX_SIZE: can control the maximum SGA size, need to restart the database to change.

The following will describe in more detail the role of the various parts and recommended settings.

Second, SGA role of the various components
1, the fixed SGA:
As the name suggests, is a change of memory areas, in other parts of SGA point, Oracle, through its SGA to find other areas, can be simply understood as a section of memory used to manage the district.

2, block buffer:
Query, Oracle will first read the data from the disk Add memory, later inquiries related data do not read the disk again. Insert and update, Oracle will cache data in the area, and then wrote the hard drive volume. Buffer through the block, Oracle can increase the disk cache memory of the I / O performance.
Block buffer in three regions:
 default pool (Default pool): By default all the data in the cache here.
 maintain pool (Keep pool): used to cache data reuse many times.
 recycling pool (Recycle pool): rarely used to cache data reuse.
There is only one pool of the original default, all data in the cache here. This will have a problem: a large number of very little reuse of data will need to reuse the data "out of" buffer zone, resulting in disk I / O increase, decrease speed. Later, the separation and recovery of maintaining pool pool often re-used depending on whether the data cache, respectively.
This three-part memory areas need to manually determine the size, and are not shared. For example: keep the pool has been filled, and the recovery of a large number of free memory pool, then the recovery of the memory pool will be allocated to maintain the pool.
9i onwards, can also set db_nk_cache. Before 9i database can use the same block size. 9i to start a database can be used with a wide range of block size (2KB, 4KB, 8KB, 16KB, 32KB), these blocks need to db_nk_cache in their cache. If the table space for different designated a different block size, the need to set their own buffer zone.

3, redo log buffer (Redo log buffer):
Data writes redo log files in the cache before, the trigger in the following situations:
 every 3 seconds
 reached 1MB cache, or 1 / 3 full
When users submit 
 buffer before the data is written to disk

4, Java pool (Java pool):
In the database used to run Java code in this part of memory. For example: the preparation of Java stored procedures running on the server. Note that the Java memory and prepared common B / S system is not the relationship. JAVA language instead of using PL / SQL language in the database will be used to write stored procedures in this part of memory.

5, large pool (Large pool):
The use of the following three conditions to the large pool:
 parallel execution: the storage process of the buffer zone between the news
 RMAN: under certain circumstances for disk I / O buffer
 shared server mode: shared server mode, the distribution of UGA in the pool (if set up a large pool)

6, shared pool (Shared pool)
SGA shared pool is the most important one of the memory segment. Shared pool too big and too small will seriously affect server performance.
SQL and PL / SQL to explain the plan, code, data, data dictionary cache and so on are here.
SQL and PL / SQL code will be conducted before the implementation of "hard analysis" to obtain permission to verify the implementation of plans and operations and other related auxiliary. "Hard analysis" It is time-consuming. Very short response time for inquiries, "hard to resolve" all the time can be accounted for 2 / 3. Longer response time for the statistics operation, "hard analysis" of the time occupied by the ratio will drop a lot. Implementation plan and the necessary data dictionary cache in a shared data pool, so follow-up to the same query can be reduced a lot of time.
Do not use "bind variable" result in:
 system needs to spend a lot of resources to resolve queries.
 never shared pool of code reuse, the system spent a large part of the cost of memory management.
On to discuss the advantages and disadvantages of shared variables has exceeded the scope of this article, simply speaking, the short response time of queries to use shared variables, response time statistics do not use shared variables.
It should be noted that, SHARED_POOL_SIZE control parameters in 9i shared pool occupied the largest part, 10g more than control the total size of shared pool.

7, stream pool (Stream pool)
An increase of more than 9iR2 "streaming" technology, 10g more than the increase in the SGA in the stream pool. Flow is used to copy the data sharing and tools.

Third, SGA did not set up common settings, all settings should be based on system load, operational needs and hardware environment to be adjusted. Here only a general summary of the settings, SGA to avoid the problems caused by improper settings.

1, automatic SGA memory management in Oracle 10g introduced Automatic SGA memory management features, DBA can set SGA_TARGET the SGA can be used to tell Oracle how much memory, according to system load from Oracle to dynamically adjust the size of the components, the corresponding number will be stored in the control file, restart the database after they remember the size of the components.
Need to note the following points:
 To use the automatic SGA memory management, STATISTICS_LEVEL parameters must be set to TYPICAL or ALL, the system automatically collects information for the corresponding set of dynamic adjustment of SGA.
 can set the value of a component, Oracle components use this value for the smallest size

Dynamically adjust the parameters:
DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE.
Need to manually set the parameters:
LOG_BUFFER, STREAMS_POOL, DB_NK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE.

2, manual SGA memory management
1) 32bit and 64bit limit of 32-bit operating system, Oracle's largest memory available for the 1.75g, that is to say SGA + PGA <= 1.75g, the memory exceed this limit will not be used in Oracle.
32 can be loaded into the Oracle operating system 64, the 64-bit Oracle can not load 32-bit operating systems.

2) See Oracle version:
SQL> select * from v $ version;
BANNER
-------------------------------------------------- --------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL / SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

3) the component settings:
JAVA_POOL_SIZE: If useless java client to the database system, 30MB enough.

LOG_BUFFER: defaults to MAX (512KB, 128KB * CPU number). 1MB general system enough to run large-scale affairs can be set to 2MB, so that 1 / 3 full into the log files can continue to write buffer, and then there is no great significance.

SHARED_POOL_SIZE: too much too small will seriously affect the system performance, 1GB of memory can be set to 100MB, 2GB of memory can be set to 150MB, 4GB of memory can be set to 300MB. First of all, hit shared pool is too low to adjust the application rather than the expansion of shared pool. The use of bind variables can reduce the demand for shared pool to improve the hit rate and reduce the burden-sharing pool of management and competitive LATCH.

LARGE_POOL_SIZE: the use of a dedicated service model can be set to 30MB, unless necessary, or does not recommend the use of shared server mode.

DB_CACHE_SIZE: to remove the above-mentioned memory can be used other than memory are allocated to the region.

Summary
32-bit Oracle:
1G memory: SHARED_POOL_SIZE = 100MB, DB_CACHE_SIZE = 0.5GB;
2G memory: SHARED_POOL_SIZE = 150MB, DB_CACHE_SIZE = 1.25GB;

64 Oracle
4G memory: SHARED_POOL_SIZE = 200MB, DB_CACHE_SIZE = 2.5GB;
8G memory: SHARED_POOL_SIZE = 400MB, DB_CACHE_SIZE = 5GB;
12G memory: SHARED_POOL_SIZE = 500MB, DB_CACHE_SIZE = 8GB

To stress once again that more than just avoid the problem caused by improper SGA general settings, the system according to the specific load and operational tools such as the logic of combining fine-tune Stackpack.

RMAN created physical standby 2009-03-30 22:49:29
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
RedHat AS 3 at oracle10.2.1 install document 2009-03-13 16:56:34
Diagnosis and principles of order 2009-02-25 20:27:28

Oracle 10g using FORALL to deal with non-consecutive array

kods.net — Mon, 29 Jun 2009 21:18:58+0000

In PL / SQL implementation of cycle data manipulation language (DML) is a time-consuming, because each cycle should be from PL / SQL engine SQL engine turning. FORALL is a better application of the method, which a one-time to a group of SQL statements submitted to the provisional value.

Oracle 10g in the former, FORALL statement can only deal with the continuity of the syntax of the array elements:

FORALL index_name IN lower_bound .. upper_bound sql_statement;

This means that: in the past, the use of the FORALL nested table can not be processed to remove the middle element of the array, and an array of processing must be carried out continuously. Oracle 10g solve these two problems, and to increase the INDICES OF and VALUES OF clauses.

INDICES OF clause to replace lower_bound .. upper_bound, all have to deal with the effective index values, even if the interval between these values. Like this:

FORALL index_name IN INDICES OF collection_name
BETWEEN lower_bound AND upper_bound
sql_statement;

You can still BETWEEN syntax can be applied to deal with the scope of restrictions, this is an optional content.

VALUES OF clause to help you deal with a different sequence of the main collection. You then create another set, which contains only the index you want to deal with numbers, these numbers you wish to order. The statement then becomes:

FORALL index_name IN VALUES OF index_collection
sql_statement;

A list of applications is a model example of HR samples. I will load into memory the name of the department in a nested table, and then search inside the relevant departments and IT. Each to a search, I save it the index table entry. VALUES OF clauses combined index for this group of table INSERT statement for each department to deal with. (This is just an example; with a single SQL statement can also be treated in the same.) Lists B is output.

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
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
An example of the use of TKPROF 2009-02-26 01:38:19

How to Correct the Error Codes in Journal Import?

kods.net — Mon, 29 Jun 2009 20:41:05+0000

Period Error Codes
------------------
EP01 This date is not in any open or future enterable period.
EP02 This set of books does not have any open or future enterable periods.
EP03 This date is not within any period in an open encumbrance year.
EP04 This date is not a business day.
EP05 There are no business days in this period.

Unbalanced Journal Error Codes
------------------------------
WU01 Warning: This journal entry is unbalanced. It is accepted because
suspense posting is allowed in this set of books.
EU02 This journal entry is unbalanced and suspense posting is not allowed in
this set of books.
EU03 This encumbrance journal entry is unbalanced and the Reserve for
Encumbrance account is not defined.

Flexfield Error Codes
---------------------
EF01 This Accounting Flexfield is inactive for this accounting date.
EF02 Detail posting not allowed for this Accounting Flexfield.
EF03 Disabled Accounting Flexfield.
EF04 This is an invalid Accounting Flexfield. Check your cross-validation
rules and segment values.
EF05 There is no Accounting Flexfield with this Code Combination ID.

Foreign Currency Error Codes
----------------------------
EC01 A conversion rate must be entered when using the User conversion rate
type.
EC02 There is no conversion date supplied.
EC03 A conversion rate type or an accounted amount must be supplied when
entering foreign currency journal lines.
EC06 There is no conversion rate for this currency, conversion type and
conversion date.
EC08 Invalid currency code.
EC09 No currencies are enabled.
EC10 Encumbrance journals cannot be created in a foreign currency.
EC11 Invalid conversion rate type.
EC12 The entered amount must equal the accounted amount in a functional or
STAT currency journal line.
EC13 The entered amount multiplied by the conversion rate must equal the
accounted amount.
ECW1 Warning: Converted amounts could not be validated because the
conversion rate type is not specified.

Budget Error Codes
------------------
EB01 A budget version is required for budget lines.
EB02 Journals cannot be created for a frozen budget.
EB03 The budget year is not open.
EB04 This budget does not exist for this set of books.
EB05 The encumbrance_type_id column must be null for budget journals.
EB06 A period name is required for budget journals.
EB07 This period name is not valid. Check calendar for valid periods.
EB08 Average journals cannot be created for budgets.
EB09 Originating company information cannot be specified for budgets.

Encumbrance Error Codes
-----------------------
EE01 An encumbrance type is required for encumbrance lines.
EE02 Invalid or disabled encumbrance type.
EE03 Encumbrance journals cannot be created in the STAT currency.
EE04 The BUDGET_VERSION_ID column must be null for encumbrance lines.
EE05 Average journals cannot be created for encumbrances.
EE06 Originating company information cannot be specified for encumbrances.

Reversal Error Codes
--------------------
ER01 A reversal period name must be provided.
ER02 This reversal period name is invalid. Check your calendar for valid
periods.
ER03 A reversal date must be provided
ER04 This reversal date is not in a valid period.
ER05 This reversal date is not in your database date format.
ER06 Your reversal date must be the same as or after your effective date.
ER07 This reversal date is not a business day.
ER08 There are no business days in your reversal period.
ER09 Default reversal information could not be determined.

Descriptive Flexfield Error Codes
---------------------------------
ED01 The context and attribute values do not form a valid descriptive
flexfield for Journals - Journal Entry Lines.
ED02 The context and attribute values do not form a valid descriptive
flexfield for Journals - Captured Information.
ED03 The context and attribute values do not form a valid descriptive
flexfield for Value Added Tax.

Miscellaneous Error Codes
-------------------------
EM01 Invalid journal entry category.
EM02 There are no journal entry categories defined.
EM03 Invalid set of books id.
EM04 The value in the ACTUAL_FLAG must be "A" (actuals), "B" (budgets), or
"E" (encumbrances).
EM05 The encumbrance_type_id column must be null for actual journals.
EM06 The budget_version_id column must be null for actual journals.
EM07 A statistical amount belongs in the entered_dr (cr) column when entering
a STAT currency journal line.
EM09 There is no Transaction Code defined.
EM10 Invalid Transaction Code.
EM12 An Oracle error occurred when generating sequential numbering.
EM13 The assigned sequence is inactive.
EM14 There is a sequential numbering setup error resulting from a missing
grant or synonym.
EM17 Sequential numbering is always used and there is no assignment for this
set of books and journal entry category.
EM18 Manual document sequences cannot be used with Journal Import.
EM19 Value Added Tax data is only valid in conjunction with actual journals.
EM21 Budgetary Control must be enabled to import this batch.
EM22 A conversion rate must be defined for this accounting date, your
default conversion rate type, and your dual currency.
EM23 There is no value entered for the Dual Currency Default Rate Type
profile option.
EM24 Average journals can only be imported into consolidation sets of books.
EM25 Invalid average journal flag. Valid values are "Y", "N", and null.
EM26 Invalid originating company.
EM27 Originating company information can only be specified when intercompany
balancing is enabled ..

Create table PCTUSED parameters PCTFREE and what does this mean? 2009-04-21 01:05:19
Day-to-day management of Oracle 10G RAC 2009-04-09 06:40:04
Release undo tablespace space 2009-04-08 17:50:03
oracle pl / sql examples of practice 2009-04-06 04:35:08
SQLPlus skills 2009-04-01 09:37:29

Oracle 10g under Redhat linux installation instructions

kods.net — Mon, 29 Jun 2009 18:45:10+0000

Oracle 10g under Redhat linux installation instructions

Oracle 10g under Redhat linux installation instructions

1. Hardware requirements

1.1 memory of at least 512M, use the following command to check

grep MemTotal / proc / meminfo

Exchange of at least 1.2 Division 1G

grep SwapTotal / proc / meminfo

1.3 / tmp directory free at least 400M

df-k / tmp 400M

1.4 hard drive idle at least 4G

df-k

2. Software Requirements

2.1 operating system, redhat ES / AS 2.1 (x86), redhat ES / AS 3 (x86)

I have a redhat AS 4.1. Can use the following command to check

cat / etc / issue

2.2 need to install the following packages (in bold blue of the general system installed by default)

binutils-2.17.50.0.6-6.el5

compat-libstdc + + -33-3.2.3-61

compat-libstdc + + -296-2.96-138

compat-gcc-34-3.4.6-4

compat-gcc-34-c + + -3.4.6-4

compat-db-4.2.52-5.1

elfutils-libelf-0.125-3.el5

elfutils-libelf-devel-0.125-3.el5

glibc-2.5-24

glibc-common-2.5-24

glibc-devel-2.5-24

gcc-4.1.2-42.el5

gcc-c + +-4.1.2-42.el5

libaio-0.3.106-3.2

libaio-devel-0.3.106-3.2

libgcc-4.1.2-42.el5

libstdc + +-4.1.2-42.el5

libstdc + +-devel-4.1.2-42.el5

make-3.81-3.el5

openmotif-2.3.0-0.5.el5

sysstat-7.0.2-1.el5

setarch-2.0-1.1

Can use the following command to check:

rpm-q package_name

Package_name Note here that does not contain any version number, for example, to check make-3.79

rpm-q make

If the above software is not installed, please install.

Can be installed online:

yum list | grep package_name --- See software list

yum install package_name --- download and install

2.3 firewall software

If you do not know how to configure, disable. How to disable:

Open Applications / System Settings / Security Level

3. To create the necessary installation of oracle group and user

The needs of the user is oracle, group dba, osinstall

Approach can be used to add the interface can also be a way to use the command line

groupadd osinstall

groupadd dba

useradd-g osinstall-G dba oracle

passwd oracle

4. To create the installation directory to install the required

4.1 Oracle Base directory / u01/app/oracle

mkdir-p / u01/app/oracle

chown-R oracle: osinstall / u01/app/oracle

chmod-R 755 / u01/app/oracle

4.2 Oracle database file storage directory. This can not. General is set to / u02/oradata

mkdir-p / u02/oradata

chown-R oracle: osinstall / u02/oradata

chmod-R 755 / u02/oradata

5. Configure the kernel parameters

5.1 Using a text editor, create or edit / etc / sysctl.conf file, add or modify the following line

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

5.2 change the current value of kernel parameters

sysctl-p

6. Check your / etc / host.conf whether it contains the following entries

127.0.0.1 localhost

If not, oracle can not start the listener

7. In order to improve the software in the linux system performance, oracle user settings must be limited to the following shell.

7.1 open a file and processes a number of restrictions on the number of

By adding the following line to the file / etc / security / limits.conf

* Soft nproc 2047

* Hard nproc 16384

* Soft nofile 1024

* Hard nofile 65536

* Refers to all the restrictions on users and groups, Oracle official documentation is required, I recommend that the * replace oracle,

That the oracle user only. * If set, will be likely to impact on other services (such as the Sybase Service to me)

7.2 by adding the following line to the file / etc / pam.d / login, if it does not exist

session required / lib / security / pam_limits.so

Oracle says the official website, but it has been found that this influence.

Version of my operating system is Redhat AS 4.1, to remove this line is no problem.

7.3 In accordance with oracle user's default shell, the default shell start-up file changes as follows.

If the Bourne, Bash, or Korn shell, modify environment variables / etc / profile file as follows:

if [\ $ USER = "oracle"]; then

if [\ $ SHELL = "/ bin / ksh"]; then

ulimit-p 16384

ulimit-n 65536

else

ulimit-u 16384-n 65536

fi

fi

If it is c shell, the environment variable file / etc / csh.login value changes as follows:

if ($ USER == "oracle") then

limit maxproc 16384

limit descriptors 65536

endif

8. For the oracle user environment variable settings, note the following command as oracle user must be logged in as

● Bash shell, change. Bash_profile, add the following line

　　 ORACLE_BASE = / u01/app/oracle

ORACLE_SID =**********

　　 export ORACLE_BASE ORACLE_SID

● c shell, change. Login file, add the following line

setenv ORACLE_BASE / u01/app/oracle

setenv ORACLE_SID **********

9. Recognizing the ORACLE_HOME and TNS_ADMIN environment variables are not user settings

Do not because the installation process of other errors, and set these values

● Bash shell

unset ORACLE_HOME

unset TNS_ADMIN

● c shell

unsetenv ORACLE_HOME

unsetenv TNS_ADMIN

10. Ready to install media

10.1 general I think it is downloaded. First extract to ensure that, after extracting a directory, owned by oracle: oinstall, and has execute permission

10.2 If the CD-ROM, it is necessary to mount, the general operating system is automatically mount the.

11. In order to visit the oracle identity xwindow, under the installation directory to run runInstaller

12. The installation process, specify the database user's password, a warning we must not contain "@" symbol, in my blog, there are detailed explanation why.

13. The final stage of the installation must be in accordance with the instructions oui to root user ID to run the two script files.

FAQ (to install Oracle in Linux platform for the more common problems)

1. Can not start the installation interface

Run runInstaller message similar to the following:

xlib:connection to "localhost:0.0" refused by server

xlib:client is not authorized to connect to server

Exception in thread "main" java.lang.InternalError:

can't connect to x11 window server using "localhost:0.0" at .......

The solution: Set your DISPLAY environment parameters.

# export DISPLAY= your_IPaddress :0.0

Your_IPaddress you to change the IP. Or root of the implementation of a simple click # xhost + (to the attention of such a security risk will be)

2. The installation of the interface shows a lot of "mouth" look like garbled

Solution: See locale output

# locale

LANG=en_US.UTF-8

LC_CTYPE=zh_CN.GB18030

LC_NUMERIC="en_US.UTF-8"

LC_TIME="en_US.UTF-8"

LC_COLLATE="en_US.UTF-8"

LC_MONETARY="en_US.UTF-8"

LC_MESSAGES="en_US.UTF-8"

LC_PAPER="en_US.UTF-8"

LC_NAME="en_US.UTF-8"

LC_ADDRESS="en_US.UTF-8"

LC_TELEPHONE="en_US.UTF-8"

LC_MEASUREMENT="en_US.UTF-8"

LC_IDENTIFICATION="en_US.UTF-8"

LC_ALL=

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
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 create tasks (to)

kods.net — Mon, 29 Jun 2009 18:35:02+0000

rn
rn
http://www.cnblogs.com/tohen/archive/2008/11/17/1335186.html

rn
First, create a PLSQL table:

rn
create table HWQY.TEST (CARNO VARCHAR2 (30), CARINFOID NUMBER)
rn

rn

rn
Second, create a PLSQL stored procedure:

rn
create or replace procedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id from dual;
insert into test (test.carno, test.carinfoid) values (carinfo_id,'123 ');
commit;
end pro_test;
rn

rn

rn
Third, in the SQL command window to start the task:
In SQL> execute:

rn
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT (: jobno, 'pro_test;', SYSDATE, 'sysdate +1/24/12');
commit;
end;
/
rn

rn

rn
Fourth, the case of tracking tasks (see task queue):

rn
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- --- ---
1 2008-2-22? 01:00:00 0 N
rn

That has a role to exist.
Implementation of select * from test t see the results of tasks regularly. As can be seen from time to time is normal for the implementation of the mandate of the.

rn

rn
Fifth, stop the timer has started tasks:

rn
Before the implementation of select job, next_date, next_sec, failures, broken from user_jobs; to view its regular task of the job.
In the SQL> statement in the implementation of the following has been launched to stop a timing tasks:

rn
begin
dbms_job.remove (1);
commit;
end;
/
rn

That the cessation of the task of job 1.
After the implementation is shown below: PL / SQL procedure successfully completed

rn

rn
6, view the process of a few:

rn
show parameter job_queue_processes;
- Must be> 0, otherwise, run the following modifications:
alter system set job_queue_processes = 5;
rn

rn

rn
Seven, and then create a task (once every 5 minutes):

rn
variable jobno number;
begin
dbms_job.submit (: jobno, 'pro_test;', sysdate, 'sysdate +1/24/12');
commit;
end;
/
rn

rn
The establishment of a regular mission, in PLSQL view JOB, it is similar to the sql statement is as follows:

rn
begin
sys.dbms_job.submit (job =>: jobno,
what => 'pro_test;',
next_date => to_date ('21-02-2008 17:37:26 ',' dd-mm-yyyy hh24: mi: ss'),
interval => 'sysdate +1/24/12');
commit;
end;
/
rn

rn
Therefore, the task of creating a complete format is:

rn
variable jobno number;
begin
sys.dbms_job.submit (job =>: jobno,
what => 'pro_test;',
next_date => to_date ('21-02-2008 17:37:26 ',' dd-mm-yyyy hh24: mi: ss'),
interval => 'sysdate +1/24/12');
commit;
end;
/
- The system will automatically assign a task No. jobno.
rn

rn

rn
8, the implementation of the results:

rn
select job, next_date, next_sec, failures, broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
1 1 2008-2-22 AM 01:00:00 01:00:00 0 N
2 2 2008-2-21 PM 05:42:45 17:42:45 0 N
3 3 2008-2-21 PM 05:42:45 17:42:45 0 N

rn
rn rn rn rnrn

rnrn

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

Truncate Table is used when no matter drop storage or reuse storage will

kods.net — Fri, 26 Jun 2009 21:20:02+0000

Truncate Table is used when no matter drop storage or reuse storage will be re-instated to the first HWM

From: http://space.itpub.net/13165828/viewspace-607258

A, B for the two Table.
6g3n ~ Hrn ^ J9o0A, B, respectively, the data on the space under the table erp_data
k3 @ 8o9Z6 [4F4Y0A, B, respectively, on the index table space erp_indx personal ITPUB space under X! A u [7Z B

! nk @ n8s't WH K & T $) 0 then ITPUB personal space (rnV $ ArnW * D, m (I
We use the following two statements to delete data in two tables ITPUB personal space \! ^ Rnq R8x (U! X h / I
Truncate table A drop storage;
. z P1F)? / m n5W%] 0Truncate table B reuse storage; ITPUB Personal Space | & G3s) 1? 0 ~ b W k

rnP / w'v d N0 results would be:
6vrn [I C7A, r ^ 0
i8s a! l5 [0Truncate table A drop storage;
0S h # D'l% I, X y3Q $ \) y0 - data: data on the extent where some of the space will be released (the release of personal space minextentsITPUB recovery to O & N jz h'e x
Of extent), the space can be freed up for use of other segment.
: O) Y + | _ t2u knbx d0 - index: B form part of the index will delete data, extent has also been part of the release of the remaining first extentITPUB personal space P9q H Jrnt TH (QT
- hwm: will be re-instated HWM to the first position of Block's (hwm will change). ITPUB personal space 0z% C / _ C k
ITPUB personal space e + M8 ^ "E f RO
ITPUB personal space $ @ ornd% K! N; i s'? T)
Truncate table B reuse storage;
r "VG" ^ CWJ R0 - data: data on the extent where some of the space will not be recovered (only data will be deleted
3 \ d7 | V G0 in addition), after deletion of data freespace space only for the use of this table can not be
b K mN k P3e u0 for the use of other segment.
_, j + c ULY C0 - index: B form part of the index data will be deleted, but retain some extent personal ITPUB space [4h Y w $ D
- hwm: will be re-instated HWM to the first position of Block's (hwm will change).

LA z ( `x (P _7b + R0
`# s% a _ s.? rnp v N/q0 1, testing began to build two tables A, B and the corresponding index inx_A, idx_B
+ ~ R (YB F6t0ITPUB personal space; o O) V, t Q "UU g2n

f x6Q s0w y: ri @ s0 SQL> show user
k + w & T v (E2X I0
# bk "UX $ A l?! CrnN w l0USER is" QIUYB ";
nj T "z" wl X0
"z5Q xa` 0SQL> create table A (col number); ITPUB personal space 4h * K D # x K
ITPUB personal space, l G ^ / ny D ~ rnW
Table created.
6X M7F IOA: W | 0ITPUB personal space * s (drn ^ A1Q \ b

-uu l & d ^ 6s0SQL> insert into A values (1); ITPUB personal space 4n4 ~ 4R (N N9J v; K * ^
ITPUB personal space 9R K0f Q bi IE
1 row created.
\ rnn Z; ~ ( `0h qpi Q3u0SQL> insert into A select * from A;
X Y5y.Qrny (~ Y01 row created.
L "O c J j" M1e a0
'bo _ G r Z P0SQL> /
T a-X ' `rnw e m2B [# S02 rows created.
g @: B _0v | - ^-g, b5 (0
, P nb) s v.X '| g Q0SQL> /
rnm A'_ "); z8r & B ^ M Q0ITPUB personal space 6T% m QBS! t2P
4 rows created.
/ zm! L & s * A0ITPUB personal space: T Orn (& SrnE `e
SQL> / ITPUB personal space J6 @-XZ (9u

$ _5 `. T f * Y c08 rows created.
(y! n B2 ^ b \ o q0ITPUB personal space + ~ M ek B `D5G q
SQL> /
: \ 2I xZ-U o0
$ | E d G3U p B? C $ i016 rows created. ITPUB personal space, itz) A + i: y1 ^

Q7q v6 @ `(p0SQL> /
! e L) a! r F t Z0D0v b0ITPUB personal space * t p8R% @ "l% V w2`
32 rows created. ITPUB personal space [1 \ 8 `9z SrnX c) Z
ITPUB personal space \ KY \ [5Z sb
SQL> /
rno N7 `Q $ l / E + QrnE / VrnE! b064 rows created.
! r4O IG, R2R (f (S @ 0
(XPA) w & m & [M k0SQL> /
3a5 ^ 'X W1R @ b3A9P0ITPUB personal space)? ^' Mn | b O @
128 rows created. ITPUB personal space + o X wn prnQ (B

; k & `; bg (O $ L't WrnK Y q0SQL> /
rno% ~ $ LrnX X0
Z) S5 (i4 \ rnX5s \ (0256 rows created. ITPUB personal space "s (N p $ a.A 'Z & P L
ITPUB personal space tl # WEKB

rn ~ @. ( 'O7E l B) a0SQL> /
5I4L # (w H g0
$ x [1o0z hv V0512 rows created.
p LM # S /) s3H0ITPUB personal space \ vy V.Q4C # _ T
SQL> /
] Ugy D2Z Q f J. `0
. crnU BV * e #] T z3X i @ 8K? 01024 rows created. ITPUB personal space) EZJ _ M3S'_
ITPUB personal space J urng @ z ZX f $ A9b
SQL> / ITPUB personal space rn? N / E ,`&?* d
2048 rows created. ITPUB personal space RS _ (_8j.G
ITPUB personal space x S? Ru / `
SQL> /
@ $ y $) 7V] (e r04096 rows created.
gn N xE; D w0
. g M4U / `-] B0SQL> /
? C3Z o5) * `# r U j08192 rows created. ITPUB personal space e0F H y & L8 ^
ITPUB personal space e H # F "G y; h! Y
SQL> / ITPUB personal space rn (j D () hv% v9G b
16384 rows created.
"KBY mrnd C $ @ j yA \ 0
# A) C ~ yq ( `$ B0SQL> / ITPUB personal space #` N5c H r T # d T. (

# trnT0Y d ~ rnz) J032768 rows created.
4q ~ OW f) c/u0ITPUB personal space # m7T, [rnT (Y e & @ * N
SQL> /
b) e Y ernJ1F) * u0
ms U-_ t M Q065536 rows created.
A-N4L s $ C0ITPUB personal space X.A7f m / H c5 [L
SQL> commit;
$ XBRV) Urnp0
o! B g J j: w? mp M z8Y0Commit complete.
k3Krn_ G C6j) t0ITPUB personal space * J2 [y @ T7F m
ITPUB personal space (Q4q c3j3y; g7E $ [ 'rr X n
SQL> create table B as select * from A; ITPUB personal space q Qt t (t "x
ITPUB personal space t A r T3b Trnd T
Table created.
Z ~? Q db Z e ~ 1T (E0ITPUB personal space? "? @ 1? $ IrnCrn @

i5 ~ a YW (b0SQL> create index idx_A on A (col);
rnB; d J? \ 0
+ A * m T h + r0Index created. ITPUB personal space YJZ rrnr ^
ITPUB personal space O; t b1n.F
ITPUB personal space 0w; Z # a.lrnC Z, ^
SQL> create index idx_B on B (col);
6l2c T "(rnB0ITPUB personal space s W d HT (
Index created.
P t /? P2M c0
]: b A3a | 3L m, | 0ITPUB personal space 1A, QZ! ga Z $ H3F5H

# R (EZ * TN _ L8O0 2, look for the extent of these four paragraphs of the use. ITPUB personal space (s8b3 (D PP "[(f7N
ITPUB personal space m% urnj * t Prns ( `
ITPUB personal space GT f-u0H p V f / [5U
SQL> select segment_name, extent_id, bytes
9I S M5F% p W) M0
& l8M7S od K9j o0 2 from user_extents
b:? 2r: x2E7V! c8_0
& ( 'h5a z * pxee | E0 3 where segment_name like'% A% 'OR segment_name like'% B% '
Pv pu) p0
_ N _ W0r V0 4 order by 1, 2
8t Q-_1J n & b0ITPUB personal space g l1BrnS RP i! G
5 /
Q6E xNS B!] 0
Q] o V & @. N, d q0
; olz; GTT z. \ 0 SEGMENT_NA EXTENT_ID BYTESITPUB personal space! ib "R [@ 6W2m
---------- ---------- ----------
| Mrn | & I5X & c l0 A 0 65536
6V e, f T c @ t0A 1 65536
| $ k7 ^ 2Q & X) Brn_ Yrn [\ 0A 2 65536ITPUB personal space; W rf L1w $) (kn] 'c!]
A 3 65536
h; O s R wo W0A 4 65536
2g2 [ "KK K0A 5 65536
C2R E / n J (0A 6 65536
T & g V N6W0A 7 65536ITPUB personal space) * P u + (3G d]
A 8 65536
% r) L% E, D, NIL%? 0A 9 65536ITPUB personal space Z8]; p! @ rn (-S3N 'N% A8i
A 10 65536ITPUB personal space 1h G W4 `V1D to!? 6S
A 11 65536
\ rn ^ 6Z: nrnW D y7J $ n9d3J% J0A 12 65536
g0e [1i w Z0A 13 65536ITPUB personal space 9] G v `6n [1v
A 14 65536ITPUB personal space 8F, B); J! L u6O
A 15 65536ITPUB personal space, \% | E4N * t T
A 16 1048576ITPUB personal space P6crn (& z b N # | E
B 0 65536ITPUB personal space KFM p T0e
B 1 65536
(T! G1c $ o H0B 2 65536
4z qk UW # t2p; ~ 9H \ 3o0B 3 65536ITPUB personal space, B5r: _ f: u C3o) U \
B 4 65536ITPUB personal space 'y0O8 \ / F3_
B 5 65536
U () 'z # N & C b0B 6 65536
A5 | 4R e'L o z & K o0B 7 65536
+ x0n.o3 `7r.E0B 8 6553
H @ M0 [ "Q # n6S0B 9 65536ITPUB personal space D y4K'f R) N B1K.N $ r
B 10 65536ITPUB personal space? 7? - | 5E (\ TrnH (# n B ob
B 11 65536
) HX xb% oz% p'j8 | q K $ l p0B 12 65536ITPUB personal space 'HC o1S5g B3A
B 13 65536
# H "L m * N N0B 14 65536ITPUB personal space? R'a e8u Y a! L $ Rrn]
B 15 65536ITPUB personal space rny + W j W.R0 ~ R
B 16 1048576ITPUB personal space] rnY-W $ qA! Zrnt (Drn_ F
IDX_A 0 65536ITPUB personal space H5a1 `* i`
IDX_A 1 65536ITPUB personal space KI _; o # df
IDX_A 2 65536ITPUB personal space w J D9H R ^
IDX_A 3 65536ITPUB personal space KK (K6N TUL nV
IDX_A 4 65536
L/U5q u DrnX,?. Y0IDX_A 5 65536ITPUB personal space 6O Q) @] # s7_
IDX_A 6 65536ITPUB personal space, Z! Q5q) f O'M "O) * N
IDX_A 7 65536
i [Y @-p _0IDX_A 8 65536ITPUB personal space. j * w0t z J1P (
IDX_A 9 65536
A + o5E urg t0IDX_A 10 65536ITPUB personal space A2q% j (^ g Q k # X ( `
IDX_A 11 65536
J r V7v Z.A6L [^ vx K0IDX_A 12 65536ITPUB personal space] 9Z $ e D7h s S _ S ~
IDX_A 13 65536
\ R fe $ L Z0IDX_A 14 65536
f2h XN, c x4H * m9 `0IDX_A 15 65536ITPUB personal space u S (` I; ErnH (K5C
IDX_A 16 1048576
4M% _ si V) ^ z7j0IDX_A 17 1048576
(F "J ~ w:? 0IDX_B 0 65536
g I * NA o0p je F, A Y0IDX_B 1 65536ITPUB personal space U? R # J (j6v3N # h.O + N
IDX_B 2 65536ITPUB personal space f X6Y0G CrnH
IDX_B 3 65536ITPUB personal space M8? 4k9 ^ ~
IDX_B 4 65536
4) & c9 ^ 6o n ns Lrn ~ (0IDX_B 5 65536
! z c9P G # y: G0IDX_B 6 65536
W u OF u "A & s.v0IDX_B 7 65536ITPUB personal space an E` K v
IDX_B 8 65536ITPUB personal space I / `R! L% z O & Y1 @
IDX_B 9 65536ITPUB personal space 1 \ # z.) 9P f6i1l
IDX_B 10 65536ITPUB personal space g8z! U c0 \ grn `y
IDX_B 11 65536
! | V _rn @ F @; pp L-(0IDX_B 12 65536ITPUB personal space: O0o% p G1F "X5S
IDX_B 13 65536ITPUB personal space l2v an (~: m R
IDX_B 14 65536ITPUB personal space 6e8a l8 ~ ^ 9o) D
IDX_B 15 65536ITPUB personal space rnh) G (i4T9p ^ \ 5f
IDX_B 16 1048576
i Krno.h X/p0IDX_B 17 1048576ITPUB personal space \ / X7Y] I
ITPUB personal space mrnb. @ RnO _: V2J x
70 rows selected.
F t ED)) u0
rnV. [h vrnqrnT * E R'o0
h * V u FG! k: x0
3a e ~ 7p G ^ & [0
& R W & r-? T) d _ b6V0ITPUB personal space / g8y APO z m'n
3, View A, B List, HWM, formula HWM = total_blocks-empty_blocks, we can see A, B, respectively HWM of 232, 168
+ v2X l6O (v! h *): r | f0SQL> analyze table A compute statistics; ITPUB personal space 8c & Q \ IY t'M
Table analyzed. ITPUB personal space sa F3 @ l
ITPUB personal space (6s%? + M! W + LD
SQL> analyze table B compute statistics; ITPUB personal space, q9g q, l2x \) j3)
Table analyzed. ITPUB personal space; Z / d T [2m._
ITPUB personal space A + b B n (? (T
SQL> col table_name format a10ITPUB personal space V / t i4H a R i ^
ITPUB personal space% | t6e! E'o v ^ g
SQL> SELECT TABLE_NAME, blocks, empty_blocks, blocks-empty_blocks hwmITPUB personal space n * BW q6_ h \, Y x K
2 FROM user_tables
1Z ~ p, t K, b0 3 WHERE table_name IN ( 'A', 'B') ITPUB personal space 1nrnr d2 ^ # oJ * J
4 / ITPUB personal space L v J / (I _8J @ O

-VV i ((* C \ G l n0ITPUB personal space 2g4Z J0c byq
SQL> set pagesize 10 ITPUB personal space 0 | Z $ I (B [3s `
SQL> / ITPUB personal space c I `y, FT

-s! Uv h + L k Y, c0 TABLE_NAME BLOCKS EMPTY_BLOCKS HWMITPUB personal space 4? M9K8f3J! BH
---------- ---------- ------------ ---------- ITPUB `personal space (\:) ~ | # u (K) ZI
A 244 12 232
"B6O uk T0B 212 44 168ITPUB personal space" v6? ~ KrnP Y / M) 6q) CX $ G

G9 \ rnS l S7s6S (c N0
: ~ T2F ~-q! IF i B04, to DROP STORAGE, REUSE STORAGE on the two options A, B do see differences between different operating ITPUB personal space * `_ e + EHT K6 \ * j

C "[% P S3a9G" q0SQL> truncate table A DROP STORAGE; ITPUB personal space) Kc z3 [RrnL4w d * (
ITPUB personal space: w +? 9z + E w B
Table truncated. ITPUB personal space S hh) ^ 9r! B / \ 2F (S
ITPUB personal space). O # drn ^ 7Z Q x) t6C * rp
SQL> truncate table B REUSE STORAGE; ITPUB personal space z; Q / A gq nx \ l
Table truncated.
arn | 2G: F z% c: f% E0ITPUB personal space 7f d? + m% @ S HG
SQL> SET PAGESIZE 300ITPUB personal space $ J, hrne) R'E $ v

5k Y) n Hrn] / z prnY u0SQL> select segment_name, extent_id, bytes
(1G @ O qe (0ITPUB personal space Q6] b V% v Q ht l & u
2 from user_extents ITPUB personal space R g N (A) r

2H.A8g M "u A w E6j0 3 where segment_name like '% A%' OR segment_name like '% B%' ITPUB personal space # ^ 'Yf% k (rnw" p8L
ITPUB personal space / @ W y!] EE
4 order by 1, 2ITPUB personal space rnq L3N'q7K (T Urn_ (drnP d V

I?-L-R3q) D @ (S0 5 / ITPUB personal space 2g9k I u9L B9X C

b-^ ~ e9x y3b0
4 ~ 4k z? W0
z% dx ((/ [a $ c "j0 SEGMENT_NA EXTENT_ID BYTESITPUB Personal Space | S9q% Z # w3u
---------- ---------- ----------
+ d _ ~ j $ _ * RB / Y u0 A 0 65536ITPUB personal space _ on; K + ^ 0B (~ $ v ~
B 0 65536ITPUB personal space / W wi W) `` K (U, Z7b
B 1 65536ITPUB personal space% X (Bd Z f @ l S9h; t
B 2 65536
h'z y! e) GF ((- ~ 0B 3 65536
Y v irnN KR j i0B 4 65536ITPUB personal space _! H Srnt vt] j: m? B4 ^
B 5 65536ITPUB personal space rnV "y T * z QF Rrn (H x% K
B 6 65536ITPUB personal space O `; J0Q g3I n Q h N
B 7 65536ITPUB personal space [ "qC W9i
B 8 65536
c1 | (F], D) e BN z0X0B 9 65536ITPUB personal space 8t'r Z] e q2T z)
B 10 65536
7 (V KL m u4L `r0B 11 65536
[$ W0h3J1A (+ n ^ Q (q0B 12 65536
) y'g% l Z z Q + J0B 13 65536
rnc h # r4 `w ~: w * U E0B 14 65536ITPUB personal space si T | b1g
B 15 65536ITPUB personal space \ * k D] z A [
B 16 1048576
'i?; ghg T s0IDX_A 0 65536
& o L7X5o i (g): N z0IX_B 0 65536
: (, o; ^ `) s6j $ zrnP a0m2A0IDX_B 1 65536ITPUB personal space _2ernzrnh f5B I p X
IDX_B 2 65536
1f nx T3U P p-z0IDX_B 3 65536ITPUB personal space # A nb S9?: I P / I)
IDX_B 4 65536ITPUB personal space \ b $ M G2J D'h
IDX_B 5 65536ITPUB personal space U a) FB Y'e! Dk j6n r * et
IDX_B 6 65536ITPUB personal space 9U A [_ c, S5l9 ^ 1_
IDX_B 7 65536
t! OX w y0IDX_B 8 65536
) K I + J) G $ d, V N2) P0IDX_B 9 65536ITPUB personal space 9) 'q \) n5g E o
IDX_B 10 65536ITPUB personal space 4Q% g * m'u E ~
IDX_B 11 65536
) YX \ n * h1W0IDX_B 12 65536ITPUB personal space: DX; H: @ E-\ \ z (
IX_B 13 65536ITPUB personal space @ 1u pj H, ($]
IDX_B 14 65536
C (MF w F (] 9B C% T0IDX_B 15 65536
2z jG (U j L;? 0IDX_B 16 1048576
v, ~ \-b X0 `m0 [* A [v0IDX_B 17 1048576
bv? m l0
(B?% [S3 [0ITPUB personal space. T2T ^ Q & c ^ ob
37 rows selected. ITPUB personal space 1 | 7 ~ (S $ dh
ITPUB personal space GI / V $ I'a _
ITPUB personal space w M; @ 6l4h O # K, F6A
A table can see itself and its index are idx_A the extent of the release of all reservations to the first extent. ITPUB personal space% z R% A7G # k V + P
SQL> analyze table A compute statistics;
X5 ^ & @: Z / e O4H0ITPUB personal space 9k $ w'g A7 |? 2d * ko
Table analyzed.
v ^ + r8r) B n C1? i C Q0
DX F7l6] N: y0SQL> analyze table B compute statistics; ITPUB personal space% pi ^ x P7k + R

! h T \ g + ~; AD u N2N0Table analyzed.
4P "ji N c0
XL w M N4i N0SQL> SELECT TABLE_NAME, blocks, empty_blocks, blocks-empty_blocks hwm
r M, X p2d ~ L% U e0
7l c8] 4y1d q5 @ 0 2 FROM user_tables ITPUB personal space c \ | q! Q.p + L Y6a

XT y NF (R "^, Irna0 3 WHERE table_name IN ( 'A', 'B')
rnS Q/J3C w) [7V [* (0s0ITPUB personal space 9l A y Y # B
4 / ITPUB personal space "R r brnW6n Z" H / K / y

| 1y, r $ \ & Z) \ v0J0ITPUB personal space Q t G | (v! E; U

, G * _: T $ n? I% N a0 TABLE_NAME BLOCKS EMPTY_BLOCKS HWM
(% lp-z3p4? \ 0 ---------- ---------- ------------ ----------
J t P u% I KD f G / | 0A 0 8 - 8ITPUB personal space cm $ O1 (0d HO
B 0 256 - 256ITPUB personal space S r9 \: Z + m H0G.r

: b6K.N, u * I u0ITPUB personal space, FU) \-xf O (_
ITPUB personal space 4n n + Grnl & \, O h C u
Can see that the two forms are returned to the hwm the first block. ITPUB personal space 5G (; (8G ~

rn rn rn

Oracle database used to maintain day-to-day command 2009-04-06 16:46:33
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
SQL optimization (2) - there is the order of the index data 2009-03-28 08:01:36
Block Oracle paragraph (reprint) 2009-03-26 17:41:14

Adjustment oracle database connection pool connections

kods.net — Fri, 26 Jun 2009 20:37:43+0000

First backup $ ORACLE_HOME \ dbs \ spfileSID.ora document

c:> set ORACLE_SID = xxx
c:> sqlplus / nolog
sql> connect / as sysdba

- View the current value of the parameter
sql> show parameter processes;
sql> show parameter sessions;
sql> show parameter sga_target;

- Amend the number of connections
sql> alter system set processes = 500 scope = spfile;
sql> alter system set processes = 555 scope = spfile; / / session = process * 1.1 +5
Modified database can be restarted

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

oracle11g installation environment variable is set to adjust the kernel parameters

kods.net — Fri, 26 Jun 2009 17:15:28+0000

1, to talk about the contents of environment variable wrote ~ oracle / .bash_profile or wrote. Bashrc, wrote this article ~ oracle / .bash_profile
#. Bash_profile

# Get the aliases and functions
if [-f ~ /. bashrc]; then
. ~ /. Bashrc
fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME / bin

export PATH

# # For Oracle # #
export EDITOR = vi
export ORACLE_SID = ccdb
export ORACLE_BASE = / oracle
export ORACLE_HOME = $ ORACLE_BASE/product/11.1.0/db_1
export ORA_CRS_HOME = $ ORACLE_BASE/product/11.1.0/crs_1
export LD_LIBRARY_PATH = $ ORACLE_HOME / lib
# export LD_LIBRARY_PATH = $ ORACLE_HOME / lib: / lib: / usr / lib
# export LD_LIBRARY_PATH = $ ORACLE_HOME / lib: / lib64: / usr/lib64: / usr/local/lib64: / usr/X11R6/lib64 /
export PATH = $ ORACLE_HOME / bin: $ ORA_CRS_HOME / bin: $ PATH
umask 022

# export NLS_LANG = 'AMERICAN_AMERICA.UTF8'
export NLS_LANG = AMERICAN_AMERICA.ZHS16CGB231280
# export NLS_LANG = AMERICAN_AMERICA.ZHT16BIG5
# export NLS_LANG = "traditional chinese_taiwan.ZHT16BIG5"

# export LC_CTYPE = en_US.UTF-8
export LANG = en_US

alias sqlplus = "uniread sqlplus"
alias rman = "uniread rman"
alias adrci = "uniread adrci"
################

2, the kernel parameter to adjust Edit / etc / sysctl.conf file, add the following content

# # For Oracle # #
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
################
fs.file-max = 6553600

Note: If it is to start vnc remote installation must be run + xhost command

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

2009/06

Disk Management Part asm

Related Posts

Change apps, the statements appear to run error, pl / sql to the normal landing

Related Posts

Oracle Certification skillfully taking outside designated records associated with the alternative in / not in query

Related Posts

Database training a sense of

Related Posts

Oracle10g - The regular expression

Related Posts

How do I enable AutoTrace features of sqlplus

Related Posts

SQL interview a subject of transnational corporations

Related Posts

Son of discrete pieces of work resulted in a single stock is negative

Related Posts

Profiles Used in Form Personalization

Related Posts

Oracle memory structure of-PGA

Related Posts

How to design a good database

Related Posts

oracle notes

Related Posts

Oracle memory structure-SGA

Related Posts

Oracle 10g using FORALL to deal with non-consecutive array

Related Posts

How to Correct the Error Codes in Journal Import?

Related Posts

Oracle 10g under Redhat linux installation instructions

FAQ (to install Oracle in Linux platform for the more common problems)

1. Can not start the installation interface

2. The installation of the interface shows a lot of "mouth" look like garbled

Related Posts

oracle create tasks (to)

Related Posts

Truncate Table is used when no matter drop storage or reuse storage will

Truncate Table is used when no matter drop storage or reuse storage will be re-instated to the first HWM

Related Posts

Adjustment oracle database connection pool connections

Related Posts

oracle11g installation environment variable is set to adjust the kernel parameters

Related Posts