Introduction to Oracle sqlplus prelim parameters

Starting with oracle10g, Sqlplus provides a parameter option-prelim, using this parameter when the system is hang. We can connect to the SGA instead of the database, which means no session is created.

First, you can obtain system information through the following steps:

Sqlplus-prelim/as SYSDBA

Oradebug Setmypid

Oradebug Unlimit;

Oradebug Dump SystemState 10

For a 9i database, the 9IR2 is working properly by installing a 10g client connection.

Ii. Characteristics of prelim parameters

1. First enable 10046 events on the database:

Sql> alter system set events ' 10046 Trace name context forever ';

System altered.

2. Connect first in a normal way:

D:/>sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Saturday November 15 15:36:02 2008

More Wonderful content: http://www.bianceng.cn/database/Oracle/

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

Sql> Conn Sys/manage as Sysdba

is connected.

Sql> exit

From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options disconnected

3. Take a look at the addition of prelim parameters:

D:/>sqlplus-prelim/nolog

Sql*plus:release 10.2.0.1.0-production on Saturday November 15 15:36:34 2008

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

Sql> Conn Sys/manage as Sysdba

The primary connection has been established

Sql> exit

Disconnect from ORACLE

As you can see from the above information, when using prelim connection, the prompt is "primary connection has been established", exit Sqlplus does not show banner.

4. You can also connect to a database in a prelim manner in the following ways:

[Oracle@xty ~]$ Sqlplus/nolog

Sql*plus:release 10.2.0.3.0-production on Tue Dec 2 07:04:28 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Sql> set _prelim on

Sql> Connect/as SYSDBA

Prelim Connection established

5. Look at the database 10046 trace:

From the generated trace file, you can see that when you connect to the database on a normal connection, Sqlplus automatically executes the following SQL:

ALTER session SET nls_language= ' Simplified Chinese ' nls_territory= ' ' nls_currency= ' ￥ ' nls_iso_currency= ' ' S_numeric_characters= '. , ' nls_calendar= ' Gregorian ' nls_date_format= ' dd-mon-rr ' nls_date_language= ' simplified CHINESE ' NLS_SORT= ' BINARY ' Time_zone= ' +08:00′nls_comp= ' BINARY ' nls_dual_currency= ' ￥ ' nls_time_format= '. Ssxff AM ' nls_timestamp_format= ' dd-mon-rr hh.mi. Ssxff AM ' nls_time_tz_format= ' HH.MI. Ssxff AM tzr ' nls_timestamp_tz_format= ' dd-mon-rr hh.mi. Ssxff AM Tzr '

Select value$ from props$ where name = ' Global_db_name '

Select Sys_context (' USERENV ', ' server_host '), Sys_context (' USERENV ', ' db_unique_name '), Sys_context (' USERENV ', ' Instance_name '), Sys_context (' USERENV ', ' service_name '), Instance_number, Startup_time, Sys_context (' USERENV ', ' db_ DOMAIN ') from V$instance where Instance_name=sys_context (' USERENV ', ' instance_name ')

Select decode (Failover_method, NULL, 0, ' BASIC ', 1, ' Preconnect ', 2, ' Preparse ', 4, 0), decode (Failover_type, NULL, 1, ' NONE ', 1, ' Session ', 2, ' SELECT ', 4, 1-failover_retries, Failover_delay, flags from service$ WHERE name =: 1

When Sqlplus uses prelim to connect to the database, it does not generate the 10046 trace file, and it does not appear to execute SQL, which means that no initialization action is performed and the necessary information is queried. Perhaps this is called the "primary Connection" of the history of it.

Because the Prelim method is used to connect, no SQL statements are executed, so the database can be connected to some hang in the database. For example, because the library cache latch is held for a long time can not be released, can not resolve the SQL statement caused by hang. Some people will say, my application just go up to have not done any operation to hang live. This is only a superficial phenomenon, connected to the database, usually do some initialization of the operation, such as setting the environment.

Sqlplus-prelim can connect to the database hang the database, but can only say that the connection, does not mean that many operations can be done. Like executing SQL queries. In this case, perhaps the most useful thing is to use Oradebug.

6. Oradebug Introduction

Oradebug was a tool for debugging when Oracle was originally designed, and then further developed to define traces through events (event), which then evolved to support SQL Trace, and SQL Trace was the event number 10046. In other words, this is a development process from the bottom debug to the advanced support, and Oracle is gradually perfected. The events in Oracle support this system's definition and extended functionality, very powerful. In fact, from the software developers to debug their own code tools gradually developed to provide users with the interface for tracking and debugging applications.