Re: PLSQL for MS Sql server devs?

There are lots of places you can geet additional help.

First and foremost, I recommend 'Oracle Essentials: Oracle8 and Oracle8i' - a book by O'Reilly. This is the first Oracle book I've seen that discusses the architecture, the technology, the DBA approaches, etc. at an introductory level - with sufficient info to get a newbie started (to be able to understand the manuals, etc.) as well as tweak an experienced DBA. That book explains the concepts you are asking about well.

Second, go to http://technet.oracle.com and register yourself (free). That site has ALL current documentation, as well as discussion groups, software, sample code, and so on.

Third, there are a number of additional books - I've been impressed at O'Reilly books in the nlast 2 years (http://www.oreilly.com) and, of course there are the Oracle Press books published by Osborne.

There are many, many similarities betwen SQL Server and Oracle - enough to confuse you for a while. More importantly, the similarities will keep a newbie at a relatively high level for a while, frequently clouding over the potential value of Oracle capabilities - for example, I've seen companies buy IBM MQ and Workflow simply because they did not realize that the database includes messaging and workflow at no additional charge. I've seen companies buy monitoring software because they did not realize that Enterprise Manager is also included. (I hate seeing money wasted out of ignorance; evaluate the product and competing product and make an informed decision.)

Others have answered your specific question. Let me add my 2 cents worth:

1. Instance is the Oracle software running, as well as all the memory & CPU cycles, necessary to control a database.

An instance may control a maximum of 1 database, however (in parallel server - ie. clustering) multiple instances may control a database.

An instance is started up using the STARTUP command, and of course stopped using the SHUTDOWN command. The STARTUP command goes through the follwing sequence (in short form)

a) fire up the processes/threads
b) attach the processes to a specific database (mount)
c) make that database available to users (open)

A single computer may have multiple instances - this depends on memory and CPU requirements vs availability

2) A database is a collection of files that may be controlled by an instance. Some of those files are 'data files' which contain all the data dictionaries you may wish to use. Others include log files, control files and so on.

The database part is the 'persistant' portion of the beast.

3) A schema is simply a userid that is designated to hold or own a data dictionary, usually for a user or applications use. (The dictionary is the collection of all relevant objects - tables, indexes, triggers, stored procedures, etc.)

A single database may have any number of schemas. Thus a single database may suport any number of applications. This provides flexibility to put the schema for multiple apps into a single database on a machine, into multiple databases on a single machine, or multiple database across multiple machines - depending on 'maintenance' issues (upgrade cycles, CPU requirements, user and user-chargeback requirements, etc.)

Multiple schemas within a single database ARE secure from each other.

Two special schemas are SYS - which owns the dictionary for Oracle itself; and SYSTEM - which owns al lot of the admin. related dictionary (including some synonyms and views on the SYS dictionary).

4) Objects are the things you can have in a dictionary.

Objects that take physical space in the database will require 'segments' in the data files. These include Tables, Indexes, etc. Other objects, such as Stored Procedures, do not have separate segments; their definition is kept (within SYS-owned tables), and used by the instance to create appropriate effects in the database.

Segments take up room in Tablespaces. The room is allocated in Extents which you can size. Extents are pre-allocated when required; when you create a table (type of segment), you let Oracle know how big the initial extent should be (possibly by using the defaults) and in which tablespace the segment should be created. Once that is used up, the 'next extent' is created, and so on.

Tablespaces are made of physical files - a tablepace may use any number of data files, but tablespaces can not share data files (one to many mapping between tablespace and data files). This allows you to control data to disk allocation, which could eventually become useful in performance situations.

A tablespace is made up of blocks. Oracle does all of it's primary work (searching, updating, etc.) in memory; a block is the base unit of memory; you define how big the disk block is (2K, 4K, 32K, etc.) and how many adjacent disk blocks the instance should grab (called multiblock read count). Since the disk to memory operations are at the block level, if you update a row in a table, all of the other rows in that table that happen to reside in that block are loaded into memory, and written back to disk.

That should eb about enough to get you started - good luck.

duanez wrote:

> I am transitioning from MS Sql server's t-SQL to Oracle's PL SQL
> (incld Oracle Web). I'd like to find a book or faq that can help me
> with the different concepts, programming practices and concepts.
>
> For example, i get confused over what we mean by instance and schema.
> Is an Oracle Instance the equivalent to a MS Sql Server Database? or
> the Server itself? And when we spek of a schema in Oracle, do we mean
> that that is a database, which is a subset of the Instance? In MS SQL
> server we had the heirarchy go SERVER.DATABASE.OBJECT. In Oracle is it
>
> INSTANCE.SCHEMA.OBJECT?
>
> I installed Oracle 8i personal edition at home. The documentation
> refers sometimes to a 'database' but this seems interchangeable with
> the concept of an Instance. So can an Oracle Database Server have > 1
> instances each with > 1 schemas?
>
> other questions i have are more directly related to programming
> practices in PL SQL. In MS SQL server, we relied heavily on #temp
> tables whereas in PL SQL i noticed (from other people's code) there is
> great emphasis on cursors. How do you deal with no temp tables? Can a
> PL SQL function return a 'table' or a recordset datatype as a result?
> And then that rs be like table object and included in joins to other
> objects?
>
> If anyone has writen an FAQ or a Book with a title like "PL SQL for
> ex-MS SQL Server Developers" that'd be great. If not, i'd like help
> starting one.
> thanks
Received on Sun May 13 2001 - 02:12:46 CDT