1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Server Developer trying to learn oracle

Discussion in 'Server Administration and Options' started by path2oracle, Mar 25, 2014.

  1. path2oracle

    path2oracle Guest


    I am basically a .NET /SQL Server developer who did some SQL DBA tasks in the past. Recently i was assigned with some oracle stuff, so trying to understand Oracle and its internal functioning at a higher level.

    I noticed oracle has something called packages where u can call various functions within the package, but what are the advantages of using package rather than having separate function calls . I noticed performance is better as the package is loaded in memory, so later calls to related subprograms in the package require no disk I/O but isn't it same for a stored procedure. Also another disadvantage of having packages is if a change is made in a function the whole package has to be recompiled

    SQL Server doesn't have the concept of synonyms, i understood the main advantage of having synonyms in Oracle database(as they hide the underlying object's identity) but the main problem i faced while working with oracle was for some objects(sequence/packages) in my database synonyms were missing, so i had to go and create synonyms for each and every object individually. Can we create a synonym for a user at a higher level to have access for all the objects(sequences/packages/any other)

    Where are the system databases(like Master, MSDB, Model, Temp) located in Oracle?? How does the authentication work at a higher level(just like SQL server uses Master database to validate the user)

    Are there any Dynamic Management views(DMVs) in oracle to look at any performance issue in queries??
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Likes Received:
    Trophy Points:
    Russian Federation

    In case of study of new system it is necessary to begin with study of its architecture.
    Begin with a system concept : On-line documentation 12c (start of Concepts)

    additional: Previouse version on-line documentation (start of Concepts)

    Yes, documentation rather big, but it doesn't mean that vamy directly it is necessary to read all.

    But you at first it is simple study of concepts Oracle.

    Then install Oracle and start studying.

    At first study all basic functionality, and also key basic features of this DBMS.

    Then it is possible decide on a role: DBA or Developer also continue more detail study.

    Resources which will help you with study(for start -- Tutorials) :

    Oracle Database 12c Learning Library

    [FONT=&quot] Oracle Technology Network

    As there are a lot of good books on Oracle: SQL,PL/SQL is in library : http://books.google.ru/

    [FONT=&quot]There will be questions in the course of study - ask, I will try to help.[/FONT]
    [FONT=&quot] [/FONT]

    path2oracle likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    The advantages of using packages over stand-alone procedures/functions include being able to declare dependent variables and types locally in the package rather than having them created in the database itself. This makes for more efficient coding and execution. A package can have both a package specification and a package body, the specification declares the package and the manner in which the procedures/functions are called. The package body provides the 'meat' of the package functionality; the procedure/function code in the body must match how the procedure/function is declared in the spec. Normally if a procedure/package needs to be modified it's not the spec that changes, it's the body. Yes, if a function/procedure in a package needs to be modified then the package body needs to be re-created, but that's usually a simple task.

    There are public and private synonyms; creating a public synonym makes it accessible to all users. Simply drop the private synonyms and re-create them as public and you will significantly reduce the number of synonyms in the database.

    There is no 'system' database; Oracle uses a data dictionary, owned by the SYSTEM and SYS users, with objects in the SYSTEM and SYSAUX tablespaces. Oracle, until 12c, used schemas. Even in 12c you can use schemas to separate tables and data. 12c offers 'pluggable' databases so it behaves more like SQL Server in that regard (use mydb is valid in 12c provided mydb is a pluggable database). Users are authenticated a login via the USER$ table (containing the password hash) or externally, depending on how the user account was created. There is no need for a Master database in Oracle.

    The data dictionary provides a wealth of information regarding wait statistics and performance metrics; there are too many views to list here. there are also AWR, ASH and ADDM reports to assist DBAs in troubleshooting and pinpointing problem areas. Such scripts are located in the $ORACLE_HOME/rdbms/admin directory (on Windows this would be %ORACLE_HOME%\rdbms\admin). Find that location and look through some of the scripts to see what is available in an Oracle database and what reports can be generated.
    path2oracle likes this.
  4. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    Packages also make it much easier to keep track of your code. I have hundreds of different functions and procedures for the various applications I have written at my current company. Each application has its own package and all of the procedures/functions for that application are stored in the same package. If they were all independent objects, maintenance would be a nightmare.