The DBC.DBCInfoV ViewUse this view to find out what version of Teradata you are on. This is a V13 system.
Restricted Views have an X at the End of their Name
With V12, they introduced a V at the end of some Data Dictionary Views. These are the newer views. The only difference is that the previous views always displayed in Char(30). The newer views (with a V) have a data type of VARCHAR(128), which can display information associated with UNICODE.
Newer V Views introduced in V12 and can display information using UNICODE.
The DBC.DatabasesV View
SELECT DatabaseName(CHAR(10)) AS “Name” ,CreatorName(CHAR(10)) AS “Creator” ,CreateTimeStamp ,PermSpace(FORMAT ‘zzz,zz9,999’) ,SpoolSpace(FORMAT ‘zzz,zz9,999’) ,TempSpace(FORMAT ‘zzz,zz9,999’) ,DBKind FROM DBC.DatabasesVWHERE DatabaseName like ‘SQL%’ORDER BY 1;
This View shows Database, user, and immediate parent information plus some great information about Space among other things.
The DBC.Users View
SELECT UserName ,CreatorName ,PermSpace ,SpoolSpace ,TempSpace ,LastAlterTimeStampFROM DBC.UsersWHERE UserName = USER ;
The DBC.Tables View
This view is quite similar to the Databases view but includes columns specific to users.
The DBC.Tables ViewSELECT TRIM(DatabaseName) || ‘.’ || TableName AS “Fully Qualified Name” ,TableKindFROM DBC.TablesVWHERE Databasename IN ( ‘SQL_Class’, ‘SQL_Views’)AND TableName like ‘Employe%’;
This view shows tables, views, macros, triggers, and stored procedures information.
Show the tables in the SQL_Class Database that are Fallback Protected
SELECT Count(*)FROM DBC.TablesVWHERE DatabaseName = ‘SQL_Class’AND TableKind IN(‘T’, ‘O’)AND ProtectionType = ‘F’;
Show the tables in the SQL_Class Database that are NOT Fallback Protected
SELECT Count(*)FROM DBC.TablesVWHERE DatabaseName = ‘SQL_Class’AND TableKind IN(‘T’, ‘O’)AND ProtectionType = ‘N’;
The DBC.Indices View
The DBC.Columns View
New V14 – The DBC.PartitioningConstraintsV View
This view shows columns and their parameters in tables, views, and macros.
SELECT TableName ,ColumnPartitioningLevel AS “Level” ,PartitioningLevels AS “#Levels” ,ConstraintText AS “ConText”FROM DBC.PartitioningConstraintsVWHERE DatabaseName = ‘SQL_Class’;
Show the number of tables in the SQL_Class database that are partitioned
SELECT COUNT(*)FROM DBC.PartitioningConstraintsVWHERE ColumnPartitioningLevel = 0;
Show the number of tables in the entire system that are partitioned
SELECT COUNT(*)FROM DBC.PartitioningConstraintsVWHERE ColumnPartitioningLevel <> 0;
Show the number of tables in the entire system that have column partitioning
The DBC.AccountInfo View
UserName , AccountName , UserOrProfile
The DBC.AMPUsage View
SELECT COUNT(DISTINCT (Vproc)) AS “# of AMPs// in our System”FROM DBC.AMPUsageV WHERE UserName = USER;
The DBC.AllTempTables
This table is only for Global Temporary Tables
The DBC.Triggers
The DBC.All_RI_ChildrenV
DBC.SessionInfoV Information
SELECT COUNT(*)FROMDBC.SessionInfoV;
Count the number of sessions currently running on the system
SELECT * FROM DBC.SessionInfoV
WHERE UserName = USER;
Show me everything about my sessions
SELECT UserName ,COUNT(*)FROM DBC.SessionInfoV
GROUP BY 1ORDER BY 2 ;
Show each User and the number of sessions that they have running that are currently on the system.
DBC.LogonOffV
AllRightsV,AllRoleRights,UserRightsV,UserGrantedRightsV,
DBC.Profiles View,RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,
AllRightsV,
SELECT COUNT(*)FROM DBC.AllRightsV;
lists all rights granted to users in the system.
AllRoleRights,
SELECT * FROMDBC.AllRoleRightsV;
lists all rights granted to roles in the system.
UserRightsV
SELECT COUNT(*)FROM DBC.UserRightsV;
lists all rights granted to the current user.
SELECT * FROM DBC.UserRightsV
WHERE Databasename = ‘sql00’;
Shows all the Access Rights for user SQL00
UserGrantedRightsV
SELECT Count(*)FROM
DBC.UserGrantedRightsV;
Show the number of Access Rights that you explicitly granted
SELECT * FROMDBC.UserGrantedRightsV;
Shows all the Access Rights that you have explicitly granted
DBC.Profiles View,RoleMembers,UserRoleRights,RoleInfo, and ProfileInfoVX,
The DBC.Profiles View
This view shows information about Profiles.
RoleMembers
SELECT * FROMDBC.RoleMembersVX;
Check to see your role names, the grantor and the date it was granted
UserRoleRights
SELECT RoleName, DatabaseName,TableName, ColumnName, AccessRightFROM DBC.UserRoleRightsVORDER BY 1;
lists all rights granted to the enabled roles of the user.
RoleInfo
SELECT COUNT(*)FROM DBC.RoleInfoVX;
Check to see all the roles that you personally have created
ProfileInfoVX
SELECT * FROMDBC.ProfileInfoVX;
Show all information about your profiles
SELECT Role;
Check to see if you have a Role
SELECT Profile;
Check to see if you have a profile
The RoleMembers, UserRoleRights, RoleInfoVX and ProfileInfoVX views show you what you need to know about Roles and Profiles. Remember that Roles are for “Rights” and Profiles are for “People”.
Understanding that Space is based on a Per-AMP Basis
The UpdateSpace utility is a utility designed to fix inconsistencies in the DBC.DatabaseSpace table, which might occur occasionally. This is run from the DB WindowFrom supervisor window: START UPDATESPACE
DBC.Tablesize
When statistics are collected on a system, those statistics are held in the User DBC. Statistics
DBC Tables used in the Collect Statistics Process
DBC.TVFields–holds statistics collected for single column or single column indexes.DBC.Indexes–holds statistics collected for multi-column or multi-column indexes.DBC.StatsTbl(NEW 14.0)–repository for the statistics management data in V14.
EXEC DBC.ClearPeakDisk;
The DBA will run the above Macro once a month to reset the Peak values for Perm, Spool and Temp for everyone
The DBC.AssociationV View
When the COPY command is used to copy an object from one Teradata system to another, it is recorded in the DBC.AssociationV View.
The DBC.JournalsV View
DBC.Databases2V is for Unresolved Reference Constraints
The DBC.Databases2[V][X] views show all unresolved reference constraints, which are caused by creating a table with a Foreign Key before creating the table with the Primary Key. Or, it can happen on a restore if the Foreign Key is restored and the Primary Key table is not.
This View shows all inconsistencies with Referential Integrity. You need to restore the Parent Table (where the Primary Key resides) to “resolve” the references constraint.
The DBC.All_RI_ChildrenV for Inconsistent RI
This view is used to identify tables with Referential Integrity from the child to the parent and can also be used to show if the RI constraint is consistent (good) or inconsistent (bad).
There are two options to fix Referential Integrity problems:
1) ALTER TABLE Tablename DROP INCONSISTENT REFERENCES;Foreign Key references are dropped. Use another ALTER statement to reproduce them
.2) Use the REVALIDATE REFERENCES command in the Archive Facility (ARC).
The DBC.ShowColChecksV View
This view shows columns that have checks inside their DDL.
The DBC.ShowTblChecksV View
This view shows table level check constraints that are “named” column constraints.
This view shows table level check constraints that are “named” column constraints.
The DBC.PartitioningConstraintsV View
Show Partitioning Constraints for a certain database
The DBC.AccessLogV View
This view shows information about the AccessLog when Access Logging is turned on.
DBC.DBQLRulesV
BEGIN QUERY LOGGING WITH SQL, EXPLAIN ON ALL;
Log default rows, SQL and Explain for ALL Queries.
DBC.QryLogV
DBC.QryLogSummaryV
he DBC.IdCol Table
When an Identify column is created, a row is placed into the table DBC.IdCol.