Creating two databases in Oracle 11g

oracleoracle-11gsqlplus

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?

Best Answer

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 ;
Related Question