Creating and Managing Tables

Database Objects

• A Oracle database can contain multiple data structures.
• The different Database objects in Oracle are:

TABLE

Used to store data, Basic Unit.

VIEW

Logically represent subsets of data from one or more tables.

SEQUENCE

Used to Generate Primary Key values.

INDEX

It is used to improve the performance of some queries.

SYNONYM

Used to give alternate names to objects.

Table in Oracle

• Table can be created at any time, even when the users are using the database.
• Size of the table need not be specified.

For create table

1) Table Name
2) Column Name
3) Data Type

Table Restriction

• The user should have permission or CREATE TABLE command, and storage area.
• Table name must be unique in schema.
• The Table name should begin with a letter and can be 1-30 characters long.
• Maximum 1000 columns(8.0), 256 in 7.x.
• Names can contain:
**A -- Z **a -- z **0-9 **_,$,#
• Names cannot be duplicated for another object in the same ORACLE server.
• Name cannot be oracle server reserved words.
• Names are not case sensitive.

COLUMN NAME

• The column name should begin with a letter and can be 1-30 characters long.
• Column name is Unique in a table.
• Column name can be renamed.
• Maximum columns in table in 1000.

Data Types in Oracle

• Each value in ORACLE is manipulated by a data type.
• The values of one data type are different from another data type.
• The data type defines the domain of values that each column can contain.
• The Built-in-data types of ORACLE are Categorized as

      o	CHARACTER Data Types
      o	NUMBER Data Types
      o	LONG and RAW Data Types
      o	DATETIME Data Types
      o	ROWID Data Types

Character Data Types

• They store character data which can Alphanumeric data.
• The Information can be

      o	Words
      o	Database Character set
      o	National Character set

• They are less restrictive than other data types an have very few properties.
• They data is stored in strings with byte values.
• The different character data types are:

    CHAR
    NCHAR 
    VARCHAR
    VARCHAR2
    NVARCHAR2

CHAR Data Type

• It specifies fixed length character string.
• The size should be specified.
• If the data is less than the original size, blank pads are applied.
• The default length is 1 Byte and the Maximum is 2000 Bytes.

NCHAR Data Type

• It is first defined in ORACLE 9i, and contains Unicode data only.
• The column’s maximum length is determined by the National Character set definition.
• The Maximum size allowed is 2000 Bytes and size has to be specified.
• If the data short than the actual size then the blank pads are applied.

VARCHAR2 Data Type

• A data type used for storing text data.
• The Minimum size is 1 Byte and the Maximum size is 4000 Bytes.
• It occupies only that space for which the data is supplied.
• Any text character (including special characters, number, dashes, and so on) can be stored.

NVARCHAR2 Data Type

• It is first defined in ORACLE 9i, and contains Unicode data only.
• The Minimum size is 1 Byte and the Maximum size is 4000 Bytes.

Number Data Type

Number(Precision, Scale)

• Number data type stores the numeric data, the precision is the total no of digits required and scale stands for the rounding of decimal place.
• Range of precision is from 1 to 38.

Integer Data Type

• Integer data type will converted as Number Data type with the maximum size 38 digit as precision.

LONG

• This data type is used to store characters or numbers.
• Maximum size limit is 2 GB.
• Only one Long column is valid per table.

Date & Time Data Type

• It is used to store dates and time information.
• The information revealed by date is:

    *Century   *Year     *Month
    *Date      *Hour     *Minute    *Second

• The default date format in ORACLE is DD-MM-YY.
• The default time accepted by ORACLE date is 12:00:00 AM (Midnight).
• The default date accept by ORACLE data is the First day of the Current month.
• The Date range provide by Oracle is
JANUARY 1,4712 BC to DECEMBER 31,9999 AD.

Timestamp Data Type

• It is an extension of the DATE data type.
• It stores

   *Day   *Month   *Year   *Hour   *Minute   *Second

Syntax: TIMESTAMP(Fractional-Seconds-Precision)

• Fractional – Seconds – Precision optional specifies the number of digits in the fractional part of the SECOND date time field.
• It can be a number in the range of 0-9, with default as 6.

RAW

• It stores binary information like photos, Signature, Thumb impressions etc.
• Maximum length is 2000 Bytes.
• The Oracle converts the RAW and LONG RAW data into Hexadecimal form.
• Each Hexadecimal character represent four bites of RAW data.

LONG RAW

• It stores the binary data similar to RAW but can store more bytes than RAW.
• Maximum length is 2GB.
• No size is required.

Large Object(LOB) Data Types

• The Built in LOB data types are
*BLOB *CLOB *NCLOB
• These data types are stored internally.
• The Bfile is an LOB which is stored externally.
• The LOB data type can store large and unstructured data like Text, Image, Video and Spatial data.
• The maximum size is upto 4GB.
• LOB columns contains LOB locators, which can refer to out-of-line to or in-line LOB values.
• LOB’s selection actually return the LOB’s locator.

BFILE Data Type

• It enables access to binary file LOB’s which are stored in the systems outside ORACLE.
• A BFILE column or the attributes stores the BFILE locator.
• The BFILE locator maintains the directory alias and the filename.
• The Binary File LOB’s do not participate in transaction and are not recoverable.
• The maximum size is 4GB.

BLOB Data Type

• It stored unstructured Binary Large Objects.
• They are Bit streams with no character set semantics.
• They are provided with full transactional support.

CLOB Data Type

• It Dynamic data type.
• They are provide with full transactional support.
• The maximum size is 4GB.

NCLOB Data Type

• It stores Unicode data using the National Character set.

ROWID Data Type

• Each row in the database has as address.
• The rows address can be queried using the pseudo column ROWID.
• ROWID’s efficient support partitioned table and indexes.

Thanks for reading. If you like this post please follow us for more updates about technology related updates.