Teradata CREATE DATABASE
A Teradata database is a local repository for various kinds of database objects like Tables (requires perm space), Views (uses no perm space), Procedures (requires perm space), functions, etc.
Teradata CREATE DATABASE syntax
Below is the syntax of the Teradata create Database.
CREATE DATABASE database_name AS [PERMANENT|PERM] = n BYTES, SPOOL = n BYTES, TEMPORARY = n BYTES;
Space limit in Teradata database can be defined as below –
- Perm Space – Max amount of space available for Tables
- Spool Space – Max amount of space available for query processing
- Temp Space – Used for creating a temporary table.
A newly created database is owned by an existing database until any objects are created in the database, it will be empty.
Teradata CREATE DATABASE Example
The following example creates a UNIVERSITY database.
CREATE DATABASE UNIVERSITY 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 Teradata database, users in Teradata also can hold different database object like tables, views, macros, and triggers. However, a user can’t hold tables until perm space is assigned to it.
A user can logon into Teradata system using password and access object within:
- Other databases or users for which it has access right.
Teradata CREATE USER Syntax
Following is the syntax of CREATE USER in Teradata.
CREATE USER username AS [PERMANENT|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 login.
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 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;