Summary: in this tutorial, you will learn how to create the
OT sample database in your Oracle database server.
Note that the commands in this tutorial may not be familiar to you, it will be fine. First, you just need to follow the instructions step by step to create the sample database. Then, you can learn each command in the next tutorials.
Creating a new user and granting privileges
First, launch the SQL*plus program the command line:
Or from the installation directory of the start menu:
Once the SQL*Plus is launched, it will prompt you for a username and password. Go ahead to log in as the
sys user with the password that you entered during the installation of the Oracle database server
Enter user-name: sys as sysdba Enter password:
When you connect to the Oracle database server, you connect to a container database (CDB) named
ROOT. To show the current database, you use the
SQL> SHOW con_name; CON_NAME ------------------------------ CDB$ROOT
Next, you need to switch to a pluggable database. Noted that during the installation of Oracle, we already created a pluggable database named
To switch to the
PDBORCL pluggable database, you use the following statement:
SQL> ALTER SESSION SET CONTAINER = pdborcl; Session altered.
Note that if you use a different pluggable database, feel free to change it in the command.
If you execute the
show command again, the database now is
SQL> SHOW con_name; CON_NAME ------------------------------ PDBORCL
Before creating a new user, you need to change the database to open by executing the following command:
SQL> ALTER DATABASE OPEN; Database altered.
Then, you create a new user for creating the sample database in the pluggable database using the following
CREATE USER statement:
SQL> CREATE USER OT IDENTIFIED BY Orcl1234; User created.
The above statement created a new user named
OT with a password specified after the
IDENTIFIED BY clause, which is
Orcl1234 in this case.
After that, you grant privileges to the
OT user by using the following
SQL> GRANT CONNECT, RESOURCE, DBA TO OT; Grant succeeded.
Finally, you can connect to the pluggable database (
PDBORCL) using the
OT user account. Type the password (
Orcl1234) for the
OT user when SQL*plus prompts you for the password.
SQL> CONNECT ot@pdborcl Enter password: Connected.
Note that OT user only exists in the PDBORCL pluggable database, therefore, you must explicitly specify the username as
ot@pdborcl in the
Creating database tables
Before going forward, you need to download the Oracle sample database.
To create tables in the sample database, you need to execute the statements in the
ot_schema.sql file from SQL*plus.
In order to execute SQL statements in a file from SQL*plus, you use the following command:
ot_schema.sql file is located in the
c:\dbsample\ directory, you execute the statement below.
Once the statement completes, you can verify whether the tables were created successfully or not by listing the tables owned by the
OT user. The following is the statement to do so.
SQL> SELECT table_name FROM user_tables ORDER BY table_name; TABLE_NAME -------------------------------------------------------------------------------- CONTACTS COUNTRIES CUSTOMERS EMPLOYEES INVENTORIES LOCATIONS ORDERS ORDER_ITEMS PRODUCTS PRODUCT_CATEGORIES REGIONS WAREHOUSES 12 rows selected.
In this statement, we selected the values in the
table_name column from the
user_tables table and sorted the table names alphabetically. As you see, 12 table names returned as expected.
Now, you are ready to load data into these tables.
Loading data into tables
To load data into the tables, you execute the statements in the
ot_data.sql file as follows:
If you don’t see any error messages, it means that the data has been loaded successfully.
You can also verify whether data has been loaded successfully by using the
SELECT statement. For example, to get the number of rows in the
contacts table, you use
COUNT(*) function as shown in the following statement:
SQL> SELECT COUNT(*) FROM contacts; COUNT(*) ---------- 319
The query returns
319 indicating that the
contacts table has 319 rows.
By replacing the table name (
contacts) by another table name, you can check data in another table. This is a good exercise for you if this is the first time you have worked with Oracle.
In this tutorial, you have learned step by step how to create the
OT sample database in the Oracle Database server. Now, you should be ready to connect to the Oracle sample database for practice.