How To Create A Database Using A Template Oraclr 11g
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:
Code language: SQL (Structured Query Language) ( sql )
>sqlplus
Or from the installation directory of the start menu:
![](https://www.oracletutorial.com/wp-content/uploads/2017/07/access-sqlplus.png)
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
Code language: JavaScript ( javascript )
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 SHOW
command:
Code language: SQL (Structured Query Language) ( sql )
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 namedPDFORCL
.
To switch to the PDBORCL
pluggable database, you use the following statement:
Code language: SQL (Structured Query Language) ( sql )
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 PDBORCL
.
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
SQL> ALTER DATABASE OPEN; Database altered.
Then, you create a new user for creating the sample database in the pluggable database using the followingCREATE USER
statement:
Code language: SQL (Structured Query Language) ( sql )
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 GRANT
statement:
Code language: SQL (Structured Query Language) ( sql )
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 theOT
user when SQL*plus prompts you for the password.
Code language: SQL (Structured Query Language) ( sql )
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 CONNECT
command.
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:
Code language: SQL (Structured Query Language) ( sql )
SQL>@path_to_sql_file
Suppose the ot_schema.sql
file is located in the c:\dbsample\
directory, you execute the statement below.
Code language: SQL (Structured Query Language) ( sql )
SQL>@c:\dbsample\ot_schema.sql
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.
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
SQL>@c:\dbsample\ot_data.sql
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:
Code language: SQL (Structured Query Language) ( sql )
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.
Was this tutorial helpful?
How To Create A Database Using A Template Oraclr 11g
Source: https://www.oracletutorial.com/getting-started/create-oracle-sample-database-for-practice/
Posted by: bennettnetaid.blogspot.com
0 Response to "How To Create A Database Using A Template Oraclr 11g"
Post a Comment