2009/02

Linux-unix at Oracle10g install documents (official)

kods.net — Sat, 28 Feb 2009 21:43:41+0000

Oracle ® Database Installation Guide
10g Release 1 (10.1) for UNIX Systems: AIX-Based Systems, hp HP-UX, hp Tru64 UNIX, Linux, and Solaris Operating System

http://download-east.oracle.com/docs/html/B10811_05/toc.htm

linux install oracle10G 2010-02-01 22:30:03
DBA skills needed 2009-06-18 23:59:43
oracle 10g installation documentation in the whole process of sharing RedHat5.3 2009-09-16 23:31:23
Red Hat AS4 install oracle9i 2009-07-10 02:06:53
solaris 10 oracle10g install 2009-05-24 02:02:42

Oracle9i initialization parameters that English

kods.net — Sat, 28 Feb 2009 16:19:47+0000

Oracle9i initialization parameters that English

Blank_trimming:
Description: If the value is TRUE, even if the length of the source than the target length (SQL92 compatible) longer, but also allows data distribution.
Range: TRUE | FALSE
Default value: FALSE

serializable:
Description: to determine whether access to query the class to read lock table to prevent the affairs of that contains the query to be submitted to deal with any object before the update to read. This mode of operation to provide repeatable read,
And to ensure that the same kinds of services to deal with the same data to see the two inquiries are the same value.
Range: TRUE | FALSE
Default value: FALSE

row_locking:
Description: specified in the table has been updated or are updating lock when access to lines. If set to ALWAYS, been updated in the table only after the access line lock. If set to INTENT,
Line lock will be used only SELECT FOR UPDATE, but will be updated at lock access table.
Value range: ALWAYS | DEFAULT | INTENT
Default: ALWAYS

shared_servers
Description: Specifies the boot routine, the shared server environment to create the number of server processes.
Range: In accordance with the operating system may be.
Default value: 1

circuits:
Description: can be used to specify inbound and outbound network sessions the total number of virtual circuits. Constitute the parameters of a routine are the total SGA requirements of one of a number of parameters.
Default value: derivative: SESSIONS parameter value (If you are using a shared server architecture); otherwise to 0.

Mts_multiple_listeners:
Description: Specifies the number of listener addresses are specified separately, or with a specified string ADDRESS_LIST. If the value is TRUE,
MTS_LISTENER_ADDRESS parameters can be specified as follows:
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp) (PORT = 5000) (HOST = zeus))
(ADDRESS = (PROTOCOL = decnet) (OBJECT = outa) (NODE = zeus))
In the 8.1.3 version of this parameter has been abandoned.
Range: TRUE | FALSE
Default value: FALSE

mts_servers:
Description: Specifies the boot routine, the shared server environment to create the number of server processes.
Range: In accordance with the operating system may be.
Default value: 1

mts_service:
Description: a shared server parameters have been used for the designated register at the scheduler,
Used to set up database connection service name only. If you do not want in the case of scheduler can still connect to the database, please set the value to with the same routine. Since this parameter
Version 8.1.3 has been abandoned.
Range: In accordance with the operating system may be.
Default value: 0

mts_sessions:
Description: permit the sharing of the designated server architecture of the total number of user sessions. This parameter can be set up dedicated servers for a number of user session reservations.
Value range: 0 to SESSIONS - 5
Default value: derivative: MTS_CIRCUITS and SESSIONS - 5, whichever is the smaller value

shared_server_sessions:
Description: permit the sharing of the designated server architecture of the total number of user sessions. This parameter can be set up dedicated servers for a number of user session reservations.
Value range: 0 to SESSIONS - 5
Default value: derivative: MTS_CIRCUITS and SESSIONS - 5, whichever is the smaller value

mts_max_dispatchers
Description: specify a shared server environment can run the scheduler at the same time the maximum number of processes.
Range: In accordance with the operating system may be.
Default value: If you have configured the scheduler, the default value of greater than 5 or any number of configurations the number of scheduler

mts_max_servers:
Description: specify a shared server environment can be run simultaneously to share the maximum number of server processes.
Range: In accordance with the operating system may be.
Default value: 20

dispatchers:
Note: In order to set up sharing using shared server environment set up scheduling the amount and type of procedure. Parameters can be specified for some options. For more information,
Please refer to "Oracle8i Administrator's Guide" and "Oracle Net Administrator's Guide". This is an example of string value:
'(PROTOCOL = TCP) (DISPATCHERS = 3)'.
Value range: specify the value of the effective parameters.
Default: NULL

max_shared_servers:
Description: specify a shared server environment can be run simultaneously to share the maximum number of server processes.
Range: In accordance with the operating system may be.
Default value: 20

mts_circuits:
Description: can be used to specify inbound and outbound network sessions the total number of virtual circuits. Constitute the parameters of a routine are the total SGA requirements of one of a number of parameters.
Default value: derivative: SESSIONS parameter value (If you are using a shared server architecture); otherwise to 0.

Mts_listener_address:
Note: Specify the shared server configuration to monitor the procedure. Listener process addresses the need for a monitor in order to deal with the various systems used in network protocol connection request. Unless
MTS_MULTIPLE_LISTENERS = TRUE, otherwise, each entry must have an independent value of the adjacent. This parameter since the 8.1.3 version has been abandoned grammar: (ADDRESS = (PROTOCOL = tcp) (HOST = myhost) (PORT = 7002))
Default: NULL

mts_dispatchers:
Note: In order to set up sharing using shared server environment set up scheduling the amount and type of procedure. Parameters can be specified for some options. For more information,
Please refer to "Oracle8i Administrator's Guide" and "Oracle Net Administrator's Guide". This is an example of string value: '(PROTOCOL = TCP) (DISPATCHERS = 3)'.
Value range: specify the value of the effective parameters.
Default: NULL

max_dispatchers:
Description: specify a shared server environment can run the scheduler at the same time the maximum number of processes.
Range: In accordance with the operating system may be.
Default value: If you have configured the scheduler, the default value of greater than 5 or any number of configurations the number of scheduler

nls_nchar_conv_excp:
Description: (if a value of TRUE) when the loss of implicit data conversion error return parameter.
Range: FALSE | TRUE
Default value: TRUE

nls_numeric_characters:
Description: will be used for the designated group and decimal separator characters. Group separator bit integer is used to separate groups (such as thousands, millions, etc.) characters. Decimal separator used to figure the integer part of a decimal part and separated. Its format is
.
Value range: Any single-byte characters, '+', '-', '<', '>' except.
Default value: NLS_TERRITORY obtained from

nls_sort:
Description: ORDER BY query specified sequence comparison. For a binary sort, ORDER BY queries is based on comparative sequence-based numerical. For the language to sort,
You need to scan the entire table to the definition of data in accordance with the sort of language to organize.
Value range: BINARY or the definition of effective language.
Default value: NLS_LANGUAGE obtained from

nls_territory:
Note: In order to specify the following naming conventions, including the date and number of weeks, the default date format, the default decimal character and group separator, and the default ISO and local currency symbols. Including the region to support the United States, France and Japan. Information on all regions, please refer to Oracle8i National Language Support Guide.
Value range: any valid name of the region.
Default value: According to the operating system may be

nls_timestamp_format:
Description: with NLS_TIME_FORMAT similar to, but it set the TIMESTAMP data type is the default value, the data type is stored YEAR, MONTH and DAY that some date values stored HOUR, MINUTE and SECOND which some time value.
Syntax: TIMESTAMP'1997-01-31 09:26:50.10 '(the value stored as 11 bytes).
Default value: NLS_TERRITORY obtained from

nls_time_format:
Description: specify a string value, set the default data type TIME value data type that contains the HOUR, MINUTE and SECOND time this some date fields.
Syntax: TIME'09: 26:50 '(the value stored as 7 bytes).
Default value: NLS_TERRITORY obtained from

nls_time_tz_format:
Description: Specifies a pair of values (UTC, TZD), set the TIME WITH TIME ZONE data type of default value, the data type that contains HOUR, MINUTE, SECOND, TIMEZONE_HOUR and date some time this TIMEZONE_MINUTE field. UTC is one of the world sometimes is TZD local time zone.
Syntax: TIME'09: 26:50.20 + 02:00 '(the value stored as 9 bytes).
Default value: NLS_TERRITORY obtained from

nls_length_semantics:
Note: Use byte or code point semantics to specify the creation of the new column, such as char, varchar2, clob, nchar, nvarchar2, nclob out. Various character sets of characters have their own definition. At client and server on the same character set should be based on the character set defined to measure the string of characters. Existing columns will not be affected.
Value range: BYTE or CHAR.
Default: nls_length_semantics the database character set characters in the measurement units used. BYTE.

nls_date_format:
Description: Specifies the TO_CHAR and TO_DATE function with the default date format to use. The default parameters determined by the NLS_TERRITORY. The value of the parameter can be included in the double quotes are any valid date format mask. For example:''MMM / DD / YYYY''.
Value range: any valid date format mask, but not exceeding a fixed length.
Default value: derivative

nls_timestamp_tz_format:
Description: with NLS_TIME_TZ_FORMAT similar to the one of a pair of TIMESTAMP data type value specifies the default value of this type in addition to storage YEAR, MONTH and DAY Date value, HOUR, MINUTE and SECOND time value, but also storage TIMEZONE_HOUR and TIMEZONE_MINUTE.
Syntax: TIMESTAMP'1997-01 - 31 09:26:50 + 02:00 '(the value stored as 13 bytes).
Default value: NLS_TERRITORY obtained from

nls_language:
Description: Specifies the default database language, the language will be used for message, date and month of, AD, BC, AM and PM symbols, as well as the default sort mechanism. Supported languages include English, French and Japanese and so on.
Range: Any valid language name.
Default value: According to the operating system may be

nls_comp:
Description: In the SQL statement, you should avoid using the cumbersome process of NLS_SORT. Under normal circumstances, WHERE clause in the comparison is binary, but the language is required compared NLSSORT function. NLS_COMP can use the designated NLS_SORT session parameters must be based on comparative language.
Value range: Oracle8i National Language Support Guide specified in any valid string of 10 bytes.
Default values: BINARY

nls_currency:
Description: for the L number format element designated for the local currency symbol string. The default parameters determined by the NLS_TERRITORY.
Value range: Oracle8i National Language Support Guide specified in any valid string of 10 bytes.
Default value: NLS_TERRITORY obtained from

nls_date_language:
Note: Specify the date of the spelling, month and date abbreviations (AM, PM, AD, BC) language. The parameters are the default values specified by the NLS_LANGUAGE language.
Value range: any valid value NLS_LANGUAGE.
Default: NLS_LANGUAGE value

nls_dual_currency:
Description: NLS_TERRITORY coverage is defined for the default dual currency symbol. If you do not set the parameters, it will use the default dual currency symbol; otherwise it will launch a dual currency symbol value of the new session.
Range: Any valid format name. .
Default value: double currency symbol

nls_iso_currency:
Description: for the C number format element of the international monetary symbol specified for the string. The default parameters determined by the NLS_TERRITORY.
Value range: any valid value NLS_TERRITORY.
Default value: NLS_TERRITORY obtained from

nls_calendar:
Description: Specifies the Oracle system as the use of what the calendar date format. For example, if NLS_CALENDAR set to 'Japanese Imperial', then the date format for 'E YY-MM-DD'. That is: If date is May 15, 1997, then the SYSDATE is displayed as' H 09-05-15 '.
Value range: Arabic Hijrah, English Hijrah, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China) and the Thai Buddha.
Default: Gregorian

plsql_native_c_compiler:
Description: earmarked for the generated C file to compile documents for the goal of the C compiler of the full path name. This parameter is optional. With each unique platform attached to make
File contains the default value of this parameter. If specified for this parameter a value, the value will make the document covers the default values.
Value range: C compiler of the full path.
Default value: None

remote_dependencies_mode:
Description: Used to specify a remote database for PL / SQL stored in the course of treatment was dependent on the way the object. If set to TIMESTAMP, only at the server and the local time stamp that matches the circumstances, to perform the process. If set to SIGNATURE, the security situation in the signature can be executed under the process.
Value range: TIMESTAMP | SIGNATURE
Default: TIMESTAMP

utl_file_dir:
Description: Database Administrator permit designated PL / SQL file I / O permission of the directory. The use of multiple parameters can be specified more than UTL_FILE_DIR directory. Please note that all users can read or write UTL_FILE_DIR parameters specified in all documents.
Range: Any valid directory path.
Default value: None

plsql_v2_compatibility:
Description: set up PL / SQL compatibility class. If set to FALSE, the implementation of PL / SQL V3 acts, and does not allow V2 acts; otherwise running PL / SQL V3 will accept certain PL / SQL V2 acts.
Range: TRUE | FALSE
Default value: FALSE

plsql_native_make_utility:
Note: Specify the make utility (such as UNIX's make or gmake, that the GNU make) a complete path name. Generated from C source to generate a shared object or DLL, need to use the make utility.
Value range: make utility of the full path name.
Default value: None

plsql_native_library_dir:
Description: from PL / SQL compiler to use. It specified a directory name, one of the storage of the machine-generated compiler shared object.
The scope of value: the directory name.
Default value: None

plsql_compiler_flags:
Description: from PL / SQL compiler to use. It will compile a list of procedures for signs designated as a comma-separated list of strings.
Value range: native (PL / SQL modules will be compiled to native code.), Interpreted (And PL / SQL module will be built with PL / SQL byte code format), debug (PL / SQL modules will be used to detect debugging symbols to the compiler), non_debug.
Default value: "interpreted, non_debug"

plsql_native_linker:
Description: This parameter specifies the link program (such as: UNIX in the ld, or aim for the file link to a shared object or DLL of the GNU ld) of the complete path name. This parameter is optional. Attached with each platform-specific make file contains the default value of this parameter. If specified for this parameter a value, the value will make the document covers the default values.
Value range: the full path to link the procedure name.
Default value: None

plsql_native_make_file_name:
Description: make the document specified the full path name. make utility (specified by the PLSQL_NATIVE_MAKE_UTILITY) make use of this document to generate a shared object or DLL. Each port has a dedicated platform attached to the make file, which includes the use of make
Utility to generate the DLL on the platform to follow the rules.
Value range: make a complete file path name.
Default value: None

plsql_v2_compatibility:
Description: set up PL / SQL compatibility class. If set to FALSE, the implementation of PL / SQL V3 acts, and does not allow V2 acts; otherwise running PL / SQL V3 will accept certain PL / SQL V2 acts.
Range: TRUE | FALSE
Default value: FALSE

remote_dependencies_mode:
Description: Used to specify a remote database for PL / SQL stored in the course of treatment was dependent on the way the object. If set to TIMESTAMP,
Only at the server and the local time stamp that matches the circumstances, to perform the process. If set to SIGNATURE, the security situation in the signature can be executed under the process.
Value range: TIMESTAMP | SIGNATURE
Default: TIMESTAMP

shared_memory_address:
Description: SHARED_MEMORY_ADDRESS and run-time HI_SHARED_MEMORY_ADDRESS designated SGA starting address. Platform at the time many links SGA starting address specified in this type of platform on these parameters will be ignored. If two parameters are set to 0 or NULL, then the address will be determined in accordance with the platform.
Value Range: any integer value.
Default value: 0

lock_sga:
Description: used to lock the entire SGA in physical memory. Not supported at this function on the platform, the value will be ignored.
Range: TRUE | FALSE
Default value: FALSE

hi_shared_memory_address:
Description: Specifies the system global area (SGA) the start of the run-time address. SGA connection at the designated start address of the platform, the value is ignored. At 64-bit platform, use this value to specify the high 32 and low 32. If you do not specify this value, the default value is specified the location of the platform.
Value Range: any integer value.
Default value: 0

pre_page_sga:
Description: A platform may be based on the parameters, if the parameter is TRUE, all the SGA page will be loaded into memory in order to make the routine to reach the best performance status. This will increase the startup routine and user log in time, but adequate system memory at up to reduce the appearance of missing page fault.
Range: TRUE | FALSE
Default value: FALSE

sga_max_size:
Description: Specifies the routine survival of the system during the occupation of the maximum size of the overall situation.
Value range: 0 to the maximum allowed by the operating system. Please note that because of the minimum value of the parameter value has been adjusted to start at the end, so it does not matter.
Default value: If you do not specify the value, sga_max_size default value will start the initial SGA size (for example, X) the same. SGA depends on the size of the size of various pools, such as buffer cache, shared pool, large pools. If the specified value is less than X, then use the value of sga_max_size for X. In other words, it is X with the user-specified value sga_max_size larger value between the two.

Fast_start_parallel_rollback:
Description: back when the implementation of parallel process to determine the maximum number. In most of the run-time transaction processing systems are longer, the value of very useful.
Range: FALSE | LOW | HIGH
Default value: LOW (2 * CPU_COUNT)

transaction_auditing:
Description: to determine whether the transaction layer generates a special redo record that contains the user login name, user name, session ID, some operating system information, as well as client information. In the use of a redo log analysis tools, these records may be useful.
Range: TRUE | FALSE
Default value: TRUE

transactions:
Description: Specifies the parallel transaction processing the maximum number. If the larger value will increase the size of SGA,
And routines to start the process of increasing the distribution of the number of paragraphs back. The default value is greater than SESSIONS, services to deal with recursive implementation.
Value range: a value.
Default value: derivative (1.1 * SESSIONS)

enqueue_resources:
Description: parallel process to the team can be able to share resources. For example, Oracle allows a process to share a table lock mode to shared mode lock to update another table.
Value range: 10 - 65535 (7.3) or 10 - no restriction (8.1).
Default value: derivative (the value has more than enough DML_LOCKS + 20)

dml_locks:
Note: All user access to the table the maximum number of locks. Table for each DML operation are required to implement a DML lock. For example, if three users to modify tables 2 requires the value of 6.
Value range: 0 or 20 to unlimited.
Default: 4 * TRANSACTIONS (derived)

hash_join_enabled:
Description: If set to TRUE, the optimizer will be calculated at the most effective method of connecting to consider hash link. Oracle data warehouse application recommendations should be used to TRUE value.

Range: TRUE | FALSE

Default value: TRUE

optimizer_features_enable:

Description: permit to change the init.ora parameter controls the optimization of the procedures. Parameters affected include PUSH_JOIN_PREDICATE,

FAST_FULL_SCAN_ENABLED, COMPLEX_VIEW_MERGING and

B_TREE_BITMAP_PLANS.

Value range: 8.0.0; 8.0.3; 8.0.4; 8.1.3.

Default value: 8.0.0

query_rewrite_integrity:

Description: Oracle Server implementation of the degree of query rewriting. If set to ENFORCED, Oracle

Will ensure its consistency and integrity. If set to TRUSTED, will be the relationship between the use of a clear statement to rewrite the permit. If set to

STALE_TOLERATED, even if the materialized view are inconsistent with the basic data, it still can be rewritten.

Value range: ENFORCED, TRUSTED, STALE_TOLERATED

Default: ENFORCED

query_rewrite_enabled

Description: Enable or Disable on the materialized view query rewrite. View of a particular entity only under the following conditions at the opening: session of the parameters and view a separate entity has been opened

And Cost-based optimization is enabled.

Range: TRUE | FALSE

Default value: FALSE

partition_view_enabled:

Description: PARTITION_VIEW_ENABLED If set to TRUE, the optimizer will be cut off (or skip)

Partition table view unnecessary visits. Can change the parameters of the optimization procedure based on the cost of basic table statistics from the calculation of geographical information view statistics information.

Range: TRUE | FALSE

Default value: FALSE

optimizer_max_permutations:

Description: substantial connection with a grammar of the query analysis

Optimization process will take into account a number of restrictions on the exchange table. This helps to ensure that the analysis of grammar queries maintained at an acceptable time limit,

But are unable to find the best price plan. If the value is less than 1000, should be able to query each time to ensure that only a few seconds or less.

Range: 4-2 ^ 32 (~ 4.3 G).

Default value: 80,000

optimizer_index_cost_adj:

Description: too many or too few in considering the path of the index visit, the Optimizer can be used to optimize performance. The lower the value, and optimize the process more easily select an index. In other words,

If you set the value to 50% of the cost of index access path is half of that under normal circumstances.

Value range: 1 -10000

Default value: 100 (an index access path of the conventional cost)

star_transformation_enabled:

Description: Based on the cost of the inquiry to determine whether the conversion will be applied to star queries. If set to TRUE, the optimizer will take into account the cost of conversion will be based on the application of satellite-based query;

If set to FALSE, will not use any conversion; if set to TEMP_DISABLE, inquiries, conversions will be considered, but not the use of temporary tables.

Range: TRUE | FALSE | TEMP_DISABLE

Default value: FALSE

optimizer_mode:

Description: Optimize the specified procedures. If set to RULE, will optimize the use of rule-based process, unless the query contains prompts. If set to CHOOSE,

Will be cost-based optimization procedure, unless the statement of the table does not contain statistical information. ALL_ROWS or FIRST_ROWS

Always use the cost-based optimizer.

Value range: RULE | CHOOSE | FIRST_ROWS | ALL_ROWS

Default: CHOOSE

optimizer_index_caching:

Description: Optimize adjust costs based on the assumption that the value of the procedure,

Buffer cache in the hope of nested loop join for the percentage of the index block. It will affect the use of indexing the cost of nested loop join. The parameter set to a higher value,

Nested loop join can optimize the procedures in relation to lower cost.

Value range: 0 - 100%.

Default value: 0

job_queue_processes:

Description: only for replication environment. It has been designated by the SNP of each routine the number of job queue processes (SNP0, ... SNP9, SNPA, ...

SNPZ). To automatically update the table or the implementation of snapshot DBMS_JOB created by the request, the parameter is set to 1 or greater value.

Range: 0-36

Default value: 0

max_transaction_branches:

Description: Control of Distributed Transaction Processing in the number of branches. Will be set to lower MAX_TRANSACTION_BRANCHES value, based on

MAX_TRANSACTION_BRANCHES * DISTRIBUTED_TRANSACTIONS * 72 bytes,

Slightly reduce the amount of shared pool space. In the 8.1.3 version of this parameter has been abandoned.

Value range: 1 - 32

Default value: 8

compatible:

Description: allows you to use a new release at the same time to ensure that previous versions of backward compatibility.

Value range: The default is the current release.

Default value: determined by the release

archive_lag_target:

Description: This parameter and time-based thread associated with advanced features.

Value range: 0 or [60, 7200] any integer.

Default value: 0 is the default value will be disabled at this point of time based on the advanced features of the thread. Otherwise, the value will represent the number of seconds

aq_tm_processes:

Note: If greater than zero, it will enable the time to monitor the message queue. The time value can be used to specify the delay and failure message property (for application development).

Value range: 0 - 10

Default value: 0

tracefile_identifier:

spfile:

Description: Specifies the current server parameter file name.

Value range: static parameters

Default: SPFILE parameter can be defined in client-side PFILE to indicate the parameters to use the name of the file server. Server using the default server parameter file, SPFILE want the value set by the server in-house.

Logmnr_max_persistent_sessions:

Standby_file_management:

Trace_enabled:

Ifile:

Description: At the current parameters used to document the parameters embedded in other documents. You can file a parameter on the line many times more than the use of the parameters, but can only have three nested.

Value range: any valid file name of the parameter (grammar are IFILE = parameter_file_name).

Default: NULL

remote_listener:

global_context_pool_size:

Description: The distribution from the SGA for storing and managing the overall application environment the amount of memory.

Value Range: any integer value.

Default: 1 M

plsql_native_library_subdir_count:

fixed_date:

Note: SYSDATE return data. Must always return for a fixed date rather than the date of the test system,

This value is useful. Use double quotes, or do not use quotation marks. Do not use single quotation marks.

Values: YYYY-MM-DD-HH24: MI: SS (or the default Oracle date format).

Default: NULL

db_name:

Description: a database identifier with the

CREATE DATABASE statement specified the name of the corresponding.

Value range: any valid name up to 8 characters.

Default value: none (but it should be specified)

cluster_database:

cluster_interconnects:

cluster_database_instances:

sql_version:

replication_dependency_tracking:

Description: Tracking the relevance of the replication server in order to parallel the changes essential to the dissemination. If set to FALSE, the database on the read / write operations to run faster,

However, the server can not have a copy of the relevant parallel information dissemination.

Range: TRUE | FALSE

Default value: TRUE (that is, the opening of read / write correlation tracking)

remote_os_roles:

Description: will REMOTE_OS_ROLES set to TRUE, to allow the remote client machine from the operating system to the role of distribution. If set to FALSE,

From a remote database client to identify and manage roles.

Range: TRUE | FALSE

Default value: FALSE

remote_os_authent:

Description: will REMOTE_OS_AUTHENT set to TRUE, allows the use of OS_AUTHENT_PREFIX

Value to verify the remote client machine.

Range: TRUE | FALSE

Default value: FALSE

open_links:

Description: At the first session the designated open simultaneously with the remote database the maximum number of connections. This value should be equal to or more than one quote more than a single SQL database

Statement cited the number of databases in order to open all database in order to implement the statement.

Value range: 0 - 255 (if it is 0, does not allow the distributed transaction processing).

Default value: 4

open_links_per_instance:

Description: Specifies the XA application can transplant the maximum number of open connections. XA transaction processing using portable open connections,

In order to submit a panel to deal with these connections after the cache. If you create a user is connected to the user has a transaction, the transaction may be shared connection.

Value range: 0 - UB4MAXVAL

Default value: 4

remote_login_passwordfile:

Description: Specifies the operating system or a file to check whether the user has the permissions password. If set to NONE, Oracle will ignore the password file. If set to

EXCLUSIVE, will use the database with a password file for each user to verify. If set to SHARED, a number of database will be shared

SYS and INTERNAL user password file.

Value range: NONE | SHARED | EXCLUSIVE

Default: NONE

hs_autoregister:

Description: Enable or Disable "Heterogeneous Services (HS)" automatic self-registration agent. If enabled, the information will be uploaded to the data dictionary, the

At the same agent in order to follow-up connection set up to reduce costs.

Range: TRUE | FALSE

Default value: TRUE

global_names:

Description: Whether or not the requirements specified by the database link to connect the database with the same name. If the value is FALSE, do not carry out inspections. Distributed to deal with the naming convention consistent,

Please set up the parameters to TRUE.

Range: TRUE | FALSE

Default value: TRUE

distributed_transactions:

Description: A database can participate in a distributed transaction the maximum number of treatment. Abnormal because of a network failure if the frequency and reduce the value, will result in substantial deal with outstanding matters.

Value range: 0 - TRANSACTIONS parameter values.

Default value: According to the operating system may be

commit_point_strength:

Description: A Distributed Transaction is used to determine the location of the point of submitting the value.

Value range: 0 -255

Default value: According to the operating system may be

db_domain:

Note: Specify the database name extension (such as: US.ORACLE.COM) To enable the creation of a database of domain name only, it is recommended that the value specified.

Value range: separated by periods of any string can have up to 128 characters.

Default: WORLD

dblink_encrypt_login:

Description: In the Oracle Server to connect to the other, the database link is the use of encryption password.

Range: TRUE | FALSE

Default value: FALSE

backup_tape_io_slaves:

Description: A Recovery Manager parameters, used to determine whether or not to read or write to tape using a server process, or want an additional I / O slave.

Range: TRUE | FALSE

Default value: FALSE

tape_asynch_io:

Description: on the order of equipment used to control the I / O operation (for example, Oracle data backup or recovery on the tape, or tape backup or recovery

Oracle data) Whether or not asynchronous. Only in your platform supports asynchronous equipment on the order of I / O operating conditions, the value is set to TRUE

To be valid; if the asynchronous I / O implementation of the unstable, please set the value to FALSE.

Range: TRUE | FALSE

Default value: FALSE

log_file_name_convert:

Description: the master database log file on a file name into a standby database on an equal footing on the path and file name. A log file will be added to a master database, the

Must be a corresponding file to the standby database. The parameters of the Oracle7 alternative LOG_FILE_NAME_CONVERT

Parameters.

Value range:''in any format for the main log file path / file names''and''back-up log file path / filename''effective path / file name

Default: NULL

fal_server:

Description: Specifies the standby database server FAL. The value is an Oracle Net service name. The Oracle Net

Services were assumed to have been right on the standby database system configuration, can point to the desired FAL server.

Value range: Oracle Net service name of the string value.

Fal_client:

Description: FAL designated for services (through FAL_SERVER configuration parameters) to invoke the FAL client FAL

Customer name. The value of the parameter is an Oracle Net service name. Oracle Net service name for this is presumed to have been at FAL

Right on the server system configuration, can point to FAL client (ie: This standby database).

Value range: Oracle Net service name of the string value.

Drs_start:

Description: so that Oracle can determine whether it should start the process of DRMON. DRMON is a fatal error will cause the Oracle background process,

Exist as long as the routine, the process exist.

Range: TRUE | FALSE.

Default value: FALSE

remote_archive_enable:

Description: Whether or not to control the implementation of long-range goal archived redo log files to operate. The parameter values must be set to "TRUE", so that Oracle

Routine database redo log files for remote archiving, and / or to receive remote archiving of redo log files.

Range: FALSE | TRUE

Default value: TRUE

standby_preserves_names:

Description: indicates that the database backup on the file name with the main database on the same file name.

Range: TRUE or FALSE. Note: If the value is set to True, and the standby database with the master database is located on the same system, then the main database file may be overwritten.

Default: FALSE.

Standby_archive_dest:

Description: designated from a main routine of the arrival of the archive log location. STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT

On standby at the location for virtual fully qualified name of the archive log file. The RFS on the standby database server will use the value, rather than

ARCHIVE_LOG_DEST.

Value range: NULL string or RAW types of effective path / device name.

Default: NULL

db_file_name_convert:

Description: the main database on a new data file is converted to the file name on the standby database filename.

Range: an effective master / backup directory and file name right.

Default value: None

max_enable_roles:

Description: Users can designate a role in opening the database (including sub-roles) the maximum number. Users can enable the role of the 2 are combined with the actual quantity

MAX_ENABLED_ROLES value, because each user has two additional roles: PUBLIC and the user's own role.

Value range: 0 -148

Default value: 20

O7_DICTIONARY_ACCESSIBILITY:

Description: mainly used for transplantation from Oracle7 to Oracle8i. If the value is TRUE, SYSTEM privileges (such as

SELECT ANY TABLE) would not limit the SYS program of the visit of the object (Oracle7 behavior). If the value of

FALSE, only to be granted SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE or

DELETE_CATALOG_ROLE permissions to access the SYS user of the object program.

Range: TRUE | FALSE

Default value: TRUE

sql92_security:

Note: To specify a reference to update or delete the value shown in the table with the required form of the class

SELECT permission.

Range: TRUE | FALSE

Default value: FALSE

audit_trail:

Description: enable or disable database auditing. If the parameter is TRUE or DB, audit records will be written into the SYS.AUD $ table; if the parameters for the OS,

Then write an operating system file.

Value range: NONE | FALSE | DB | TRUE | OS

Default: NONE

rdbms_server_dn:

Description: RDBMS servers were the only judge. It is used in an enterprise directory service to retrieve the role of enterprises. For more information, please see "Oracle

Advanced Security Administrator's Guide ".

Value range: Unique identification of all the X.500 format value.

Default value: None

os_roles:

Description: the operating system or database to determine whether the role for each user ID. If set to TRUE, the operating system will be fully managed database of all users granted the role. Otherwise,

Role identification and management of the database will be.

Range: TRUE | FALSE

Default value: FALSE

os_authent_prefix:

Note: Use the user's operating system account name and password to authenticate users to connect to the server. The value of the parameter with the user's operating system account to connect together. OS account to remove the prefix,

Please specify the null value.

Value range: Any identifier.

Default value: According to the operating system of the (usually 'OPS $')

object_cache_max_size_percent:

Description: Specifies the session object cache can grow more than the best percentage of cache size,

Best equal the size of the largest size with the size of the percentage of the product with the best. If the cache size exceeds this maximum size,

System will try to reduce the cache size to the best.

Value range: 0% to the operating system may be based on the Maximum.

Default value: 10%

object_cache_optimal_size:

Description: Specifies the cache size exceeds the maximum, the session object cache will be reduced to size.

Value range: 10K to be based on the maximum operating system.

Default: 100K

session_max_open_files:

Description: can be specified at any given session to open the maximum number of BFILE. Once this quantity,

Since then the conversation will not be able to open more documents. This parameter also depends on the operating system parameters MAX_OPEN_FILES.

Value range: 1 - at least (50, OS on the class MAX_OPEN_FILES).

Default value: 10

parallel_execution_message_size:

Note: Specify parallel execution (parallel query, PDML, restore and replication in parallel) the size of the message. If the value is greater than 2048 or 4096,

On the need for greater shared pool. If PARALLEL_AUTOMATIC_TUNING = TRUE,

Will be outside the storage pool in the designated message buffer.

Value range: 2148 - infinity.

Default values: If PARALLEL_AUTOMATIC_TUNING to FALSE, usually a value of 2148; PARALLEL_AUTOMATIC_TUNING as if TRUE, the value of 4096 (according to the operating system may be).

Paralle_min_percent:

Description: Parallel implementation of the requirements specified minimum percentage of the thread. Setting the parameters to ensure that parallel execution can be used in the absence of appropriate subordinate to the process of inquiry, it will display an error message,

And that the inquiry will be not implemented.

Value range: 0 -100

Default values: 0, indicated that he did not use the parameter.

Parallel_automatic_tuning:

Description: If set to TRUE, Oracle will control the parallel execution parameters to determine the default value. In addition to setting the parameters,

For the system you must also set up tables in parallel.

Range: TRUE | FALSE

Default value: FALSE

parallel_threads_per_cpu:

Description: Description of a CPU in the parallel implementation of the process or to deal with the number of threads,

And optimize parallel adaptive algorithms and load balancing algorithm. If your computer is in the implementation of a typical query has the signs of overload, the value should be reduced.

Value range: Any non-zero value.

Default: determined in accordance with the operating system (usually 2)

parallel_broadcast_enabled:

Description: By using a hash or merge link connection can be a number of large result sets will be connected to a small result set (in bytes rather than acts to measure the size of units)

Improve performance. If the value is set to TRUE, the optimizer can be a small result set of each line set to spread to large-scale database clusters for each line of treatment.

Range: TRUE | FALSE

Default value: FALSE

parallel_adaptive_multi_user:

Description: enable or disable an adaptive algorithm designed to enhance the use of parallel implementation of the performance of multi-user environment. Adopted by the system automatically reduces the load of requests for degree of parallelism,

Inquiries at start implementation of this feature. When PARALLEL_AUTOMATIC_TUNING = TRUE when its best.

Range: TRUE | FALSE

Default values: If PARALLEL_AUTOMATIC_TUNING = TRUE, then the value is TRUE; otherwise FALSE

parallel_max_servers:

Description: Parallel routine to specify a server or a parallel implementation of the maximum number of restore process. If necessary, the routine starts the query server's distribution will increase the number of quantity.

Value range: 0 -256

Default value: from CPU_COUNT, PARALLEL_AUTOMATIC_TUNING and determine PARALLEL_ADAPTIVE_MULTI_USER

parallel_min_servers

Description: designated as the startup routine after the parallel execution, Oracle server processes the query to create the smallest quantity.

Value range: 0 - PARALLEL_MAX_SERVERS.

Default value: 0

log_archive_dest_state_3:

Note: Specify the archive log parameters (only LOG_ARCHIVE_DEST_3) availability status. If this is enabled,

Goal will be archiving the log. If the delay, the goal will be excluded from the archiving operations until re-opening of the outside.

Range: ENABLE | DEFER

Default values: ENABLE

log_archive_dest_state_4:

Note: Specify the archive log parameters (only LOG_ARCHIVE_DEST_4) availability status. If this is enabled,

Goal will be archiving the log. If the delay, the goal will be excluded from the archiving operations until re-opening of the outside.

Range: ENABLE | DEFER

Default values: ENABLE

log_archive_dest_state_5:

Note: Specify the archive log parameters (only LOG_ARCHIVE_DEST_5) availability status. If this is enabled,

Goal will be archiving the log. If the delay, the goal will be excluded from the archiving operations until re-opening of the outside.

Range: ENABLE | DEFER

Default values: ENABLE

log_archive_dest_state_6:

Description: identify a specific goal of the recent log archive of user-defined status.

Range: ENABLE - effective if the target property, the goal the opening of archive log; DEFER - even if the goal of effective property, but also delay the treatment goal archive log; or ALTERNATE - archive log aim delayed treatment until the failure of another goal cause of this goal is automatically enabled (provided that the reserve goal of the property must be valid).

log_archive_dest_state_7:

Description: identify a specific goal of the recent log archive of user-defined status.

Range: ENABLE - effective if the target property, the goal the opening of archive log; DEFER - even if the goal of effective property, but also delay the treatment goal archive log; or ALTERNATE - archive log aim delayed treatment until the failure of another goal cause of this goal is automatically enabled (provided that the reserve goal of the property must be valid).

log_archive_dest_state_8:

Description: identify a specific goal of the recent log archive of user-defined status.

Range: ENABLE - effective if the target property, the goal the opening of archive log; DEFER - even if the goal of effective property, but also delay the treatment goal archive log; or ALTERNATE - archive log aim delayed treatment until the failure of another goal cause of this goal is automatically enabled (provided that the reserve goal of the property must be valid).

log_archive_dest_state_9:

Description: identify a specific goal of the recent log archive of user-defined status.

Range: ENABLE - effective if the target property, the goal the opening of archive log; DEFER - even if the goal of effective property, but also delay the treatment goal archive log; or ALTERNATE - archive log aim delayed treatment until the failure of another goal cause of this goal is automatically enabled (provided that the reserve goal of the property must be valid).

Log_archive_start:

Note: n in the database only in the "archive log" mode applies. It specified redo log is automatically or manually copy the. Recommended value is TRUE, that is, the implementation of automatic archiving;

Otherwise, it required manual intervention, the use of

ALTER SYSTEM ARCHIVE LOG ... order to prevent hang routines.

Range: TRUE | FALSE

Default value: FALSE

log_archive_duplex_dest:

Description: LOG_ARCHIVE_DEST outside the designated filing another goal. At Oracle8i Enterprise Edition, the parameters are

LOG_ARCHIVE_DEST_n replace.

Value range: A NULL string or any valid path or device name, except the original partition.

Default: NULL

log_archive_format:

Description: LOG_ARCHIVE_FORMAT only in the database is in "Archive Log" mode useful in the case. Text string with the variable% s (log sequence number)

And% t (thread number) used in combination, is used to specify the archive redo log files the only file names. The string was attached to

LOG_ARCHIVE_DEST parameters.

Value range: any valid file name.

Default value: According to the operating system may be

log_archive_max_processes:

Note: Specify the required number of ARCH process. If LOG_ARCHIVE_START = TRUE, the value can be evaluated at startup routines;

Can also, through SQL * Plus or SQL syntax ARCH process called assessment.

Value range: 1 - 10 (including endpoints) to any integer.

Default value: 1

log_archive_dest_2:

Description: The five local (designated LOCATION) or remote (designated SERVICE) goal in the second position,

Archived redo log files can be up at the location of these targets being copied. This parameter only oracle8i Database Enterprise Edition or higher version of the database effectively.

Value range: grammar for (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]

Default: NULL

log_archive_min_succeed_dest:

Note: the definition of coverage in the log file before copy it to the goal of a minimum quantity. This value should be greater than or equal to LOG_ARCHIVE_DEST_n Medium

The number of MANDATORY goal.

Value range: 1 - 5 (if LOG_ARCHIVE_DEST and share LOG_ARCHIVE_DUPLEX_DEST, they are restricted at 1-2).

Default value: 1

log_archive_trace:

Description: control the process of archiving the log output generated. This process can be activated through

Backgrounds ARCn process (in the log output is designated as ARCn)

Specific conversation Front Desk call process (in the output log designated as ARCH) or

"Managed standby database" of the remote file server (RFS) process.

Value range:

0: disable the archive log to track (This is the default setting)

1: follow-up redo log files archived

2: The goal of each archived log archive status tracking

4: Tracking archiving operational phase

8: archive logs to track activity goal

16: Tracking a detailed archive log activity goal

32: follow-up archive log parameters modified

64: ARCn the process of tracking activity status

Default value: 0

log_archive_dest_10:

log_archive_dest_3:

Description: The five local (designated LOCATION) or remote (designated SERVICE) goal in the third position,

Archived redo log files can be up at the location of these targets being copied. This parameter only oracle8i Database Enterprise Edition or higher version of the database effectively.

Value range: grammar for (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]

Default: NULL

log_archive_dest:

Description: The five local (designated LOCATION) or remote (designated SERVICE) goal in the third position,

Archived redo log files can be up at the location of these targets being copied. This parameter only oracle8i Database Enterprise Edition or higher version of the database effectively.

Value range: grammar for (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]

Default: NULL

log_archive_dest_state_2:

Note: Specify the archive log parameters (only LOG_ARCHIVE_DEST_2) availability status. If this is enabled,

Goal will be archiving the log. If the delay, the goal will be excluded from the archiving operations until re-opening of the outside.

Range: ENABLE | DEFER

Default values: ENABLE

log_archive_dest_5:

Description: The five local (designated LOCATION) or remote (designated SERVICE) goal in the fifth position,

Archived redo log files can be up at the location of these targets being copied. This parameter only oracle8i Database Enterprise Edition or higher version of the database effectively.

Value range: grammar for (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]

Default: NULL

log_archive_dest_6:

log_archive_dest_1:

log_archive_dest_8:

log_archive_dest_9:

log_archive_dest_state_1:

Note: Specify the archive log parameters (only LOG_ARCHIVE_DEST_1) availability status. If this is enabled,

Goal will be archiving the log. If the delay, the goal will be excluded from the archiving operations until re-opening of the outside.

Range: ENABLE | DEFER

Default values: ENABLE

log_archive_dest_state_10

Description: The goal specified archive log.

Value range: the local file system location (the location of the disk) or through the Oracle Net service name (tns Service) designated by the location of the remote archive. :

log_archive_dest_4:

Description: The five local (designated LOCATION) or remote (designated SERVICE) goal in the fourth position,

Archived redo log files can be up at the location of these targets being copied. This parameter only oracle8i Database Enterprise Edition or higher version of the database effectively.

Value range: grammar for (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]

Default: NULL

create_bitmap_area_size:

Description: CREATE_BITMAP_AREA_SIZE designated for the creation of bitmap indexing and distribution of memory capacity.

Range: In accordance with the operating system may be.

Default: 8 MB

pga_aggregate_target:

Description: Specifies the routine to connect to the goal of all server process PGA memory total. Automatically set in the opening of the work area before you set this parameter to a positive number. This part of memory does not reside in

Medium SGA. Database used for this parameter value of the goal it uses the volume of PGA memory. Set this parameter can be used to SGA from Oracle

Routine by subtracting the total system memory. And the remaining amount of memory can be allocated to the pga_aggregate_target.

Range: integer add the letters K, M or G, to this limit is designated as kilobytes, megabytes or gigabytes. The minimum is 10M, Maximum for the 4000G

Default value: "not specified", said the ban completely the use of automatic optimization of the work area.

Sort_area_size:

Description: SORT_AREA_SIZE in bytes, specify the sort used by the largest amount of memory. Sort is completed, all will be back,

And memory will be released. Increase the value to increase the efficiency of large-scale sequencing. If more than the amount of memory will be above the use of temporary disk.

Value range: the equivalent of six database block value (minimum) to the operating system to determine the value of (Maximum).

Default value: According to the operating system may be

sort_area_retained_size

Description: in bytes, specified in a sort of running after the user to retain the overall area (UGA) of the Maximum amount of memory. The last line was from the sort of space after extraction,

The memory will be released back to UGA, but not released to the operating system.

Value range: from the equivalent value of the two databases to block SORT_AREA_SIZE value.

Default: SORT_AREA_SIZE value

bitmap_merge_area_size:

Description: Specifies the index of the merger from a range of scanning and retrieval of digital map to be the amount of memory to be used.

Value range: According to system.

Default: 1MB

workarea_size_policy:

Description: the size of the designated work area adjust strategy. Optimization of this parameter to control the work area where the model.

Range: AUTO, MANUAL.

Default: If set PGA_AGGREGATE_TARGET, compared AUTO; otherwise MANUAL.

Hash_area_size:

Description: with parallel DML or DDL operations and related statements. It is in bytes, specify the connection to be used for hashing the largest amount of memory. For more information,

Please refer to the manual Oracle8i Concepts.

Value range: 0 to be in accordance with the value of the operating system.

Default value: derivative: 2 * SORT_AREA_SIZE parameter values

db_create_online_log_dest_4:

Description: set up an online log created and control the default file location. If you create an online log or control file does not specify a file name, then use the default file name.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

db_create_online_log_dest_3:

Description: set up an online log created and control the default file location. If you create an online log or control file does not specify a file name, then use the default file name.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

Db_files:

Note: You can open the database the maximum number of data files.

Value range: MAXDATAFILES - determined in accordance with the operating system.

Default value: According to the operating system may be (in the Solaris system for the 200)

db_create_online_log_dest_2:

Description: set up an online log created and control the default file location. If you create an online log or control file does not specify a file name, then use the default file name.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

db_create_online_log_dest_1:

Description: set up an online log created and control the default file location. If you create an online log or control file does not specify a file name, then use the default file name.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

Db_create_file_dest:

Description: Set the created data files, control files and online logs of the default location.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

Control_files:

Description: Specify one or more control file name. Oracle recommended for different device or OS image using multiple document files.

Value range: 1 - 8 file name (with path names).

Default value: According to the operating system may be

db_create_online_log_dest_5:

Description: set up an online log created and control the default file location. If you create an online log or control file does not specify a file name, then use the default file name.

Value range: the file system directory name. The directory must already exist. The directory must have at one of Oracle can create a file permission.

Large_pool_size:

Note: Specify the distribution of a large pool heap size, it can be used for shared server session memory, parallel execution for message buffers, as well as for RMAN

Backup and restore disk I / O buffer.

Value range: 600K (minimum);> = 20000M (Maximum are determined in accordance with the operating system).

Default values: 0, unless parallel execution or configured DBWR_IO_SLAVES

shared_pool_size:

Description: in bytes, specify the size of shared pool. Such as shared pool contains: cursor sharing, storage, process,

And parallel control structures such as the implementation of message buffer object. Larger values can improve the performance of multi-user systems.

Value range: 300 KB - determined in accordance with the operating system.

Default value: If it is 64-bit operating system, a value of 64MB; other cases, the value of 16MB.

Java_pool_size:

Description: in bytes, specify the Java memory pool size, it used to store Java method and class definition in the shared memory of that law,

And call at the end of the conversation migrated to Java object Java space. For more information, please refer to Oracle8i Java

Developer's Guide.

Range: In accordance with the operating system may be.

Default value: According to the operating system may be

shared_pool_reserved_size:

Description: want to specify a larger memory requests for shared pool reserved space,

In order to avoid debris caused by the decline in performance. The size of the pool should be in accordance with such conditions: the object can be stored to prevent the update from the shared pool and common requirements of all major process and package.

Value range: SHARED_POOL_RESERVED_MIN_ALLOC to

SHARED_POOL_SIZE half (bytes).

Default: SHARED_POOL_SIZE value 5%

cursor_sharing:

Description: Control can be shared to share the same termination of the cursor types of SQL statements.

Value range:

Mandatory: mandatory sentences to express meaning in different ways the same but share a cursor statement.

EXACT: the only identical SQL statements to share a cursor.

Default: EXACT

open_cursors:

Note: the designation of a conversation can open a cursor (environment area) the largest quantity, and limit the PL / SQL used in PL / SQL

The size of the cursor cache, to avoid the statement again when the user re-analysis of grammar. Please set the value high enough,

Application in order to prevent depletion of the cursor open.

Value range: 1 - operating system limit.

Default value: 64

serial_reuse:

Description: Specifies what type of SQL cursors should make use of serial memory function reusable. If CURSOR_SPACE_FOR_TIME = TRUE,

SERIAL_REUSE values will then be ignored, as is set to

DISABLE or NULL handle the situation.

Value range: DISABLE | SELECT | DML | PLSQL | ALL | NULL

Default: NULL

session_cached_cursors:

Description: Specify the session cache the number of the cursor. On the same SQL statement many times grammar analysis,

Cursor of the conversation it will be moved to the session cursor cache. This will shorten the time for the analysis of grammar, because the cursor cache was no need to be re-opened.

Value range: 0 to be in accordance with the value of the operating system.

Default value: 0

cursor_space_for_time:

Note: the cursor in a shared SQL areas cited, determined to remain in the Shared SQL area from the pool or by treatment expired.

Range: TRUE | FALSE

Default: FALSE (expired)

rollback_segments:

Description: designated to start the process of routine access to one or more Rollback segment, even if the quantity more than TRANSACTIONS /

TRANSACTIONS_PER_ROLLBACK_SEGMENT value. The formula for ROLLBACK_SEGMENTS =

(rbseg_name [, rbseg_name] ...)

Value range: In addition to SYSTEM outside, DBA_ROLLBACK_SEGS listed in paragraph any rollback.

Default: NULL (default Rollback segment to use the public)

undo_suppress_errors:

Description: At SMU mode allows the user to try to implement RBU operations (such as

ALTER ROLLBACK SEGMENT ONLINE) ignoring the error. This will enable users of all applications and script to switch to SMU

SMU before a pattern of features to use.

Range: True or False

Default value: False.

Max_rollback_segments:

Description: Medium designated SGA cache Rollback segment size of the largest. The numerical designation of a routine that can be online at the same time maintaining the status (that is for status INUSE)

Rollback segment of the largest quantity.

Range: 2 -65535

Default value: Maximum (30, TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT)

undo_retention:

Description: UNDO_RETENTION

Parameters used to specify the database to retain the information has been submitted to the total amount of the withdrawal. Startup routine can be set at the parameter values. Can be calculated to meet the requirements of the withdrawal of reservations to withdraw the amount of space:

UndoSpace = RD * UPS, one of UndoSpace to block withdrawal of the number of express, RD for the second unit

UNDO_RETENTION to express, UPS few per second to indicate the block to withdraw.

Value range: the maximum allowed for the (2 ** 32) second.

Default value: 30 seconds.

Transactions_per_rollback_segment:

Description: Rollback segment designated for each of the permit the number of parallel transaction processing. Start of the Rollback segment to obtain the minimum number of TRANSACTIONS

Parameter value divided by the result. If the named parameter in the other ROLLBACK_SEGMENTS Rollback segment, you can access these Rollback segment.

Value range: 1 - In accordance with the operating system may be.

Default value: 5

undo_management:

Description: Specifies the system should either withdraw the use of space management. If set to AUTO, the routine will be SMU mode. Otherwise will be RBU mode. At

RBU mode, undo space will be back as the same paragraph at the external distribution. At SMU mode, undo space as the undo tablespace will be the same as at the external distribution.

Range: AUTO or MANUAL

Default values: If the first start, when neglect of routine UNDO_MANAGEMENT parameters will use the default value of MANUAL, and routines will be RBU mode. If this is not the first routine will be activated by other existing routines used to start the withdrawal mode routines.

Undo_tablespace:

Description: undo tablespace used to store undo information only. UNDO_TABLESPACE only permit withdrawn in the system management (SMU)

Mode. Routine will be used to withdraw the specified table space, . If the table space does not exist, or table space is not withdrawn,

Or are being used by another routine, the routine will fail STARTUP.

Default value: Each database contains 0 or more undo tablespace. At SMU mode, for each distribution of a routine ORACLE (and only one) undo tablespace.

Instance_name

Note: use the same routine in a number of cases of Services to uniquely identify a database routines. INSTANCE_NAME should not be confused with the SID,

It is in fact at a host of shared memory on the unique identifier in all routines.

Value range: Any alphanumeric characters.

Default value: database SID

service_names:

Description: Oracle Net listener is used to identify a service (such as: copy of a specific environment database)

The routine specified service name. If the service is not the domain will be DB_DOMAIN additional parameters.

Grammar: SERVICE_NAMES = name1.domain, name2.domain

Default: DB_NAME.DB_DOMAIN (if defined)

local_listener:

Description: a list of Oracle Net addresses for Oracle Net logo

Where the listener on the same computer database routines. Scheduling of all routine procedures and monitoring procedures on the register in order to enable the client to connect. The parameters of coverage at

Version 8.1 and MTS_LISTENER_ADDRESS abandoned MTS_MULTIPLE_LISTENERS parameters.

Range: A valid Oracle Net address list.

Default: (ADDRESS_LIST = (Address = (Protocol = TCP) (Host = localhost) (Port = 1521)) (Address = (Protocol = IPC) (Key = DBname)))

license_sessions_warning:

Description: Specifies the number of concurrent user sessions warning limit. After that limit is reached, other users can still connect, but an ALERT message will be written into the document. With

RESTRICTED SESSION users will receive a warning message that the system is near maximum capacity.

Value range: 0 - LICENSE_MAX_SESSIONS

Default value: 0

license_max_sessions:

Description: designated permit concurrent users at the same time the maximum number of sessions. After that limit is reached only with RESTRICTED SESSION

Users can connect to the server. All other users will receive a warning message that the system has reached maximum capacity restrictions.

Value range: 0 - number of session licenses.

Default value: 0

license_max_users:

Description:

You can specify the database to create the maximum number of users. Parallel session and user licensing should not be permitted to use the opening at the same time. LICENSE_MAX_SESSIONS

Or LICENSE_MAX_USERS or both, the value should be zero.

Value range: 0 - number of user license.

Default value: 0

db_block_checksum:

Description: DBWn, ARCH and SQL * loader Whether or not the data for each block read or write or checksum calculation of the checksum block.

Range: TRUE | FALSE

Default value: FALSE

shadow_core_dump:

Description: One of the parameters for UNIX, SGA is used to specify whether or not to dump information to a core file generated. If set to FULL, SGA

Will be included in the core dump. If set to PARTIAL, SGA will not be dump.

Value range: FULL | PARTIAL

Default: FULL

db_block_checking:

Description: Whether or not used to control transaction processing to check whether the damaged block management.

Range: TRUE | FALSE

Default value: FALSE

background_core_dump:

Description: Whether or not the SGA information to a dump file generated by the core (for UNIX).

Value range: FULL | PARTIAL

Default: FULL

event:

Description: from the use of Oracle technical support personnel to debug the system. Under normal circumstances, should not change the value.

Value range: not available.

Default value: None

user_dump_dest:

Description: for the server process will be a user identity in one of the trace file into the debug directory of the specified path name. For example, the directory can be set up to do: NT operating system on the C: /

ORACLE / UTRC; UNIX operating system on the / oracle / utrc; or VMS operating system on the

DISK $ UR3: [ORACLE.UTRC].

Range: A valid local pathname, directory, or disk.

Default value: According to the operating system may be

timed_statistics:

Description: The time to collect the operating system information, such information can be used to optimize the database and the SQL statement. To prevent the request from the operating system time overhead caused by,

Please set the value to zero. Set the value to TRUE for a long time to view the progress of the operation is also useful.

Range: TRUE | FALSE

Default value: FALSE

timed_os_statistics:

Description: by the system administrators to collect statistical information on the operating system. For efficient use of resources only when needed to provide the value. For dedicated servers,

Users will be connected and disconnected from the pop-up call (if it exceeded the time limit specified), the operating system to gather statistical information. For shared server,

Will pop into or call the collection of statistical information.

Value range: The second time as a unit.

Default value: 0 (operating system statistics are not broke)

core_dump_dest:

Description: Specifies the location of the core dump directory name (for UNIX).

Range: Any valid directory name.

Default: ORACLE_HOME / dbs

oracle_trace_collection_name:

Description: Oracle Trace collection name specified in the output file name to use (the definition of a document collection. Cdf and data collection documents

. dat). If the parameter is empty, and ORACLE_TRACE_ENABLE = TRUE, it will start a default

Oracle Trace collection, once again until the value is set to NULL.

Range: an effective collection name, up to 16 characters (eight characters using a platform other than the file name).

Default: NULL

oracle_trace_collection_path:

Note: Specify the Oracle Trace collection definition file (. Cdf) and data collection file (. Dat) where the directory path name.

Value range: a complete directory path name.

Default value: According to the operating system may be (usually the ORACLE_HOME / otrace / admin / cdf)

background_dump_dest:

Description: Specifies the operation for the Oracle background processes (LGWR, DBW n and so on) into the trace file path name

(Directory, or disk). It also recorded an important case definition and warning message database file.

Range: Any valid directory name.

Default: ORACLE_HOME / rdbms / log (determined in accordance with the operating system)

oracle_trace_enable:

Description: To enable Oracle Trace collection of server, please set the value to TRUE. If set to TRUE, the server can use

Oracle Trace. To start a collection process for ORACLE_TRACE_COLLECTION_NAME

Designate a non-empty value; or use the Oracle Trace Manager to start a collection.

Range: TRUE | FALSE

Default value: FALSE

oracle_trace_collection_size:

Description: in bytes, Oracle Trace collection file specified maximum size. Once the size of the collection of documents to achieve this maximum,

Collection will be disabled. Zero if the specified range, it means there is no size limit.

Value range: 0 -4294967295

Default value: 5242880

oracle_trace_facility_path:

Note: Specify the Oracle TRACE instrument definition file (. Fdf) where the directory path name.

Value range: a complete directory path name.

Default: ORACLE_HOME / otrace / admin / fdf / (depending on your operating system may be)

sql_trace:

Description: Disable or enable the SQL tracking devices. If set to TRUE, will be optimized to collect information useful to improve performance. Because of the use of SQL

Tracking devices will lead to system overhead, it should only be in the information necessary to optimize the use of TRUE.

Range: TRUE | FALSE

Default value: FALSE

oracle_trace_facility_name:

Description: Oracle Trace product definition specified file name (. Fdf file name). This file contains for the use of Oracle Trace

API data collection products to collect all of the case definition and data information. Oracle recommend using the default file ORCLED.FDF.

Range: an effective device name, up to 16 characters.

Default: oracled

max_dump_file_size:

Description: Specifies the maximum of each trace file size. If you are concerned about trace file will take up too much space and may be changed by the restrictions. Dump file can be achieved if the maximum allowable size of the operating system,

Please specify the value as "unlimited."

Value range: 0 - unlimited (can use 'K' or 'M' as a unit)

Default value: 10,000

resource_limit:

Description: to determine whether a summary of the document in the database implementation of resource constraints. If set to FALSE, the resource constraints will be disabled. If the value is TRUE, ie opening of the resource constraints.

Range: TRUE | FALSE

Default value: FALSE

resource_manager_plan:

Description: If you specify this value, resource management plans and will activate all the sub-routine items (sub-plans, instructions and user group). If you do not specify, the resource manager will be disabled,

However, the use of

ALTER SYSTEM command can also be enabled.

Range: Any valid string.

Default: NULL

sessions:

Description: Specifies the user session and systems of the total session. The default quantity is greater than PROCESSES, to allow recursive conversation.

Value Range: any integer value.

Default value: derivative (1.1 * PROCESSES + 5)

java_soft_sessionspace_limit:

Description: in bytes, specified in Java using the Java session memory 'soft constraints'. If the duration of the user's session Java

Status of the use of too much memory, Oracle will generate a warning to the trace file to write a message.

Value range: 0 - 4GB

Default value: 0

cpu_count:

Note: Oracle of the number of available CPU (used to calculate the other parameter values). Do not change the value.

Value range: 0 - unlimited.

Default value: automatically set by Oracle

java_max_sessionspace_size:

Description: in bytes, specified for the server running Java program used by the largest amount of memory. Database used to store each time it calls the Java

状态. If the duration of the user's session Java state exceeds the value, the conversation will be terminated because of insufficient memory.

Range: In accordance with the operating system may be.

Default value: 0

processes:

Description: At the same time designated to connect to an Oracle Server process on the operating system, the maximum number of users. This value should be allowed to carry out all the background processes, such as:

Job queue (SNP) and parallel implementation of the process (Pnnn) process.

Value range: 6 to operating system may be based on a value.

Default value: PARALLEL_MAX_SERVERS determined by

control_file_record_keep_time:

Description: control file could be re-used parts of the records must be retained in the shortest time (days).

Value range: 0 -365

Default value: 7

log_checkpoint_timeout:

Note: a check from the designated point of maximum time interval (seconds). The time value is designated as 0,

Will be disabled in order to time-based checkpoints. A lower value can shorten recovery time routine, but it may cause excessive disk operations.

Value range: 0 - unlimited.

Default: Oracle8i: 900 second. Enterprise Edition: 1800 seconds

recovery_parallelism:

Description: Specifies the media to participate in routine or the number of restore process. If the value is 0 or 1, it means that a process will be to restore the serial manner.

Range: In accordance with the operating system may be (should not exceed PARALLEL_MAX_SERVERS).

Default value: According to the operating system may be

fast_start_mttr_target:

Description: Specifies the database from a single routine in the collapse of the time required to restore (the estimated number of seconds). FAST_START_MTTR_TARGET

Will be converted to in-house a set of parameters, to modify the operation of the database in order to restore its control in the total time "the average time to restore (MTTR)"

The scope of the must. Only a "Quick Start Fault Recovery" version features support for this parameter.

Value range: [0, 3600]. It will calculate the data buffer cache entries above, and log more than the maximum number of block limits.

Default value: 0

log_checkpoints_to_alert:

Description: check points will be assigned to the early warning information document records. The parameters for determining whether the requirements of checkpoint frequency useful.

Range: TRUE | FALSE

Default value: FALSE

fast_start_io_target:

Description: Specifies the system crash or during routine restore the required I / O quantity. The value DB_BLOCK_MAX_DIRTY_TARGET

Recovery process for more precise control.

Value range: 0 (disable I / O Recovery restriction), or 1000, to cache all of the buffer number.

Default value: all the cache buffer in the number of

log_checkpoint_interval:

Description: At designated checkpoints before there must be written into the redo log files in the OS block (rather than the database block) of the quantity. No matter how this value,

At log switch checkpoint will occur. A lower value can shorten the time required to restore routine, but it may cause excessive disk operations.

Range: unlimited (0 to disable the specified parameters).

Default value: According to the operating system may be

log_buffer:

Description: in bytes, specified in the LGWR to write redo log entries before the redo log files,

Cache these entries for the amount of memory. Redo entries in the database block to retain a record of the change. If the value is greater than 65536, you can reduce the redo log file I /

O, there is a long time especially in the affairs or deal with a large number of transaction processing systems.

Range: In accordance with the operating system may be.

Default value: Maximum for the 500K or 128K * CPU_COUNT, whichever is the greater access

read_only_open_delayed:

Description: used to accelerate some operations, such as: a great database to start one of the most data is stored on read-only table space. If set to TRUE,

Read from the table space data will be the first to visit the read-only table space data file. Of the possible side-effects, see the Server Reference

manual.

Range: TRUE | FALSE

Default value: FALSE

parallel_instance_group

Description: a cluster database parameters, logo used to generate a large number of concurrent parallel execution routine group affiliation. Parallel operation only in its INSTANCE_GROUPS

Parameter to specify a matching group produced a large number of parallel execution routine subordination.

Value range: a representative group of the string.

Default value: all the current composition of the group of routine activity

parallel_server:

Description: will PARALLEL_SERVER set to TRUE, the database cluster option can be enabled.

Range: TRUE | FALSE

Default value: FALSE

parallel_server_instances:

Description: The routine is currently the number of configurations. SGA is used to determine the structure of its size, the structure of the routine configured to determine the quantity. Setting the right parameters will improve the SGA

Memory usage. Some have used the number of parameters are calculated.

Value range: Any non-zero value.

Default value: 1

thread:

Description: a cluster database parameters for the routines for each assigned a unique number of the redo thread. Redo thread in the routine is disabled,

The routine can not start. If the value is zero, this indicates that there is a common thread is enabled to choose from.

Value range: 0 - has enabled the maximum number of threads.

Default value: 0

gc_files_to_locks:

Description: a cluster database parameters will be used to control parallel cache management (PCM) lock is mapped to the data files.

Grammar: GC_FILES_TO_LOCKS = 'file_list = lock_count [! Blocks] [R] [EACH ][:...]'

Default: NULL

instance_number:

Description: a cluster database parameters used to specify a unique number to the routine use of mapping to a FREELIST GROUPS

Storage parameters to create the database objects owned by the free list. At

ALTER TABLE ... the INSTANCE value of the use of the clause. ALLOCATE EXTENT

Statement can be dynamically allocated area of the routine.

Value range: 1 to MAX_INSTANCES (to create the database specified).

Default value: the smallest number of available (depending on the order of startup routine and other routines INSTANCE_NUMBER specified value)

active_instance_count:

Description: two routines in the permit constitutes a cluster of a routine designated as the main routine, and the other for the sub-routine routine. If the cluster contains more than two of the routines,

This parameter has no effect.

Value range: 1 or> = set of routines in the number of clusters.

Default value: None

lock_name_space:

Description: Specifies the Distributed Lock Manager (DLM) used to generate lock names namespace. If a cluster with the same name on there is a backup database or clone database

Set the value of the best.

Value range: there is a maximum of eight characters, special characters are not allowed.

Default: NULL

max_commit_propagation_delay:

Description: a cluster database parameters for the SGA to designate a routine included in the system change number (SCN) has been set before LGWR,

Permit to retain most of the time (to 1 / 100 seconds). This is a very small change of the performance parameters required, because SCN

Will not be possible based on the time to refresh.

Value range: 0 - 90000

Default value: 700

instance_groups:

Description: a cluster database parameters, you can use a comma-separated list,

The current routine is assigned to a specific group. Distribution for the parallel query slave process to operate when used in routine group.

Value range: a series of group names, separated by commas.

Default: NULL

buffer_pool_recycle:

Description: cycle pool size (from DB_BLOCK_BUFFERS distribution). The purpose is to use the object after its removal in order to re-use memory.

Value range: a specific string value (for example: buffers: 50, lru_latches: 1).

Default value: None

use_indirect_data_buffers:

Description: Control can support more than 4GB of physical memory of 32-bit platform for the expansion of the buffer cache on the use of mechanisms. In other platforms, this parameter will be ignored.

Range: TRUE | FALSE

Default value: FALSE

disk_asynch_io:

Description: Control of data files, control files and log files of the I / O is asynchronous (that is, in the table in the process of scanning process is overlap I / O and CPU

Request). Only in your platform supports asynchronous disk I / O in order to change the circumstances of the parameters.

Range: TRUE | FALSE

Default value: TRUE

dbwr_io_slaves:

Description: DBW0 the process of using the I / O quantity subordinate. Process and its subordinate DBW0 always write to the disk.

Value range: 0 - In accordance with the operating system may be.

Default value: 0

db_8k_cache_size:

Description: Specifies the size of cache for 8K buffers. Only db_block_size at 8K when the value is not in order to set this parameter.

Value range: 0M, or at least 16M. The value of block size to satisfy the specific requirements of a specific platform.

Default: 0M

db_4k_cache_size:

Description: Specifies the size of cache for 4K buffers. Db_block_size only when the value is not 4K in order to set this parameter.

Value range: 0M, or at least 16M. The value of block size to satisfy the specific requirements of a specific platform.

Default: 0M

db_block_buffers:

Description: buffer cache Oracle block the number. This parameter will be a significant impact on the total size of SGA routine.

Value range: 4 - may be in accordance with the operating system.

Default value: 32768

db_32k_cache_size:

Description: Specifies the size of cache for 32K buffers. Only db_block_size at 32K when the value is not in order to set this parameter.

Value range: 0M, or at least 16M. The value of block size to satisfy the specific requirements of a specific platform.

Default: 0M

db_block_size:

Description: an Oracle database block size (bytes). The value set in the creation of the database, but can not be changed thereafter.

Value range: 1024 - 65536 (depending on your operating system may be).

Default value: 2048 (based on the operating system may be)

db_cache_advice:

Description: for a variety of capacity is expected to conduct cache enable and disable the collection of statistical information. V $ DB_CACHE_ADVICE view at the collection of information.

Value range: OFF - turn off the consultation and does not allocate memory for the advisory; ON - Open consultative (ie: will lead to CPU and memory overhead); READY - turn off the consultation, but to retain the memory allocated to the advisory.

Default value: OFF

db_2k_cache_size:

Description: Specifies the size of cache for 2K buffers. Db_block_size only when the value is not 2K in order to set this parameter.

Value range: 0M, or at least 16M. The value of block size to satisfy the specific requirements of a specific platform.

Default: 0M

db_cache_size:

Note: In order to specify the standard block cache buffer size.

Range: At least 16M.

Default: 48M

db_16k_cache_size:

Description: Specifies the size of cache for 16K buffers. Only db_block_size at 16K when the value is not in order to set this parameter.

Value range: 0M, or at least 16M. The value of block size to satisfy the specific requirements of a specific platform.

Default: 0M

db_file_multiblock_read_count:

Description: At a fully involved in a continuous scanning I / O operation to read the maximum number of the block.

Range: In accordance with the operating system may be.

Default value: 8

db_keep_cache_size:

Description: KEEP buffer pool specified number of the buffer zone. KEEP buffer pool size is the size of the main block (ie db_block_size

The definition of block size).

Value range: 0 or at least one block size (a smaller value will be automatically rounded up to block size).

Default: 0M

db_recycle_cache_size:

Description: Specifies the size of the RECYCLE buffer pool. RECYCLE buffer pool size is the size of the main block.

Value range: 0 or at least one block size (a smaller value will be automatically rounded up to block size).

Default: 0M

db_writer_processes:

Description: Writing a routine process in the initial database quantity. If you are using a DBWR_IO_SLAVES, only the use of a database write process.

Value range: 1 - 10

Default value: 1

buffer_pool_keep:

Description: reservation pool size (from DB_BLOCK_BUFFERS distribution). The aim is to retain objects in memory to reduce I / O.

Value range: a particular string value (for example: buffers: 400, lru_latches: 3).

Default value: None

Oracle Database initialization parameter changes to note 2009-06-26 06:38:13
oracle books 2009-04-02 03:10:57
OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ adjustment 2009-09-24 18:44:39
The new initialization parameters 2009-11-04 23:29:20
RMAN said, I can back up (6) - RMAN backup of a backup set of initialization parameters and 2010-01-26 19:39:21

Paste data

kods.net — Sat, 28 Feb 2009 14:04:30+0000

Umpdp into use:

Backup file. Dmp on d: \ sd directory

First of all system users visit

create directory dump_dir as' d: \ sd ';

Exit

The implementation of impdp system / password dumpfile =. dump directory = dump_dir

oracle database to start and stop 2009-11-06 19:58:14
oracle under linux Log in 2009-11-19 07:27:56
oracle data types 2009-09-28 01:34:13
Data Warehouse I have a little understanding of certain aspects of 2009-08-06 14:37:00
EXPDP and IMPDP instructions 2009-11-12 15:09:38

ORACLE 9I SYS authentication methods

kods.net — Sat, 28 Feb 2009 07:45:12+0000

(1) REMOTE_LOGIN_PASSWORDFILE = none and SQLNET.AUTHENTICATION_SERVICES = none:

oracle user to install a local sqlplus "/ as sysdba" can not log in

Non-oracle user to install a local sqlplus "sys / change_on_install as sysdba" can not log in

Oracle user to install long-range non-sqlplus "/ as sysdba_on_install @ sid as sysdba" can not log in

(2) REMOTE_LOGIN_PASSWORDFILE = exclusive and SQLNET.AUTHENTICATION_SERVICES = none:

oracle user to install a local sqlplus "/ as sysdba" can not log in

Non-oracle user to install a local sqlplus "sys / change_on_install as sysdba" to log in

Oracle user to install long-range non-sqlplus "/ as sysdba_on_install @ sid as sysdba" to log in

(3) REMOTE_LOGIN_PASSWORDFILE = none and SQLNET.AUTHENTICATION_SERVICES = (NTS):

oracle user to install a local sqlplus "/ as sysdba" to log in

Non-oracle user to install a local sqlplus "sys / change_on_install as sysdba" can not log in

Oracle user to install long-range non-sqlplus "/ as sysdba_on_install @ sid as sysdba" can not log in

(4) REMOTE_LOGIN_PASSWORDFILE = exclusive and SQLNET.AUTHENTICATION_SERVICES = (NTS):

oracle user to install a local sqlplus "/ as sysdba" to log in

Non-oracle user to install a local sqlplus "sys / change_on_install as sysdba" to log in

Oracle user to install long-range non-sqlplus "/ as sysdba_on_install @ sid as sysdba" to log in

sysdba 2009-09-21 16:10:03
Operating system (OS) authentication and Password 2009-09-20 03:19:42
oracel verification process 2009-08-21 09:32:55
Oracle database password file to use and maintenance of 2009-12-21 06:54:13
[Change] Oracle data types and storage methods 2009-11-25 19:53:56

RAC LOG

kods.net — Fri, 27 Feb 2009 12:32:50+0000

admin => Record number of summary information
alertgnd-rac01.log => Change Record node status crs some time to outline the information in detail to look at css log
client => Record crs initialization, ocr application including: CLSCFG, CSS, OCRCHECK, OCRCONFIG, OCRDUMP and OIFCFG
crsd => Record crsd related to the log, crs into the fatal Wait for css mode, then start crsd start resource-related
cssd => Record cssd related to the log, the node to stop, start, reconfig, etc., all questions will be recorded, the most important log
evmd => Record evmd log
racg => Record ons, vip relevant log

Generally speaking, look at the problem encountered ocssd.log, then according to time and will be required to view the log crsd that all resources are in the log crsd.log, another key to see if the log information, you can put the relevant module log level increase (the different versions do not like the default log level)

ORA-01452

kods.net — Fri, 27 Feb 2009 07:32:41+0000

ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

SQL> delete ZJDA.ZJ_T_YJFP where rowid not in (select min (rowid) from ZJDA.ZJ_T_YJFP group by C_JGBH, C_SJLX, N_SJHM, N_GS);

214789 rows deleted

SQL>
SQL> create UNIQUE index ZJDA.ZJ_T_YJFP_PK on ZJDA.ZJ_T_YJFP (C_JGBH,
2 C_SJLX,
3 N_SJHM,
4 N_GS) tablespace ZJDADATA;

Index created

SQL>

Using SQL TRACE and TKPROF

kods.net — Thu, 26 Feb 2009 21:36:37+0000

Can type in the operating system to obtain tkprof all available options and output options to sort a list of note has

Sort Option Description
call analysis prscnt execnt fchcnt the implementation of the number of extraction
Analysis of the implementation of prscpu execpu fchcpu extract CPU time occupied by
Analysis of the implementation of prsela exela fchela the time to extract the
prsdsk exedsk fchdsk extracted during the analysis of the implementation of the number of disk read
prsqry exeqry fchqry analysis of the implementation period for the continuous extraction buffer to read a few
prscu execu fchcu analysis during the implementation of extraction buffer for the current number of read
Analysis prsmis exemis library cache during the implementation of the number of non-hit
exerow fchrow analysis of the implementation of the treatment period the number of rows
Analysis of cursor userid user ID of the user

TKPROF statistics
• Count: the implementation of call number
• CPU: CPU usage in seconds
• Elapsed: total time spent
• Disk: the number of physical reads
• Query: the logic of continuing to read to read a few
• Current: the current mode logic to read a few
• Rows: the number of rows have been processed

TKPROF statistics

Statistical meaning of
Count analysis or implementation as well as the number of statements issued statements calling the extract a few
CPU processing time for each stage in seconds if the shared pool to find the expression for the analysis phase to 0
Elapsed time in seconds occupied units are generally not very useful because the time occupied by other processes
Disk from the physical database file to read data block buffer if the data is very low then the statistics may be
Search Query for continuing to read the logic of the buffer used in SELECT statement
Current search in the current mode logic buffer used in DML statements

Rows statement by the external treatment of the line for the SELECT statement in the extraction stage show it at the implementation stage of DML statement that it

Query and Current access to the sum of the logic for the total number of buffer

SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
RedHat AS 3 at oracle10.2.1 install document 2009-03-13 16:56:34
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28

High Availability Oracle Flashback

kods.net — Thu, 26 Feb 2009 17:23:02+0000

Brief introduction

Flashback Database is a point in time (PIT) restore the database approach. This incomplete recovery strategy can be used to restore the logic because of human error cause damage to the database. At the introduction of 10g, it is designed to shorten the recovery time and maximum availability.

Restore the traditional vs. Reproduce the database

The first cause downtime reason for this is that the logic of human error cause damage, it has been widely recognized. Examples of damage on the logic from the user the right to update the data and the interception table, run the batch error mission to disrupt the order of 2 times or so on. The results are the same - a database damaged, and wide-ranging and difficult to identify. Oracle adopted two strategies to bring the database back to a previous time point: the traditional database recovery and reproducibility.

Are not completely restore the database to restore the previous status of a restore. This process has two steps: re-stored data, and restore services activity forward to a time you want. Recovery and reproducibility of traditional database is the main difference between the traditional re-Recovery from the storage of all data files begin and then want to restore to a restore time, and reproduce the damaged database is in storage after being changed through the re - to back the operation of the block. From this point of view of the problem, let us think about 10TB in a database on, there is damage to 1MB of data. Recovery from the traditional re-start the application store 10TB of data start, and to reproduce it is to recover this database applications 1MB of data in order to reach that point before the damage. Now we look at each strategy separately.

Traditional Recovery

Oracle 10g at before because of human error will cause the problem to a database restore to a previous point in time the only option is to restore the traditional. This policy includes removed from the backup and restart the database to store all data files, and then the implementation of forward you want to restore to a point in time. Recovery can be based on the media server (RMAN), can also be based on the user (operating system tools). Restore from backup and re-store all the data files and redo log rollback forward to hope and finish point in time. Recovery time is proportional to the size of the database, rather than the required number of changes to restore. This means that the recovery time (MTTR) with the database in fact the growth of ever-increasing scale.

Reproduce the database

In Oracle 10g, the reproducibility of a new technical features, known as the Flashback Database (repeat database) as an alternative to traditional restore introduced. Reproduce the database allows you to quickly restore the entire database to a previous point in time without the need to re-store from a backup database. Often in the database has been described as reverse button, and it is only those who have been modified to block the data you want to restore to prior to the recovery time. Redo then applied to change the record to achieve the desired point in time restore. This data has been modified to reproduce the log block is called.

Reproduce the data provided in relation to the traditional advantages of the database is very obvious. Database for the analysis is not so obvious advantage. In the data warehouse, the operation of block is usually not recorded in log mode. Reproduce at the database, as long as the database log files are running mode, it can block the operation back to a previous state, because the block can be modified and removed through the implementation of the restoration operation.

Configured to reproduce the database

The following example demonstrates the command-line configuration. This can also be used to complete the Enterprise Manager.

Configured to reproduce in our database, we need to take care of the following prerequisites.

Flash Recovery Area

First of all, we need to configure a Flash Recovery Area (FRA). At 10g, this is a new thing, FRA is just a restore disk positioning the relevant documents. To reproduce the database, a new background process, called the Recovery Writer (RVWR), in the SGA from the database cache to reproduce the image before the stage to write the disk, as the FRA return in the log. Reproduce the log is at FRA automatically by the Oracle database management.

The cost of the log to reproduce the space and performance is measured. Writing the density of the database space is a factor. A 24-hour operation to 5% of the data into the block as a way to reproduce the log of total disk space will inevitably lead to the 5% increase. Block because the interval is written into the law, rather than part of the Panel, so the impact on performance is negligible.

To configure the FRA, you need to set the following initialization parameters:

alter system set db_recovery_file_dest =
'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area' scope = both;
alter system set db_recovery_file_dest_size = 10G scope = both;

Archive

Next, we need to configure the archive. Again, we need to use the FRA as a destination for our log files. Restore the traditional similar filing required to reproduce the database in order to move forward to restore the services, re-stored at the log to reproduce the hope that after the reference time-point before the time.

Want to minimize the configuration files, the implementation of the following orders, in accordance with the order:

SQL> startup mount
ORACLE instance started.
.
.
.
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4

Reproduce the database

Configuration of these prerequisites, we are ready to reproduce the configuration of the database.

First of all, we need to maintain the goal to reproduce. This initialization parameter, calculated in minutes, mean that we can put the database back to before long. Its value determines the FRA in the log to reproduce the amount and time period. The following example we set it to 24 hours. To understand this is not a guarantee to keep time is very important. If the FRA required space, to reproduce the log will be automatically deleted before the goal to maintain the records of the time. Later on, we will see that we have to ensure that the way to reproduce the log of maintenance at FRA. Has been set up to keep time, to reproduce the database can be activated. SQL> startup mount;
ORACLE instance started.
.
.
.
Database mounted.

SQL> alter system set db_flashback_retention_target = 1440 scope = both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v $ database;

FLASHBACK_ON
------------------
YES

Reproduce the sample database

Here is an example for demonstration, it wants to describe the damage to a single table outside. 45. Monitoring FRA
46. Select name, space_limit, space_used, space_reclaimable from v $ recovery_file_dest;
47.
48. NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE
49. --------------------------------------------
50. C: \ oracle \ product \ 10.2.0/flash_recovery_area 2147483648 166646272 0
51.
52.
53. Select * from v $ flash_recovery_area_usage;
54.
55. FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
56. ------------ ------------------ ------------------ ------- ---------------
57. CONTROLFILE 0 0 0
58. ONLINELOG 0 0 0
59. ARCHIVELOG 7.38 0 29
60. BACKUPPIECE 0 0 0
61. IMAGECOPY 0 0 0
62. FLASHBACKLOG .38 0 1
63. The image in the table before the show
64. Select c1, ora_rowscn from my_table;
65.
66. C1 ORA_ROWSCN
67. ---------- ----------
68.1 1,320,954
69. To determine the point in time database

In 10gR1, you have two options to capture your database PIT: modification timestamp and the system number (SCN). This information is reproduced as part of the requirements of the operation. Captured SCN or later submitted a very important, not the language of data management operations. Oracle to provide a more awkward way to capture the SCN, through userenv ( 'commitscn') function. Examples of our data in the event of damage before the management of language to capture this information.

select current_scn from v $ database;
CURRENT_SCN
-----------
1321065
or
select to_char (sysdate, 'YYYY-MM-DD: HH24: MI: SS')
"Recover Time" from v $ database;

Recover Time
-------------------
2006-09-23:20:13:48

10gR2 at Medium, Oracle storage point by re-simplifying the process. A re-storage point is a user-defined PIT associated with the database name, you can use timestamp or SCN. Can be stored to re-think is a reference marker redo history. Re-storage at the control points to retain the document until the point has been re-store or reproduce the delete log is deleted. The second example to ensure a repeat database are available for restore. create restore point my_restore_point;

Operation 206 succeeded.

Storage points, or re-created to ensure the reproducibility my_restrore_point database;

Note: re-storage point and ensure that all matters will not have to submit on that point in time. It should not be confused with the DB2 relational database management system in a quiet point of confusion.

Analog database damage
70. Analog database damage
71. Insert into my_table values (2);
72.
73. 1 row created.
74.
75. Commit;
76.
77. Submitted to achieve
78. To determine whether the database because of the logic of cause damage to human error.
79. Select c1, ora_rowscn from my_table;

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
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

Conventional. Increments. Differential backup and the advantages and disadvantages of the difference (change)

kods.net — Thu, 26 Feb 2009 13:13:42+0000

Differential backup and incremental backup, the backup only the data changes. However, the former backup is "the cumulative (cumulative)" - and a file since the last full backup as long as after, and was updated, then the next to do a differential backup each time, this file will be backed up (of course, until the next until a full backup).

This means that the differential backup of the file, are since the last full backup, the files have been changed. If you want to recover the entire system, then you can recover a full backup first, and then recover the difference between the last backup can be. Incremental backups are up against in a backup (either backup): Backup on a backup, all changes in documents.

(Incremental backup process, there is marked only backup selected files and folders, it detreated both: the backup file after the tag, in other words, clear the disk property.)

Used with the incremental backup strategy, you make a regular basis as long as a normal full backup, and then back up from time to time can make the difference.

Therefore, differences in the size of the backup, as increasing over time (assuming the full backup, the files modified every day is different). To back up the space and speed, the difference between backup and incremental backup between full backups; but whether a file or recover the entire system, speed is usually more than a full backup, incremental backup quickly (due to the search / recovery disk number less).

Based on these characteristics, the difference is worth considering a backup program, incremental backup and differential backup technology in the high-end part of the network-attached storage devices such as IBM, HP, and free escape of some brands such as ancillary products, software has been built.

3, different types of combination of back-up sample applications

(1), full backup and differential backup

To weekly data back-up plan as an example, we can carry out a full backup on Monday, Tuesday to Friday in the differential backup. If the data is destroyed on Friday, then Monday, you only need to restore the full backup and differential backup Thursday. This strategy required to back up data more time, but to restore the data using less time.

(2), full backup or incremental backups and weekly data back-up as an example, full backup on Monday, Tuesday to Friday at incremental backup. If the data is destroyed on Friday, then you need to restore a normal backup on Monday and Tuesday to Friday from all the incremental backups. This strategy required to back up data more time, but to restore the data using less time.

rman is said to have many advantages than conventional heat 2009-10-13 21:00:03
rman incremental backups 2009-04-02 15:10:17
ORACLE Change Data Capture introduced two (former) 2009-08-26 18:57:59
image copy and the difference between the backup set 2009-03-26 01:59:00
image copy and backup set the difference between 2009-03-26 01:17:42

ORACLE data block dump and conversion RDBA

kods.net — Thu, 26 Feb 2009 12:52:51+0000

Transfer from: http://space.zdnet.com.cn/html/80/289380-1442131.html

A lot of time in our study required further dump (dump) Oracle data block in order to study its contents, Oracle provided a good way, we adopt the following simple example to explain:

[oracle @ jumper udump] $ sqlplus "/ as sysdba"
SQL * Plus: Release 9.2.0.3.0 - Production on Tue Aug 31 17:01:27 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> select rowid, deptno, dname, loc from scott.dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------- -----
AAADZ7AABAAAGK6AAA 10 ACCOUNTING NEW YORK
AAADZ7AABAAAGK6AAB 20 RESEARCH DALLAS
AAADZ7AABAAAGK6AAC 30 SALES CHICAGO
AAADZ7AABAAAGK6AAD 40 OPERATIONS BOSTON
SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'DEPT';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
1,252,738
SQL> alter system dump datafile 1 block min 25273 block max 25274; System altered.
SQL>!
[oracle @ jumper udump] $ ls-l
total 4
-rw-r ----- 1 oracle dba 3142 Aug 31 17:04 hsjf_ora_13674.trc
[oracle @ jumper udump] $ more hsjf_ora_13674.trc
/ opt/oracle/admin/hsjf/udump/hsjf_ora_13674.trc
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = / opt/oracle/product/9.2.0
System name: Linux
Node name: jumper.hurray.com.cn
Release: 2.4.18-14
Version: # 1 Wed Sep 4 13:35:50 EDT 2002
Machine: i686
Instance name: hsjf
Redo thread mounted by this instance: 1
Oracle process number: 9
Unix process pid: 13674, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
*** 2004-08-31 17:04:27.820
*** SESSION ID: (8.3523) 2004-08-31 17:04:27.819
Start dump data blocks tsn: 0 file #: 1 minblk 25273 maxblk 25274
buffer tsn: 0rdba: 0x004062b9 (1 / 25273) scn: 0x0000.0057c70d seq: 0x01 flg: 0x04 tail: 0xc70d1001
frmt: 0x02 chkval: 0x12e3 type: 0x10 = DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-------------------------------------------------- ---------------
Extent Header:: spare1: 0 spare2: 0 # extents: 1 # blocks: 7
last map 0x00000000 # maps: 0 offset: 4128
Highwater:: 0x004062bb ext #: 0 blk #: 1 ext size: 7
# blocks in seg. hdr's freelists: 1
# blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 # extents: 1 obj #: 13947 flag: 0x40000000
Extent Map
-------------------------------------------------- ---------------
0x004062ba length: 7

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1
SEG LST:: flg: USED lhd: 0x004062ba ltl: 0x004062ba
buffer tsn: 0rdba: 0x004062ba (1 / 25274) scn: 0x0000.0131909b seq: 0x07 flg: 0x04 tail: 0x909b0607
frmt: 0x02 chkval: 0xa8e7 type: 0x06 = trans data
Block header dump: 0x004062ba
Object id on Block? Y
seg / obj: 0x367b csc: 0x00.131909a itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x0001.02a.000003f3 0x0080000b.0188.08 C --- 0 scn 0x0000.0057c70e
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump, data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x0adb505c
bdba: 0x004062ba
76543210
flag =--------
ntab = 1
nrow = 4
frre =- 1
fsbo = 0x1a
fseo = 0x1f44
avsp = 0x1f2a
tosp = 0x1f2a
0xe: pti [0] nrow = 4 ffs = 0
0x12: pri [0] ffs = 0x1f86
0x14: pri [1] ffs = 0x1f70
0x16: pri [2] ffs = 0x1f5c
0x18: pri [3] ffs = 0x1f44
block_row_dump:
tab 0, row 0, @ 0x1f86
tl: 26 fb: - H-FL - lb: 0x0 cc: 3
col 0: [2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @ 0x1f70
tl: 22 fb: - H-FL - lb: 0x0 cc: 3
col 0: [2] c1 15
col 1: [8] 52 45 53 45 41 52 43 48
col 2: [6] 44 41 4c 4c 41 53
tab 0, row 2, @ 0x1f5c
tl: 20 fb: - H-FL - lb: 0x0 cc: 3
col 0: [2] c1 1f
col 1: [5] 53 41 4c 45 53
col 2: [7] 43 48 49 43 41 47 4f
tab 0, row 3, @ 0x1f44
tl: 24 fb: - H-FL - lb: 0x0 cc: 3
col 0: [2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 0 file #: 1 minblk 25273 maxblk 25274

Always made a lot of people are a problem, rdba is how to convert?
rdba: 0x004062ba (1 / 25274)
Through the example of our brief.
rdba from Oracle6-> Oracle7-> Oracle8 happened three changes:
Oracle6 at Medium, rdba by six 2-band number that the data block that is to say a maximum of 2 ^ 6 = 64 data files (remove the 0 and the whole wide one, in fact only represent a maximum of 62 files)
Oracle7 at Medium, rdba increase the number of documents for the 10, for backward-compatible, from a high of Block No. 4 show the document number as high. So, 6 -> 7 Rowid no change.
The number of data files and in theory is extended to 1022 (remove the 0 and the whole wide one), in the Oracle7 Medium, rowid format: BBBBBBBB.RRRR.FFFF
In Oracle8, the document still said 10, but no longer need replacement, for backwards compatibility, while the introduction of the relative document (rfile #), so from Oracle7 to Oracle8, Rowid still no change.
Oracle8i at Medium, Oracle introduced dataobj #, rowid into the format: OOOOOOFFFBBBBBBSSS, Oracle further upward through dataobj # for table space, so that each table space, the number of data files can be achieved in theory, 1022, for example as follows:
Medium Oracle6 at:
Such as: file 8, block 56892
Block No. 26 == 56892
vv vvvvvvvv vvvvvvvv vvvvvvvv
00100000 00000000 11011110 00111100
^^^^^^
Document No. 6 == 8
Oracle7 at Medium:
For instance: File 255, block 56892
11111100 11000000 11011110 00111100
FCC 0 DE 3 C
\ _____/ \ ___ / \ _______________________/
| | |
| | Block = 0xDE3C = 56892
\ _____________
| \
VV
0011 111111 = 0xFF = 255 - high and low attention to here in order to obtain the correct replacement file #
Oracle8 at Medium:
For instance: File 255, block 56892
11111100 11000000 11011110 00111100
FCC 0 DE 3 C
\ _____/ \ ___ / \ _______________________/
| | |
| | Block = 0xDE3C = 56892
\ _____________
| \
VV
0011 1111 0011 = 03F3 = 1011 - This is the document relative to our test example:
rdba: 0x004062ba (1 / 25274)
Is: 0000 0000 0100 0000 0110 0010 1011 1010
10 for the former rfile #: 0000 0000 01 = 1
After 22 to Block #: 00 0000 0110 0010 1011 1010 = 25274
This article from Technology blog 51CTO.COM

Row migration and row chaining of physical storage 2010-01-01 08:40:03
oracle data export / import (exp / imp) summary 2009-03-30 00:52:47
[Change] oracle dump function 2009-11-25 05:55:36
Study Notes 2009-04-06 19:21:30
Dump Systemstat Error 2009-05-20 20:50:48

An example of the use of TKPROF

kods.net — Thu, 26 Feb 2009 01:38:19+0000

First, view and edit parameters

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
max_dump_file_size string UNLIMITED

SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
user_dump_dest string / u01/app/oracle/admin/dbnms/udump

SQL> alter session set timed_statistics = true;

Session altered.

Log in using the database dyx
[oracle @ ora ~] $ sqlplus dyx / dyx

SQL * Plus: Release 10.2.0.1.0 - Production on Mon Mar 2 09:44:20 2009

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

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

SQL>

Sys user in another session in which to view the relevant information dyx

SQL> select sid, SERIAL #, username from v $ session;

SID SERIAL # USERNAME
---------- ---------- ------------------------------
5 31948 DAIL
6 10910 DAIL
7 4793 DAIL
8 53527 DAIL
10 14549 SYS
11 47706 DAIL
12 2435 DAIL
15 1422 DAIL
17 47689 DAIL
18 15617 DYX
20 35969 DAIL

SID SERIAL # USERNAME
---------- ---------- ------------------------------
21 15456 DAIL
23 2544 DAIL
........

SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (18,15617, TRUE);

PL / SQL procedure successfully completed.

Cut to the session where dyx
SQL> insert into d values (120);

1 row created.

SQL> commit;

Where the sys-cut session

SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (18,15617, FALSE);

PL / SQL procedure successfully completed.

Show / u01/app/oracle/admin/dbnms/udump document under
[oracle @ ora udump] $ ll
total 4
-rw-r ----- 1 oracle oinstall 2812 Mar 2 10:04 dbnms_ora_13576.trc

The implementation of tkprof

[oracle @ ora udump] $ tkprof dbnms_ora_13576.trc dyx.txt sys = no

TKPROF: Release 10.2.0.1.0 - Production on Mon Mar 2 10:05:51 2009

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

The use of TKPROF command SYS = NO option to remove the involved recursive SQL output

[oracle @ ora udump] $ ll
total 12
-rw-r ----- 1 oracle oinstall 2812 Mar 2 10:04 dbnms_ora_13576.trc
-rw-r - r - 1 oracle oinstall 5902 Mar 2 10:05 dyx.txt

Show dbnms_ora_13576.trc

[oracle @ ora udump] $ more dbnms_ora_13576.trc
/ u01/app/oracle/admin/dbnms/udump/dbnms_ora_13576.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = / u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: ora
Release: 2.6.9-78.ELlargesmp
Version: # 1 SMP Fri Jul 25 00:02:41 EDT 2008
Machine: x86_64
Instance name: dbnms
Redo thread mounted by this instance: 1
Oracle process number: 65
Unix process pid: 13576, image: oracle @ ora (TNS V1-V3)

*** 2009-03-02 10:04:26.683
*** ACTION NAME: () 2009-03-02 10:04:26.682
*** MODULE NAME: (SQL * Plus) 2009-03-02 10:04:26.682
*** SERVICE NAME: (SYS $ USERS) 2009-03-02 10:04:26.682
*** SESSION ID: (18.15617) 2009-03-02 10:04:26.682
=====================
PARSING IN CURSOR # 3 len = 227 dep = 1 uid = 0 oct = 3 lid = 0 tim = 1206991666682160 hv = 2190775527 ad = 'deb64ed0'
select u.name, o.name, t.update $, t.insert $, t.delete $, t.enabled from obj $ o, user $ u, trigger $ t where t.baseobject =: 1 and t.obj # = o
. obj # and o.owner # = u.user # and bitand (property, 16) = 0 and bitand (property, 8) = 0 order by o.obj #
END OF STMT
PARSE # 3: c = 1999, e = 1557, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, tim = 1206991666682141
EXEC # 3: c = 5999, e = 5081, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, tim = 1206991666687965
FETCH # 3: c = 0, e = 93, p = 0, cr = 1, cu = 0, mis = 0, r = 0, dep = 1, og = 4, tim = 1206991666688106
STAT # 3'SORT ORDER BY (cr = 1 pr = 0 pw = 0 time = 138 us) '
STAT # 3'NESTED LOOPS (cr = 1 pr = 0 pw = 0 time = 96 us) '
STAT # 3'NESTED LOOPS (cr = 1 pr = 0 pw = 0 time = 92 us) '
STAT # 3'TABLE ACCESS BY INDEX ROWID TRIGGER $ (cr = 1 pr = 0 pw = 0 time = 89 us) '
STAT # 3'INDEX RANGE SCAN I_TRIGGER1 (cr = 1 pr = 0 pw = 0 time = 60 us) '
STAT # 3'TABLE ACCESS BY INDEX ROWID OBJ $ (cr = 0 pr = 0 pw = 0 time = 0 us) '
STAT # 3'INDEX UNIQUE SCAN I_OBJ1 (cr = 0 pr = 0 pw = 0 time = 0 us) '
STAT # 3'TABLE ACCESS CLUSTER USER $ (cr = 0 pr = 0 pw = 0 time = 0 us) '
STAT # 3'INDEX UNIQUE SCAN I_USER # (cr = 0 pr = 0 pw = 0 time = 0 us) '
=====================
PARSING IN CURSOR # 5 len = 26 dep = 0 uid = 58 oct = 2 lid = 58 tim = 1206991666689109 hv = 2638150261 ad = 'bb667268'
insert into d values (120)
END OF STMT
PARSE # 5: c = 10998, e = 11531, p = 0, cr = 1, cu = 0, mis = 1, r = 0, dep = 0, og = 1, tim = 1206991666689106
EXEC # 5: c = 4000, e = 5509, p = 0, cr = 1, cu = 20, mis = 0, r = 1, dep = 0, og = 1, tim = 1206991666694664
=====================
PARSING IN CURSOR # 1 len = 6 dep = 0 uid = 58 oct = 44 lid = 58 tim = 1206991669747805 hv = 3480936638 ad ='0 '
commit
END OF STMT
PARSE # 1: c = 1000, e = 919, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 0, tim = 1206991669747795
XCTEND rlbk = 0, rd_only = 0
EXEC # 1: c = 999, e = 899, p = 0, cr = 0, cu = 1, mis = 0, r = 0, dep = 0, og = 0, tim = 1206991669748865

Show dyx.txt

[oracle @ ora udump] $ more dyx.txt

TKPROF: Release 10.2.0.1.0 - Production on Mon Mar 2 10:34:49 2009

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

Trace file: dbnms_ora_13576.trc
Sort options: default

************************************************** ******************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
************************************************** ******************************

insert into d
values
(120)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 1 20 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 2 0.01 0.01 0 2 20 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
************************************************** ******************************

commit

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 2 0.00 0.00 0 0 1 0

Misses in library cache during parse: 0
Parsing user id: 58

************************************************** ******************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 2 0.01 0.01 0 1 0 0
Execute 2 0.00 0.00 0 1 21 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 4 0.01 0.01 0 2 21 1

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 3 0.00 0.00 0 1 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1

2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.
************************************************** ******************************
Trace file: dbnms_ora_13576.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
2 user SQL statements in trace file.
1 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
48 lines in trace file.
3 elapsed seconds in trace file.

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

Diagnosis and principles of order

kods.net — Wed, 25 Feb 2009 20:27:28+0000

SQL> select disk.value "Disk", mem.value "Mem",
2 (disk.value / mem.value) * 100 "Ratio"
3 from v $ sysstat mem, v $ sysstat disk
4 where mem.name = 'sorts (memory)'
5 and disk.name = 'sorts (disk)';

Disk Mem Ratio
--------- --------- ---------
23 206 11.1650 49

• the number of disk and memory sort to sort a few of the ratio should be less than 5%
• If the ratio is greater than 5%, please increase the size of SORT_AREA_SIZE

The ratio of the number of disk and memory sort to sort the ratio of quantity should be less than 5%
Principle shows that if the ratio of the disk should be there is substantial increase in SORT_AREA_SIZE sort it increases the size of each memory chip reduces the size of the combined storage of the total number of films and large sort area sort of advantages and disadvantages of increased size will result in the implementation of every sort server process to allocate more memory it may affect the operating system memory allocation and cause the page to sort the exchange if the size increase, please consider reducing the sort area retained size is not expected to be in the area soon to sort the data cited the case of Oracle will be sorted District to retain the size of the smaller sort area can reduce the memory occupancy rate but can cause temporary paragraph from the disk to read and write data on the additional I / O

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
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Surveillance and reconstruction index

kods.net — Wed, 25 Feb 2009 20:25:47+0000

SQL> ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100
2 AS index_usage
3 FROM index_stats;
INDEX_USAGE
-----------
24
SQL> ALTER INDEX acct_no_idx REBUILD;

Index altered.

Index space can be used to monitor the following command to monitor the use of the space index
SQL> ANALYZE INDEX index_name VALIDATE STRUCTURE;
Then query the view INDEX_STATS

Help
LF_ROWS the current index value of quantity
LF_ROWS_LEN the length of the sum of all values in bytes
DEL_LF_ROWS delete from the index value of the quantity
DEL_LF_ROWS_LEN delete the value of all the length of note: INDEX_STATS view contains the most recent analysis of the index are specific to the view if the conversation to the same user to connect to another session will be placed in the view is about the view of the analysis of conversation Index

Reconstruction index
Even though the reconstruction of the index also depends on the priority of the applications and delete the entry if you have reached or exceeded 20% of the current entries can be decided to rebuild the index can use the search query to use the proportion of

ALTER INDEX REBUILD statement rebuilds the existing index or compressed, or change the storage characteristics of index REBUILD statement to use the existing index as a new index to support all the basic commands such as index storage area for distribution STORAGE Index TABLESPACE to move to a new table space and change the INITRANS entry of the initial quantity in order to set up indexing to reduce the time the reconstruction can also use the following keywords
• PARALLEL or default values for NOPARALLEL NOPARALLEL
• RECOVERABLE or default values for UNRECOVERABLE RECOVERABLE - UNRECOVERABLE's faster to use because it is in the creation or re-indexing does not write redo log entries - This clause does not set up a permanent object and property index is only created at the time being in force and therefore It does not appear in the data dictionary - this property can not be updated - only in the creation or reconstruction of the object can use this property - the default mode LOGGING it implied that this means that the property will be attached to insert Record - if during the recovery operations required to re - the creation of this index is an index of Recovery
• LOGGING or NOLOGGING
- The use of the NOLOGGING faster during the period in the index because it does not write redo log entries of any NOLOGGING change until LOGGING
- This property is permanent and therefore appear in the dictionary - this property can be updated at any time (ALTER INDEX NOLOGGING / LOGGING) UNRECOVERABLE and are not compatible with the LOGGING Note: UNRECOVERABLE options Oracle8 can use the previous version but will be finally selected NOLOGGING To replace duplicate items of the semantic clause UNRECOVERABLE Please use NOLOGGING option to create an object and then use the ALTER command specified LOGGING To duplicate semantics of RECOVERABLE clause LOGGING option please create an object using the ALTER INDEX REBUILD than delete and re-create A quick index to use because it features a fast full scan using multi-block I / O block to read all of the index branch block and then discarded Oracle8i introduced a method that allows for concurrent operation of the implementation of the base table at the same time to create or re-create the current This approach has allowed the index at the case of non-stop machine-line implementation of maintenance operations to help achieve this goal require the availability of

SQL optimization (3) - four index 2009-03-28 13:10:06
Summary of the partition table (continued) 2009-04-21 14:25:09
Failure Index Summary 2009-10-23 00:44:54
How to dump the B * Tree index branching structure 2009-04-10 03:23:56
oracle index theory 2010-01-18 22:15:52

Checkpoint not complete failure reports caused by oracle and solutions

kods.net — Wed, 25 Feb 2009 17:26:31+0000

10,12 Month 1 day downtime oracle restart the accident happened, alert file reports the following error:

Fri Jan 12 04:07:49 2007

Thread 1 cannot allocate new log, sequence 187398

Checkpoint not complete

This error is repeated in recent reports, as soon as possible.

The cause of the problem arising from this analysis:

CKPT background process, the checkpoint is to do this, checkpoint was triggered on the occurrence of one of the conditions are redo log switch, Checkpoint specific job include:

Write to the disk DBWn trigger Dirty data.
The checkpoint information on updates to the datafile header.

• update the checkpoint information to the control file in.

Checkpoint One of the things to do to trigger DBWn are put in the buffer cache disk Dirty cache. Another system is the most recent update of the SCN to the datafile header and control file (for each Service has a SCN), the first thing to do in order to reduce system downtime and suddenly the recovery time required, the second thing to do truth in order to guarantee the consistency of the database.
Redo log switch and checkpoint is triggered in case the main (event), when the first redo log group after being run out, Oracle will be to stop using the current redo log, switch to another group of redo log, which is called log switch . Log switch to trigger the checkpoint.
Oracle requirements of the redo group of at least two are, but we are generally recommended configuration 3 or more than 3 redo log group. Assuming we have only two redo log group: group 1 and group 2, and the system has always required a lot of dirty block into the datafile, as we move from group 1 switch to group 2, we will trigger the checkpoint, checkpoint request put DBWn buffer cache to write dirty block in the datafile, however, when we once again run out of space inside the group 2, need to switch to group 1 with group 1 both, if we find that redo log group 1 of those protected by dirty block also not completely written to the datafile, the database must wait for the entire DBWn put all the dirty block written to the datafile before to do other things, that is, we have encountered "checkpoint not complete" problem. This problem is often implied that the configuration of the redo log have questions on this case, the redo log are either too small, or as we are here, too few redo log group, only 2. The solution to this problem is to increase the redo log or add more redo log group, no matter what kind of solutions, our goal is give more time for DBWn.

Problem solution:

This system is currently configured 6 Group logfile, each group has two members, the size of 50M, is proposed to change the log size of 200-500M.

By the query (see below) every day the volume of log switch at between 100-120, more than 500 in excess of the peak (considered here to be the busiest time of the business suddenly, from which we can see that January 12 is the switch number 505, downtime are just the day), necessary to optimize:

Number

of

Date Switches Redo Size

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

23-NOV-06 44 2,200

24-NOV-06 111 5,550

25-NOV-06 112 5,600

26-NOV-06 110 5,500

27-NOV-06 109 5,450

28-NOV-06 114 5,700

29-NOV-06 124 6,200

30-NOV-06 107 5,350

01-DEC-06 113 5,650

02-DEC-06 103 5,150

03-DEC-06 103 5,150

04-DEC-06 104 5,200

05-DEC-06 103 5,150

06-DEC-06 105 5,250

07-DEC-06 111 5,550

08-DEC-06 108 5,400

09-DEC-06 110 5,500

10-DEC-06 107 5,350

11-DEC-06 108 5,400

12-DEC-06 113 5,650

13-DEC-06 107 5,350

14-DEC-06 118 5,900

15-DEC-06 109 5,450

16-DEC-06 111 5,550

17-DEC-06 112 5,600

18-DEC-06 106 5,300

19-DEC-06 108 5,400

20-DEC-06 105 5,250

21-DEC-06 116 5,800

22-DEC-06 122 6,100

23-DEC-06 110 5,500

24-DEC-06 122 6,100

25-DEC-06 125 6,250

26-DEC-06 117 5,850

27-DEC-06 110 5,500

28-DEC-06 114 5,700

29-DEC-06 112 5,600

30-DEC-06 115 5,750

31-DEC-06 128 6,400

01-JAN-07 127 6,350

02-JAN-07 104 5,200

03-JAN-07 101 5,050

04-JAN-07 111 5,550

05-JAN-07 122 6,100

06-JAN-07 114 5,700

07-JAN-07 107 5,350

08-JAN-07 110 5,500

09-JAN-07 110 5,500

10-JAN-07 82 4,100

11-JAN-07 180 9,000

12-JAN-07 505 25,250

13-JAN-07 506 25,300

14-JAN-07 444 22,200

15-JAN-07 108 5,400

16-JAN-07 120 6,000

17-JAN-07 115 5,750

18-JAN-07 128 6,400

19-JAN-07 124 6,200

20-JAN-07 118 5,900

21-JAN-07 123 6,150

22-JAN-07 117 5,850

23-JAN-07 119 5,950

24-JAN-07 113 5,650

25-JAN-07 113 5,650

26-JAN-07 115 5,750

27-JAN-07 127 6,350

28-JAN-07 111 5,550

29-JAN-07 112 5,600

30-JAN-07 76 3,800

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

sum 8848 442,400

For the purposes of this machine, the proposed changes only the size of your redo log, to change the size of 200M.

The current log information is as follows:

SQL> select * from v $ log;

GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS

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

1 1 190547 52428800 2 YES INACTIVE

2 1 190548 52428800 2 YES INACTIVE

3 1 190549 52428800 2 YES INACTIVE

4 1 190546 52428800 2 YES INACTIVE

5 1 190550 52428800 2 NO CURRENT

6 1 190545 52428800 2 YES INACTIVE

6 rows selected.

SQL> select member from v $ logfile;

MEMBER

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

/ opt/oracle/db02/oradata/ORCL/redo01.log

/ opt/oracle/db03/oradata/ORCL/redo01.log

/ opt/oracle/db02/oradata/ORCL/redo02.log

/ opt/oracle/db03/oradata/ORCL/redo02.log

/ opt/oracle/db02/oradata/ORCL/redo03.log

/ opt/oracle/db03/oradata/ORCL/redo03.log

/ opt/oracle/db02/oradata/ORCL/redo04.log

/ opt/oracle/db03/oradata/ORCL/redo04.log

/ opt/oracle/db02/oradata/ORCL/redo05.log

/ opt/oracle/db03/oradata/ORCL/redo05.log

/ opt/oracle/db02/oradata/ORCL/redo06.log

/ opt/oracle/db03/oradata/ORCL/redo06.log

12 rows selected.

This oracle of the LOG size 50M, a total of 6 groups, each group has two members, which are located in / opt/oracle/db02 and / opt/oracle/db03 directory.

Space utilization of inquiry:

df-k query in this space for both the utilization of Contents:

Kbytes panel which has been in use for the remainder of the percentage of space used

/ opt/oracle/db02 51200000 12900020 35906297 27%

/ opt/oracle/db03 2048000 343661 1597823 18%

After the proposed changes to the size of the redo log and its member are still on the original panel / opt/oracle/db02, / opt/oracle/db03, / opt/oracle/db02 enough space left, and not have to worry about, and / opt / oracle/db03 need at least 200M * 6 = 1.2G, there is no need to worry about, at least the space occupied by the original member can re-use!

1, the following query statement:

select GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS

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

1 1 190547 52428800 2 YES INACTIVE

2 1 190548 52428800 2 NO CURRENT

3 1 190549 52428800 2 YES INACTIVE

4 1 190546 52428800 2 YES INACTIVE

5 1 190550 52428800 2 YES INACTIVE

checkpoint introduced 2009-07-05 15:17:45
Checkpoint trigger conditions 2009-03-31 09:50:46
Failure Index Summary 2009-10-23 00:44:54
Performance Test Problems and Solutions 2009-03-26 07:14:07
Recorded warning DBA: DBA needs to develop some habits 2009-06-18 20:27:33

Henan Mobile Oracle database parameters adjustment and optimization of the proposed

kods.net — Wed, 25 Feb 2009 12:23:00+0000

• 1, outlines the problem

January 22, 2009 inspection of dg_vassms system and database performance analysis, we found problems as follows:

l Problem:

1) buffer hit rate is very low, 54.5%;

SQL> select round ((1 - (phy.value / (cur.value + con.value))) * 100,1 )||'%' ratio

2 from v $ sysstat phy, v $ sysstat cur, v $ sysstat con

3 where phy.name = 'physical reads' and cur.name =' db block gets' and con.name = 'consistent gets';

RATIO

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

54.5%

2) the current system sort_area_size lower, resulting in higher disk sort;

SQL> select a.value "Sort (Disk)", b.value "Sort (Memory)",

2 round (100 * (a.value / decode ((a.value + b.value), 0,1, (a.value + b.value))), 2) "% Ratio (STAY UNDER 5%)"

3 from v $ sysstat a, v $ sysstat b

4 where a.name = 'sorts (disk)'

5 and b.name = 'sorts (memory)';

Sort (Disk) Sort (Memory)% Ratio (STAY UNDER 5%)

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

6526 141481 4.41

3) The system is very busy business systems, solaris has four pairs of nuclear CPU, proposed to amend the initialization parameter db_writer_processes;

4) The system log switch more frequently, every day about 200 proposed amendments to the log file size group, from 50M changed to 150M, to maintain the 3 groups;

5) udump directory 4G near trc file size, it is recommended to clear in time.

• 2, System Configuration

Sun-Fire-V490 --- CPU: 4 * 1350Mhz Dual-Core MEMORY: 16G, ORACLE 8.1.7.4 64bit

ORACLE some of the important parameters:

CPU_COUNT 8

db_block_buffers 450000

db_block_size 8192

shared_pool_size 314572800

db_writer_processes 1

db_file_multiblock_read_count 8

The entire SGA 4116197052

• 3, adjust the proposed

To sum up. The size of the SGA for approximately 4.1G. The entire physical memory size of 16G, SGA total memory size is only about 25%, this resulted in lower performance and waste of hardware resources, it is essential to adjust the parameters of the individual to meet the needs of database performance, so adjust the recommendations of as follows:

1) the value of db_file_multiblock_read_count changed from 8 to 16;

Improve the performance of full-table scan, because the whole table scan, Oracle will first read a number of Blocks. Read a few of each block will be initialized and operating system parameters DB_FILE_MULTIBLOCK_READ_COUNT the I / O buffer size restrictions.

2) db_writer_processes = 3;

V490 is a 4 because of CPU, and are dual-core processor, db_writer_processes for the current one, so that many could not bring dual-core CPU performance improve, it is recommended to 3, this increase in bandwidth to write the database.

3) db_block_buffers = 800000;

As mentioned above, the majority of performance bottlenecks in the database cache hit rate is very low, only about 54 percent, the database cache (Block Buffer) for the operation of Oracle databases and performance play a key role, it occupy the Oracle database SGA (system shared memory area) of the main part. Oracle database by using the LRU algorithm, the recent visit to the data block stored in the cache, thereby optimizing the efficiency of disk access to data to make it close to the speed of memory access. Because of the core operation of database application is data access and deal with, so if the majority of the application system access data in the cache block, that is, data in the cache hit, then used in data access on disk I / O wait and the bottleneck will be eliminated.

Therefore increase the database cache to improve database cache hit rate on the performance of the database has greatly improved. Based on the current situation, the proposal to adjust db_block_buffers to 800,000, accounting for approximately about 6.3G of memory space.

4) sort_area_size = 8388608;

For each session, will be used to sort First sort_area_size, if less than a temporary table space will be used. Sort cause a performance degradation.

5) root users to modify / etc / system file: set shmsys: shminfo_shmmax = 12884901888;

6) removal udump directory.

• 4, Notes

1) Root user to check system files, to view the configuration of the relevant parameters, it is proposed that adjust shared memory segment

The current configuration:

set shmsys: shminfo_shmmax = 4294967295

set shmsys: shminfo_shmseg = 50

set semsys: seminfo_semmns = 2010

set semsys: seminfo_semmni = 200

set semsys: seminfo_semmsl = 1010

set semsys: seminfo_semvmx = 32767

set semsys: seminfo_semopm = 100

set msgsys: msginfo_msgmnb = 409600

set msgsys: msginfo_msgtql = 200

Proposed to adjust the set shmsys: shminfo_shmmax = 12884901888, but need to restart to take effect.

2) Whether or not the above parameters to adjust? Such as agreed to sign, then the appointed time, and formulated a plan to form, around the time required.

• 5, adjust the steps

1) modify pfile files, settings above;

2) Stop the application, turn off monitor, turn off oracle server;

3) modify system files, restart the entry into force;

4) Check / var / adm / message, such as normal, the next step;

5) start the database, check database status, check whether the entry into force of parameters;

6) such as normal, then start listening, start the application;

7) the application of the test;

8) several times to check io, cpu status, check the alert file, / var / adm / message *;

9) See udump directory, it is proposed that udump cleared trc file directory;

10) such as the normal application testing, inspection did not find errors, then sign the report of a single-site services;

11) to clean up the scene to check accuracy.

Oracle storage parameters 2009-03-26 13:48:03
OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ adjustment 2009-09-24 18:44:39
Oracle Database initialization parameter changes to note 2009-06-26 06:38:13
Optimization of Oracle statements explain the rules of 53 2009-06-16 03:40:46
SQL Optimization 2010-01-27 06:48:14

Remove the transfer line

kods.net — Wed, 25 Feb 2009 04:03:16+0000

Can use the SQL * Plus script to eliminate the transfer line has been

/ * Get the name of the table with migrated rows * /
accept table_name prompt 'Enter the name of the table with migrated rows:'
/ * Clean up from last execution * /
set echo off
drop table migrated_rows;
drop table chained_rows;
/ * Create the CHAINED_ROWS table * /
@? / rdbms / admin / utlchain
set echo on
spool fix_mig
/ * List the chained & migrated rows * /
analyze table & table_name list chained rows;
/ * Copy the chained / migrated rows to another table * /
create table migrated_rows as
select orig .* from & table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper ( '& table_name');
/ * Delete the chained / migrated rows from the original table * /
delete from & table_name
where rowid in (select head_rowid from chained_rows);
/ * Copy the chained / migrated rows back into the original table * /
insert into & table_name select * from migrated_rows;
spool off

In the use of this script must be banned because of some external key constraint delete at will in violation of these constraints when

MERGE 2009-07-09 01:04:58
To remove ASM instances of Oracle 2009-09-23 22:52:19
erp Data Migration 2009-11-13 19:20:29
Logfile damage caused by oracle solution could not be activated 2009-04-13 03:19:31
SQL optimization (6) - nested loop drive source select line 2009-03-29 04:11:45

RAC upgrade to 10.2.0.4 encountered some problems and ways to deal with

kods.net — Tue, 24 Feb 2009 18:57:44+0000

RAC upgrade

hpux under oracle RAC from 10.2.0.3 to 10.2.0.4 to upgrade the entire process (End): to remove the remnants of 2009-08-19 11:38:21
standby database upgrade steps 2009-07-08 00:50:02
Oracle database. Net connectivity problems 2009-06-22 12:06:07
HP-UX do each prepare two aircraft encountered the problem of the oracle from dual-monitor 2009-11-01 14:44:22
Add a new RAC node 2009-03-16 15:12:07

At oracle11g installation on es5.3

kods.net — Tue, 24 Feb 2009 17:50:14+0000

es5.3 Release Notes:
I use the oracle enterprise linux 5.3 version, you can also use redhat enterprise 5.3
Or centos enterprise 5.3 version, the basic is the same.

Preparation work: first
www.oracle.com
Download linux_x86_11gR1_database.zip (x86 architecture on behalf of the computer is that, in general, computers are x86), install the file at a download.
1. To create the necessary users, groups, and directory
# mkdir-p / u01/oracle
# groupadd dba
# groupadd oinstall
# useradd-g oinstall-G dba-p / u01/oracle oracle (the original version of this lack of-p parameter)
password # passwd oracle
# chown-R oracle: oinstall / u01 to u01 directory oinstall given oracle user group
# ls-l command to view the entry into force of this is created is not an official recommendation by the OFA (the best most flexible architecture) standard, OFA directory designed complex than this, rookie right, at first it said installed oracle. After studying at ...
Two. (1) rpm update package, linux system has set up (on my system some of the missing packet in italics), with rpm-qa | grep * See installed those packages required
binutils-2.15.92.0.2-18
compat-libstdc + + -33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c + + -3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc + + -3.4.5-2
libstdc + +-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11
(2) set to open linux kernel modified / etc / sysctl.conf, add at the end the following:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
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
(Vi / etc / sysctl.conf open an add After ESC +: + w save, ESC +: + q Exit)
After the # sbin / sysctl-p implementation of what the system came into effect

(3) modify / etc / security / limits.conf, add the contents of
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
(4) modify / etc / profile, add content
if [$ USER = "oracle"]; then
if [$ SHELL = "/ bin / ksh"]; then
ulimit-p 16384
ulimit-n 65536
else
ulimit-u 16384-n 65536
fi
fi
Three entered the oracle user's environment variable settings oracle
# su oracle
Open v. Bash_profile add
ORACLE_BASE = / u01
ORACLE_HOME = $ ORACLE_BASE / oracle
ORACLE_SID = orcl (the database you first name)
PATH = $ ORACLE_HOME / bin: $ PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

Four to start the installation enter the root user you put under linux_x86_11gR1_database.zip extract with unzip linux_x86_11gR1_database.zip get out of database, to move it to the / u01 directory
# mv database / / u01
# chown-R oracle: oinstall / u01/database
su oracle in the database directory $. / runInstaller
Have prompted diaplay variables such as errors, enter the root user # xhost + localhost (attention +, and in fact the spaces between characters)
# xhost +
Turn off access restrictions, any permit to visit. install oracle with xhost - turn off select advanced install, customize the installation at the installation, a warning about oraInventory directory, click OK, it does not matter to you, then at the window / oraInventory revised to / u01 / oraInventory.
Subsequent parameters are the default, the installation will pop up a request u01/oraInventory/oraInstRoot.sh and / u01/oracle
/ root.sh prompt box can be in secureCRT to ROOT user to perform these two documents, we must pay attention to case, the entry for End of
installtion windows, install the end.
Listening netca enter configuration commands. One of the parameters can be default. Can use ps-ef check whether the service is enabled.
Enter the command dbca, the database creation, the following are listed some of my attention to a matter of selection gernel purpose, the name of the SID and the overall situation, the selection at you. Bash_profile created SID.
Character Sets Character Set in the options section 2 AL32UTF8.
OK, finish.
If dbca Can not open netca and check your environment variable settings are correct
www.boobooke.com
The screen has a very good website

ChinaUnix this article from the blog, if the view point of the original text, please: http://blog.chinaunix.net/u2/67130/showart_572002.html

Building an Inexpensive Oracle RAC 11g Release 1 2009-03-01 05:52:12
Oracle 11g Training (OCA, OCP) 2009-10-15 02:52:07
Oracle11G for Linux installation (RHEL5) 2009-08-14 06:58:29
oracle11g installation environment variable is set to adjust the kernel parameters 2009-06-26 17:15:28
statspack installation configuration 2009-09-14 09:30:09

Encyclopedia of Oracle functions (string functions, math functions, date function, logic operation function, other functions)

kods.net — Tue, 24 Feb 2009 15:39:39+0000

Single SQL Record function
1.ASCII
Return with a specified number of characters corresponding to the metric system;
SQL> select ascii ( 'A') A, ascii ( 'a') a, ascii ('0 ') zero, ascii (' ') space from dual;
AA ZERO SPACE
--------- --------- --------- ---------
65 97 48 32

2.CHR
Give an integer, return the corresponding character;
SQL> select chr (54740) zhao, chr (65) chr65 from dual;
ZH C
- --
Zhao A
3.CONCAT
Connecting two strings;
SQL> select concat ('010-','88888888')||' to 23' cadres competing phone from dual;
Competing cadres Phone
----------------
010-88888888 to 23
4.INITCAP
String and will return the first letter of the string to uppercase;
SQL> select initcap ( 'smith') upp from dual;
UPP
-----
Smith

5.INSTR (C1, C2, I, J)
Search in a specified string of characters, the return of characters found in the specified location;
C1 was the search string
C2 would like to search for the string
I start searching the location, the default is 1
J appear, the default is 1
SQL> select instr ( 'oracle traning', 'ra', 1,2) instring from dual;
INSTRING
---------
9

6.LENGTH
Return the length of string;
SQL> select name, length (name), addr, length (addr), sal, length (to_char (sal)) from gao.nchar_tst;
NAME LENGTH (NAME) ADDR LENGTH (ADDR) SAL LENGTH (TO_CHAR (SAL))
------ ------------ ---------------- ------------ ---- ----- --------------------
3 Sea King senior cadres in Beijing ingot District 6 9999.99 7

7.LOWER
Return the string, and all lowercase characters
SQL> select lower ( 'AaBbCcDd') AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd

8.UPPER
Return the string, and all uppercase characters
SQL> select upper ( 'AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD

9.RPAD and LPAD (paste characters)
RPAD out at the right side of paste characters
Listed at the left side of LPAD paste characters
SQL> select lpad (rpad ( 'gao', 10 ,'*'), 17 ,'*') from dual;
LPAD (RPAD ( 'GAO', 1
-----------------
******* gao *******
Are not enough characters to fill with *

10.LTRIM and RTRIM
LTRIM delete string left
RTRIM delete string right there
SQL> select ltrim (rtrim ( 'gao qian jing', ''), '') from dual;
LTRIM (RTRIM ( '
-------------
gao qian jing

11.SUBSTR (string, start, count)
From sub-string, beginning from the start, take count of
SQL> select substr ('13088888888 ', 3,8) from dual;
SUBSTR ( '
--------
08888888

12.REPLACE ( 'string', 's1', 's2')
want to be the replacement string or character variable
s1 string is replaced
To replace the string s2
SQL> select replace ( 'he love you', 'he', 'i') from dual;
REPLACE ( 'H
----------
i love you

13.SOUNDEX
Return a string given the same pronunciation of the string
SQL> create table table1 (xm varchar (8));
SQL> insert into table1 values ( 'weather');
SQL> insert into table1 values ( 'wether');
SQL> insert into table1 values ( 'gao');
SQL> select xm from table1 where soundex (xm) = soundex ( 'weather');
XM
--------
weather
wether

14.TRIM ( 's' from' string ')
LEADING cut in front of the characters
TRAILING character off the back If you do not specify, the default for the space at
15.ABS
Return the absolute value of the specified value
SQL> select abs (100), abs (-100) from dual;
ABS (100) ABS (-100)
--------- ---------
100 100

16.ACOS
Give the value of anti-cosine
SQL> select acos (-1) from dual;
ACOS (-1)
---------
3.1415927

17.ASIN
Give the value of arcsine
SQL> select asin (0.5) from dual;
ASIN (0.5)
---------
.52359878

18.ATAN
Return the arctangent of a number of value
SQL> select atan (1) from dual;
ATAN (1)
---------
.78539816

19.CEIL
Give back greater than or equal to the minimum number of integer
SQL> select ceil (3.1415927) from dual;
CEIL (3.1415927)
---------------
4

20.COS
Return a given number of cosine
SQL> select cos (-3.1415927) from dual;
COS (-3.1415927)
---------------
-1
21.COSH
Return the value of a number of anti-cosine
SQL> select cosh (20) from dual;
COSH (20)
---------
242582598

22.EXP
E back of a figure of the n-th power root
SQL> select exp (2), exp (1) from dual;
EXP (2) EXP (1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
Of a given figure from integer
SQL> select floor (2345.67) from dual;
FLOOR (2345.67)
--------------
2345

24.LN
Back a number of numerical
SQL> select ln (1), ln (2), ln (2.7182818) from dual;
LN (1) LN (2) LN (2.7182818)
--------- --------- -------------
.69314718 .99999999

25.LOG (n1, n2)
N1 for a return at the end of the logarithm of n2
SQL> select log (2,1), log (2,4) from dual;
LOG (2,1) LOG (2,4)
--------- ---------
0 2

26.MOD (n1, n2)
N1 divided by n2 a return of more than a few
SQL> select mod (10,3), mod (3,3), mod (2,3) from dual;
MOD (10,3) MOD (3,3) MOD (2,3)
--------- -------- ---------
1 0 2

27.POWER
Return of the n2-th power root n1
SQL> select power (2,10), power (3,3) from dual;
POWER (2,10) POWER (3,3)
----------- ----------
102,427

28.ROUND and TRUNC
Carried out in accordance with the specified rounding precision
SQL> select round (55.5), round (-55.4), trunc (55.5), trunc (-55.5) from dual;
ROUND (55.5) ROUND (-55.4) TRUNC (55.5) TRUNC (-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55

29.SIGN
Check the number of n symbols, greater than 0 return 1, return -1 is less than 0, equal to 0 return 0
SQL> select sign (123), sign (-100), sign (0) from dual;
SIGN (123) SIGN (-100) SIGN (0)
--------- ---------- ---------
1 -1 0

30.SIN
Sine the return value of a figure
SQL> select sin (1.57079) from dual;
SIN (1.57079)
------------
1

31.SIGH
Return the value of sinh
SQL> select sin (20), sinh (20) from dual;
SIN (20) SINH (20)
--------- ---------
.91294525 242582598

32.SQRT
Return the root of the number of n
SQL> select sqrt (64), sqrt (10) from dual;
SQRT (64) SQRT (10)
--------- ---------
8 3.1622777

33.TAN
Return the number of tangent
SQL> select tan (20), tan (10) from dual;
TAN (20) TAN (10)
--------- ---------
2.2371609 .64836083

34.TANH
Return the number of n hyperbolic tangent
SQL> select tanh (20), tan (20) from dual;
TANH (20) TAN (20)
--------- ---------
1 2.2371609

35.TRUNC
Accuracy in accordance with the interception of a specified number of
SQL> select trunc (124.1666, -2) trunc1, trunc (124.16666,2) from dual;
TRUNC1 TRUNC (124.16666,2)
--------- ------------------
100 124.16

36.ADD_MONTHS
Increase in the month or less
SQL> select to_char (add_months (to_date ('199912 ',' yyyymm '), 2),' yyyymm ') from dual;
TO_CHA
------
200002
SQL> select to_char (add_months (to_date ('199912 ',' yyyymm '), -2),' yyyymm ') from dual;
TO_CHA
------
199910

37.LAST_DAY
The last day of the date of return
SQL> select to_char (sysdate, 'yyyy.mm.dd'), to_char ((sysdate) +1, 'yyyy.mm.dd') from dual;
TO_CHAR (SY TO_CHAR ((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day (sysdate) from dual;
LAST_DAY (S
----------
-04 Month 31-5

38.MONTHS_BETWEEN (date2, date1)
Give the month of date2-date1
SQL> select months_between ('19-12 Month -1999 ','19-3 Month -1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL> selectmonths_between (to_date ('2000 .05.20 ',' yyyy.mm.dd '), to_date ('2005 .05.20', 'yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60

39.NEW_TIME (date, 'this',' that ')
Give in this time zone = other time zone the date and time
SQL> select to_char (sysdate, 'yyyy.mm.dd hh24: mi: ss') bj_time, to_char (new_time
2 (sysdate, 'PDT', 'GMT'), 'yyyy.mm.dd hh24: mi: ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY (date, 'day')
Date and give the date of the calculation of the next week x 1 weeks after the date of
SQL> select next_day ('18-5 month -2001 ',' Friday ') next_day from dual;
NEXT_DAY
----------
-01 Month 25-5
41.SYSDATE
System used to be the current date
SQL> select to_char (sysdate, 'dd-mm-yyyy day') from dual;
TO_CHAR (SYSDATE, '
-----------------
09-05-2004 Sunday
trunc (date, fmt) give the request in accordance with the cut-off date, if the fmt = 'mi' expressed reservations about the points, second cut-off
SQL> select to_char (trunc (sysdate, 'hh'), 'yyyy.mm.dd hh24: mi: ss') hh,
2 to_char (trunc (sysdate, 'mi'), 'yyyy.mm.dd hh24: mi: ss') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID
Character data types will be converted to the type of ROWID
SQL> select rowid, rowidtochar (rowid), ename from scott.emp;
ROWID ROWIDTOCHAR (ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

43.CONVERT (c, dset, sset)
Sset the source string from one language to another character set dset objective of Character Set
SQL> select convert ( 'strutz', 'we8hp', 'f7dec') "conversion" from dual;
conver
------
strutz

44.HEXTORAW
Constitute a hexadecimal string into a binary

45.RAWTOHEXT
Consisting of a binary string is converted to hexadecimal

46.ROWIDTOCHAR
ROWID data type will be converted to character types

47.TO_CHAR (date, 'format')
SQL> select to_char (sysdate, 'yyyy / mm / dd hh24: mi: ss') from dual;
TO_CHAR (SYSDATE, 'YY
-------------------
2004/05/09 21:14:41

48.TO_DATE (string, 'format')
ORACLE string into a date

49.TO_MULTI_BYTE
String of single-byte characters into a multi-byte character
SQL> select to_multi_byte ( 'high') from dual;
TO
--
High

50.TO_NUMBER
Characters will be converted to give the figure
SQL> select to_number ('1999 ') year from dual;
YEAR
---------
1999

51.BFILENAME (dir, file)
Specify an external binary file
SQL> insert into file_tb1 values (bfilename ( 'lob_dir1', 'image1.gif'));

52.CONVERT ( 'x', 'desc', 'source')
The x field or variable source is converted to the source desc
SQL> select sid, serial #, username, decode (command,
2 0, 'none',
3 2, 'insert',
4 3,
5 'select',
6 6, 'update',
7 7, 'delete',
8 8, 'drop',
9 'other') cmd from v $ session where type! = 'Background';
SID SERIAL # USERNAME CMD
--------- --------- ------------------------------ -- ----
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none

53.DUMP (s, fmt, start, length)
DUMP function fmt internal digital format specified a return type of the value of VARCHAR2
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name, dump (global_name, 1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ -------------------- ------------------------------
ORACLE.WORLD Typ = 1 Len = 12 CharacterSet = ZHS16GBK: W, O, R, L, D

54.EMPTY_BLOB () and EMPTY_CLOB ()
These two functions are used for large data type field to initialize a function of operation

55.GREATEST
Return a Maximum of expression, that is, compare the size of character encoding.
SQL> select greatest ( 'AA', 'AB', 'AC') from dual;
GR
--
AC
SQL> select greatest ( 'ah', 'security', 'day') from dual;
GR
--
Days

56.LEAST
Expressions return a set of minimum
SQL> select least ( 'ah', 'security', 'day') from dual;
LE
--
Ah

57.UID
Logo return integer the current user only
SQL> show user
USER for the "GAO"
SQL> select username, user_id from dba_users where user_id = uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25

58.USER
Return the current user's first name
SQL> select user from dual;
USER
------------------------------
GAO

59.USEREVN
Return the current user environment information, opt can be:
ENTRYID, SESSIONID, TERMINAL, ISDBA, LABLE, LANGUAGE, CLIENT_INFO, LANG, VSIZE
Show ISDBA are DBA Whether or not the current user is returned if it is true
SQL> select userenv ( 'isdba') from dual;
USEREN
------
FALSE
SQL> select userenv ( 'isdba') from dual;
USEREN
------
TRUE
SESSION
Conversation back logo
SQL> select userenv ( 'sessionid') from dual;
USERENV ( 'SESSIONID')
--------------------
152
ENTRYID
Return the session marked population
SQL> select userenv ( 'entryid') from dual;
USERENV ( 'ENTRYID')
------------------
0
INSTANCE
A sign of the current return INSTANCE
SQL> select userenv ( 'instance') from dual;
USERENV ( 'INSTANCE')
-------------------
1
LANGUAGE
Return the current environment variables
SQL> select userenv ( 'language') from dual;
USERENV ( 'LANGUAGE')
-------------------------------------------------- --
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
Return the current environment of the language abbreviations
SQL> select userenv ( 'lang') from dual;
USERENV ( 'LANG')
-------------------------------------------------- --
ZHS
TERMINAL
Return the user's terminal or a sign of the machine
SQL> select userenv ( 'terminal') from dual;
USERENV ( 'TERMINA
----------------
GAO
VSIZE (X)
X return the size (bytes) Number of
SQL> select vsize (user), user from dual;
VSIZE (USER) USER
----------- ------------------------------
6 SYSTEM

60.AVG (DISTINCT | ALL)
all express the value of all for the average, distinct only on the average of different values for
SQLWKS> create table table3 (xm varchar (8), sal number (7,2));
Statements have been processed.
SQLWKS> insert into table3 values ( 'gao', 1111.11);
SQLWKS> insert into table3 values ( 'gao', 1111.11);
SQLWKS> insert into table3 values ( 'zhu', 5555.55);
SQLWKS> commit;
SQL> select avg (distinct sal) from gao.table3;
AVG (DISTINCTSAL)
----------------
3333.33
SQL> select avg (all sal) from gao.table3;
AVG (ALLSAL)
-----------
2592.59

61.MAX (DISTINCT | ALL)
Maximum demand, ALL of the value of all express Maximum demand, DISTINCT to express different values for the maximum, the same time, only
SQL> select max (distinct sal) from scott.emp;
MAX (DISTINCTSAL)
----------------
5000

62.MIN (DISTINCT | ALL)
For the minimum, ALL of all express the value of seeking the minimum, DISTINCT to express different values for the minimum, the same time, only
SQL> select min (all sal) from gao.table3;
MIN (ALLSAL)
-----------
1111.11

63.STDDEV (distinct | all)
For standard deviation, ALL of the value of all express the standard deviation of demand, DISTINCT only express different values for standard deviation
SQL> select stddev (sal) from scott.emp;
STDDEV (SAL)
-----------
1182.5032
SQL> select stddev (distinct sal) from scott.emp;
STDDEV (DISTINCTSAL)
-------------------
1229.951

64.VARIANCE (DISTINCT | ALL)
For covariance
SQL> select variance (sal) from scott.emp;
VARIANCE (SAL)
-------------
1398313.9

65.GROUP BY
One group is mainly used for a number of statistical
SQL> select deptno, count (*), sum (sal) from scott.emp group by deptno;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
1,038,750
20,510,875
3,069,400

66.HAVING
Restrictions on the group are coupled with conditions
SQL> select deptno, count (*), sum (sal) from scott.emp group by deptno having count (*)>= 5;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20,510,875
3,069,400
SQL> select deptno, count (*), sum (sal) from scott.emp having count (*)>= 5 group by deptno;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20,510,875
3,069,400

67.ORDER BY
For the results of the query to sort the output
SQL> select deptno, ename, sal from scott.emp order by deptno, sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950

Functions 2009-07-02 11:25:00
2_SQL 11g (functions, Dates and Times) 2009-07-10 18:14:56
yangtingkun personal BLOG Article Index ---- SQL connection 2010-01-15 23:35:31
yangtingkun Article Index ----PL/SQL aspects of personal BLOG 2010-01-15 17:09:22
3_SQL 11g (Advanced Queries_4) 2009-07-16 17:03:04

Full table scan query

kods.net — Tue, 24 Feb 2009 07:48:41+0000

SQL> SELECT name, value FROM v $ sysstat
2 WHERE name LIKE '% table scans%';
NAME VALUE
-------------------------------------------------- ------- -----
table scans (short tables) 125
table scans (long tables) 30
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 21224
table scan blocks gotten 804
7 rows selected.

Table scans and table scans a long table of the value of a short table with a table scan on the table if the table scan of a large number of long visited so there is a large proportion of the table is not indexed by the search application may need to optimize or increase the index to ensure that the necessary Optimize the index there is an appropriate full-table scan
Initialization parameters DB_FILE_MULTIBLOCK_READ_COUNT full table scan to determine the period at an I / O operation to read a few of the largest block of the database parameter settings can reduce the required full-table scan I / O call to improve the performance of quantity
I / O is a function of the operating system-specific restrictions, therefore the operating system will set the parameters affect the Oracle server to read more than the ability to block the operating system limit for the time limit I / O call can read the word the number of at most 7.3 versions of the platform before the biggest read on for the 64 KB block of memory so will the above-mentioned parameters DB_FILE_MULTIBLOCK_READ_COUNT set to 64 KB each
NOT DB_BLOCK_SIZE the performance benefits of the majority of any additional platform version 7.3 or higher no longer set
In addition DB_FILE_MULTIBLOCK_READ_COUNT the upper limit of the parameters of the parameters are dynamic so you can use a separate session ALTER SESSION SET command the type of job for batch size greater settings DB_FILE_MULTIBLOCK_READ_COUNT parameter settings achieve table scan provides the necessary I /
O For example, if the number of calls is set to DB_FILE_MULTIBLOCK_READ_COUNT
Oracle block 16 and equal the size of 4 KB at a process then can be read by a 64 KB
This form of continuous scanning to improve the speed of the table scan and enhance the overall query performance settings
The purpose of DB_FILE_MULTIBLOCK_READ_COUNT parameters are greater in less I / O
Perform a table scan method to achieve this goal is to assess the completion of this period of time required to scan each table and then block the number of parameters in order to adjust the scanning can be at most one I / O completed the implementation of the practical requirements of full-table scan of the I / O depends on the total number of other factors such as the size of the table and whether or not to use parallel query optimization based on the cost of the procedure including the use of all these factors
DB_FILE_MULTIBLOCK_READ_COUNT parameters to determine the cost of full table scan if the cost of full table scan than index scans are based on the procedures to support optimization of the cost of full table scan Supervisor

SQL optimization (3) - four index 2009-03-28 13:10:06
SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
oracle exp / imp command parameters used 2010-01-29 11:30:03
oracle query table contains a field all inquiries form 2009-07-03 14:57:15
ORACLE used a fool question 1000 Q (6) 2009-09-06 11:52:48

2009/02

Linux-unix at Oracle10g install documents (official)

Related Posts

Oracle9i initialization parameters that English

Related Posts

Paste data

Related Posts

ORACLE 9I SYS authentication methods

Related Posts

RAC LOG

ORA-01452

Using SQL TRACE and TKPROF

Related Posts

High Availability Oracle Flashback

Related Posts

Conventional. Increments. Differential backup and the advantages and disadvantages of the difference (change)

Related Posts

ORACLE data block dump and conversion RDBA

Related Posts

An example of the use of TKPROF

Related Posts

Diagnosis and principles of order

Related Posts

Surveillance and reconstruction index

Related Posts

Checkpoint not complete failure reports caused by oracle and solutions

Related Posts

Henan Mobile Oracle database parameters adjustment and optimization of the proposed

• 1, outlines the problem

• 2, System Configuration

• 3, adjust the proposed

• 4, Notes

• 5, adjust the steps

Related Posts

Remove the transfer line

Related Posts

RAC upgrade to 10.2.0.4 encountered some problems and ways to deal with

Related Posts

At oracle11g installation on es5.3

Related Posts

Encyclopedia of Oracle functions (string functions, math functions, date function, logic operation function, other functions)

Related Posts

Full table scan query

Related Posts