Re: Oracle newbie--opening database and listing tables?

From: Jerry Gitomer <jgitomer_at_ictgroup.com>
Date: Wed, 05 Aug 1998 08:51:00 -0400
Message-ID: <35C85534.5DE9@ictgroup.com>

Hi Martin,

martin wrote:
>
> Im new to Oracle but experienced in Sybase. What is the command in
> Oracle to (these
> are all from the SQL server command prompt):
> 1. List databases from the SQL prompt?

Oracle doesn't have the multiple database concept of Sybase and Informix. When using Oracle you are connected to an Instance. An Instance can have one or more Schemas (each of which equates to a user). There isn't a one-to-one mapping of database to either Instance or Schema. You can get a list of all of the users with:

        SQL> SELECT username FROM dba_users;

This will include all of the schema names.

> 2. Use a particular database?

You can have multiple instances on a machine. You can change which Instance you will be connected with by changing the values of ORACLE_HOME and ORACLE_SID before you issue sqlplus from the command line.

> 3. List tables and their definitions?

To get the table names for a user:

	SQL> CONNECT username/password
	SQL> SELECT table_name FROM user_tables;

To get the table names for the entire instance:

	SQL> CONNECT system/<password>
	SQL> SELECT table_name FROM all_tables;

To get the description of a table: (the hard way)

        SQL> DESC table_name

To get the description of a table: (the easy way)

        download T.O.A.D. from www.toadsoft.com

        Tool for Oracle Application Developers (T.O.A.D) is excellent quality freeware that runs under windows on your PC. As far as I am concerned it is the one killer application that makes me willing to use windows on my PC. Using it will simplify your life and allow you to avoid unnecessary frustration.         

> 4. Export data from a table (join) to an ascii output file?

	SQL>  SET HEAD OFF
	SQL>  SET PAGES 0
	SQL>  SPOOL filename.ext
	SQL>  <your select statement here>
	SQL>  SPOOL OFF
	SQL>  !ls -l filename.ext     --  this is because I never trust a

computer

>
> Im good to go w/SQL but cant seem to do the above like I could in
> Sybase, Just need the commands as I have no documentation at all.
>
> TIA,
> Martin

Since you don't have any documentation you should also look into the knowledgebase products from Revealnet (www.revealnet.com). I have found them to be more useful than the official documentation due to, IMHO, better organization and the fact that the knowledgebase is online.

regards

Jerry

--

Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_yahoo.com 	Opinions are mine not those of ICT Group

Received on Wed Aug 05 1998 - 07:51:00 CDT