none
Emulating multiple SQL Server databases in Oracle RRS feed

  • Question

  • I've an application working with multiple databases in SQL Server. Each database has the same schema but must be kept separate. As some potential users have Oracle, rather than SQL Server, I have been looking at the ADO.Net OracleClient and doing some background reading on Oracle. It appears the word 'database' refers to a single instance of Oracle, and that Oracle does not support having multiple 'databases' within a single instance. Oracle does support the concept of a 'tablespace', which allows tables, views, etc. to be 'grouped' similar to that of an SQL Server database, but before reading on I thought I'd ask if others have wrestled with and solved this problem.

    The SQL command 'CREATE DATABASE DatabaseName' will generate a new database in SQL Server and the various tables can then be added, but not so with Oracle where 'CREATE DATABASE' appears initiate a new instance, which I do not want. Would 'CREATE TABLESPACE PseudoDatabaseName' be an approach, with the tables then being added to each tablespace, or is there a better way?

    Thanks in advance for any ideas you might have.

    Tuesday, November 27, 2012 11:37 PM

Answers

  • As per Oracle,

    A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database. Infect this oracle links says “A one-to-many relationship exists between the database and instances”

    I am not sure about your question. AFIK both Oracle and SQL server platform behaves same behavior for CREATE DATABASE command.


    Lingaraj Mishra

    • Marked as answer by Alexander Sun Friday, December 7, 2012 1:35 AM
    Wednesday, November 28, 2012 9:33 AM
  • Thanks for your reply Lingaraj and I'm sorry I've been unable to respond earlier as my internet service has been down for a while. I had, however, continued to work this problem off-line and now have Oracle working with ADO.Net. I found there is a difference in the way SQLServer and Oracle interpret CREATE DATABASE and this, together with a summary of other differences encountered, is summarized in the following thread: https://forums.oracle.com/forums/thread.jspa?threadID=2469492&stqc=true

    Thanks again, Lingaraj, and bye for now.

    Paul


    Friday, January 4, 2013 10:28 AM

All replies

  • As per Oracle,

    A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database. Infect this oracle links says “A one-to-many relationship exists between the database and instances”

    I am not sure about your question. AFIK both Oracle and SQL server platform behaves same behavior for CREATE DATABASE command.


    Lingaraj Mishra

    • Marked as answer by Alexander Sun Friday, December 7, 2012 1:35 AM
    Wednesday, November 28, 2012 9:33 AM
  • Thanks for your reply Lingaraj and I'm sorry I've been unable to respond earlier as my internet service has been down for a while. I had, however, continued to work this problem off-line and now have Oracle working with ADO.Net. I found there is a difference in the way SQLServer and Oracle interpret CREATE DATABASE and this, together with a summary of other differences encountered, is summarized in the following thread: https://forums.oracle.com/forums/thread.jspa?threadID=2469492&stqc=true

    Thanks again, Lingaraj, and bye for now.

    Paul


    Friday, January 4, 2013 10:28 AM