Click here for non-aggregate storage version
Change database-wide settings. Permission required: Database Manager.
alter database AsoSamp.Sample clear aggregates;
Deletes all aggregate views of AsoSamp.Sample.
alter database AsoSamp.Sample initialize load_buffer with buffer_id 1;
See Loading Data Using Buffers for more information.
alter database AsoSamp.Sample disable query_tracking;
Turns off the harvesting of query data for database AsoSamp.Sample.
You can change the following database-wide settings using alter database.
|enable startup||Enable users to start the database directly or as a result of requests requiring the database to be started. Startup is enabled by default.|
|disable startup||Prevent all users from starting the database directly or as a result of requests that would start the database. Startup is enabled by default.|
|enable autostartup||Automatically start the database when the application to which it belongs starts. Autostartup is enabled by default. This setting is applicable only when startup is enabled.|
|disable autostartup||Prevent automatic starting of the database when the application to which it belongs starts. Autostartup is enabled by default.|
Begin collecting query data for this database, to be used for query-based view optimization.
To utilize the results of query tracking, use the optional based on query_data clause found in either of the following statements:
Query tracking is disabled by default.
|disable query_tracking||Stop collecting query data for query-based view optimization. Query tracking is disabled by default.|
|Change the database retrieval buffer size. This buffer holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer commands. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.|
|Change the database retrieval sort buffer size. This buffer holds data until it is sorted. The Report Writer and Analytic Services Query Designer use the retrieval sort buffer. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.|
|Set a level of permission that all users or groups can have to the database. Users or groups with higher granted permissions than the minimum permission are not affected.|
|set variable||Change the value of an existing subsitution variable on the database. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&).|
|set active alias_table||Set an alias table as the primary table for reporting and any additional alias requests. Only one alias table can be used at a time. This setting is user-specific; it only sets the active alias table for the user issuing the statement.|
|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
|clear aggregates||Delete all aggregate views.|
|compact outline||Compact the outline file to remove the records of members that have been deleted. Compaction helps keeps the outline file at an optimal size.|
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
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.|
|initialize|destroy load_buffer||Create or destroy a temporary buffer in memory for loading data.|
|unlock all objects||Unlock all objects on the database that are in use by a user or process.|
|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 Spreadsheet Add-in users, use