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 .

These variables and keywords are not sensible to casing.

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:

CREATETABLESPACEWikibooks
DATAFILE'C:\oraclexe\app\oracle\oradata\XE\Wikibooks.dbf'size10Mreuse
DEFAULTSTORAGE(INITIAL10KNEXT50KMINEXTENTS1MAXEXTENTS999)
ONLINE;

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

CREATESCHEMAAUTHORIZATIONroot
CREATETABLETableName1...
CREATETABLETableName2...
;

Example:

CREATETABLEclient1(lastVARCHAR(10),firstVARCHAR(10),addressVARCHAR(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:

CREATETABLEclient1
(idINTNOTNULL
,lastVARCHAR2(50)
,firstVARCHAR2(50)
,addressVARCHAR2(255)
,CONSTRAINTclient1_PKPRIMARYKEY(ID)ENABLE
)TABLESPACEWikibooks;

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

The possible column types are:^[2]

1. Characters:
   1. CHAR: 2 kB.
   2. VARCHAR: 4 kB.
   3. VARCHAR2: 4 kB, synonymous of VARCHAR.
   4. NCHAR: 2 kB.
   5. NVARCHAR2: 4 kB.
2. Numeric:
   1. NUMBER.
   2. BINARY_INTEGER.
   3. BINARY_FLOAT.
   4. BINARY_DOUBLE.
3. Date:
   1. DATE.
   2. TIMESTAMP.
4. RAW.
5. LONG RAW.
6. BLOB.
7. CLOB.
8. NCLOB.
9. ROWID.
10. UROWID.
11. BFILE.
12. XMLType.
13. UriType.

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

SELECTowner,table_nameFROMall_tables;

INSERTINTOclient1(id,last,first,address)VALUES(1,'Doe','Jane','UK');

1 line created.

Multiple rows:

INSERTALL
INTOclient1(id,last,first,address)VALUES(2,'Doe','Jack','US')
INTOclient1(id,last,first,address)VALUES(3,'Doe','John','US')
SELECT1FROMDUAL;

2 lines created.

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.

UPDATEclient1SETaddress='US'WHEREid=1;

DELETEclient1WHEREID=2;

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

descclient1;

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.

SELECT*FROMALL_TAB_COLUMNSWHERETABLE_NAME="client1"

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

To get its content:

SELECT*fromclient1;

LAST FIRST ADDRESS
---------- ---------- --------------------
Doe Jane UK

The number of dashes represents the field size.

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:

createtabletblA(colXnumber,colYchar);
createtabletblB(colXnumber);

altertabletblBadd(constraintcolX_FKforeignkey(colX)referencestblA(colX));
-- ORA-02270: no matching unique or primary key for this column-list

altertabletblAadd(constraintcolX_PKprimarykey(colX));
altertabletblBadd(constraintcolX_FKforeignkey(colX)referencestblA(colX));
-- alter table success.

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.

altertabletblAadd(constraintcolY_PKprimarykey(colY));
-- ORA-02260: table can have only one primary key

Cannot create PK or Unique on a column contains duplicate data

insertintotblAvalues(1,'A');
insertintotblAvalues(2,'A');
altertabletblAadd(constraintcolY_UKunique(colY));
-- ORA-02299: cannot validate (HR.COLY_UK) - duplicate keys found


deletefromtblAwherecolx=2;
altertabletblAadd(constraintcolY_UKunique(colY));
-- alter table success.

createtabletblC(colYchar);
altertabletblCadd(constraintcolY_FKforeignkey(colY)referencestblA(colY));
-- alter table success.

Insert data into a column with FK, the value must already exist in the column that the FK reference to.

insertintotblCvalues('B');
-- ORA-02291: integrity constraint (HR.COLY_FK) violated - parent key not found

insertintotblCvalues('A');
-- 1 rows inserted

As long as a foreign key exist, the parent table can truncate/delete the data or disable the PK or Unique constraint

truncatetabletblA;
-- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Find out the constraint information in Oracle

descall_constraints;

select
a.owner,a.table_name,a.constraint_name,
a.constraint_type,a.status,a.r_owner,a.r_constraint_name,
b.table_nameasr_table_name,b.statusasr_status
fromall_constraintsa
leftjoinall_constraintsbona.owner=b.owneranda.r_constraint_name=b.constraint_name
wherea.table_namelike'TBL%';

select*
fromall_cons_columns
wheretable_namelike'TBL%';

Disable constraint that have foreign key refer to is not allowed, in order to do this, you have to disable the foreign key first.

altertabletblAdisableconstraintcolX_PK;
-- ORA-02297: cannot disable constraint (HR.COLX_PK) - dependencies exist
altertabletblAdisableconstraintcolY_UK;
-- ORA-02297: cannot disable constraint (HR.COLY_UK) - dependencies exist

altertabletblCdisableconstraintcolY_FK;
altertabletblBdisableconstraintcolX_FK;

altertabletblAdisableconstraintcolX_PK;
altertabletblAdisableconstraintcolY_UK;
truncatetabletblA;

If the data in parent table is deleted, re-enable the foreign key that contain data reference to the missing data is not allowed.

select*fromtblC;
altertabletblAenableconstraintcolY_UK;
altertabletblCenableconstraintcolY_FK;
-- ORA-02298: cannot validate (HR.COLY_FK) - parent keys not found

Generate a SQL statements to disable all the Foreign Key on a specified table

select
'alter table '||a.owner||'.'||a.table_name||
' disable constraint '||a.constraint_name||';'asSTMT
fromall_constraintsa,all_constraintsb
wherea.constraint_type='R'
anda.r_constraint_name=b.constraint_name
anda.r_owner=b.owner
andb.table_name='TBLA';

Example of renaming:

ALTERTABLEclient1RENAMEtoclient2

First field values constraint addition:

ALTERTABLEclient1CHECKid>1;

Primary key addition:

ALTERTABLEclient1ADDCONSTRAINTclient1_pkPRIMARYKEY(id);

Primary key removal:

ALTERTABLEclient1ADDPRIMARYKEY(id)DISABLE;

Foreign key addition:

ALTERTABLEclient1
ADDCONSTRAINTfk_client2
FOREIGNKEY(client2_id)
REFERENCESclient2(id);

DROPTABLEclient1;

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

Example:

CREATETABLEt_range
(t1VARCHAR2(10)NOTNULL,
t2NUMBERNOTNULL,
t3NUMBER
)
PARTITIONBYRANGE(t2)
(PARTITIONpart1VALUESLESSTHAN(1),
PARTITIONpart2VALUESLESSTHAN(11),
PARTITIONpart3VALUESLESSTHAN(MAXVALUE)
);

Example:

CREATETABLEt_hash
(t1VARCHAR2(10)NOTNULL,
t2NUMBERNOTNULL,
t3NUMBER
PARTITIONBYHASH(t2)
PARTITIONS4
;

Example:

CREATETABLEt_list
(ortVARCHAR2(30)NOTNULL,
t2NUMBER,
t3NUMBER
)
PARTITIONBYLIST(ort)
(PARTITIONpart_nordVALUESIN('Hamburg','Berlin'),
PARTITIONpart_suedVALUESIN('Muenchen','Nuernberg'),
PARTITIONpart_westVALUESIN('Koeln','Duesseldorf'),
PARTITIONpart_ostVALUESIN('Halle'),
PARTITIONpart_defVALUES(DEFAULT)
);

Example:

CREATETABLEt_interval
(buchungs_datumDATENOTNULL,
buchungs_textVARCHAR2(100),
betragNUMBER(10,2)
)
PARTITIONBYRANGE(buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITIONp_historieVALUESLESSTHAN(TO_DATE('2014.01.01','YYYY.MM.DD')),
PARTITIONp_2014_01VALUESLESSTHAN(TO_DATE('2014.02.01','YYYY.MM.DD')),
PARTITIONp_2014_02VALUESLESSTHAN(TO_DATE('2014.03.01','YYYY.MM.DD'))
);