# Oracle Database/Tables
## Architecture

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.
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

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 schemas

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

## Create tables

Example:
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:
We can also set the table tablespace, by selecting it into the GUI, or with the keyword TABLESPACE in the creation clause.

### Available data types

The possible column types are:
* Characters:
** CHAR: 2 kB.
** VARCHAR: 4 kB.
** VARCHAR2: 4 kB, synonymous of VARCHAR.
** NCHAR: 2 kB.
** NVARCHAR2: 4 kB.
* Numeric:
** NUMBER.
** BINARY_INTEGER.
** BINARY_FLOAT.
** BINARY_DOUBLE.
* Date:
** DATE.
** TIMESTAMP.
* RAW.
* LONG RAW.
* BLOB.
* CLOB.
* NCLOB.
* ROWID.
* UROWID.
* BFILE.
* XMLType.
* UriType.

## List tables

The following system view can display the system and the users tables:

## Manage tables content
### Insert rows

Multiple rows:
The DUAL table is a special one-row, one-column table present by default in Oracle, used because the SELECT clause needs a FROM clause, but some queries don't require any tables.

### Delete rows
## Read tables
### Structure

To get its structure, we can use either the function desc (description) or the system view ALL_TAB_COLUMNS.

#### desc

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

#### ALL_TAB_COLUMNS
### Content

To get its content:
The number of dashes represents the field size.

## Constraints

In essence, constraints safeguard and validate the data.
Primary Key (PK) and Unique constraints both ensure the data is not duplicated. PK also ensure the data is not null. Oracle will automatically generate index for PK and Unique constraints. A table can only have one PK, but it can have multiple unique constraints.
Foreign Key (FK) ensure the data exists in the column of the parent table it refer to. Each parent record can have multiple child records, but each child can relate to ONLY one parent record. A column with FK may not necessary to have an index.
FK can only refer to column with PK or Unique constraint. Example:
A table can ONLY have one Primary Key, but it can have multiple UNIQUE key. if the child table(s) require to referencing column other than primary key, the column on the parent table must have UNIQUE constraint.
Cannot create PK or Unique on a column contains duplicate data
Insert data into a column with FK, the value must already exist in the column that the FK reference to.
As long as a foreign key exist, the parent table can truncate/delete the data or disable the PK or Unique constraint
Find out the constraint information in Oracle
Disable constraint that have foreign key refer to is not allowed, in order to do this, you have to disable the foreign key first.
If the data in parent table is deleted, re-enable the foreign key that contain data reference to the missing data is not allowed.
Generate a SQL statements to disable all the Foreign Key on a specified table

## Modify tables structure

Example of renaming:
First field values constraint addition:
Primary key addition:
Primary key removal:
Foreign key addition:

## Partitioning

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

### Range

Example:

### Hash

Example:

### List

Example:

### Interval

Example:

### Create and use external tables
## References

* ↑ https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6013.htm
* ↑ https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm