Oracle

Default Value of Oracle

2009-05-23T18:04:00.000-07:00

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

2009-05-12T17:51:00.000-07:00

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

2009-05-12T09:18:00.000-07:00

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

2009-05-08T09:30:00.000-07:00

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]

2009-05-07T09:55:00.001-07:00

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

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.

About Oracle-life

2009-05-07T09:20:00.000-07:00

Oracle-life is a resource blog for database learners, trying to offer greatest database tutorials, database tips and tricks, database problems and solutions etc. to help enhance the experience of database knowledge. The aim of this blog is to make an expert database guy. This site is basically built for database related topics. You can learn and understand about database, SQL, Oracle etc.It's developing day-by-day. For the experienced database experts, it may seem that this is a new site and will not be very helpful for the new learners. But I say, it may be his or her own experience about the site or blog unless he or she doesn't read any page. I personally prefer to visit any page of this site and make a comment about the blog contents. I think, the expert database guys must tell that this website is really helpful. I'm sure if any expert guy just read any page, then he or she will realize how this website refreshes his or her mind. Really, it's very helpful for the beginners, the intermediate learners and the advanced database learners.

Nevertheless, for the beginners of database learners, it's a great resource center. I arrange this site's contents by an interesting effective way to learn the Oracle related tutorials. If you want to clear all of your concepts, I personally advice to subscribe this site or visit this site by putting the address http://oracle-life.blogspot.com/ to the address bar of your browser.

This website is published just May '09. But I see many people already subscribe through RSS or by e-mail to get the new contents. I don't know how they know or aware this site. I don't advertise this website anywhere. I just build the blog through www.blogger.com and update the template. Later, I published some posts and I found many people already not only visit but also subscribe to oracle-life.blogspot.com. I feel really nice and very hopeful that by the end of this month the no of visitors must be above 500. I don't know whether it's possible or not. But the current traffic of visitors is really high then I was expected before.

To sum up, I can say that as I learn more about database, I must share my own knowledge and experience here and posts those materials to this site. I think, you will find all the contents are easy to learn, easy to understand and easy to remember.

I personally advice you to present your comments in the comment section about oracle-life site. I always try to hear from your side.

Some Tips of Table Structure

2009-05-06T18:39:00.000-07:00

This page tells some of the great tips about table structure. Some of the tips are explained below:

Table creation time:

Tables can be created at any time. Users can also create tables at the time of using database.

Size of the table:

There is no restriction regarding the size of the table. Moreover, you don't need to specify the size of the table. But, it's important for estimating the space required of the table which will be used by that table in future.

Modifying structure:

The structure of the tables can be modified online.

Naming convention:

Table name begins with letters and no reserved words. For details, naming Convention of Tables and columns.

Size of table columns:

A table can have up to 1000 columns.

Naming Convention of Tables and Columns

2009-05-06T18:20:00.000-07:00

Oracle database tables and columns naming follow some standard rules. Those rules are explained in this page:

Name starts with a letter:

All table names and column names start with a letter. The name must be 1-30 characters long.

Allowable characters for naming:

The follwoing charactesr are allowed to name any table or column-

characters: A-Z
characters: a-z
characters: 0-9
characters: _ [underscore]
characters: $
characters: #

No duplication:

Names must not duplicate any other object's name of the same user.

No reserved word:

Oracle server reserved word must not be the name of tables or columns.

Note:

Names aren't case sensitive. For example, STUDENTS is same as students or sTuDeNtS or STUdents etc.

Database Objects...

2009-05-06T07:37:00.000-07:00

A database contains many objects. Oracle has several objects like table, view, sequence, index, synonym. This page describes some of the basic objects of Oracle database:

Table:

Table is the basic unit of storage. All data in the database is stored in tables.
A table is composed of several rows and columns.

View:

It's the subset of data.
It's the logical representation of data from one or multiple tables.

Sequence:

Sequence is the numeric values generator.

Index:

Index is useful for increasing the performance of several queries.

Synonym:

It is used to alterante the objects name.

This is the short description of several oracle database objects. There are many other objects. But you must have to learn and understand the above objects firstly.

About Me & Why I started This Blog...

2009-05-06T06:54:00.000-07:00

I am just a student(still) and start a job few days back. I like to write pages and articles. I like to write articles, useful tips, some great tips about browser related topics, programming related tips, windows tips, antivirus related topics, blog related topics etc. I'm already a writer of one blog and an author of an website. My blog is:

itis123.blogspot.com

A Tutorial & Interview Zone

[Click here to visit my blog]

This blog is related to blogging related topics. I wrote there when I get some time. Moreover, that blog as a great attraction and it's the free keys of antivirus. If you like to visit the blog, just Click here to visit.

I write hubpages as an author rancidTaste. To visit my writings, just follow the link:

http://hubpages.com/profile/rancidTaste.

I start this blog based on tutorials of Oracle. It's the blog to learn several Oracle related topics. And that's all as the first introduction about the blog.

Hope you like it. Bye...

Contact

2009-05-05T09:37:00.000-07:00

Your Name :
Your Email :
Subject :
Message :
Image (case-sensitive):