Re: help: create database

Xiaofeng Cheng wrote in message <6f97ao$d2o_at_camel21.mindspring.com>... <snipped>
>1) Does sqlplus connect to only one database instance? It seems like that.

Yep.

>Can I specify which instance to connect at command line instead of using
>environment ORACLE_SID? I tried:
>sqlplus scott/tiger @GEST

Almost right, just remove the space between the SID and the userid/password string, e.g. sqlplus scott/toger_at_gest

>2) While I was in GEST using sqlplus, I tried
>drop database WG73;
>The error message told me, it need a "link" keyword. So what is the
>proper way to delete an existing database?

You can not drop a database with a SQL command. There is a DROP DATABASE LINK command however, but this is only applicable for zapping a database link from one Oracle database to another. Let's say you did a create database link for WG73 in the GEST database. This link will allow you to (while connected to GEST) run a SQL statement to select data from WG73, e.g. SELECT * FROM foo_at_WG73

How to delete an existing database? Well, I would first create a list of all the database file and logs from V$DATAFILE and V$LOGFILE. The names of the controlfiles will be in the initSID.ora file for that database. Next, shutdown the database, delete all the files, delete the initSID.ORA and remove the entry for that database from the listener.ora file. If there is a password file for that database you can also delete that too.

>
>3) While I use sqlplus in GEST, I tried
>create database MYDBASE;
>The error always tell me I do not have the privilege even though I
>tried using INTERNAL, SYSTEM and SYS
>So what is the proper way (command) to create a database?

That's not so easy to explain. Basically, you need to create a initSID.ora config file, start the database without mounting it, create the database files, temp space,roll backs etc, config the listener etc. On NT you also need to create an instance first. I suggest that you refer to your documentation. It explains in detail how to create a database. Remember, Oracle is not SQL-Server! :-)

>
>4) Can someone point out what is SID anyway? Is it a database name
>or what? If so, why restrict it to 4 letter long?

SID = system id of the database. You also get a SID in some of the process and session tables. In this case it refers to the session id of an Oracle session. The SID is not restricted to 4 characters only AFAIK - I think it's dependant on the platform and Oracle version you're running.

>5) I think sqlplus might be the equivalent of "dbaccess" in Informix. When
>I used dbaccess, I can see a lot of database names, and then I can choose
>one to connect or create a new one. But How can I see all the databases
>in sqlplus? And once I choose a database, How can I see all the table infos
>of that database in sqlplus?

I think you're confusing the implementation of databases by products such as SQL-Server and Informix with Oracle. With SQL-Server (and Sybase I assume) you have a single database engine (instance) that services numerous databases. So in SQL-Server you can connect to the engine and issue a CREATE DATABASE command to create a new database. Oracle OTOH only has one or more instances (engines) per database. You therefore need to create an instance for that database first and then create the database. It may sound complex and even unecessary, but it makes a lot of sense when you're running multiple databases on a single platform and need the ability to tune and manage each database individually - simply a nightmare when it comes to a product like SQL-Server.

regards,
Billy Received on Thu Mar 26 1998 - 00:00:00 CST