some basic oracle concepts

Question

Hi: In our new application we have to use the oracle as the db,and we use mysql/sqlserver before,when I come to oracle I am confused by its concepts,for exmaple,the table space,the object,the schema table,index, procedure, database link,...:(

And the schema is closed to the user,I can not make it.

Since when we use the mysql,I just know that one database contain as many tables,and contain as many users,user have different authentication for different table.

But in oracle,everything is different.

Anyone can tell me some basic concepts of oracle,and some quick start docs?

Answer 1

Oracle has specific meanings for commonly-used terms, and you're right, it is confusing. I'll build a hierarchy of terms from the bottom up:

• Database - In Oracle, the database is the collection of files that make up your overall collection of data. To get a handle on what Oracle means, picture the database management system (dbms) in a non-running state. All those files are your "database."
• Instance - When you start the Oracle software, all those files become active, things get loaded into memory, and there's an entity to which you can connect. Many people would use the term "database" to describe a running dbms, but, once everything is up-and-running, Oracle calls it an, "instance."
• Tablespace - A abstraction that allows you to think about a chunk of storage without worrying about the physical details. When you create a user, you ask Oracle to put that user's data in a specific tablespace. Oracle manages storage via the tablespace metaphor.
• Data file - The physical files that actually store the data. Data files are grouped into tablespaces. If you use all the storage you have allocated to a user, or group of users, you add data files (or make the existing files bigger) to the tablespace they're configured to use.
• User - An abstraction that encapsulates the privileges, authentication information, and default storage areas for an account that can log on to an Oracle instance.
• Schema - The tables, indices, constraints, triggers, etc. that are owned by a particular user. There is a one-to-one correspondence between users and schemas. The schema has the same name as the user. The difference between the two is that the user concept is all about account information, while the schema concept deals with logical database objects.

This is a very simplified list of terms. There are different states of "running" for an Oracle instance, for example, and it's easy to get into very nuanced discussions of what things mean. Here's a practical exercise that will let you put your hands on these things, and will make the distinctions clearer:

1. Start an already-created Oracle instance. This step will transform a group of files, or as Oracle would say, a database, into a running Oracle instance.
2. Create a tablespace with the CREATE TABLESPACE command. You'll have to specify some data files to put into the tablespace, as well as some storage parameters.
3. Create a user with the CREATE USER command. You'll see that the items you have to specify have to do with passwords, privileges, quotas, and the like. Specify that the user's data be stored in the tablespace you created in step 2.
4. Connect to the Oracle using the credentials you created with the new user from step 3. Type, "SELECT * FROM CAT". Nothing should come back yet. Your user has a schema, but it's empty.
5. Run a CREATE TABLE command. INSERT some data into the table. The schema now contains some objects.

Answer 2

• table spaces: these are basically storage definitions. when defining a table or index, etc., you can specify storage options simply by putting your table in a specific table_space
• table, index, procedure: these are pretty much the same
• user, schema: explained well before
• database link: you can join table A in instance A and table B in instance B using a - database link between the two instances (while logged in on of them)
• object: has properties (like a columns in a table) and methods that operate on those poperties (pretty much like in OO design); these are not widely used

A few links:

Start page for 11g rel 2 docs http://www.oracle.com/pls/db112/homepage

Database concepts, Table of contents http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/toc.htm