ALTER SESSION. Purpose Use the ALTER SESSION statement to set or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database. Prerequisites To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege. To enable or disable resumable space allocation, you must have the RESUMABLE system privilege. You do not need any privileges to perform the other operations of this statement unless otherwise indicated.
Syntax alter_session::= Description of the illustration alter_session.gif alter_session_set_clause ::= Description of the illustration alter_session_set_clause.gif Semantics ADVISE Clause The ADVISE clause sends advice to a remote database to force a distributed transaction. You can send different advice to different remote databases by issuing multiple ALTER SESSION statements with the ADVISE clause in a single transaction. CLOSE DATABASE LINK Clause ENABLE Clause DISABLE Clause FORCE Clause. Configuring SQL*Plus. This chapter explains how to configure your SQL*Plus command-line, Windows GUI, and iSQL*Plus environments. It has the following topics: SQL*Plus and iSQL*Plus Configuration You can set up your SQL*Plus or iSQL*Plus Application Server environment to use the same settings with each session.
There are two operating system files to do this: The Site Profile file, glogin.sql, for site wide settings, and settings for the iSQL*Plus sessions from an iSQL*Plus Application Server.Additionally, in the command-line user interface and the Windows GUI, the User Profile, login.sql, sets user specific settings. The exact names of these files is system dependent. Some privileged connections may generate errors if SET SERVEROUTPUT or SET APPINFO commands are put in the Site Profile or User Profile. The following tables show the profile scripts, and some commands and settings that affect the Command-line, Windows and iSQL*Plus user interfaces. Table 2-2 Profile Scripts affecting SQL*Plus User Interface Settings. Oracle: auto kill lock session. Oracle Locking Survival Guide. Understanding Locks :: Chapter 7: Sessions, Transactions, and Locks :: Part III: Data Manipulation and Transaction Control :: SQL Bible Oracle :: SQL.
Concurrency is one of the major concerns in a multiuser environment. When multiple sessions write or read data to and from shared resources, a database might loose its integrity. To prevent this from happening, every RDBMS worth its salt implements a concurrency control mechanisms. In the case of RDBMS servers, the concurrency is managed through various locking mechanisms. All three leading RDBMS vendors have implemented sophisticated mechanisms for concurrency management.
Oracle has probably the most evolved and complex locking schema. It follows the rule that reading and writing processes cannot block each other, even if working on the same (or a close) set of data. Each session receives a read-consistent image of the data. IBM DB2 UDB and Microsoft SQL Server 2000 both employ locks that can enable a reader to block a writer and vice versa.
Locking modes There are two broad categories of concurrency — optimistic and pessimistic. LOCK TABLE customer IN EXCLUSIVE MODE; How Oracle Processes SQL Statements. This chapter describes how Oracle processes Structured Query Language (SQL) statements. Topics include the following: Although some Oracle tools and applications simplify or mask the use of SQL, all database operations are performed using SQL, to take advantage of the security and data integrity features built into Oracle. Overview of SQL Statement Execution Figure 7-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, these steps might be executed in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how your code is written. For many Oracle tools, several of the stages are performed automatically. Identifying Extensions to SQL92 (FIPS Flagging) The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions.
When flagging is on and non-standard SQL is encountered, the following message is returned: Closing Cursors.