To create a table, you need to define three things:
The basic syntax to create a table is:
create table <table_name> ( <column1_name> <data_type>, <column2_name> <data_type>, <column3_name> <data_type>, ... )
For example, to create a table to store the names and weights of toys, run the following statement:
create table toys ( toy_name varchar2(100), weight number );
The data dictionary stores information about your database. You can query this to see which tables it contains. There are three key views with this information:
The following query will show you the toys table you created in the previous step:
select table_name, iot_name, iot_type, external, partitioned, temporary, cluster_name from user_tables;
The other columns display details of the properties of each table. The rest of this tutorial will explore these.
Complete the following statement to create a table to store the following details about bricks:
Use the data type varchar2(10) for both columns.
create table bricks ( /*TODO*/ ); select table_name from user_tables where table_name = 'BRICKS';
When you create the table, the query should return the value "BRICKS".
Create table in Oracle Database has an organization clause. This defines how it physically stores rows in the table.
The options for this are:
By default, tables are heap-organized. This means the database is free to store rows wherever there is space. You can add the "organization heap" clause if you want to be explicit:
create table toys_heap ( toy_name varchar2(100) ) organization heap; select table_name, iot_name, iot_type, external, partitioned, temporary, cluster_name from user_tables where table_name = 'TOYS_HEAP';
These are good general purpose tables and are the most common type in Oracle Database installations.
Unlike a heap table, an index-organized table (IOT) imposes order on the rows within it. It physically stores rows sorted by its primary key. To create an IOT, you need to:
create table toys_iot ( toy_id integer primary key, toy_name varchar2(100) ) organization index;
You can find IOT in the data dictionary by looking at the column IOT_TYPE. This will return IOT if the table is index-organized:
select table_name, iot_type from user_tables where table_name = 'TOYS_IOT';
Complete the following statement to create the index-organized table bricks_iot:
create table bricks_iot ( bricks_id integer primary key ) /*TODO*/; select table_name, iot_type from user_tables where table_name = 'BRICKS_IOT';
The query afterwards should return the following row:
TABLE_NAME IOT_TYPE BRICKS_IOT IOT
You use external tables to read non-database files on the database server. For example, comma-separated values (CSV) files. To do this, you need to:
create or replace directory toy_dir as '/path/to/file'; create table toys_ext ( toy_name varchar2(100) ) organization external ( default directory tmp location ('toys.csv') );
Note: LiveSQL doesn't support external tables. So these statements will fail!
When you query this table, it will read from the file:>
This file must be accessible to the database server. You cannot use external tables to read files on your machine!
Temporary tables store session specific data. Only the session that adds the rows can see them. This can be handy to store working data.
There are two types of temporary table in Oracle Database: global and private.
To create a global temporary table add the clause "global temporary" between create and table. For example:
create global temporary table toys_gtt ( toy_name varchar2(100) );
The definition of the temporary table is permanent. All users of the database can access it. But only your session can view rows you insert.
Starting in Oracle Database 18c, you can create private temporary tables. These tables are only visible in your session. Other sessions can't see the table!
To create one use "private temporary" between create and table. You must also prefix the table name with ora$ptt_:
create private temporary table ora$ptt_toys ( toy_name varchar2(100) );
For both temporary table types, by default the rows disappear when you end your transaction. You can change this to when your session ends with the "on commit" clause.
But either way, no one else can view the rows. Ensure you copy data you need to permanent tables before your session ends!
The column temporary in the *_tables views tell you which tables are temporary:
select table_name, temporary from user_tables where table_name in ( 'TOYS_GTT', 'ORA$PTT_TOYS' );
Note that you can only see a row for the global temporary table. The database doesn't write private temporary tables to the data dictionary!
Partitioning logically splits up a table into smaller tables according to the partition column(s). So rows with the same partition key are stored in the same physical location.
There are three types of partitioning available:
To create a partitioned table, you need to:
The following statements create one table for each partitioning type:
create table toys_range ( toy_name varchar2(100) ) partition by range ( toy_name ) ( partition p0 values less than ('b'), partition p1 values less than ('c') ); create table toys_list ( toy_name varchar2(100) ) partition by list ( toy_name ) ( partition p0 values ('Sir Stripypants'), partition p1 values ('Miss Snuggles') ); create table toys_hash ( toy_name varchar2(100) ) partition by hash ( toy_name ) partitions 4;
By default a partitioned table is heap-organized. But you can combine partitioning with some other properties. For example, you can have a partitioned IOT:
create table toys_part_iot ( toy_id integer primary key, toy_name varchar2(100) ) organization index partition by hash ( toy_id ) partitions 4;
The database sets the partitioned column of *_tables to YES if the table is partitioned. You can view details about the partitions in the *_tab_partitions tables:
select table_name, partitioned from user_tables where table_name in ( 'TOYS_HASH', 'TOYS_LIST', 'TOYS_RANGE', 'TOYS_PART_IOT' ); select table_name, partition_name from user_tab_partitions;
Note that partitioning is a separately licensable option of Oracle Database. Ensure you have this option before using it!
Complete the following statement to create a hash-partitioned table. This should be partitioned on brick_id and have 8 partitions:
create table bricks_hash ( brick_id integer ) partition by /*TODO*/; select table_name, partitioned from user_tables where table_name = 'BRICKS_HASH';
The query should return the following row:
TABLE_NAME PARTITIONED BRICKS_HASH YES
A table cluster can store rows from many tables in the same physical location. To do this, first you must create the cluster:
create cluster toy_cluster ( toy_name varchar2(100) );
Then place your tables in it using the cluster clause of create table:
create table toys_cluster_tab ( toy_name varchar2(100) ) cluster toy_cluster ( toy_name ); create table toy_owners_cluster_tab ( owner varchar2(20), toy_name varchar2(100) ) cluster toy_cluster ( toy_name );
Rows that have the same value for toy_name in toys_clus_tab and toy_owners_clus_tab will be in the same place. This can make it faster to get a row for a given toy_name from both tables.
You can view details of clusters by querying the *_clusters views. If a table is in a cluster, cluster_name of *_tables tells you which cluster it is in:
select cluster_name from user_clusters; select table_name, cluster_name from user_tables where table_name in ( 'TOYS_CLUSTER_TAB', 'TOY_OWNERS_CLUSTER_TAB' );
Note: Clustering tables is an advanced topic. They have some restrictions. So make sure you read up on these before you use them!
You can remove existing tables with the drop table command. Just add the name of the table you want to destroy:
select table_name from user_tables where table_name = 'TOYS_HEAP'; drop table toys_heap; select table_name from user_tables where table_name = 'TOYS_HEAP';
Once you've dropped a table you can't access it. So take care with this command!
Complete the following statement to drop the toys table:
drop table /*TODO*/ ; select table_name from user_tables where table_name = 'TOYS';
The query afterwards should return no rows.