n Oracle/PLSQL, the cast function converts one datatype to another.
The syntax for the cast function is:
cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )
cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )
$ORACLE_HOME/dbs> on unix and %ORACLE_HOME%\database on Windows. On Windows, the location can be changed by changingORA_%ORACLE_SID%_PFILE.create pfile='/some/path/init.ora' from spfile;
create pfile one needs the sysdba role.init.ora.55200519329 where 55200519329 is a timestamp of a sort. This init.ora.55200519329 will not be used by Oracle afterwards, so modifiying it will not change the init parameters.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.ALTER SESSION system privilege.RESUMABLE system privilege.ADVISE clause sends advice to a remote database to force a distributed transaction. The advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database (the values are 'C' for COMMIT, 'R' for ROLLBACK, and ' ' for NOTHING). If the transaction becomes in doubt, then the administrator of that database can use this advice to decide whether to commit or roll back the transaction.ALTER SESSION statements with the ADVISE clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued.CLOSE DATABASE LINK to close the database link dblink. When you issue a statement that uses a database link, Oracle Database creates a session for you on the remote database using that link. The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS. If you want to reduce the network overhead associated with keeping the link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, then specify DISABLE COMMIT IN PROCEDURE clause to prevent procedures and stored functions called during your session from issuing these statements.COMMIT and ROLLBACK statements in your session by issuing the ENABLE COMMIT IN PROCEDURE.COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation for more information.security_clause of ALTER DATABASE lets you prevent anyone other than the SYS user from making any changes to data or database objects on the primary or standby database. This clause lets you override that setting for the current session.PARALLEL parameter determines whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves. Uncommitted transactions must either be committed or rolled back prior to executing this clause for DML.ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements.DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.DDL: DDL statements are executed in parallel mode if a parallel clause is specified.QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specifiedPARALLEL integer with ENABLE.DISABLE to execute subsequent statements in the session serially. This is the default for DML statements.DML: DML statements are executed serially.DDL: DDL statements are executed serially.QUERY: Queries are executed serially.PARALLEL integer with DISABLE.FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint.DML: Provided no parallel DML restrictions are violated, subsequent DML statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause.DDL: Subsequent DDL statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism.FORCE DDL automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with the default degree) in the CREATE TABLE statement.QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause.FORCE DDL, the degree overrides any parallel clause in subsequent DDL statements.FORCE DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary.LONG or LOB datatypes.TIMEOUT lets you specify (in seconds) the time during which an operation can remain suspended while waiting for the error condition to be fixed. If the error condition is not fixed within the TIMEOUT period, then Oracle Database aborts the suspended operation.NAME lets you specify a user-defined text string to help users identify the statements issued during the session while the session is in resumable mode. Oracle Database inserts the text string into the USER_RESUMABLE and DBA_RESUMABLE data dictionary views. If you do not specify NAME, then Oracle Database inserts the default string 'Userusername(userid), Session sessionid, Instance instanceid'.alter_session_set_clause to set parameter values. You can set two types of parameters using this clause:ALTER SESSION statement (listed in "Initialization Parameters and ALTER SESSION")alter_session_set_clause.ALTER SESSION appears in Table 11-1. In PDF and HTML, the parameters are hyperlinks that take you to their full descriptions in Oracle Database Reference. In print, for a description of each parameter, please refer to Oracle Database Reference.ALTER SESSION, the value you set persists only for the duration of the current session.ALTER SESSION are not initialization parameters. That is, you can set them only with ALTER SESSION, not in an initialization parameter file. Those session parameters are described in "Session Parameters and ALTER SESSION".| Parameter | Comments |
|---|---|
In earlier releases, CREATE_STORED_OUTLINES was a session parameter only. | |
See also Oracle Database Performance Tuning Guide for information on setting this parameter in these and other environments. | |
The setting made by ALTER SESSION SET DB_BLOCK_CHECKING will be overridden by any subsequent ALTER SYSTEM SETDB_BLOCK_CHECKING statement. | |
-- | |
-- | |
-- | |
-- | |
-- | |
See "Referring to Objects in Remote Databases" and Oracle Database Heterogeneous Connectivity Administrator's Guide for more information on global name resolution and how Oracle Database enforces it. | |
-- | |
-- | |
-- | |
-- | |
-- | |
When you start an instance, Oracle Database establishes globalization support based on the values of initialization parameters that begin with "NLS". You can query the dynamic performance table V$NLS_PARAMETERS to see the current globalization attributes for your session. For more information about NLS parameters, see Oracle Database Globalization Support Guide. | |
-- | |
-- | |
-- | |
See "Datetime Format Models" for information on valid date format models. | |
-- | |
See "Number Format Models" for information on number format elements. | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
See Oracle Database Performance Tuning Guide for information on how to set this parameter. | |
-- | |
-- | |
-- | |
See Oracle Database Concepts and Oracle Database Performance Tuning Guide for information on how to choose a goal for the cost-based approach based on the characteristics of your application. | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- | |
-- |
CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }
CONSTRAINT[S] parameter determines when conditions specified by a deferrable constraint are enforced.IMMEDIATE indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement. This setting is equivalent to issuing the SET CONSTRAINTS ALL IMMEDIATE statement at the beginning of each transaction in your session.DEFERRED indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed. This setting is equivalent to issuing theSET CONSTRAINTS ALL DEFERRED statement at the beginning of each transaction in your session.DEFAULT restores all constraints at the beginning of each transaction to their initial state of DEFERRED or IMMEDIATE.CREATE_STORED_OUTLINES was a session parameter only. It is now an initialization parameter. Please refer to CREATE_STORED_OUTLINES in the Oracle Database Reference.CURRENT_SCHEMA = schema
CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMAstatement.ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}
ERROR_ON_OVERLAP_TIME parameter determines how Oracle Database should handle an ambiguous boundary datetime value--that is, a case in which it is not clear whether the datetime is in standard or daylight savings time.TRUE to return an error for the ambiguous overlap timestamp.FALSE to default the ambiguous overlap timestamp to the standard time. This is the default.FLAGGER = { ENTRY | INTERMEDIATE | FULL | OFF }
FLAGGER parameter specifies FIPS flagging, which causes an error message to be generated when a SQL statement issued is an extension of ANSI SQL92. FLAGGER is a session parameter only, not an initialization parameter.ALTER SESSION SETFLAGGER statement will work, but generates the message, ORA-00097. This allows FIPS flagging to be altered without disconnecting the session. OFF turns off flagging.INSTANCE = integer
INSTANCE parameter lets you access another instance as if you were connected to your own instance. INSTANCE is a session parameter only, not an initialization parameter. In a Real Application Clusters (RAC) environment, each RAC instance retains static or dynamic ownership of disk space for optimal DML performance based on the setting of this parameter.ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED}
ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. ISOLATION_LEVEL is a session parameter only, not an initialization parameter.SERIALIZABLE indicates that transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates.READ COMMITTED indicates that transactions in the session will use the default Oracle Database transaction behavior. That is, if the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.PLSQL_DEBUG was a session parameter only. It is now an initialization parameter. Please refer to PLSQL_DEBUG in the Oracle Database Reference.SKIP_UNUSABLE_INDEXES was a session parameter only. It is now an initialization parameter. Please refer to SKIP_UNUSABLE_INDEXES in the Oracle Database Reference.SQL_TRACE was a session parameter only. It is now an initialization parameter. Please refer to SQL_TRACE in the Oracle Database Reference.TIME_ZONE = '[+ | -] hh:mm' | LOCAL | DBTIMEZONE | 'time_zone_region'
TIME_ZONE parameter specifies the default local time zone offset or region name for the current SQL session. TIME_ZONE is a session parameter only, not an initialization parameter. To determine the time zone of the current session, query the built-in function SESSIONTIMEZONE (see SESSIONTIMEZONE).'[+|-]hh:mm') indicating the hours and minutes before or after UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The valid range for hh:mm is -12:00 to +14:00.LOCAL to set the default local time zone offset of the current SQL session to the original default local time zone offset that was established when the current SQL session was started.DBTIMEZONE to set the current session time zone to match the value set for the database time zone. If you specify this setting, then the DBTIMEZONE function will return the database time zone as a UTC offset or a time zone region, depending on how the database time zone has been set.time_zone_region. To see a listing of valid region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. If you specify this setting, then the SESSIONTIMEZONE function will return the region name.ORA_TZFILEenvironment variable.ORA_TZFILE environment variableORA_SDTZ environment variable. Please refer to Oracle Database Globalization Support Guide for more information on this variable.USE_PRIVATE_OUTLINES = { TRUE | FALSE | category_name }
USE_PRIVATE_OUTLINES parameter lets you control the use of private outlines. When this parameter is enabled and an outlined SQL statement is issued, the optimizer retrieves the outline from the session private area rather than the public area used when USE_STORED_OUTLINES is enabled. If no outline exists in the session private area, then the optimizer will not use an outline to compile the statement. USE_PRIVATE_OUTLINES is not an initialization parameter.TRUE causes the optimizer to use private outlines stored in the DEFAULT category when compiling requests.FALSE specifies that the optimizer should not use stored private outlines. This is the default. If USE_STORED_OUTLINES is enabled, then the optimizer will use stored public outlines.category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.USE_STORED_OUTLINES is enabled.USE_STORED_OUTLINES = { TRUE | FALSE | category_name }
USE_STORED_OUTLINES parameter determines whether the optimizer will use stored public outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.TRUE causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.FALSE specifies that the optimizer should not use stored outlines. This is the default.category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.USE_PRIVATE_OUTLINES is enabled.ALTER SESSION ENABLE PARALLEL DML;
employees table on the database identified by the database link remote and deletes an employee record from the employees table on the database identified by local:ALTER SESSION
ADVISE COMMIT;
INSERT INTO employees@remote
VALUES (8002, 'Juan', 'Fernandez', 'juanf@hr.com', NULL,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SA_CLERK', 3000,
NULL, 121, 20);
ALTER SESSION
ADVISE ROLLBACK;
DELETE FROM employees@local
WHERE employee_id = 8002;
COMMIT;
ALTER SESSION statements with the ADVISE clause. If the transaction becomes in doubt, then remote is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and local is sent the advice 'ROLLBACK' by virtue of the second statement.jobs table on the local database using a database link, commits the transaction, and explicitly closes the database link:UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK'; COMMIT; ALTER SESSION CLOSE DATABASE LINK local;
'YYYY MM DD-HH24:MI:SS':ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
SELECT TO_CHAR(SYSDATE) Today FROM DUAL; TODAY ------------------- 2001 04 12 12:30:38
ALTER SESSION SET NLS_DATE_LANGUAGE = French; SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') Today FROM DUAL; TODAY --------------------------- Jeudi 12 Avril 2001
ALTER SESSION
SET NLS_ISO_CURRENCY = America;
SELECT TO_CHAR( SUM(salary), 'C999G999D99') Total
FROM employees;
TOTAL
------------------
USD694,900.00
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' ;
ALTER SESSION SET NLS_CURRENCY = 'FF';
SELECT TO_CHAR( SUM(salary), 'L999G999D99') Total FROM employees;
TOTAL
---------------------
FF694.900,00
DM':ALTER SESSION
SET NLS_CURRENCY = 'DM';
SELECT TO_CHAR( SUM(salary), 'L999G999D99') Total
FROM employees;
TOTAL
---------------------
DM694.900,00
ALTER SESSION SET NLS_LANGUAGE = FRENCH; Session modifiee. SELECT * FROM DMP; ORA-00942: Table ou vue inexistante
ALTER SESSION SET NLS_SORT = XSpanish;
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;