Oracle Tables

• Heap-organized tables: 

A heap-organized table is nothing but the normal Oracle table, where data is stored in no particular order.

• Index-organized tables:

 An index-organized table stores data sorted in a B-tree indexed structure.

• Clustered tables:

 A clustered table is part of a group of tables that shares the same data blocks, because columns of the clustered tables are often requested together.

• Partitioned tables:

 A partitioned tables lets you divide a large amount of data into sub tables, called partitions, according to various criteria. Partitioning is especially useful in a data warehouse environment.

Create Table:-

sql>create table test3 (
       numb number(5),
       name varchar2(20) not null,
       addr varchar2(30)
       constraint test3_fkey primary key)
       tablespace test01;
                  OR
sql>create table test as select * from emp;

Add Column to the Table:-

sql>alter table test add (job varchar2(20));    ('Job' New Column name)

Droping a Column from Tables:-

sql>alter table test drop (job);
Where 'Job' is Column name.

Rename a Column:-

sql>alter tables test rename column job to designation;

Insert Rows in a Table:-

sql>insert into test values (3,'manager','local','business');

Rename a Table:-

sql>alter table test rename to final;

Removing all data from Table:-

sql>truncat table test;          (you cannot rollback data)

Move Table to new Tablespace:-

sql>alter table test move to (New Tablespace);

Dropping a Table:-

sql>drop table emp;            (It will move table to recycle bin)

sql>drop table emp Purge;       (It will permanently drop table)

sql>drop table emp cascade constraints;             (It will drop constraints eg, primary key foreign key etc.)

Bring back a deleted table (no purged):-

sql>flashback table emp to before drop;

Recyclebin:-

sql>select * from recyclebin;
sql>select owner, object_name, ts_name from dba_recyclebin;
sql>select * from "BIN$8sL/jMU2hOrgQKjACQADrQ==$0";
sql>purge table "BIN$8sL/jMU2hOrgQKjACQADrQ==$0";   (It will delete table from Recycle bin)