Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008

    Unanswered: Oracle 101 (Coming from MS SQL Server)

    I'm converting a product of ours that currently uses MS SQL Server 2000 to Oracle 10g. Coming to Oracle from MS Server is a bit of a culture shock, with it's huge overhead of stuff and bizarre twist on terminology. I've got a few Oracle books and also trawled websites, but can't seem to do the most simple things, so would appreciate some help.

    1. In MS SQL Sever you have a database and within it tables. Would I be right in assuming the Oracle equivalent is Schemas and tables.

    2. If so how do I create a schema? I basically want to set up a new database container called OurCompany and within it, 30 tables, say for simplicities sake table01..table30

    3. For this table I want to have a user called myuser with a password of mypassword who is able to do use usual CRUD operations. I also want an admin user who can do all the SYSDBA stuff, although I assume I can just use the SYS account.

    4. How do I relate that user to the schema / database created.

    To get up to speed, I used the Scott schema, deleted the four tables in there and did a datapump from MS SQL to Oracle. My application works fine, but I just need to know how to setup our database from scratch. The client is going to be managing the Oracle database.

    Thanks in advance


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    In Oracle, there is one database per instance. Remember, SQL Server can have multiple schemas as well, but most people just use "dbo"

    Oracle has a CREATE SCHEMA statement. You can find it here:

    Granting permissions is the same in Oracle, as it is in SQL Server. You can grant SELECT, UPDATE, DELETE, and INSERT permissions on tables, and EXECUTE permissions on procedures, functions, and packages. The user must remember to qualify the schema along with the table name, in order to access the data, unless the user is the schema owner.

  3. #3
    Join Date
    Feb 2003
    1. sql server is 4 level, oracle is 3
    sql server = [instance.]database.schema.table
    oracle = [instance=database.]schema.table

    so the statemant "sql server database equivalent of oracle is schema" is not exactly true but can be assumed for parctical purpose

    2. schema is created whenever an user is created in oracle. so, if any oracle database is having 100 users it will have 100 schemas, even if they are empty

    3. to give selective rights u need to create another user (schema will be created automatically) and assign rights to OurCompany.table01

    4. no relation is neeeded. any user can can access any table on any schema if having rights.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts