Solved

show database, show all_tables ?

Posted on 2006-11-28
5
5,321 Views
Last Modified: 2008-01-09
hi guys, please view my screenshot :

http://img143.imageshack.us/img143/2308/sqldatamh8.jpg

this is capture from sql 2000, we can see the databases, tables easily in one view.

But what if I am using oracle 8i and oracle 10g ? how do I view it in sqlplus ? what is the command?

when i do a show database or show databases,  it say unknow show option database/databases.

i want to view all databases in one glance and the tables inside the database in one glance.

if i am in a command prompt, i wont know wat databases are running.

if i am in a command prompt,  i wont know wat tables are inside the database.

I want to learn the command.

THanks.


Thanks.


0
Comment
Question by:binary_1001010
5 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 125 total points
ID: 18034030
to see the database name :

select name from v$database; -- but for this you need privileges to access v$ data dictionary views.

select global_name from global_name;

to see the list of tables:

select * from cat ; -- from catalogue ..this gives a list of tables/views/synonyms etc which you have in your schema
select * from tab; -- this will give only tables

actually you have user_tables , all_tables , dba_tables which you can query accordingly. You have these views similarly for other objects like indexes which is user_indexes,all_indexes...,user_triggers,...user_synonyms..., user_views...

first give

desc emp  -- this is to see the structe of any table

Thanks


0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18034056
sqlplus is not a graphical tool, it's a command line tool. (PERIOD) if you want graphical representation of instances and database objects use the "Oracle Enterprise Manager Console" provided with 8i, or download a copy of Oracle Sql Developer and use it.

(sql developer)
http://www.oracle.com/technology/products/database/sql_developer/index.html

(OEM)
start menu -> programs -> Oracle - OraHome8i -> Enterprise Manager Console

or from command line.

C:> oemapp console

to do so make sure the Oracle home is in your path.

good luck,
daniels
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18034089
Hi binary_1001010,

For Oracle 8i, there is the DBA Studio. In Oracle 10g, I think they change it to Enterprise Manager. You should be able to find this tool when u install Oracle. These will be choice that I use to manage the database objects ONLY when there is no TOAD installed on the machine otherwise, usually I would use TOAD which is much faster and more user friendly to use.

Cheers
Nickson
0
 
LVL 9

Author Comment

by:binary_1001010
ID: 18034243
thanks nav_kum_v, btw  desc emp  does not work.
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18034800
try "sql> desc scott.emp" if you are logged in as a user other than "scott". if logged in as scott then "sql> desc emp" will work if table does exist.

regards,
daniels
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now