Alters a profile definition.
1 – Environment
You can use the ALTER PROFILE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mALTER[m [1mqwq>[m [1;4mPROFILE[m [1mqq>[m [1m<profilename>[m [1mqqqqqqqqqqqqqqwqqwq> profile-options[m [1mqwq>[m [1m [m
[1mmq>[m [1;4mDEFAULT[m[1m [1;4mPROFILE[m [1mqwqqqqqqqqqqqqqqqqqqqqqwj[m [1mmqqqqqqqq<qqqqqqqqqqqj[m
[1mmq> [1;4mALIAS[m[1m aliasname qqj[m [1m [m
(B)0[m[1mprofile-options =[m
[1mqqwq>[m [1;4mCOMMENT[m [1mIS[m [1mqwq>[m [1mchar-literal qqwqqqqqqqqqqqqqqqqqqqqw>[m
[1mx[m [1mmqqqqqqq /[m [1m<qqqqqqqj[m [1mx[m
[1mtq> [1;4mRENAME[m[1m [1;4mTO[m[1m <new-profile-name> qqqqqqqqqqqqqqqqqqqqqqqu[m
[1mtq>[m [1;4mDEFAULT[m [1;4mTRANSACTION[m [1mqq>[m [1mtxn-options[m [1mqqqqqqqqqqqqqqqqu[m
[1mtq> [1;4mTRANSACTION[m [1;4mMODES[m [1mqq>[m [1m(txn-modes) qqqqqqqqqqqqqqqqqqu[m
[1mtq>[m [1;4mLIMIT[m [1mqwqwq>[m [1;4mROWS[m [1mlimit-value[m [1mqqqqqqqqqqqqqqqqqqqqwwu[m
[1mx[m [1mx[m [1mtq>[m [1;4mTIME[m [1mlimit-value[m [1mqqqqqwqwqqqqqqqqqqqwjxx [m
[1mx[m [1mx[m [1mmq>[m [1;4mCPU[m [1;4mTIME[m [1mlimit-value[m [1mqj[m [1mtq>[m [1;4mSECONDS[m[1m u[m [1mxx [m
[1mx[m [1m [m [1mx[m [1mmq>[m [1;4mMINUTES[m[1m j[m [1mxx [m
[1mx[m [1mmqqqqqqqqqqqqqqqqqqqq[m [1m<qqqqqqqqqqqqqqqqqqqqqjx [m
[1mmq> [1;4mNO[m[1m qw> [1;4mDEFAULT[m[1m [1;4mTRANSACTION[m [1mqqwqqqqqqqqqqqqqqqqqqqqqqj [m
[1mt>[m [1;4mTRANSACTION[m[1m [1;4mMODES[m [1mqwqj[m
[1m m> [1;4mLIMIT[m[1mqwq> [1;4mCPU[m[1m [1;4mTIME[m [1mqu[m
[1mtq>[m [1;4mROWS[m [1mqqqu[m
[1mmq>[m [1;4mTIME[m [1mqqqqj[m
3 – Arguments
3.1 – ALIAS aliasname
When attached to multiple databases, the aliasname is required to
direct the ALTER command to the appropriate database.
3.2 – COMMENT_IS
Adds a comment about the profile. SQL displays the text of the
comment when it executes a SHOW PROFILES statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.3 – DEFAULT_PROFILE
Alters the special profile RDB$DEFAULT_PROFILE. This profile
will be used by any user who is not assigned a profile using the
PROFILE clause of CREATE or ALTER PROFILE.
3.4 – DEFAULT_TRANSACTION
DEFAULT TRANSACTION provides a default transaction for the user.
By default, Oracle Rdb starts a READ WRITE transaction if none is
explicitly started. Use the DECLARE TRANSACTION or START DEFAULT
TRANSACTION statement to make use of this definition. You can
override this clause with a DECLARE or SET TRANSACTION statement.
NOTE
Oracle Rdb does not permit the RESERVING or EVALUATING
clauses to appear in the default transaction.
3.5 – LIMIT_CPU_TIME
Syntax options:
LIMIT CPU TIME | NO LIMIT CPU TIME
LIMIT CPU TIME sets the maximum CPU time that can be used by the
query compiler. The keyword DEFAULT indicates that no value is
defined by this profile and is equivalent to NO LIMIT CPU TIME.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT CPU TIME
statement is present in the session, or when the logical name
RDMS$BIND_QG_CPU_TIMEOUT is defined.
NO LIMIT CPU TIME is the default. Units can be specified as
seconds or minutes.
3.6 – LIMIT_ROWS
Syntax options:
LIMIT ROWS | NO LIMIT ROWS
LIMIT ROWS sets the maximum number of rows that can be returned
by a query started by the user. The keyword DEFAULT indicates
that no value is defined by this profile and is equivalent to NO
LIMIT ROWS.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT ROWS
statement is present in the session, or when the logical name
RDMS$BIND_QG_REC_LIMIT is defined.
NO LIMIT ROWS is the default.
3.7 – LIMIT_TIME
Syntax options:
LIMIT TIME | NO LIMIT TIME
LIMIT TIME sets the maximum elapsed time that can be used by the
query compiler. The keyword DEFAULT indicates that no value is
defined by this profile and is equivalent to NO LIMIT TIME.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT TIME
statement is present in the session, or when the logical name
RDMS$BIND_QG_TIMEOUT is defined.
NO LIMIT TIME is the default. Units can be specified as seconds
or minutes.
3.8 – negated-attributes
Syntax options:
NO DEFAULT TRANSACTION
NO TRANSACTION MODES
NO LIMIT CPU TIME
NO LIMIT ROWS N
NO LIMIT TIME
These options explicitly record the negated attribute setting.
These clauses will remove the current setting of any clause being
negated.
3.9 – RENAME_TO
Changes the name of the profile being altered. See the RENAME
statement for further discussion.
3.10 – TRANSACTION_MODES
Syntax options:
TRANSACTION MODES | NO TRANSACTION MODES
TRANSACTION MODES provides the list of allowable transactions
for this user. Please see the SET TRANSACTION MODES clause of the
CREATE DATABASE and ALTER DATABASE statements for more details of
txn-modes.
The transaction modes specified may include modes disabled
for all database users by CREATE, IMPORT, or ALTER DATABASE
statements. However, only the subset allowed by both profile and
database settings will be used. For instance, if the database
specifies (READ ONLY, SHARED READ, PROTECTED READ) and the
profile specifies (READ ONLY, SHARED), the session will be
allowed the subset (READ ONLY, SHARED READ).
See the description in the CREATE PROFILE statement for all other
attributes supported by ALTER PROFILE.
4 – Examples
The following example changes a default transaction for an
existing profile.
SQL> ALTER PROFILE DECISION_SUPPORT
cont> DEFAULT TRANSACTION READ ONLY;