Table partitioning

Table partition is used to improve the performance, manageability and availability of applications

Before creating table partitioning:

1. Create user:

SQL>create user user1 identified by user1;

2. Grant privileges to user:

SQL>grant connect,resource to user1;

SQL>grant create tablespace to user1;

3. Create tablespaces:

SQL> create tablespace t1 datafile ‘/u01/user38/test/t01.dbf’ size 100m autoextend on;

Similarly create 3 more tablespaces t2,t3,t4

SQL>alter user user1 quota unlimited on t1, quota unlimited on t2, quota unlimited on t3, quota unlimited on t4;

SQL>conn user1/user1

Types of table partitioning:

a. Range partitioning: The data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2007” will contain all the rows that have the partition key values between 01-JAN-2007 and 31-JAN-2007 (assuming the range of the partition is from first of the month to the last date in the month).

SQL>Create table emp_range(empid number(4),empname varchar2(10), empsal(10,2)) partition by range(empid) (partition p1 values less than (10) tablespace t1, partition p2 values less than (20) tablespace t2, partition p3 values less than (30) tablespace t3, partition p4 values less than (40) tablespace t4);

b. Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row. This provides I/O balancing, but cannot be used for range or inequality queries.

SQL>Create table emp_hash(empid number(4),empname varchar2(10), empsal(10,2)) 

PARTITION BY HASH (empid) PARTITIONS 4  STORE IN (data1, data2, data3, data4);

NOTE: The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added.

c. List Partitioning: The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc.

SQL>Create table emp_list(empid number(4),empname varchar2(10), empsal(10,2), dept_name varchar2(10)) partition by list(dept_name) (partition dept_A va;ues ('sales', 'marketing'), partition dept_B VALUES (‘advt'), Partition  dept_c values (‘manager') partition  college_others VALUES(DEFAULT) );

d. Composite Partitioning: A combination of 2 data distribution methods are used to create a composite partition. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method. The following composite partitions are available:
Range-Hash, Range-List, Range-Range, List-Range, List-List and List-Hash.

SQL> create table emp_composit(empid number(4),empname  varchar2(10),empdoj date) PARTITION BY RANGE(empdoj) 

SUBPARTITION BY HASH(empid)

SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,

SUBPARTITION sp2 TABLESPACE t2, SUBPARTITION sp3 TABLESPACE t3,

SUBPARTITION sp4 TABLESPACE t4) (PARTITION emp_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))  PARTITION emp_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))  PARTITION emp_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))  PARTITION emp_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))

 PARTITION emp_may2000 VALUES LESS THAN (TO_DATE('06/01/2000','DD/MM/YYYY')));

Next: **Insert data into the table

Retrieve data from the table:

SQL>select * from emp;

SQL> select * from <table_name>   <partition_name>;

SQL>select * from emp partition(p1);

SQL>select * from emp partition(p2);

To add another partition:

SQL>alter table emp add partition p4 values less than (maxvalue) tablespace t4;

To merge partition:

SQL>alter table emp merge partitions p1,p2 into partition p5 tablespace t3;

Splitting a partition:

SQL> alter table emp split partition p5 at(10) into (partition p1 tablespace t1,partition p2 tablespace t2);

Dropping a partition:

SQL> alter table emp drop partition p1;

Sequences and synonyms

Sequence: Sequence is a type of object that oracle supports, which can be used to generate numbers in sequence order, this can be used to generate numbers for primary keys.

Syntax:

SQL> Create sequence <sequence_name> start with <integer_value> increment by <integer_values> maxvalue <integer_value> minvalue <integer_value> nocycle or cycle nocache or cache or order;

Start with: First sequence number to be generated.

Increment by: The integer value by which sequence number should be incremented for generating next number.

Maxvalue: If the increment number is positive then maxvalue determines the maxvalue up to which the sequence numbers will be generated.

NOMaxvalue: specifies a maximum value of 10^27 for an ascending sequence or -1 for a descending sequence.

Minvalue: If the increment number is negative then minvalue determines the minvalue up to which the sequence numbers will be generated.

NOMinvalue: specifies a minvalue of 1 for an ascending sequence and 10^26 for a descending sequence.

Cycle: Causes the sequence to automatically recycle to minvalue is reached for a ascending sequence, for descending sequences, it causes a recycle from minvalue back to maxvalue.

NOCycle: Sequence numbers willnot be generated after reaching the maxvalue for ascending sequences or minimum value for descending sequences.

Cache: Specifies how many values are pre-allocated in buffer for faster access. Default value is 20.

NOCache: Sequence numbers are not pre-allocated.

Order: Generates the number in serial order.

NOOrder: Generates the numbers in a random order.

SQL> Create Sequence sq_no start with 1000 increment by 1 maxvalue 9999 nocycle nocache order.

SQL> Insert into emp values(sq_no.nextval,’&empname’); // inserting using sequnce

Modifying Sequence:

SQL> Alter sequence sq_emp increment by 2; // alter sequence by 2 numbers

SQL> Select sq_emp.currval from dual; // current sequence number

SQL> Select sq_emp.next from dual;//next generating sequence number

SQL> Drop sequence sq-emp; // dropping a sequence

Synonyms: It is an alternate name to an object.

SQL> Create Synonym employee for emp; // synonym created

SQL> Drop synonym employee; //synonym dropped

Data Dictionaty:

SQL> Select * from user_sysnonyms; // shows user created synonyms

SQL> Select * from user_sequences;// shows user created sequences

VIEWS

View: Logically represents subsets of data from one or more tables, To reduce redundancy, data security.

Types of Views:

1. Simple Views: It is a sub query that retrieves data from one table. We can apply DML statements for these views.

SQL> create view student_v as select rollno,name from student; // view created

SQL> create view student_new_v as select rollno num,name studentname from student; //creating view from table student with other names

SQL> insert into student_v values(5,’has’); // value updated in table from view

2. Complex Views: It is a sub query that performs some actions like retrieving data from multiple tables and performing function call.

SQL> create view stu_course as select s.rollno,c.coursename from student s, course c; // creating view from multiple tables

3. Materialized views: Materialized view store a local copy of the base table records where as normal tables fetch records from the base tables;

When to use materialized views:

- When base tables have lakhs of records and summaries are executed against al or - large number of records of base table quite frequently.

- The data does not change frequently. The volume of insert, update and delete operations is considerably low.

- Any changes in the base tables needed not be reflected immediately in view. There can be some minimum allowed time delay between the changes in base tables to be reflected in materialized view data.

SQL> create materialized view mv_emp as select rollno,name from student; // materialized view created

SQL> execute DBMS_MVIEW.REFRESH (‘mv_emp’,’c’); // refreshing the materialized view

NOTE: Materialized view can also be automatically refreshed by using the START WITh <date> NEXT <date>.

SQL> create materialized view mvw_emp refresh complete start with sysdate next sysdate+1 as select rollno,name,age from student;

// view will be created and refreshed right now (sysdate) and there after once every day at the same time (sysdate+1).

SQL> drop materialized view mv_emp; // materialized view dropped SQL> drop view mv_emp; // view dropped

Data Dictionary:

SQL> select * from user_views; // shows all user created views;

INDEXING

Index: Index is an object that contains value for each entry of the record in the table object used for faster searching and sorting of data.

Oracle assigns a unique identification number to every record in the database called “ROWID” – Row Identification. It contains the physical address/location of a record in the Database (Hard disk).

SQL> select  ROWID from <Table_name>

By default when ever we search for records with the “WHERE” clause, Oracle looks for the desired records sequentially. This takes considerable time for the retrieval of records, there fore, oracle provides as object called “INDEX” which is used to speedup the searching records.

Syntax for creating Index:

a. Creating index on single column:

SQL> create index <index_name> on <table_name>(<column_name>);

Ex:

SQL> create index emp_ind on emp (empid);

b. Creating index on multiple columns:

SQL> create index <index_name> on <table_name>(<column_1,column_2,……..>);

Ex:

SQL> create index stucourse_ind on student (stuid,course);

c. Removing index:

SQL> drop index <index_name>;

Ex:

SQL> drop index stucourse_ind;

Types of indexes:

i.                    Non-unique: The values in column may repeat in various records.

SQL> create index stu_ind1 on student(course);

Ex: multiple students can have same course or may be different

ii.                  Unique: duplicate values can not exists in different records of the same table.

iii.                B-Tree Index: B-tree should be used when a column has large number of duplicate values. There will be several colleges, there fore we will create the B-tree index for college column.

iv.                Bitmap indexes: Bitmap index should be used when a column has few duplicate values.

SQL> create bitmap index stu_gender on student(gender);

Data Dictionary tables for indexs:

SQL> select index_name,index_type from user_indexes;

SQL>select index_name,table_name,column_name from user_ind_columns;

Constraints

Constraint: Is to achieve data integrity.

Data Integrity: It is a state where all the values in the database are correct.

Classification of constraints:

1. Column level: When a constraint is applied to a single column then it is a column level constraint. These constraints will affect the values being entered for that particular column only irrespective of values of other columns.
2. Table level: when a single constraint is applied to more than one column then it is called table level constraints. These values impact the values being entered for combination of column values, for example salary can never be less than the commission is a table level constraint.

Types of constraints

1. Not Null: It prohibits the column having NULL values.

SQL> Create table student (rollno number (4), name varchar2 (10) not null);

SQL> Create table student (rollno number(4), name varchar2(10) constraint name_nn not null); // alternative way

2. Unique: It designates a column as unique key, no two rows can have same values. But it allows NULL values.

SQL> create table student (rollno number (4) unique, name varchar2 (10));

SQL> create table student (rollno number (4) constraint rollno_uq unique, name varchar2 (10)); // alternative way

3. Primary Key: Primary key can be defined as the single column or combination of columns uniquely identifies a row in a table and field can not be left NULL.

SQL> Create table student (rollno number (4) Primary key, name varchar2 (10));

SQL> Create table student (rollno number(4), name varchar2(10), primary key (rollno));

SQL> Create table student (rollno number(4) constraint rollno_pk primary key, name varchar2(10)); // Alternative way

Composite Primary key: Primary key can be defined for combination of columns also. When a primary key consists of multiple columns, then it is called a composite key.

Ex: assume a table Student having college,rollno and name columns, If we presume that students of different colleges can have the same rollno then we have to declare College+rollno as primary key.

SQL> create table student (rollno number (4), college varchar2(10), name varchar2 (10), Primary key (college,rollno));

4. Default: Default implies that if we do not specify the values in the insert statement, then the value specified in the default clause will be inserted defaultly.

SQL> Create table student(rollno number(4), marks number(4) default 0);

5. Check: Check constraint allows verifying the values being supplied against specified condition, e.g., age of employees between 20 and 50.

SQL> Create table emp(empid number(4), name varchar2(10), age number(2) check (age between 20 and 50));

SQL> Create table emp(empid number(4), name varchar2(10), age number(2) constraint age_ck check (age between 20 and 50)); // alternative way

6. Foreign Key: It designates a column as foreign key and establishes the relation between primary key column and foreign key column. It is also called reference ‘Reference Integrity Constraints’.

- The table which has foreign key is called child table and the table that contain primary key is called parent column.

SQL> Create table student(rollno number(4) primary key,name varchar2(10)); //parent table

SQL> Create table Result(rollno number(4) references student(rollno), subject varchar2(10), marks number(2)); // child table

SQL> Create table Result(rollno number(4), subject varchar2(10), marks number(2), foreign key(rollno) references student(rollno)); // alternative way of foreign key syntax

It determines how oracle maintains referential integrity, when primary key values are removed.

Options on delete while declaring foreign key:

On Delete Cascade: Is to specify that the dependency foreign key rows must be deleted, when primary key rows are removed.

SQL> Create table emp(empno number(4) primary key, empname varchar2(10), mgrno number(4) references emp(empno) on delete Cascade);

On Delete Set Null: It is to specify that the dependent foreign key values must be set to NULL when the primary key values are removed.

SQL> Create table emp(empno number(4) primary key, empname varchar2(10), mgrno number(4) references emp(empno) on delete set null);

Adding, Removing and altering constraints:

SQL> Alter table student add primary key (rollno);//adding primary key for existing table

SQL>Alter table student add constraint rollnopk primary key(rollno); // alternative way

SQL> Alter table student drop primary key // droping primary key constraint.

SQL> Alter table student drop constraint rollnopk; // alternative way

SQL> alter table student disable constraint rollnopk; // disabling

SQL> alter table student enable constraint rollnopk; // enabling

Operator and Functions in SQL

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

NOTE: Dual is a dummy table, used for miscellanies operations/calculations in SQL.

SQL Operators:

1. Arithmetic operators (+, - , * , /):

SQL> select 5+6 from dual; // adds numbers

SQL> select 5-6 from dual; // subtracts  numbers

SQL> select 5*6 from dual; // multiplies numbers

SQL> select 25/5 from dual; // divides  numbers

2. Comparison / Relational operators (=, >, <, >=, <=, <>, !=):

SQL>select * from emp where empsal=5000; // displays empsal matches 5000

SQL>select * from emp where empsal<5000; // displays empsal values<5000

SQL>select * from emp where empsal>5000; // displays empsal values>5000

SQL>select * from emp where empsal<=5000; // displays empsal values<=5000

SQL>select * from emp where empsal>=5000; // displays empsal values>=5000

SQL>select * from emp where empsal!=5000; // displays empsal values!=5000

SQL>select * from emp where empsal<>5000; // displays empsal values<>5000

3. Logical operators ( AND, OR, NOT):

SQL>select * from student where empsal>5000 and deptname=’sales’;

SQL>select * from student where empsal>5000 or deptname=’sales’;

4. Set Operators (UNION, UNION ALL, INTERSECT, MINUS):

Ex: Assume tables Set1={1,2,3,4,5,6,7,8,9} and Set2={1,2,3,4,5}

UNION: it returns rows from both the queries, excluding commands.

SQL> Select * from Set1 UNION Select * from Set2;

UNION ALL: It returns rows from both the queries including the duplicates in the results

SQL> Select * from Set1 UNION ALL Select * from Set2;

INTERSECT: It returns common rows from both the queries.

SQL> Select * from Set1 INTERSECT Select * from Set2;

MINUS: It returns rows from first query that are not present in second query.

SQL> Select * from Set1 MINUS Select * from Set2;

SQL Star plus Operators:

BETWEEN … AND (negation of it is NOT BETWEEN…AND): It is to be specified that inclusive range of values. Ex: Range 10,20,30,40. Between 10 and 30=10, 20, 30.

SQL> Select empname,empsal from emp where empsal between 1000 and 10000;

SQL> Select empname,empsal from emp where empsal not between 1000 and 10000;

IN (negation of it is NOT IN): This IN operator is to test the list of values.

SQL> Select empname,empsal from emp where empjob in (‘manager’);

SQL> Select empname,empsal from emp where empjob not  in (‘manager’);

IS NULL ( negation of it is IS NOT NULL): Is to test NULL values.

SQL> Select empname,empsal from emp where empjob IS NULL;

SQL> Select empname,empsal from emp where empjob IS NOT NULL;

LIKE (negation of it is NOT LIKE): Is to search the matching character pattern.

SQL> Select empname,empsal from emp where empname LIKE ‘S%’; //empname starts with ‘S’

SQL> Select empname,empsal from emp where empname LIKE ‘%%\_%’ ESCAPE ‘\’;

 //displays empname having ‘_’

SQL> Select empname,empsal from emp where empname NOT LIKE ‘S%’;//empname except ‘S’

Functions in SQL:

Function: It takes arguments as input and returns result as output.

Types of Functions:

(1)   Single row functions: It manipulates on every row of the table and returns single result per each row.

Character Functions:

SQL> Select lower (‘HASZONE’) from dual; // changes to lower case

SQL> Select upper (‘haszone’) from dual; // changes to Upper case

SQL> Select Initcap (‘haszone technologies’) from dual; // changes to sentence case

SQL> Select concat (‘haszone’,’technologies’) from dual; // joints two words

SQL> Select length (‘HASZONE’) from dual; // calculates number of characters

Number Functions:

SQL> Select round(123.444,2) from dual; // rounds the number to two decimal nos

SQL> Select trunc(123.444,2) from dual; // truncate decimal nos

SQL> Select mod(5,2) from dual; // gives reminder

SQL> Select pow(2,3) from dual; // gives power of 3 on 2=8

SQL> Select sqrt(121) from dual; // gives sqrt of 121=11

SQL> Select abs(-15) from dual; // removes signs and give abs value

Date Functions:

SQL> select months_between(’13-oct-10’,’13-jul-10’) from emp; // gives of months between=3

SQL> select add_between(’13-oct-10’,3) from emp; // adds months

SQL> select last_day(’13-oct-10’) from emp; // gives last day of the month

SQL> select next_day(’13-oct-10’,’friday’) from emp; // displays when is next Friday

Data Converstion Functions:

Here 9-number,$-dollar indicator,G-group indicator,D-decimal indicator,L-local currency

To_Char:

SQL>to_char(123456,’$9G99G999D99’) from dual;//converts numbers as decimal

SQL>to_char(sysdate,’d’) from dual; // displays day in the week

SQL>to_char(sysdate,’dd’) from dual; // displays date of the month

SQL>to_char(sysdate,’ddd’) from dual; // displays day  in the year

SQL>to_char(sysdate,’day’) from dual; // displays  week day

SQL>to_char(sysdate,’mm’) from dual; // displays two digit month

SQL>to_char(sysdate,’month’) from dual; // displays spelled month

SQL>to_char(sysdate,’yy’) from dual; // displays last two digits of the year

SQL>to_char(sysdate,’yyyy’) from dual; // displays year in  digits.

SQL>to_char(sysdate,’year’) from dual; // displays spelled  year

(2)   Group row functions: It operates on group of rows and returns single result for group of rows.

SQL> Select sum(empsal) from emp; // calculates total salary of employees

SQL> Select avg(empsal) from emp; // calculates average salary of employees

SQL> Select min(empsal) from emp; // calculates min salary employee

SQL> Select max(empsal) from emp; // calculates max salary employee

SQL> Select count(*) from emp; // calculates total number of rows in emp table

DBMS

Data: Collection of raw facts.

Database: A database is a collection of related information that is organized so that it can easily be accessed, managed and updated.

Data Base Management System (DBMS): To manage our databases we require DBMS. DBMS is a software program that is used to store, modify and retrieve the data from the database upon user request.

What's an RDBMS ?

This concept was first described around 1970 by Dr. Edgar F. Codd in an IBM research publication called "System R4 Relational".

A relational database uses the concept of linked two-dimensional tables which comprise of rows and columns. A user can draw relationships between multiple tables and present the output as a table again. A user of a relational database need not understand the representation of data in order to retrieve it. Relational programming is non-procedural.

What's procedural and non-procedural ?

Programming languages are procedural if they use programming elements such as conditional statements (if-then-else, do-while etc.). SQL has none of these types of statements.

In 1979, Relational Software released the world's first relational database called Oracle V.2

Earlier we used FMS(File Management system) but it has various drawbacks like:

1.    Data redundancy (duplicate data)

2.    In consistency of data

3.    Risk to data Integrity (Integrity Constraints

4.    Data Isolation

5.    Difficult access to the stores data.

6.    No Security

Thus to over come these drawbacks DBMS was introduced, other examples DBMS are Fox Base, Dbase, Fox pro.., etc.

Various Database Models:

1.    File Management System (FMS)

2.    Hierarchical Model( Has a DOS directory Structure hence can only be used Hierarchical data).

3.    Network Model.

4.    Relational Model.

Relational Ship is named association between database objects.

To be relational it must have

a)    One to One

b)    One to Many

c)    Many to Many Relations.

Hierarchical database does not satisfy the many to many relationships

Network database uses pointers which are not easy to access.

What's SQL ?

In 1971, IBM researchers created a simple non-procedural language called Structured English Query Language. or SEQUEL. This was based on Dr. Edgar F. (Ted) Codd's design of a relational model for data storage where he described a universal programming language for accessing databases.

In the late 80's ANSI and ISO (these are two organizations dealing with standards for a wide variety of things) came out with a standardized version called Structured Query Language or SQL. SQL is prounced as 'Sequel'. There have been several versions of SQL and the latest one is SQL-99. Though SQL-92 is the current universally adopted standard.

SQL is the language used to query all databases. It's simple to learn and appears to do very little but is the heart of a successful database application. Understanding SQL and using it efficiently is highly imperative in designing an efficient database application. The better your understanding of SQL the more versatile you'll be in getting information out of databases.

 Dr. E.F. Codd formulated 12 rules about relational theory in 1970 in his paper named “A RELATIONAL MODEl OF DATA FOR LARGE SHARED DATA BANKS”.

12 RULES:

1.    Information rule( name should not be same).

2.    Rule of guaranteed access (should know the table and column name and the primary key).

3.    Systematic treatment of null values (missing information).

4.    Database description rule (user information in system tables must be read only).

5.    Comprehensive Sub Language (understandable language SQL)

6.    Insert and Update rule.

7.    The view updated rule (partially satisfied by oracle).

8.     The Physical independence rule

9.    The logical data independence rule

10.  The integrity independence rule

11. the distribution rule

12. the non – version rule.

Any DBMS Which satisfies half these rules i.e., 6/12 is called a Relational DBMS. Oracle satisfies 11.5 rules.

An Entity is a real-world item or concept that exists on its own. The set of all possible values for an entity is the entity type.

An attribute of an entity is a particular property that describes the entity. The set of all possible values of an attribute is the attribute domain.

Relation Ship: A relationship type is a set of associations among entity types. For example, the student entity type is related to the team entity type because each student is a member of a team. In this case, a relationship or relationship instance is an ordered pair of a specific student and the student's particular physics team, such as (haszone, db01), where db01 is haszone's team number.

What's Database Normalization?

Normalization is the process where a database is designed in a way that removes redundancies, and increases the clarity in organizing data in a database.

Normalization of a database helps in modifying the design at later times and helps in being prepared if a change is required in the database design. Normalization raises the efficiency of the database in terms of management, data storage and scalability.

Now Normalization of a Database is achieved by following a set of rules called 'forms' in creating the database.

These rules are 5 in number (with one extra one stuck in-between 3&4) and they are:

1st Normal Form or 1NF:

Each Column Type is Unique.

2nd Normal Form or 2NF:

The entity under consideration should already be in the 1NF and all attributes within the entity should depend solely on the entity's unique identifier.

3rd Normal Form or 3NF:

The entity should already be in the 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.

If such an entity exists, move it outside into a new table.

Now if these 3NF are achieved, the database is considered normalized. But there are three more 'extended' NF for the elitist.

These are:

BCNF (Boyce & Codd):

The database should be in 3NF and all tables can have only one primary key.

4^th Normal Form or NF:

Tables cannot have multi-valued dependencies on a Primary Key.

5^th Normal Form or 5NF:

There should be no cyclic dependencies in a composite key.

ORACLE DATA TYPES

Data type	Description	Max Size
VARCHAR2(size)	Variable length character string having max length size bytes. You must specify size	4000bytes min is 1
VARCHAR	VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data.	2000 bytes Default and minsize is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.
DATE	Valid date range	from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP (fractional_seconds_precision)	The number of digits in the fractional part of the SECOND date time field.	Accepted values of fractional seconds precision are 0 to 9. (default = 6)
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)	10 bytes
CLOB	Character Large Object	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BLOB	Binary Large Object	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BFILE	pointer to binary file on disk	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)

Table: