Oracle Database/Tables

Architecture
The Oracle architecture considers one database per server, in which we can find several s, 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  specifies the user name:

Create tables
Example: 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:

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

Available data types
The possible column types are:
 * 1) Characters:
 * : 2 kB.
 * : 4 kB.
 * : 4 kB, synonymous of.
 * : 2 kB.
 * : 4 kB.
 * 1) Numeric:
 * 2) Date:
 * 1) Date:
 * 1) Date:
 * 1) Date:
 * 1) Date:

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

Insert rows
1 line created.

Multiple rows: 2 lines created.

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

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

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

ALL_TAB_COLUMNS
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DAT --- DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID DEFAULT_LENGTH --- -- -- - -- -- DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE -- -- --- --- CHARACTER_SET_NAME                           CHAR_COL_DECL_LENGTH GLO USE --- --- AVG_COL_LEN CHAR_LENGTH C V80 DAT HISTOGRAM      DEF IDE --- --- - --- --- --- --- --- EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING

Content
To get its content: LAST       FIRST     ADDRESS -- -- Doe         Jane      UK

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: