Alter Database (Misc)

Click here for aggregate storage version

Permission required: create_application.

Syntax

Description of the illustration altdb_misc.gif

Use alter database to change the following database-wide settings:

Keywords

reset

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

reset all

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

reset data

Same as using reset.

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.

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

unload alias_table

Delete the specified alias table.

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.

drop variable

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

delete lro

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

unlock all objects

Unlock all objects on the database that are in use by a user or process.

begin archive to file

Prepare the database for backup by an archiving program, and prevent writing to the files during backup.

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.

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.

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.

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'

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.

rename to

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

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.

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 Sample.Basic begin archive to file 'samplebasic.arc';

Backs up the Sample.Basic database files to the specified archive file (samplebasic.arc).

alter database Sample.Basic end archive

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

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 Sample.Basic replay transactions after '11_20_2007:12:20:00';

Replays all transactions that were logged after the specified time.