Oracle Database/Tables

From Wikibooks, open books for an open world
Jump to: navigation, search

Architecture[edit]

The Oracle architecture considers one database per server, in which we can find several tablespaces, equivalent to the MySQL and MS-SQL databases objects, containing tables and stored procedures.

vignette

In the Windows Express version, these data are stored into C:\oraclexe\app\oracle\oradata\XE.

These variables and keywords are not sensible to casing.

Create tablespaces[edit]

Once connected, it's possible to begin to create some tables directly, in the default tablespace. However before, we can add some tablespaces in some defined files:

   CREATE TABLESPACE Wikibooks
   DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\Wikibooks.dbf' size 10M reuse
   DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999) 
   ONLINE;

Create schemas[edit]

A schema is a permission accorded to a set of elements[1], like tables and stored procedures. The keyword AUTHORIZATION specifies the user name:

CREATE SCHEMA AUTHORIZATION root
  CREATE TABLE table1...
  CREATE TABLE table2...
;

List tables[edit]

SELECT owner, table_name FROM all_tables;

Create tables[edit]

Example:

CREATE TABLE client1 (last VARCHAR(10), first VARCHAR(10), address VARCHAR(20) );
Table created.

In SQL Developer, with a right click on the tables, New table..., we can generate and execute this creation in an array, which is translated into PL/SQL in the DDL tab:

CREATE TABLE client1
( id INT NOT NULL 
, last VARCHAR2(50) 
, first VARCHAR2(50) 
, address VARCHAR2(255) 
, CONSTRAINT client1_PK PRIMARY KEY (ID) ENABLE
) TABLESPACE Wikibooks;

We can also set the table tablespace, by selecting it into the GUI, or with the keyword TABLESPACE in the creation clause.

Modify tables structure[edit]

Example of renaming:

ALTER TABLE client1 RENAME to client2

First field values constraint addition:

ALTER TABLE client1 CHECK id > 1;

Primary key addition:

ALTER TABLE client1 ADD CONSTRAINT client1_pk PRIMARY KEY (id);

Primary key removal:

ALTER TABLE client1 ADD PRIMARY KEY (id) DISABLE;

Foreign key addition:

ALTER TABLE client1
ADD CONSTRAINT fk_client2
  FOREIGN KEY (client2_id)
  REFERENCES client2(id);

Drop tables[edit]

DROP TABLE client1;

Insert rows[edit]

INSERT INTO client1 (last, first, address) VALUES ('Doe', 'Jane', 'UK'), ('Doe', 'Jane', 'UK');
1 line created.

Read a table[edit]

To get its structure:

desc client1;
Name    NULL     Type          
------- -------- ------------- 
ID      NOT NULL NUMBER(38)    
LAST             VARCHAR2(10)  
FIRST            VARCHAR2(10)  
ADDRESS          VARCHAR2(20) 

If the table doesn't exist, the error which occurs is: ORA-00923: FROM keyword not found where expected.

To get its content:

SELECT * from client1;
LAST        FIRST     ADDRESS
---------- ---------- --------------------
Doe         Jane      UK

The number of dashes represents the field size.

Update rows[edit]

UPDATE client1 SET address = 'US' WHERE id = 1;

Delete rows[edit]

DELETE client1 WHERE ID = 2;

Partitioning[edit]

The Oracle partitioning is a process to split a huge table into several smaller ones in order to increase its performance.

Range[edit]

Example:

CREATE TABLE t_range 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
) 
PARTITION BY RANGE (t2) 
( PARTITION part1 VALUES LESS THAN (1),
  PARTITION part2 VALUES LESS THAN (11),
  PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

Hash[edit]

Example:

CREATE TABLE t_hash 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
PARTITION BY HASH (t2)
PARTITIONS 4
;

List[edit]

Example:

CREATE TABLE t_list 
( ort     VARCHAR2(30) NOT NULL,
  t2      NUMBER,
  t3      NUMBER  
)
PARTITION BY LIST(ort) 
( PARTITION part_nord VALUES IN ('Hamburg','Berlin'),
  PARTITION part_sued VALUES IN ('Muenchen', 'Nuernberg'),
  PARTITION part_west VALUES IN ('Koeln','Duesseldorf'),
  PARTITION part_ost VALUES IN ('Halle'),
  PARTITION part_def VALUES (DEFAULT)
);

Interval[edit]

Example:

CREATE TABLE t_interval 
( buchungs_datum  DATE NOT NULL,
  buchungs_text   VARCHAR2(100),
  betrag          NUMBER(10,2)
)  
PARTITION BY RANGE (buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_historie VALUES LESS THAN (TO_DATE('2014.01.01', 'YYYY.MM.DD')),
  PARTITION p_2014_01  VALUES LESS THAN (TO_DATE('2014.02.01', 'YYYY.MM.DD')),
  PARTITION p_2014_02  VALUES LESS THAN (TO_DATE('2014.03.01', 'YYYY.MM.DD'))
);


Describing a database transaction[edit]

Using DBCA[edit]

Create a database[edit]

Database Configuration Assistant (DBCA) is a graphical interface.[2]

Configure a database[edit]

Drop (or delete) a database[edit]

Manage templates[edit]

References[edit]