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.
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.