Setting DEFAULT STORAGE for Users and Databases - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Optionally, you can specify a default storage for databases and users. After creation, a table is placed in the default storage for the database.

If storage is not specified (for the user or database or during the CREATE TABLE command), database tables are stored on Block File System by default.

To specify the type of storage when a table is created, see CREATE TABLE STORAGE Option.

See the following to set default storage for a user or database:

Description Command
Create a user and optionally specify the default storage for the user. Tables created by the user are stored on the default storage. If no storage is specified, tables created by the user are stored on Block File System.

Example: Create a user with default storage on Object File System.

 
CREATE USER user_name FROM SYSDBA AS
PASSWORD = user_password
PERM = 10E9
SPOOL = 10E9
DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE ON ERROR
NO BEFORE JOURNAL
NO AFTER JOURNAL;
Modify a user to change its default storage to Object File System. 
MODIFY USER user_name 
AS DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE ON ERROR;
Create a database and optionally specify the default storage for the tables. If no storage is specified, tables are stored on Block File System.

Example: Create a database with default storage on Object File System.

 
CREATE DATABASE db_name FROM DBC AS
PERM=12E12
DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE ON ERROR;
Modify a database to change its default storage to Object File System. 
MODIFY DATABASE db_name AS
DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE ON ERROR;

See Object File System.