Default Value of Oracle

A column can be given a default value by using DEFAULT option. If any table has DEFAULT options enabled, then it specify a default value far a column during data or value inserting time.

Example:

Let, we have a table and there is a column named as hire_date which insert values automatically when we give an entry of a particular user or employee. Here the automatic column is hire_date which automatically insert the system date (SYSDATE) to the hire_date column of the table.

• ...... hire_date DATE DEFAULT SYSDATE, .....

Allowable DEFAULT values:
A default value can be one of the followings:

• literal values
• expression
• SQL function
• Example: SYSDATE, USER etc.
  

Non-allowable DEFAULT values:
A default value can't be the followings:

• another column's name
• pseudocode
• Example: NEXTVAL, CURRVAL, ROWNUM etc.

Note:
A default expression must match the data type of the current column.

Differences between User Tables and Data Dictionary

There are some differences between User Tables and Data Dictionary. The differences are mentioned below:

• User Tables are a collection of tables which are created and maintained by the user. Every user has a specific no of tables which are only created by them and also maintained by them. But Data Dictionary is a collection of tables which are created and maintained by the Oracle Server.
• User Tables only contain user information. Whereas Data Dictionary contains database information.
• All the user tables are specified and owned by the user who created and maintained those. But all Data Dictionary tables are owned by SYS user.
• There is no such type of categories of User Tables views. But Data Dictionary has four categories of views: USER_, ALL_, DBA_ and V$.
  

These are the differences between User Tables and Data Dictionary.

© Written by meRancidTaste

Data Dictionary

What is Data Dictionary?

• Data Dictionary is a collection of tables.
• All the tables in Data Dictionary is created and maintained by Oracle Server.
• Data Dictionary contains all the database related information.
• All the tables of Data Dictionary are maintained by SYS user.

Which type of information does Data Dictionary holds?

Data Dictionary stored the following type of information:

• all of the user name of Oracle server
• privileges granted to all of the users
• name of the database objects
• table constraints
• information about auditing
  

Views of Data Dictionary:

There are total four types of data dictionary views. They are:

• USER_ :These type of views contain those information which are relates the objects by the user.
• ALL_ : These type of views contain table related information by the user which are accessible to the user.
• DBA_ : These type of views are restricted type of views. Only the people who are DBA, can access those type of views.
• V$ : These views are one of the important view. It includes dynamic performance views, database server performance, memory and locking related information.

Summary of this page:

Data Dictionary is the collection of tables which is created and maintained by Oracle Server and which is maintained by SYS user. There are four types of Data Dictionary view: USER_, ALL_, DBA_ and V$.

© Written by meRancidTaste

Oracle table and it's syntax and structure

Oracle table can be created using CREATE TABLE command. Table creation is a DDL language.
There is a syntax or structure to create table in Oracle database. For creating a table, table name must have to specify and column name, data type & size also should be mentioned.

Conditions of creating tables:

Bear in mind, two conditions must be fulfilled before creating any table in Oracle. Those conditions are:

• You must have the privileged to create table.
• A storage area must be needed to create any table.

Structure of creating table:

The structure or syntax of database table looks like the following:

CREATE TABLE [schema.] table
(column datatype [Default expr] [, ......]);

Description of the syntax of oracle table:

• schema: schema is a collection of objects. Schema objects refer to logical structure which includes tables, views, synonyms, sequences etc. In the syntax, schema is the same as table owner's name.
• table: it means the name of the table.
• column: it refers the name of the table's column.
• datatype: it refers the data type of the column and the datatype's size.
• DEFAULT expr: it specifies the default value of the column. Sometimes, some values are not put in the INSERT statement. That time, the default value is inserted to that column value.

Note:

• All CREATE TABLE syntax must be ended to a semicolon (;).
  

Example of creating table:

CREATE TABLE students(
 firstName  varchar2(20),
 lastName varchar2(20),
 age  number(2)
 );

In this example, a table named as "students" is created which has three columns: firstName, lastName and age. The syntax is ended by a semicolon (;).

Big Index of Database [adding contents day-by-day]

This page provides a complete list of all database tutorials posted on Oracle-life, organized by several groups or categories. So, browse the indexes and select your preferred category to read, learn and apply the tutorials which are most helpful to you.

• About Oracle-life
  

All about tables

• A Brief Description of Database objects
  
• Some tips of table structure
  
• Naming convention of tables and columns
• Oracle table and it's syntax and structure
• Data Dictionary
• Default value of oracle

Differences

• Differences between User Tables and Data Dictionary

The contents are still adding, so visit this website or subscribe the feeds to automatically redirected the newest contents.