Click here for aggregate storage version

Syntax

• DBS-NAME

• FILE-NAME

• ALT-NAME-SINGLE

• VARIABLE-NAME

• USER-NAME

• DATE

• LOG-TIME

• ID-RANGE

• DBS-STRING

• COMMENT-STRING

Keywords

Use MaxL alter database to change the following database-wide settings. The minimum application permission required for most of the statements is Database Manager, with exceptions noted.

alter database DBS-NAME reset

Clear all data and linked-reporting objects from the database, but preserve the outline.

Example:

alter database Sample.Basic reset;

alter database DBS-NAME reset all

Clear all data, Linked Reporting Objects, and the outline.

Example:

alter database Sample.Basic reset all;

alter database DBS-NAME reset data

Clear all data and linked-reporting objects from the database, but preserve the outline (same as using reset).

Example:

alter database Sample.Basic reset data;

alter database DBS-NAME force restructure

Explicitly restructure the database to eliminate or reduce fragmentation. By default, this statement is run in serial. To enable parallel restructuring, use the RESTRUCTURETHREADS configuration setting.

Example:

alter database Sample.Basic force restructure;

alter database DBS-NAME load alias_table …

Load an alias table from a file to the current database. The feeder file (FILE-NAME) must follow these rules:

• Must be correctly formatted.

• Must be located on the Essbase Server, in the cube directory (not on a client computer).

  See Environment Locations in the Essbase Platform for information about <Application Directory>, cube directory, and other directory locations in Essbase.

• FILE-NAME must include the full path.

Sample contents of a feeder file for loading an alias table:

   $ALT_NAME
   "400-10"        Guava
   "400-20"        Tangerine
   "400-30"        Mango
   $END

Example:

alter database Sample.Basic load alias_table "Long Names" from data_file 'newalias.alt';

Imports the alias table newalias.alt into Sample Basic. The file newalias.alt must be already uploaded to the Sample Basic cube directory.

alter database DBS-NAME unload alias_table …

Delete the specified alias table.

Example:

alter database Sample.Basic unload alias_table "Long Names";

alter database DBS-NAME add variable …

Create a database-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable. A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts.

If substitution variables with the same name exist at server, application, and database levels, the order of precedence for the variables is as follows: a database level substitution variable supersedes an application level variable, which supersedes a server level variable.

Example:

alter database Sample.Basic add variable Month Oct;

alter database DBS-NAME drop variable …

Remove a substitution variable and its corresponding value from the database.

Example:

alter database Sample.Basic drop variable Month;

alter database DBS-NAME delete lro ...

Delete Linked Reporting Objects linked to the active database for a given user name or modification date.

Example:

alter database Sample.Basic delete lro by user3 before '11/01/2024';

alter database DBS-NAME unlock all objects

Unlock all objects on the database that are in use by a user or process. You must be a service administrator. Service administrators can unlock any object, but other users can unlock only those objects that they locked.

Example:

alter database Sample.Basic unlock all objects;

alter database DBS-NAME begin archive …

Prepare the database for backup by an archiving program, and prevent writing to the files during backup. You must be a system administrator to perform this action.

This statement requires the database to be started.

Begin archive achieves the following outcomes:

• Commits any modified data to disk.

• Switches the database to read-only mode. The read-only state persists, even after the application is restarted, until it is changed back to read-write using end archive.

• Reopens the database files in shared, read-only mode.

• Creates a file containing a list of files that need to be backed up. Unless a different path is specified, the file is stored in the database directory.

Begin archive and end archive do not perform the backup; they simply protect the database during the backup process.

Note:

Using the begin archive to file and end archive grammar is the only supported way to initiate backup and recovery of a database using MaxL.

Example:

alter database Sample.Basic begin archive to file 'samplebasic.arc';

Backs up the Sample.Basic files to samplebasic.arc.

alter database DBS-NAME end archive

Return the database to read-write mode after backing up the database files.

This statement requires the database to be started.

End archive achieves the following outcomes:

• Returns the database to read-write mode.

• Re-opens database files in exclusive, read-write mode.

Note:

Using the begin archive to file and end archive grammar is the only supported way to initiate backup and recovery of a database using MaxL.

Example:

alter database Sample.Basic end archive;

Returns the Sample.Basic database to read-write mode after backing up the database files.

alter database DBS-NAME replay transactions …

Replays the database transactions that were logged after the last replay request was originally executed or after the last restored backup's time (whichever occurred later).

Transactions that are executed and logged after the restore operation are not replayed, unless you replay those transactions using their sequence IDs. After restoring a database, Oracle recommends that you finish replaying the transactions that were logged after the backup and before the restore and that are needed to fully recover the database; then you can continue executing new transactions.

Note:

Transaction logging and replay is available only for backward compatibility support in Essbase 21c. Features added after Essbase 11g On-Premise are not supported for transaction logging and replay; including (but not limited to):

• Batch outline editing
• Application workbooks and Cube Designer activity
• Scenario management
• External data load using a Datasource
• REST API data loads and other updates
• Federated partitions
• MDX inserts
• Drill through transactions

alter database DBS-NAME replay transactions after LOG-TIME

Replays the transactions that were logged after the specified time. Enclose the TIME value in quotation marks; for example: '11_20_2007:12:20:00'

Example:

alter database Sample.Basic replay transactions after '11_20_2007:12:20:00';

Replays all transactions that were logged after the specified time.

alter database DBS-NAME replay transactions using sequence_id_range ID-RANGE

Replays the transactions specified by a comma-separated list of sequence ID ranges. A range can consist of:

• A single transaction: n to n; for example, 1 to 1

• Multiple transactions: x to y; for example, 20 to 100

Each logged transaction is assigned a sequence ID, indicating the order in which the transaction was performed. To ensure the integrity of the restored data after a replay, Essbase enforces the replay of transactions in the same order in which they were originally performed. The order of sequence IDs are tracked across multiple replay commands.

Note:

You can skip replaying a transaction if you are absolutely sure that the transaction results are not required to recover the database.

Example:

alter database Sample.Basic replay transactions using sequence_id_range 1 to 10,20 to 100;

Replays the transactions in the Sample.Basic database with sequence IDs 1 through 10 and 20 through 100.

alter database DBS-NAME rename …

Rename the database. When you rename a database, the cube directory is also renamed.

Example:

alter database Sample.Basic rename to Basic2;

alter database DBS-NAME comment …

Create a description of the database. The maximum number of characters is 80. This description is available to database administrators. To annotate the database for Smart View or other grid client users, use set note instead.

Example:

alter database Sample.Basic comment 'my comment';