CREATE DATABASE Examples | Teradata Vantage - 17.10 - Example: Creating a New Database - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

To create a new database for the Finance department, use the CREATE DATABASE request as follows.

    CREATE DATABASE finance FROM sysadmin 
    AS PERMANENT = 60000000, 
       SPOOL = 120000000, 
       FALLBACK PROTECTION, 
       AFTER JOURNAL, 
       BEFORE JOURNAL, 
       DEFAULT JOURNAL TABLE = finance.journals, 
       ACCOUNT = 'ACCTG';

The finance database is created from the space available in sysadmin. The 60,000,000 value represents the amount of storage in bytes. To create a database, the initiator must have CREATE DATABASE privileges on the FROM user or database. The new database receives all privileges that have been granted to the initiator.

In the example, the FROM clause allocates space for finance from the sysadmin space rather than from user space; therefore, sysadmin is the immediate owner of finance, and marks is not in the hierarchy of ownership. However, marks is granted automatic creator privileges on finance, which include the privilege to create other objects in the space owned by finance.

The CREATE DATABASE statement does not include the PASSWORD and STARTUP clauses or the DEFAULT DATABASE and COLLATION options. Because these clauses and options affect the session environment, and only a user can establish a session, they do not apply to a database.