Creating tables in Oracle

  1. Start by logging in to your Oracle SQL*Plus. You must have registered for this in order to do so.
  2. Create the tables that will be worked on. In this case, we will create the tables ‘SnookerPlayer’ and ‘CompetitionsWon’.
create table snookerplayer
(playerid number primary key not null, 
name varchar2(50),
dob date,
location varchar2(50),
height number,
style varchar2(30))
create table competitionswon
(compid number primary key not null,
playerid number not null,
compname varchar2(30),
year number(4),
foreign key (playerid) references snookerplayer(playerid))

3. Next, fill the tables with some ‘dummy’ data.

insert into snookerplayer values(1, 'Steve nugget Davis', '01-feb-1920', 'London', 180, 'Slow/Boring');

insert into competitionswon values(1, 1, 'World Championship', 1981);

4. Type in Commit; (by doing this, your work will be saved)

5. Try these useful commands:

Desc snookerplayer
Select * from SnookerPlayer
Drop SnookerPlayer Cascade

Advanced table creation

  1. Start by logging in to your Oracle SQL*Plus. You must have registered for this in order to do so.
  2. Query the view user_ts_quota to see what quota you have on which tablespaces, and how much you have used.
    For example:
select tablespace_name, bytes, max_bytes from user_ts_quotas; 

Please note, it is not worth asking for more space if you are not using any of your non-default tablespaces!

3. Create your table again but with the added clause tablespace TABLESPACENAME;
For example:

create table snookerplayer (playerid number primary key not null,
name varchar2(50),
dob date,
location varchar2(50),
height number,
style varchar2(30))
tablespace comp1_4;

Comments are closed.