Tutorial 4: Creating a Schema

Now that we have a database we can create schemas inside the database. A schema is defined as a user that owns data such as tables, views, indexes, and so forth. If a user has no data of their own and just connects and queries for information then they are not considered a schema. This is the difference between a schema and a user. Also you may be familiar with other database systems and will notice that what Oracle calls a schema the other systems call a database.

The Oracle database can have numerous schemas and each schema in the database can query other schemas in the same database. Also schemas can communicate over database links to schemas in other remote databases. The communication between schemas is only limited by the permissions you put in place. Generally most applications will sit inside just a single schema.

Creating the Tablespace

Before creating a schema you will need to create a file (or more than one file) for the schema to place its data into. This file (or set of files) where the schema writes its data is called a tablespace. Note that you can create more than one tablespace for a schema and then have it write tables to one tablespace and indexes into the other and so forth. However we are going to keep this tutorial simple and just create a single tablespace that is made up of a single 1GB file.

First connect to the database from the command line as the sys user:

$ sqlplus /nolog

SQL> connect / as sysdba

Now that you are connected you can create a tablespace. The definition that I use below creates a tablespace called CRAIG_DATA which is sized 1GB and uses the data file /ora/fs2000/u04/CRAIG/craig_data_01.dbf. You can change this to whatever you want to call the tablespace and data file.

SQL> create tablespace CRAIG_DATA
     datafile '/ora/fs2000/u04/CRAIG/craig_data_01.dbf' size 1000M
     extent management local autoallocate segment space management auto;

Creating the User

With the tablespace created we can now create the user. The reason we had to create the tablespace first was that the tablespace name is used in the definition of the user. To create a user we use the following string which creates a user named craig with password craig and uses the tablespace CRAIG_DATA. Of course change the name, password, and tablespace to your own:

SQL> create user craig identified by "craig" default tablespace CRAIG_DATA;

Now that the user is created we will give them some basic permissions so they can start working. The first permission they will need is called CONNECT. This will allow them to connect to the database. The second permission they will need is quota on their tablespace so they can write to it. And finally we will give them one more permission called CREATE TABLE which will allow them to create tables. You will notice that Oracle has a fine control over everything users are allowed to do. Here is the syntax for the three permissions (change the names to your own):

SQL> grant connect to craig;
SQL> alter user craig quota unlimited on CRAIG_DATA;
SQL> grant create table to craig;

With the permissions in place we can now create a basic table which will change this user into a schema since it will now own its own data. First connect as the user, you can use the following syntax (connect username/password):

SQL> connect craig/craig

Now create a quick test table to ensure things are working as expected. In this example we will create a table called TEST which has two columns called CITY and POPULATION:

SQL> create table TEST (CITY varchar2(32), POPULATION number);

Now lets insert two rows into the table with some random data:

SQL> insert into TEST (CITY, POPULATION) values ('edmonton', 10);
SQL> insert into TEST (CITY, POPULATION) values ('vancouver', 20);
SQL> commit;

Finally examine the contents of the table by using SELECT to view the rows that were inserted:

SQL> select * from test;

Now that we have the basics in place the next tutorial will cover how to write SQL inside our C++ code so that we can query the database remotely from our application.

Back to Tutorial Index