How to create a database? 3

[vbSun]

Hi all,

I have been working with SQL Server all these times, and now got posted to an ORACLE DB Design. I would like to ask the ORACLE pros my doubts, the first one being this.

We can create databases in SQLServer via queries (Create database). Is it possible in ORACLE? Like, there is a database called Shop and all the tables should come under it. Is there a way? If there is, how is it? I am using Windows 2000 Server and ORACLE 8.

Thanks a lot, in advance.

 

[sem]

AFAIK the entity called database in SQL Server is almost the same as SCHEMA in Oracle. You may create it by issuing CREATE USER command. CREATE DATABASE command is also valid in Oracle, but its meaning is different.

Regards, Dima

 

[Turkbear]

Hi,
Pay close attention to sem's reply..
In SqlServer the definition of database is radically different from Oracle's..
In Oracle, tables, etc are created in entities called Schemas - As sem noted, schemas are created when a user is created and then that user's created objects ( tables, views, etc ) are placed in that schema ...

NB: Because of the extreme conceptual differences between these 2 products, training is usually required before you can be an effective Oracle DBA..
Either get your employer to pay for Oracle University classes ( at least the introductory series for DBAs ) or purchase some good books..
It has even been said that to be really good at Oracle you need to forget everything you know about SqlServer

Also, review the documentation ( especially the Administration and Concepts manuals ) at

http://tahiti.oracle.com

( register for Technet if requested, it is free and will be a 'life-saver' as you get more involved with Oracle.)

Enjoy the journey into Oracle land...

 

[vbSun]

Thanks guys, that was real enlightment.

I am going to the link provided, now itself, and thanks for the wish. Am sure its going to be a great trip...

Also, i would like to ask you, just a fundamental doubt, now as i understand there are Create Database statements in ORACLE, what exactly does it do?

If you have links for some online tutorials, pls provide.

Thanks again Dima and Turk,

Best Regards,
Sunil

 

[sem]

In brief, this statement creates initial layout for your database. You may read about concepts on tahiti.oracle.com.

Regards, Dima

 

[vbSun]

Thanks Dima,

Am already in the tahiti.oracle.

Best Regards,
Sunil

 

[sybaseguru]

Sunil,

As you are so interested in knowing the answer, Oracle database creation basically creates a named Oracle database for you. As an example have a look at a triviliased example below

CREATE DATABASE DB1
        USER SYS IDENTIFIED BY oracle
        USER SYSTEM IDENTIFIED BY oracle
        LOGFILE GROUP 1 ('/oradata1/DB1/redo01.log') SIZE 100M,
               GROUP 2 ('/oradata1/DB1/redo02.log') SIZE 100M,
               GROUP 3 ('/oradata1/DB1/redo03.log') SIZE 100M
       MAXLOGFILES 5            -- max number of redo log file groups
       MAXLOGMEMBERS 5          -- max number of copies for a redo log file group
       MAXLOGHISTORY 1          -- used with RAC option
       MAXDATAFILES 1000
       MAXINSTANCES 1           -- relevant to RAC
       NOARCHIVELOG
       CHARACTER SET "WE8ISO8859P1"     -- cannot change database character set afterwards
       DATAFILE '/oradata2/DB1/system01.dbf' SIZE 500M REUSE
       EXTENT MANAGEMENT LOCAL
       DEFAULT TEMPORARY TABLESPACE tempts1
         TEMPFILE '/oradata2/DB1/temp01.dbf'
         SIZE 100M REUSE
      UNDO TABLESPACE undotbs1                          -- this name should agree with the one in the init.ora file
         DATAFILE '/oradata2/DB1/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
/

Before you start Oracle database running, you need to allocate the resourcse from O/S to enable Oracle to start. You have to appreciate two terms, the Instance and database. You need to start the instance, i.e the allocation of Memory or SGA (System Global Area) and the Oracle processes before accessing data files. Every RDBMS including Oracle uses shared memory for work. So your 'SA' sets up shared memory (based opon RAM) with enough values to start Oracle backround processes and the shared memory in order for these background processes to have resources to communicate with each other and have access to data. This memory structures referred to as SGA. The database as correctly pointed in earlier replies is a colloection of schemas. Roughly speaking, the master database becomes the Oracle control file. The Oracle startup file init<SID>.ora translates to SQL_SERVER_NAME.cfg file.
Every Oracle database can have many schemas. Oracle database is made up of one system tablespace and one or more other tablespaces. In a very simplistic world system tablespace holds the oracle data dictionary (sysobjects,syslogins etc stuff in SQL Server) plus other bits and pieces. The schema or user rougly translates to SQL Server login with &quot;dbo&quot; rights (schema) in any user database. Oracle roles (not to be confused with Sybase/SQL Server roles) can be created, grant on a schema's tables can be given to the role and Oracles users can be assigned these roles. So simplistically a role in Oracle is synonym to creating a user group in Sybase/SQL Server database and giving grants to tables/sps to the group and adding logins to the group.

However as our friends pointed out there is a lot to be covered. Just to get ideas how things mean look at the sites

[URL unfurl="true"]http://sybernet.sri.com/oracle/Translator.html[/URL]

[URL unfurl="true"]http://www.amphlett.com/default.asp?page=CodeTipsSQL[/URL]

also look at document on SQL Server/Sybase to Oracle migration workbench (from Oracle site or search in google)

A very useful book for you to start would be
Oracle 8i and Microsoft SQL Server 2000 Integration, Stephen Chelack, M&T books, ISBN 0-7645-4699-6

Good luck and hope all helps

 

[vbSun]

Thanks guru,

You guys are great. I am going to pay more attention to this, and thanks for creating the interest.

Special Mention: SybaseGuru, the response was really helpful. It gives me a good starting point.

Best Regards,
Sunil