Creates a profile that extends a user definition within the
database with special attributes that control transactions
and resource usage. When a user attaches to the database using
ATTACH, CONNECT or SET SESSION AUTHORIZATION, they will either
load their assigned profile definition or inherit the default
profile (if defined).
1 – Environment
You can use the CREATE 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
CREATE -+--> PROFILE --> <profilename> -------------+--+-> profile-options -+->
+--> DEFAULT PROFILE -+--------------------++ +---------<----------+
+> ALIAS aliasname -+
profile-options =
--+-> COMMENT IS -+-> char-literal --+--------------------+>
| +------- / <-------+ |
+-> DEFAULT TRANSACTION --> txn-options ----------------+
+-> TRANSACTION MODES --> (txn-modes) ------------------+
+-> LIMIT -+-+-> ROWS limit-value --------------------+++
| | +-> TIME limit-value -----+-+-----------++||
| | +-> CPU TIME limit-value -+ +-> SECONDS + ||
| | +-> MINUTES + ||
| +-------------------- <---------------------+|
+-> NO -+> DEFAULT TRANSACTION --+----------------------+
+> TRANSACTION MODES -+-+
+> LIMIT-+-> CPU TIME -+
+-> ROWS ---+
+-> TIME ----+
limit-value =
--+-> positive-integer-literal -+->
+-> UNLIMITED ----------------+
+-> DEFAULT ------------------+
3 – Arguments
3.1 – ALIAS aliasname
When attached to multiple databases, the aliasname is required to
direct the CREATE command to the appropriate database.
3.2 – COMMENT_IS
This optional clause can be used to add several lines of comment
to the profile object. The comment is displayed by the 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
Creates 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 – 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).
4 – Examples
Example 1
The following example specifies the allowed transaction modes for
any user assigned this profile.
SQL> CREATE PROFILE DECISION_SUPPORT
cont> COMMENT IS 'limit transactions used by report writers'
cont> TRANSACTION MODES (NO READ WRITE, READ ONLY);
Example 2
This example shows the use of the LIMIT clauses to set boundaries
for standard database users.
SQL> create profile STANDARD_USER
cont> limit rows 10000
cont> limit time 10 minutes
cont> limit cpu time 20 seconds;
SQL> show profile STANDARD_USER;
STANDARD_USER
Limit rows 10000
Limit time 10 minutes
Limit CPU time 20 seconds
SQL> alter profile STANDARD_USER
cont> limit time 60 minutes;