Teradata CREATE DATABASE
A Teradata database is a repository for various kinds of database objects like Tables (requires perm space), Views (uses no perm space), Procedures (requires perm space), functions, macros, etc.
This is similar to the SCHEMA in other relational databases.
To create a database, one must have the
CREATE DATABASE privilege on the immediate owner database.
Teradata CREATE DATABASE syntax
The syntax of the Teradata Create Database is as follows.
CREATE DATABASE database_name [FROM database_name] AS PERM = n BYTES, SPOOL = n BYTES, TEMPORARY = n BYTES, [ACCOUNT = account_string,] [DEFAULT MAP = map_name,] [NO FALLBACK PROTECTION,] [NO|DUAL BEFORE JOURNAL,] [NO|DUAL|LOCAL|NOT LOCAL AFTER JOURNAL,] [DEFAULT JOURNAL TABLE = database_name.table_name];
Space limit in the Teradata database can be defined as below –
- Perm Space – Max amount of space available for Tables and other objects.
- Spool Space – Max amount of space available for query processing.
- Temp Space – Used for creating a temporary table.
A newly created database remains empty until any database objects are created in it.
Teradata CREATE DATABASE Example
The following example creates a STORE database from the SYSDBA database.
CREATE DATABASE TUTORIALSBOOK FROM SYSDBA AS PERM = 5368709120, -- 5GB SPOOL = 5368709120, -- 5GB TEMPORARY = 2147483648; -- 2GB NO FALLBACK NO BEFORE JOURNAL NO AFTER JOURNAL;
Teradata CREATE USER
In Teradata, users and databases are exactly the same except that the user is assigned a password.
Same as the Teradata database, users in Teradata also can hold different database objects like tables, views, macros, and triggers. However, a user can’t hold tables until perm space is assigned to them.
A user can logon into the Teradata system using a password and access objects within:
- Other databases or users to which it has access rights.
Teradata CREATE USER Syntax
The syntax of CREATE USER in Teradata is as follows.
CREATE USER username AS PERM = n BYTES, PASSWORD = password, TEMPORARY = n BYTES, SPOOL = n BYTES;
Everything is the same as DATABASE creation except the PASSWORD parameter where you have to provide a password for the user to log in.
Teradata CREATE USER Example
CREATE USER SYSDBA AS PERM = 5368709120, PASSWORD = password1 SPOOL= 5368709120, TEMPORARY = 2147483648;
In the above example, we have created a user
PERM space 5368709120 or 5GB,
SPOOL space 5368709120 or 5GB, and
TEMPORARY space 2147483648 or 2GB.
A newly created user also remains empty until any objects are created in it.
The next step is to provide all the privileges to the SYSDBA user to perform operations on the
SYSDBA user. This step should be performed after logging into the
GRANT ALL ON SYSDBA to SYSDBA WITH GRANT OPTION;