Creating your own oracle database for free on windows – Oracle Database Express XE

This is actually amazingly simple if you wish to have your own database to either play around in or use for home projects or prototyping projects at work.

Downloading and Installing On Windows 10:
Go to the download website https://www.oracle.com/database/technologies/xe-downloads.html, where you will need to create an account to download. Just an email you can access is needed and you can fill out fake details for the rest if you like.

Additionally I would recommend installing SQL developer as a very easy GUI interface (instead of SQL*plus) to manage the database.

The first steps are pretty basically installation steps, just run the setup and click next until you come to the following screen. Here you will enter the password for the admin accounts, so choose this carefully:

At the end of the installation, you will see your connection strings. The multitenant container database is the entire database hosted on your computer at port 1521. Your pluggable database is called XEPDB1 and the web browser based management url is listed as well.

Connecting to database from SQL Developer:

You first may wish to connect the database to see it working. You can do this by firing up SQL Developer and create a new connection, the connection string you will need is the following to connect as sysdba (make special note on the role selection to the right of username!):

Creating a tablespace and a schema in the main multitenant container:

You will need to run the following commands from the command prompt:

# connect to the database as the sysdba

sqlplus / as sysdba

# create a new table space called mytablespace with a size of ~5GB.
create tablespace mytablespace datafile 'mytablespace.dbf' size 5000M;

# hidden parameter to remove need for C## naming conventions
alter session set "_ORACLE_SCRIPT"=true;

# create our schema (or user) and use our new tablespace
create user myuser identified by password default tablespace mytablespace;

# give the new user permissions
grant connect, create session, create table to myuser;

Here is the code shown in the terminal:

Here is the connection set up for the new schema/user:

Creating a tablespace and a schema in the plugable database:

You will need to run the following commands from the command prompt:

# connect to the database as the sysdba

sqlplus / as sysdba

# alter the session to connect to the pluggable database
alter session set container=XEPDB1;

# create a new table space called mytablespace with a size of ~1GB.
create tablespace mytablespace datafile 'mytablespace.dbf' size 5000M;

# create our schema (or user) and use our new tablespace
create user myuser identified by password default tablespace mytablespace;

# give the new user permissions
grant connect, create session, create table to myuser;

Here is the code shown in the terminal (I forgot to set new tablespace, so I altered the user later):

The connection in SQLDeveloper will now look like this, notice that we must use the service name of the plugable database now.

Finally you can see that we have set up a new user/schema called myuser for both the main container and the pluggable database with a separate connection as SYSDBA for admin purposes.

Leave a Reply Cancel reply