//*/

Thursday, 21 January 2016

Oracle Tables(Segments)

Database is consist of Logical structure,Physical structure and Instance. Logical structure contains the Database Objects. We can able to access the Database objects when database up and running only.
Database objects are:
Tables
Indexes
Synonyms
Sequences
Packages
Triggers
Functions
Clusters
Object Types

These Database objects are holding the transnational and metadata information.
Basically there are two categories in tables/views.
1 Base Tables
2. Transaction Tables

Base Tables contains the meat data information.These tables contain information about the Transnational Tables information .
 These tables are created while installing the oracle database and owner of the base tables is SYS(DBA). We can find the base tables in dba_tables(or)all_tables.
And base tables stored into System Tablesapce.

select * from all_tables where owner='SYS';
When we are creating the any transnational tables which we call as DDL operation.
example:
drop table test;
create table test (testid number,testname varchar2(20));
desc test;

Here test is a Table or Table segment/database object. When we are using create statement we can call as DDL statement in the user/developer prospective.
But DBA/SYS prospective which is DDL and DML also.
We know how it DDL but why it DML here?
When we are using the create statement it will insert the record in base tables.Every insert we can call as DML statement.

select * from user_tables where lower(table_name)='test';
Every create statement should be inserted in user_tables .

Transaction Tables/User defined tables:
Transaction tables also known as User defined Tables or Business Tables which is created by user and created based on business requirements.
User tables are stored in the user table space.