Basics of Oracle
page 2 of 5
by Abhinash Jena
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23502/ 84

Tables in Oracle

A table is the most visible object in the Oracle RDBMS. It is the structure used by Oracle for storing data and this consists of rows and columns. It is normally created in a table segment which consists of one or more extents. Extents consist of data blocks. It is normally used to minimize the amount of wastage storage. Extents act as the building blocks of segments. When a table grows filling the current extents, a new extent is created for that table. These extents grow in the manner specified by the STORAGE clause used while creating a table which if not included during table creation; the default STORAGE clause defined on the tablespace is used.

Tables can be defined and created by using Enterprise Manager, Schema Manager, or the CREATE TABLE command. I personally like the CREATE TABLE way to create a table as it works as a powerful tool when we need to accomplish some complex tasks. One more advantage is that we can include the STORAGE clause while creating a table using the CREATE TABLE command. And as I have started talking about STORAGE, let me give some more details on STORAGE as this is important for specifying the initial size and characteristics of a tablespace, etc. The DEFAULT STORAGE is used to define the storage parameters on a tablespace which can be used for any table created on the particular tablespace that does not have a STORAGE clause defined during its creation. And if a STORAGE clause is defined which creation of a table in that particular tablespace, it would override the DEFAULT STORAGE clause defined. The STORAGE clause has various parameters such as:

·         INITIAL parameter – for specifying the initial size

·         NEXT parameter – for specifying the size of subsequent extents to be of some number

·         MINEXTENTS parameter – for specifying the minimum number of extents created (during the creation of the schema object)

·         MAXEXTENTS parameter – just the reverse of MINEXTENTS parameter (used for specifying the maximum number)

·         PCTINCREASE parameter – for specifying the size of extends after a second

Though there are many other parameters of STORAGE clause, those mentioned above are the ones normally used.

Listing 1

CREATE TABLE demo
{
ID NUMBER,
Name VARCHAR2(30),
Address VARCHAR2(80)
}
TABLESPACE example
STORAGE
(
INITIAL 3M NEXT 3M PCTINCREASE 0 MINEXTENTS 3
);

The various important data types that are supported by Oracle are the following.

1.    CHAR – stores fixed length characters

2.    DATE – stores date in the form containing Year, Month, Date, Hour, Minute and Second

3.    NUMBER – stores fixed and floating point numbers

4.    VARCHAR – stores variable length character

5.    VARCHAR2 – Similar to VARCHAR data type

6.    ROWID – stores the data block, the row, and the datafile in the form of a triplet

7.    LONG – stores variable length character data up to 2GB in size

8.    LONG RAW – similar to LONG data type, except that it is not converted, as the LONG data type is converted by the Oracle utilities.


View Entire Article

User Comments

Title: Basiscs of Oracle   
Name: code Guru
Date: 2008-08-18 3:58:07 PM
Comment:
thanks 4 making the basics verry simple..otherwise most of the people make the basics so much difficult that beginners quit learning ..
nd yeah um looking 4 more topics
Title: Oracle Basics   
Name: Chiranjeevi
Date: 2008-04-08 7:12:23 AM
Comment:
Hi Abhinash Jena,

Its a good and wonderful article.
please continue it and add cover more topics.
Title: Data Types   
Name: Steven M. Swafford
Date: 2007-09-01 10:14:23 PM
Comment:
Is there are reason you did not cover all data types? Also why do you consider those you listed as important? It would have been nice to see TIMESTAMP, BLOB, and etc... There are also data types releveant to PL/SQL: binary_double, binary_float, dec, decimal, and more...
Title: Re:Whats wrong with ASPALLIANCE   
Name: Abhas Roy
Date: 2007-08-31 11:42:42 AM
Comment:
Hello Mr Tired. First of all if this article was not of your use why did you read it. There are people for whom this knowledge is important. May not be for you. And second I dont think there is anything on internet that are not found on books. Internet and books are just two alternative mediums to gain knowledge.
Thanks Abhinash Jena for providing fundamentals of Oracle in this article.
Title: Re:Whats wrong with ASPALLIANCE   
Name: Haissam Abdul Malak
Date: 2007-08-31 7:20:10 AM
Comment:
First thank you for this article.
Second Aspalliance provides articles for all users levels if you are an advanced developer, i think there are lots of beginners who need to read such articles.

Best Regards,
Title: What you want is what you get!!   
Name: Abhinash
Date: 2007-08-31 2:16:47 AM
Comment:
well i tried to write your so called "advanced" article i.e Siebel Analytics but the article was rejected..hope you heard about siebel analytics.
Title: Whats wrong with ASPALLIANCE   
Name: Tired
Date: 2007-08-30 10:54:10 PM
Comment:
ASP Alliance is losing it as per me. They are publishing useless articles. This information can be found in any book. Whats the use in these kind of articles. Nothing against the author but asp alliance should promote some advanced articles.
Title: Good start   
Name: Debjani
Date: 2007-08-30 1:02:37 AM
Comment:
Good article for beginners
Title: Mr   
Name: Tarun Kanti Nanda
Date: 2007-08-28 3:44:20 AM
Comment:
Thanks Abhi. This is very nice. Previously i was some doubt in view but now it is fine.
Title: Mr.   
Name: Byomokesh Sarangi
Date: 2007-08-28 2:43:37 AM
Comment:
thanks abhi for such a wonderful article i was reall confused with the tables and views on tables and synonyms.

thanks buddy really appreciate this work,, and now even i can finish up my project
keep up the good work
Title: GR8   
Name: JOHN
Date: 2007-08-28 2:33:08 AM
Comment:
GR8 ARTICLE GOOD FOR BEGINNERS.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-08-25 2:59:24 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search