This discussion is archived
8 Replies Latest reply: Jan 10, 2013 6:33 AM by andredecotia RSS

Trying to understand the basics of architecture

andredecotia Newbie
Currently Being Moderated
Hi there, that's my first post here.

I began working with Oracle SGBD, started using Oracle 11g, then I have installed it on a computer and created sucessufully an user/database, the question is: I don't understand clearly
yet the difference between tablesapce, schema, instance , database/user (is an user a database and vice-versa) and other question is about view, trigger, procedure and profile?

Could you help me on understanding both questions?

André AS.
  • 1. Re: Trying to understand the basics of architecture
    EdStevens Guru
    Currently Being Moderated
    user13002323 wrote:
    Hi there, that's my first post here.

    I began working with Oracle SGBD, started using Oracle 11g, then I have installed it on a computer and created sucessufully an user/database, the question is: I don't understand clearly
    yet the difference between tablesapce, schema, instance , database/user (is an user a database and vice-versa) and other question is about view, trigger, procedure and profile?

    Could you help me on understanding both questions?

    André AS.
    Start with the Concepts Guide: http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm

    Beyond that, at this point your questions are too vague to address in any meaningful way. If you can focus on one thing at a time and ask a specific question, it will help us to provide a meaningful answer.

    Edited by: EdStevens on Jan 10, 2013 6:43 AM
  • 2. Re: Trying to understand the basics of architecture
    andredecotia Newbie
    Currently Being Moderated
    hmm, very good, liked it... thanks for sharing the reference EdStevens.

    I have read some tips now that helped me out too, let me try saying the concepts I've got and being more specific on questioning:

    1 - When I created the user, it also meant I've created the database by creating the user, I wanted to say, on Oracle user and database are the same?

    2 - The Oracle's instance is a scope where someone could find the schemas that hierarchically are the databases linked to a tablespace (directory to record data)?

    3 - Every user/database I generate will use some profile?

    4 - What's the difference between procedure, trigger and view?

    hope that's clearer.

    Edited by: user13002323 on 10/01/2013 05:02
  • 3. Re: Trying to understand the basics of architecture
    Rob_J Journeyer
    Currently Being Moderated
    user13002323 wrote:
    hmm, very good, liked it... thanks for sharing the reference EdStevens.

    I have read some tips now that helped me out too, let me try saying the concepts I've got and being more specific on questioning:

    1 - When I created the user, it also meant I've created the database by creating the user, I wanted to say, on Oracle user and database are the same?
    No, the database can contain many users (schemas). There is one database, many users within that database. A user (schema) must belong to one and only one database.
    2 - The Oracle's instance is a scope where someone could find the schemas that hierarchically are the databases linked to a tablespace (directory to record data)?
    The Oracle instance is a set of memory areas and some processes, called background processes, which are used to read/write data and allow users to get to the data.

    A tablespace is a logical group which is made of up datafiles on the disk. you define different characteristics for each tablespace depending on what you use it for. Segments (tables, indexes, etc) are owned by a user (schema) and they have to be in a tablespace. So, userA comes along and creates a table. That table must belong to a tablespace. UserA must have permission to write into that tablespace. UserA can be allowed to write into any tablespace, providing he is granted permission to do so.

    There are different types of tablespaces, like UNDO and TEMPORARY. I'd recommend having a look in the concepts guide again for some more detail on that. The concepts guide will take you a long time to get through and has a huge amount of information in it.
    3 - Every user/database I generate will use some profile?
    Each user can be granted privileges directly or through a role. A role is just an object within the DB which has privileges assigned to it - it's there to make managing user permissions easier. There are profiles in the DB, but don't worry about those at the moment.
    4 - What's the difference between procedure, trigger and view?
    A procedure is a piece of code with a name. You can execute a procedure, and therefore the code, and it will complete the actions you have told it to. Procedures can accept input values and return output values. You'd typically use a procedure when you are going to execute that piece of code many times and have it shared among many users/session executing it.

    A trigger is a piece of code which fires based on a certain event. You could create a trigger to say when the database starts up, fire this trigger and the code will be executed).

    A view is like a window which has (usually) a restricted view of some data. EG. You create a table with names, addresses, tel numbers and salaries for all employees. You want the HR department to see the names, address, etc but not the salaries so you could create a view of just the columns you want them to see and then only let them query the view and not the table. This restricts the data and they have no idea that the salaries are there.

    All of the above is quite general and there are a lot more details to each area.

    Hope that helps,
    Rob
  • 4. Re: Trying to understand the basics of architecture
    moreajays Pro
    Currently Being Moderated
    Hi,

    1 - When I created the user, it also meant I've created the database by creating the user, I wanted to say, on Oracle user and database are the same?

    This is the hierarchy>

    Oracle Database
    |
    Oracle Instances
    |
    Oracle User/Schema

    2 - The Oracle's instance is a scope where someone could find the schemas that hierarchically are the databases linked to a tablespace (directory to record data)?

    Database constitutes of tablespaces & user/schemas makes use of tablespace

    3 - Every user/database I generate will use some profile?

    Yes , default profile if specific profile not specified

    4 - What's the difference between procedure, trigger and view?
    http://www.geekinterview.com/question_details/35078



    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 5. Re: Trying to understand the basics of architecture
    EdStevens Guru
    Currently Being Moderated
    user13002323 wrote:
    hmm, very good, liked it... thanks for sharing the reference EdStevens.
    Good, but you haven't had time to do more than scan the TOC. Go back and start reading
    >
    I have read some tips now that helped me out too, let me try saying the concepts I've got and being more specific on questioning:

    1 - When I created the user, it also meant I've created the database by creating the user, I wanted to say, on Oracle user and database are the same?
    Absolutely untrue. Sounds like you are trying to apply SQL Server concepts to Oracle. The very term 'database' means different concepts between the two products.

    In oracle, the 'database' is the all-encompassing structure. EVERYTHING else exists inside the database - including users. Go back and
    2 - The Oracle's instance is a scope where someone could find the schemas that hierarchically are the databases linked to a tablespace (directory to record data)?
    The instance is the memory space and processes that operate the database.
    >
    3 - Every user/database I generate will use some profile?
    Every user you created will have a profile. Creating a user is NOT the same as creating a database.

    >
    4 - What's the difference between procedure, trigger and view?
    What is the difference between a car, a truck, and a duck?

    A procedure is a named and stored set of sql and plsql statements, that can be executed on-demand by referencing the name of the procedure.
    A trigger is a special kind of procedure, that executes when a defined event occurs.
    A view is a pre-defined and name query that can be SELECTed as if it were a table.
    hope that's clearer.
    Please spend some time actually reading the Concepts Guide, and specifically looking for the answers to your questions. We don't mind answering questions -- that's why we're here -- but we expect you to do some reading and thinking on your own.

    =================================================
    Learning how to look things up in the documentation is time well spent investing in your career. To that end, you should drop everything else you are doing and do the following:

    Go to tahiti.oracle.com.

    Drill down to your product and version.

    <b><i><u>BOOKMARK THAT LOCATION</u></i></b>

    Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab you will find the complete documentation library.

    Spend a few minutes just getting familiar with what <b><i><u>kind</u></i></b> of documentation is available there by simply browsing the titles under the "Books" tab.

    Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what <b><i><u>kind</u></i></b> of information is available there.

    Do the same with the SQL Reference Manual.

    Do the same with the Utilities manual.

    You don't have to read the above in depth. They are <b><i><u>reference</b></i></u> manuals. Just get familiar with <b><i><u>what</b></i></u> is there to <b><i><u>be</b></i></u> referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.

    Then set yourself a plan to dig deeper.
    - Read a chapter a day from the Concepts Manual.
    - Take a look in your alert log. One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them (listed in your alert log) in the Reference Manual.
    - Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.
    - When you have finished reading the Concepts Manual, do it again.

    Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

    =================================
    Edited by: user13002323 on 10/01/2013 05:02
  • 6. Re: Trying to understand the basics of architecture
    andredecotia Newbie
    Currently Being Moderated
    Thanks, folks, for all the help.

    Edited by: user13002323 on Jan 10, 2013 6:27 AM

    Edited by: user13002323 on Jan 10, 2013 6:30 AM
  • 7. Re: Trying to understand the basics of architecture
    moreajays Pro
    Currently Being Moderated
    Hi,

    if this thread is answered can you mark it correct & close

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 8. Re: Trying to understand the basics of architecture
    andredecotia Newbie
    Currently Being Moderated
    I'm finishing to read yet... Thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points