1

I am using Oracle 11g and there is a database create during installation name "xyz" and user "xyz".Now i created a user 'abc' and gave following permission to abc.

"CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, 
 CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, 
 CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, 
 CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE"

Now, I am trying to create database named 'abc'

At the time of creation of the database, I am get the following exception:

ORA-01501:CREATE DATABASE FAILED 
ORA-01100:DATABASE ALREADY MOUNTED

Please tell me how to come out of this. What standard procedure to be for creating database?

  • Are you sure yo want to create a database? You already have a schema (since you've created a user) in your existing database, you should probably be working with that. (See e.g. dba.stackexchange.com/questions/37012/…) – Mat Jul 15 '14 at 11:13
  • What I did is I created user'xyz' and database 'xyz' and with xyz user I am creating user 'abc' user was created. Then i connected by user'abc' and tried creating database'abc' and got this error. – nitesh.kodle123 Jul 15 '14 at 11:24
  • My question stands - why do you want to create another database? Oracle doesn't work like MySQL or SQL Server. The 'abc' user you created exists in database 'xyz' only. Creating a new database "with that user" doesn't make sense in the context of Oracle. – Mat Jul 15 '14 at 11:27
  • so what needs to be done to create a new database.What is the procedure to be followed to do that? – nitesh.kodle123 Jul 15 '14 at 11:29
  • You follow the docs (docs.oracle.com/cd/E11882_01/server.112/e25494/…) for that. But again, are you sure you want a new database in the first place? – Mat Jul 15 '14 at 11:41
3

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace.

If you want to create tables, or other schema objects, you create them with a chosen user as their owner. Such an owner is a schema and there can exist multiple schemas in one database. Schemas can share tablespaces, if you want to. Often, tablespaces are created dedicated for a schema, giving a bit more oversight in who is using what resources.

Since Oracle 12c there also is the option to create a pluggable database inside a container database. Once that pluggable database is created, you can connect to it and use it in a similar way as the pre-12c database, where you again start creating users/owners/schemas and their objects.

| improve this answer | |
1

@nitesh.kodle123 make the difference between a database instance and a database schema !!!

Identify what you want to do and then look for how to do !

| improve this answer | |
0

The following steps need to be followed :

1.Create suitable directory for new database

Like:

  1.C:\oraclexe\app\oracle\admin\DB_NAME
  2.C:\oraclexe\app\oracle\admin\DB_NAME\adump
  3.C:\oraclexe\app\oracle\admin\DB_NAME\dpdump
  3.C:\oraclexe\app\oracle\admin\DB_NAME\pfile

2.Create new database instance for new database

  C:\Windows\system32>oradim -new -sid DB_NAME

3.Create password file for new database.

Like: C:\Windows\system32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\database\PWDDB_NAME.ora password=password

4.Create New pfile in C:\oraclexe\app\oracle\admin\DB_NAME\pfile link

  1. Startup nomount stage db using new pfile

    • Set oracle instance.

         SET ORACLE_SID=DB_NAME
      
    • start sql plus and connect.

    • start oracle Instance.

      SQL>startup nomount pfile='path of initDB_NAME.ora';

6.Create database script with necessary space and file.

7.Check instance status

 select status from v$instance;

8.version

 select * from v$version;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.