Creating two databases in Oracle 11g

Question

I am a student. I have assignments and projects simultaneously.

I want my project database and the other simple databases separate.

I have no option to use MySQL or others as my lecturer suggested to use only Oracle 11g and SQLPlus.

Could you please outline the process?

Answer 1

I'm a student and i have assignments and projects simultaneously. So I just want my project database and the other simple databases separate.

Kudos to you for some excellent thinking.

I know of "experienced", "professional" Developers that wouldn't have even thought of this.

... I have no option to use MySQL or others as my lecturer suggested only to use Oracle 11G and SQLPlus

Take this idea to your lecturer. With any luck, they'll be as impressed as I was.

That said, Oracle 11 isn't SQL Server and creating an Oracle database is not a trivial exercise. It's a shame you don't have access to Oracle 12, where Pluggable Databases make life a lot easier, but if you're stuck with '11 ...

I would suggest you use schemas within a single database to achieve your goal.
Here's how I would set up [one of] your Project[s]:

A Tablespace can be restored independently of any other so let's put all the "Project" stuff into a Tablespace of its own:

create tablespace my_project 
   datafile '/.../my_project01.dbf' size 50M autoextend ; 

A schema is just another account.

create user my_project 
   identified by a_really_strong_password 
   default tablespace my_project 
   ; 
grant create session, resource to my_project ; 

Obviously to change the objects under the my_project schema, you'd sign on as the schema owner account. However, to just use those objects, you probably ought to put a "safe distance" between you and doing any "damage".

Taking this step "away" from your data is another, big step to working well with Databases.

create role my_project_role 
   identified by a_proper_password ; 

The role will give [update] access to the objects in the schema, but only once you've given those privileges to the Role! You'll need lots of the following, especially after you add, say, any new tables.

grant delete, insert, select, update on my_project.table_1 to my_project_role ; 

If you're going to have other people looking at this, you might also want a second role to allow this, but not to let them break anything:

create role my_project_read 
   identified by another_proper_password 
   ; 
grant select on my_project.table_1 to my_project_read ; 

Lastly, you need an account for yourself:

create user me 
   identified by yet_another_proper_password 
   ; 

grant create session, my_project_role to me ; 

alter user me default role none ; 

Now, when you sign on, you'll need to activate the role, with its password, before you can see any of your my_project.* objects.

set role my_project_role identified by a_proper_password ; 

OK, I grant you this is a bit inconvenient, but it is more secure.

That said, if you're anything like me, after about a week you'll be royally fed up with it and you'll want the role to just be there, available "by default".

To achieve this, don't have a password on it and make it [one of] your "default" role[s]:

alter role my_project_role NOT IDENTIFIED ; 
alter user me default role all ; 

Answer 2

11gR2 or 11.2.0.4 is not longer under support. You should be learning on 12cR2 or 19C, and eventually 20c. Having said that, creating a tablespace, user and role is not the same as creating a database. You can still manually create a database with Oracle and sqlplus. Here are some instructions:

Creating a Database with the CREATE DATABASE Statement

A shortcut to going through all of that would be to use dbca. Dbca is a gui that will walk you through all of the decisions that you will make to create your database. At the end of the menus you have the option to create a database, save the configuration as a template or as scripts. I don't have an 11g Oracle home installed at the moment. So I can't be sure about the options. But one option should be to create the scripts for creating the database. You can turn off the option for creating the database.

From the perspective of learning about what happens when a database is created this is a good exercise. Another thing that you can do is once your database has been created log into sqlplus and do "ALTER DATABASE BACKUP CONTROLFILE TO TRACE;". Then go into your diag trace directory and read the file that was created. This url tells you how to find a trace file. Looking through that file will tell you what files are a part of the database and where they live.

Finding Trace Files