The syntax of the AW command varies depending on the task that you want to perform.
Triggering Program Execution When an AW Statement Executes
When a program named TRIGGER_AW exists in an analytic workspace, the execution of an AW statement for that workspace automatically executes that program. See "Trigger Programs" and the TRIGGER_AW program, for more information.
When an AW ATTACH statement executes Oracle OLAP checks for other programs as well. See "Startup Programs" for more information.
Options Related to the AW Statement
"Analytic Workspace Options" lists the options that you might want to reset before you either create or attach an analytic workspace.
When your database is installed with the OLAP option, the EXPRESS workspace is always attached in read-only mode in your session. It never automatically becomes the current workspace, even when it is the first or only workspace in your workspace list, because it is for internal use by Oracle OLAP. You can make the EXPRESS workspace the current workspace by explicitly attaching it, but this is not recommended. You cannot detach the EXPRESS workspace.
The AW ALIASLIST command assigns or deletes one or more workspace alias for the specified attached workspace or, when no workspace is specified, for the current workspace. ALIAS indicates that the alias or aliases should be assigned, and UNALIAS indicates that the alias or aliases should be deleted. All aliases for a given workspace are automatically deleted when you detach an analytic workspace.
The name of the analytic workspace. You can specify either an analytic workspace name or an analytic workspace alias, depending on the keywords you are using.
Assigns one or more workspace alias for the specified attached workspace or, when no workspace is specified, for the current workspace. ALIAS indicates that the alias or aliases should be assigned, and UNALIAS indicates that the alias or aliases should be deleted.
All aliases for a given workspace are automatically deleted when you detach an analytic workspace. Therefore, each time you attach an unattached workspace, you must reassign its aliases.
Deletes one or more workspace alias for the specified attached workspace or, when no workspace is specified, for the current workspace.
The alias name for the analytic workspace. Alias names:
Can be from 1 - 26 characters in length. All characters must come from the database character set and must be letters, numerals, or underscores.
Cannot begin with a numeral and cannot be reserved words in the DML. (Use RESERVED to identify reserved words.)
Example 9-49 Assigning an Alias
The following statement assigns sdemo as an alias for the demo workspace, which was created by a user named scott. The full name of the workspace is specified because the current user is not scott.
AW ALIASLIST scott.demo ALIAS sdemo
In the following statement, the user named scott assigns mydemo as an alias for the same workspace.
AW ALIASLIST demo ALIAS mydemo
The AW ATTACH command attaches an analytic workspace to your session. Oracle OLAP makes the specified workspace the current one. Previously attached workspaces move down in the list of attached workspaces to make room for the new current one at the top of the list. When there is a cached version of the requested analytic workspace then the cached version is moved back to the list of attached workspaces unless, of course, the current version of the analytic workspace is more recent than the cached version.
When you attach multiple workspaces, the code and data in all the attached workspaces are available during your session. The current workspace is first on the workspace list, which Oracle OLAP keeps for your session.
Note:
When an AW ATTACH statement executes, it can trigger the execution of several programs. See "Startup Programs" for more information.AW ATTACH workspace -
[ONATTACH [progname]|NOONATTACH] -
[RO [THAW] ] | RW | RWX | MULTI [THAW]] [WAIT | NOWAIT] ] -
[AUTOGO [progname]|NOAUTOGO] -
[AFTER workspace|BEFORE workspace|LAST|FIRST] -
[PASSWORD password]
The name of the analytic workspace. When you use the ATTACH keyword to attach an analytic workspace that is not already attached, you must specify the workspace name. Again this is because no alias has been assigned using AW ALIAS LIST. However, when you use the ATTACH keyword on an already attached workspace (for example, to change its position in the workspace list), you can assign an alias using AW ALIAS LIST and then use that assigned alias.
(Default) When you do not specify progname, the ONATTACH clause automatically runs a program named ONATTACDH if one exists in the attached workspace. You can get the same results by not specifying NOONATTACH.
Specifying NOONATTACH indicates that when a program named ONATTACH exists in the workspace, Oracle OLAP should not execute that program.
(Default) When you do not specify progname, the AUTOGO clause automatically runs a program named AUTOGO if one exists in the attached workspace. You can get the same results by not specifying NOAUTOGO.
When you do specify progname, the AUTOGO clause automatically runs the specified program in the attached program.
Specifying NOAUTOGO indicates that when a program named AUTOGO exists in the workspace, Oracle OLAP should not execute that program.
(Default) Specifies that the workspace is attached in read-only access mode. Users can make private changes to the data in the workspace to perform what-if analysis but cannot commit any of these changes.
An analytic workspace that is attached read-only can be accessed simultaneously by several sessions. The read-only attach mode is compatible with the read/write and multiwriter access mode. A user can attach an analytic workspace in read-only mode when other users have the workspace attached in either read/write and multiwriter access mode. Likewise, a user cannot attach an analytic workspace in read/write exclusive mode when another user has it attached in read-only mode. When you attach an analytic workspace with read-only access, Oracle OLAP executes a program called PERMIT_READ, when it finds one in the workspace.
Specifies that Oracle OLAP attach the current view of an analytic workspace that was frozen using an AW FREEZE command without the NOTHAW keyword.
Specifies that the workspace is attached in read/write access mode. Only one user can have an analytic workspace open in read/write at a time. The user has to commit either all or none of the changes made to the workspace.
An analytic workspace that is attached read/write non-exclusive can be accessed simultaneously by several sessions. The read/write non-exclusive attach mode is only compatible with the read-only access mode. A user can attach an analytic workspace in read/write mode when other users have the workspace attached in read-only mode; however, a user cannot attach an analytic workspace in read/write mode when another user has it attached in any other mode. Likewise, a user cannot attach an analytic workspace in any mode other than read-only when another user has it attached in read/write non-exclusive mode. When you attach an analytic workspace with read/write access, Oracle OLAP executes a program called PERMIT_WRITE, when it finds one in the workspace.
Specifies that the workspace is attached in read/write exclusive access mode. Only one user can have an analytic workspace open in read/write exclusive at a time. The user has to commit either all or none of the changes made to the workspace.
An analytic workspace that is attached read/write exclusive cannot be accessed by any other sessions. The read/write exclusive attach mode is not compatible with any other access modes. A user cannot attach an analytic workspace in read/write exclusive mode when another user has it attached in any mode. Likewise, a user cannot attach an analytic workspace in any other mode when another user has it attached in read/write exclusive mode. When you attach an analytic workspace with read/write access, Oracle OLAP executes a program called PERMIT_WRITE, when it finds one in the workspace.
Specifies that the workspace is attached in multiwriter access mode. An analytic workspace that is attached in multiwriter mode can be accessed simultaneously by several sessions. In multiwriter mode, users can simultaneously modify the same analytic workspace in a controlled manner by specifying the attachment mode (read-only or read/write) for individual variables, relations, valuesets, and dimensions.
The multiwriter attach mode is only compatible with read-only and multiwriter modes. A user cannot attach an analytic workspace in multiwriter mode when another user has it attached in read/write or exclusive modes. Likewise, a user cannot attach an analytic workspace in read/write or exclusive mode when another user has it attached in multiwriter mode.
Specifies whether Oracle OLAP waits for an analytic workspace to become available for access when you request access to an analytic workspace that is being used with read/write exclusive access or when you request read/write access to an analytic workspace that is being used with read/write non-exclusive access. NOWAIT (the default) causes Oracle OLAP to produce an error message indicating that the workspace is unavailable. When you specify WAIT, Oracle OLAP waits for the workspace to become available for access. The number of seconds that Oracle OLAP waits for access depends on the value of the Oracle OLAP AWWAITTIME option.
(Default) Makes the workspace you are attaching the current workspace in the workspace list.
Puts the workspace after the current workspace in the workspace list and before the EXPRESS workspace. When there are other workspaces attached before the EXPRESS workspace, the specified workspace is attached after them. When there are no workspaces before the EXPRESS workspace, LAST makes the specified workspace the current one. LAST ignores any workspaces after the EXPRESS workspace.
Let you specify the position in the workspace list of the newly attached workspace relative to an analytic workspace that is attached. Use AFTER, rather than LAST, to attach an analytic workspace after the EXPRESS workspace. When specifying BEFORE puts the workspace first, the workspace becomes the current one.
The order of the workspace list determines the order in which workspaces are searched when Oracle OLAP looks for programs or objects named in programs.
Specifies a password to be checked in a startup program to give or deny access to the workspace being attached. See "Startup Programs".
Using ATTACH on an Already-Attached Workspace
Reattaching an attached workspace with an AW ATTACH workspace statement does not cause Oracle OLAP to bring a new copy of the workspace into working memory. Instead, Oracle OLAP takes the following actions:
Makes the workspace the current workspace.
Runs an Autogo program, when you specify the AUTOGO keyword
However, when you have made any changes to data during the session, they are not discarded when you reattach an active workspace. Furthermore, current aliases for the workspace are not changed.
Managing Analytic Workspaces Attached in Multiwriter Mode
You use the following commands to manage objects in multiwriter mode:
ACQUIRE -- Acquires and (optionally) resynchronizes the specified objects so that their changes can be updated and committed.
RELEASE -- Changes the access mode of the specified variables, relations, valuesets, or dimensions from read/write (acquired) access to read-only access.
RESYNC -- Drops private changes for the specified read-only objects and retrieves the data from the latest visible generations.
REVERT-- Drops all changes made to the specified objects since they were last updated, resynchronized (using a RESYNC statement), or acquired using ACQUIRE with the RESYNC phrase, or since the analytic workspace was attached.
The following considerations apply:
Only one user can acquire an object in read/write mode at a time. You can first acquire an object in read-only mode, and then, assuming another user has not also acquired it in read-only mode, you can acquire it in read/write mode without releasing it first. However, once another user has acquired an object in read-only mode, you cannot acquire the same object in read/write mode until the other user releases the object. When a specified object has been acquired by another user or when your read-only generation for a specified object is not the latest generation for the object, an acquire fails.
You must resynchronize all variables, valuesets, and relations that share a composite dimension at the same time.
When resynchronizing objects, keep in mind the logical relationship of different objects to avoid losing the logical consistency of the data by promoting some objects, but not others to a new generation.
Objects that share a composite dimension can be resynchronized separately when all such objects that are not being resynchronized are either unchanged or acquired.
You cannot update a variable if any of its dimensions have been acquired and modified.
You must acquire a dimension before you maintain it.
If you release a dimension, then an automatic revert occurs.
Releasing objects that have been updated does not allow others to acquire the object until you commit or roll back the transaction. It may still be useful to release an object that has been updated before a commit when one wants to make further what-if changes and later update all acquired variables
Reverting a dimension after adding dimension values is not recommended because it can result in suboptimal space allocation for variables dimensioned by that dimension
If an acquired variable is dimension by an acquired dimension that has been maintained then you cannot update that variable until after you update or release the dimension.
You cannot delete dimension values.
Attaching a Frozen Analytic Workspace
Once an analytic workspace is frozen, attaching an analytic workspace in RO and MULTI attaches the frozen view of the workspace unless you specify the THAW keyword to request that the current view be attached. (When you attach in RW or RW, you always get the latest generation.)
When you attach the current view, the state of the analytic workspace may not necessarily be consistent if there is a multi-step build with intermediate commits. For example, assume that there is an analytic workspace that has two variables: actual and budget. Assume also that you have populated actual and then issued UPDATE and COMMIT commands. At this point in time, there is data only in actual.
When you are attaching a frozen analytic workspace in read multi mode, you can use the multi-writer commands (RESYNC and ACQUIRE) to retrieve up-to-date versions of the data whether or not you have specified AW FREEZE with the NOTHAW keyword.
Conflicts between Workspace Names and Aliases
You cannot attach an analytic workspace that is in your schema and whose name is the same as an assigned alias. Similarly, you cannot assign an alias that duplicates the name of an attached workspace that is in your schema. Furthermore, you cannot assign the same alias to two attached workspaces.
In an AW DELETE statement, when you specify an analytic workspace name (for an analytic workspace that is not attached) and the name is the same as an assigned alias, Oracle OLAP interprets the name as an alias and reports an error.
Assume that you have created an analytic workspace named awtest that contains five programs named PERMIT_READ, PERMIT_WRITE, ONATTACH, MYATTACH, and AUTOGO that have the following definitions.
DEFINE PERMIT_READ PROGRAM BOOLEAN PROGRAM SHOW 'permit_read program executing' AW LIST RETURN YES END DEFINE PERMIT_WRITE PROGRAM BOOLEAN PROGRAM SHOW 'permit_write program executing' AW LIST RETURN YES END DEFINE ONATTACH PROGRAM BOOLEAN PROGRAM SHOW 'onattach program executing' AW LIST RETURN YES END DEFINE MYATTACH PROGRAM BOOLEAN PROGRAM SHOW 'myattach program executing' AW LIST RETURN YES END DEFINE AUTOGO PROGRAM PROGRAM SHOW 'autogo program executing' AW LIST END
The programs that execute when you attach awtest vary depending on the attachment mode and keywords in the AW ATTACH statement:
When you attach awtest in read/write mode using the following statements.
AW DETACH awtest AW ATTACH awtest RW
First the PERMIT_WRITE program executes, and then the ONATTACH program executes.
When you attach awtest in read-only mode using the following statements.
AW DETACH axuserwtest AW ATTACH awtest NOONATTACH RO
Only the PERMIT_READ program executes.
When you attach awtest in read-only mode using the following statements.
AW DETACH awtest AW ATTACH awtest RO
First the PERMIT_READ program executes, and then the ONATTACH program executes.
When you attach awtest in read-only mode using the following statements.
AW DETACH awtest AW ATTACH awtest ONATTACH myattach RO
First the PERMIT_READ program executes, and then the MYATTACH program executes.
When you attach awtest in multi mode using the following statements.
AW DETACH awtest AW ATTACH awtest MULTI
First the PERMIT_WRITE program executes, and then the ONATTACH program executes.
When you attach awtest in read-only mode using the following statements.
AW DETACH awtest AW ATTACH awtest AUTOGO
First the PERMIT_WRITE program executes. Secondly, the ONATTACH program executes. Finally, the AUTOGO program executes.
Example 9-51 Attaching an Analytic Workspace Using an ONATTACH Program
Suppose you have two workspaces of sales data, one for expenses and one for revenue. You have a third workspace called analysis contains programs to analyze the data. Your analysis workspace has the following ONATTACH program to attach the other two.
DEFINE onattach PROGRAM PROGRAM AW ATTACH expenses RW AFTER analysis AW ATTACH revenues RW AFTER analysis END
To run the ONATTACH program, attach the analysis workspace with the following statement.
AW ATTACH analysis
When you issue an AW LIST statement, you can see from the following output, that all three of your analytic workspaces are attached.
ANALYSIS R/W CHANGED XUSER.ANALYSIS REVENUE R/W UNCHANGED XUSER.REVENUES EXPENSES R/W UNCHANGED XUSER.EXPENSES EXPRESS R/O UNCHANGED SYS.EXPRESS
The AW CREATE command creates a new workspace and make it the current workspace in your session.
Oracle OLAP automatically executes a COMMIT as part of its procedure for creating an analytic workspace. Previously attached workspaces move down in the list of attached workspaces to make room for the new one at the top of the list.
Also, if the current analytic workspace is creating a different analytic workspace and the current workspace contains a program named TRIGGER_AW, then the TRIGGER_AW program executes.
Note:
Before you can create an analytic workspace you need the appropriate SQL GRANT privileges as outlined in "Privileges Needed to Create and Delete Analytic Workspaces".AW CREATE workspace [position] [UNPARTITIONED|PARTITIONS n] -
[TABLESPACE tblspname [SEGMENTSIZE n [K, M, or G]]]
where position specifies the workspace's position in the workspace list and is one of the following values. (FIRST is the default.)
The name of the analytic workspace. Workspace names:
Can be from 1 - 26 characters in length. All characters must come from the database character set and must be letters, numerals, or underscores.
Cannot begin with a numeral and cannot be reserved words in the DML. (Use RESERVED to identify reserved words.)
(Default) Makes the workspace you are attaching the current workspace.
Puts the workspace after the current workspace and before the EXPRESS workspace. When there are other workspaces attached before the EXPRESS workspace, the specified workspace is attached after them. When there are no workspaces before the EXPRESS workspace, LAST makes the specified workspace the current one. LAST ignores any workspaces after the EXPRESS workspace.
Specify the position of the newly attached workspace relative to an analytic workspace that is already attached. Use AFTER, rather than LAST, to attach an analytic workspace after the EXPRESS workspace. When specifying BEFORE puts the workspace first, the workspace becomes the current one.
The order of the workspace list determines the order in which workspaces are searched when Oracle OLAP looks for programs or objects named in programs.
Specifies that the relational table that is the analytic workspace is not a partitioned table.
Specifies that the relational table that is the analytic workspace is a hash partitioned table with n partitions. Specifying a value of 0 (zero) for n is the same as specifying UNPARTITIONED. The default value of n is 8.
Specifies the name of an Oracle Database tablespace in which the analytic workspace is created.
Tip:
Oracle suggests that you use the TABLESPACE argument to create your workspace in a tablespace that has been prepared for this purpose. Ask your DBA which tablespace use.With the CREATE keyword, this argument sets the maximum size of each segment for the workspace being created. When you do not specify K, M, or G, the value you specify for n is interpreted as bytes. When you specify K, M, or G after the value n, the value is interpreted as kilobytes, megabytes, or gigabytes, respectively.
Analytic Workspace Permissions
You can add security to analytic workspaces at several levels:
At the relational table level using SQL GRANT statements
At the analytic workspace level and workspace object level using different attachment modes and startup programs. See the AW ATTACH command and "Startup Programs".
The AW DELETE command deletes a detached analytic workspace from the database. It is important to note that Oracle OLAP automatically executes a COMMIT as part of its procedure for deleting an analytic workspace. The DELETE keyword executes successfully only when no user has the workspace attached.
Note:
If the current analytic workspace is deleting a different analytic workspace and the current workspace contains a program named TRIGGER_AW, then the TRIGGER_AW program executes.The name of the analytic workspace. You must specify the name; you cannot specify an alias.
The AW DETACH command removes an analytic workspace from the workspace list. When you remove the first workspace, the second workspace becomes the current workspace (unless it is the EXPRESS workspace). When you detach an analytic workspace, changes that were made before an UPDATE was issued remain in the database and become permanent with the next COMMIT. When changes were made after the UPDATE was issued, they are discarded.
Note:
When a program named TRIGGER_AW exists in the analytic workspace, the execution of an AW DETACH statement automatically executes that program.Specifies that the analytic workspace is cached if there have been no changes to it since it was attached. (Default)
Specifies that the analytic workspace is not cached even if there have been no changes to it since it was attached.
Note:
You must specify NOCACHE when you detach an analytic workspace if you want Oracle OLAP to execute any Permission, OnAttach, or Autogo programs the next time you attach the workspace in the same session.The name of the analytic workspace. You can specify either an analytic workspace name or an analytic workspace alias, depending on the keywords you are using.
Determining if an Analytic Workspace Has Changed
The following statements indicate if an analytic workspace has been changed while it was attached:
AW function with the CHANGED keyword
AW LIST shows the analytic workspace as unattached.
By default the list of cached analytic workspaces is two. In other words, by default only two analytic workspaces can be on the cached at one time and as new workspaces are added to the cache list, earlier workspaces are removed. For example, assume that you have detached two analytic workspaces in the following order: 1) mywk1, 2) mywk2. Now you issue an AW DETACH CACHE command for mywk3. Oracle OLAP removes mywk1 from the cache and the cache list and caches mywk3 adding it to the cache list after mywk2.
Note:
Under severe memory contention, Oracle OLAP may release memory by emptying the cache.You can change the size of the cache by using the event number 37372 where level is the number of analytic workspaces to retain. Specify a level of 1024 to disable the cache entirely. Not determined for beta: Is this information valid to regular developers?
Programs Executed When an Analytic Workspace is Detached
When an analytic workspace is detached, the following programs may execute:
If that analytic workspace being detached contains a program named ONDETACH, the ONDETACH program executes.
If the current analytic workspace is detaching a different analytic workspace and the current workspace contains a program named TRIGGER_AW, then the TRIGGER_AW program executes.
The AW FREEZE command commits the current transaction (if any) and sets a flag that specifies that the analytic workspace is the default attach version of the workspace. Later, when a request is made to attach the workspace in read only or read multi mode, Oracle OLAP attaches this flagged generation of the analytic workspace.
Note:
You must be attached to the analytic workspace in a write mode to execute this command.Specifies that you cannot specify the THAW keyword with AW ATTACH when you attach the workspace at a later time.
Note:
Once an analytic workspace is frozen, attaching an analytic workspace in read only or read multi mode attaches the analytic workspace as of the frozen view unless you specify the THAW keyword with the AW ATTACH command.Freezing an Analytic Workspace
Keep the following points in mind when freezing an analytic workspace:
Only one generation of an analytic workspace can be frozen at a time
You cannot refreeze a currently frozen analytic workspace without first thawing it using the AW THAW command.
The AW LIST command sends to the current outfile a list of the active workspaces, along with their update status.
The first workspace in the list is the current workspace, unless you do not have a current workspace. The meaning of the update status, CHANGED or UNCHANGED, depends on whether the workspace is attached with read/write or read-only access and whether or not the workspace is being shared with other users. The update status displayed by AW LIST is as follows:
An unshared workspace in read/write mode -- The update status is CHANGED when you have made changes since attaching the workspace or since your last update.
An unshared workspace in read-only mode -- The status is always UNCHANGED because you cannot update it.
A shared or unshared workspace in read/write mode -- The status is CHANGED when you have made changes since attaching the workspace or since your last update.
A shared workspace in read-only mode -- The status is CHANGED when another user has updated it since you accessed it. To access the new objects or data, you must detach and reattach the workspace after the other user commits his or her changes. If you keep the workspace attached, then your view of the workspace remains unchanged.
The name of the current workspace is first on the workspace list and is the name returned by the AW(NAME) function. (See the AW function for details.) The NAME dimension includes only the objects in the current workspace. Programs such as AWDESCRIBE and LISTBY list only objects in the current workspace. When an analytic workspace is active but not current, you can change and update its data, edit and run its programs, and modify its objects.
Assume that you have just connected to Oracle OLAP using the OLAP Worksheet. You issue an AW LIST statement that returns a value showing that the only attached analytic workspace is EXPRESS.
AW LIST EXPRESS R/O UNCHANGED SYS.EXPRESS
Now you create an analytic workspace and issue another AW LIST statement. You can see that both the EXPRESS analytic workspace and the newly created analytic workspace are attached.
AW CREATE myaw AW LIST MYAW R/W UNCHANGED MYNAME.MYAW EXPRESS R/O UNCHANGED SYS.EXPRESS
The AW PURGE CACHE command detaches any analytic workspaces that are currently in the cache.
The AW ROLLBACK TO FREEZE makes current the version of the analytic workspace that was created by the last AW FREEZE command.
The AW SEGMENTSIZE command sets up an analytic workspace for multiple segments.
The name of the analytic workspace. You can specify either an analytic workspace name or an analytic workspace alias, depending on the keywords you are using.
Sets the maximum size of each segment for a specified workspace or, when no workspace is specified, for the current workspace.
When the current workspace already has several segments, setting SEGMENTSIZE affects only the most recent one and has no effect on previous ones. Previous segments may have various sizes, determined by the SEGMENTSIZE setting at the time each one was created. When you do not specify K, M, or G, the value you specify for n is interpreted as bytes. When you specify K, M, or G after the value n, the value is interpreted as kilobytes, megabytes, or gigabytes, respectively.
The AW THAW command commits the current transaction (if any) and undoes a previous AW FREEZE command.
Deletes all of the objects and data from an existing analytic workspace. Oracle also deallocates all of the table space used by the analytic workspace.Removing data using AW TRUNCATE can be more efficient and less "destructive" than deleting an analytic workspace using AW DELETE. For example, when you remove data using AW TRUNCATE, all of the object privileges that were previously granted remain.
For more information on truncating a table, see TRUNCATE TABLE in Oracle Database SQL Language Reference.
Note:
Before you can truncate an analytic workspace in a schema that you do not own, you need the appropriate SQL GRANT privilege as outlined in "Privileges Needed to Create and Delete Analytic Workspaces".The name of the analytic workspace. You must specify the name; you cannot specify an alias.