17

How to create a small and simple database using Oracle 11 g and SQL Developer ? I am seeing too many errors and I cannot find any way to make a simple database. For example

create database company;

Caused the following error:

Error starting at line 1 in command:
create database company
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted
01501. 00000 -  "CREATE DATABASE failed"
*Cause:    An error occurred during create database
*Action:   See accompanying errors.

EDIT- This is completely different from MySQL and MS-SQL that I am familiar with. Not as intuitive as I was expecting.

Improve this question
1
  • 3
    Creating a DB in MySQL and MS-SQL seems much easier than in Oracle. Is Oracle really that difficult ? Commented Aug 7, 2012 at 21:29

3 Answers 3

Reset to default
38

First off, what Oracle calls a "database" is generally different than what most other database products call a "database". A "database" in MySQL or SQL Server is much closer to what Oracle calls a "schema" which is the set of objects owned by a particular user. In Oracle, you would generally only have one database per server (a large server might have a handful of databases on it) where each database has many different schemas. If you are using the express edition of Oracle, you are only allowed to have 1 database per server. If you are connected to Oracle via SQL Developer, that indicates that you already have the Oracle database created.

Assuming that you really want to create a schema, not a database (using Oracle terminology), you would create the user

CREATE USER company
  IDENTIFIED BY <<password>>
  DEFAULT TABLESPACE <<tablespace to use for objects by default>>
  TEMPORARY TABLESPACE <<temporary tablespace to use>>

You would then assign the user whatever privileges you wanted

GRANT CREATE SESSION TO company;
GRANT CREATE TABLE TO company;
GRANT CREATE VIEW TO company;
...

Once that is done, you can connect to the (existing) database as COMPANY and create objects in the COMPANY schema.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I am totally lost. I see a connection. When I expand it, I see Tables, Views, <more>, other users. So, how do I create a "database" of Employees (not company for now) with tables emp Id, name, age ?
@sweetdreams - You don't create a "database" (using Oracle terminology). You create a schema. In order to create the schema, you create the user that will own the objects in the schema. Then you create the tables, views, etc. that are part of the schema. Do you just want to create a table Employee with columns emp_id, name, and age?
If there is no concept of "database", then why does the Oracle DB docs have a create database statement ? Lets talk in MySQL for now - this is what I want to do - create Database called Company. Make a Table of Employees inside it. Employee has columns id, name, age etc. Can you translate this into Oracle SQL ?
@sweetdreams - The equivalent in Oracle would be the commands I posted. Create a user Company. Grant privileges to the Company user. Connect to the database as the company user and run the CREATE TABLE statement to create the Employee table. The CREATE DATABASE statement was run by the installer when you were installing Oracle to create the database that you are connected to now via SQL Plus where your new Company schema will reside-- it generally doesn't need to be executed a second time on the same server.
Yes, that works. How can I grant all privileges with just one command, instead of doing it via GUI?
|
1

From your question description, I think you were to create a database schema, not a database instance. In Oracle terminology, a database instance is a set of files in the file system. It's more like data files in MySQL. Whereas database in MySQL is somewhat equivalent to Oracle's schema.

To create a schema in Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

To create a database instance in Oracle (I personally prefer CDBA): https://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11068

Notice the Oracle Express edition does not support mounting more than one database instance at one time.

Improve this answer

Comments

0

Actually the answer from Justin above could not be more incorrect. SQL Server and MySQL are for smallish databases. Oracle is for large enterprise databases, thus the difference in it's structure. And it is common to have more than one Oracle database on a server provided that the server is robust enough to handle the load. If you received the error posted above then you obviously are trying to create a new Oracle database and if you are doing that then you probably already understand the structure of an Oracle database. The likely scenario is that you attempted to create a database using dbca, it initially failed, but the binaries were created. You then adjusted your initial parameters and re-tried creating the database using dbca. However, the utility sees the binaries and folder structure for the database that you are creating so it thinks that the database already exists but is not mounted. Dropping the database and removing the binaries and folders as well as any other cleanup of the initial attempt should be done first, then try again.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.