simple question, help pls...

[h3nd]

Hi guys,

I'm new in oracle here, I just wanna ask when the first time I type "sql" in the prompt and login.

How can I tell which database am I in ? and what command if I wanna know how many tables in that database and what tables ?

Thanks guys

 

[TheEntertainer]

h3nd,

As you've logged into the database, I would think you'd know what database you were in


Number of tables:
select count(*)
from all_tables;

Name of tables:
select table_name
from all_tables

Look at the data dictionary for a wealth of tables that provided database information.

 

[Welshbird]

Someone prompted me to add this to my 'login.sql' file when I began

set timing on
set SQLPROMPT "&_USER>"

And its very useful. It shows me who I have loged in as (as for me there may be a choice) and also shows me how long a query took when it is finished.

I think you can change the SQLPROMPT to add the database name into it as well if you choose.

This article might be useful to you too.

http://www.praetoriate.com/oracle_tips_enhancing_sqlplus.htm

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LKBrwnDBA]

Try:

SELECT * FROM V$DATABASE;
SELECT COUNT(*) FROM {ALL|DBA|USER}_TABLES;
SELECT TABLE_NAME FROM {ALL|DBA|USER}_TABLES;
-- Or --
To see which tables are available:
SELECT * FROM DICT;


Also try this as login.sql (or $OH/sqlplus/admin/glogin.sql) script:

set term off
col name noprint new_value dbname
select name, lower(name) dbname from v$database;
set sqlprompt '&&dbname>'
set term on

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

I'm new in oracle...How can I tell which database am I in ?

These are two phrases that, when used together, tend to raise a flag of concern for me.

There are two very different definitions for "database" in the Database Universe:

In the Oracle World, "database" refers to all of the objects (e.g., tables, indexes, et cetera) owned by all of the Oracle user/schemas (e.g., "SYS", "SYSTEM", "DBSNMP", "OUTLN", "<your login>", "<my login>", et cetera) that make up a single Oracle installation.

In the Non-Oracle World, "database" is roughly equivalent to an Oracle user/schema, for example, for a single Oracle instance/installation, there may be 30 Oracle user/logins/schemas. That would mean if the same "logical" structure existed in a Sybase, SQL Server, DB2, MySQL, EnterpriseDB, et cetera installation, then the installation would contain 30 "databases".

So, when you say, "...what command if I wanna know how many tables in that database and what tables ?" which meaning of "database" should I infer?...1) The tables for an entire Oracle database instance, or 2) the tables for an individual schema/user in the database instance?

The replies above have tended to interpret your meaning of "database" using the Oracle definition, but I suspect/infer that you are using the non-Oracle definition of "database", correct?

Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Welshbird]

(Dave - you always manage to explain things so well - I want to be Dave when I 'grow up'!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[h3nd]

Hi SantaMufasa,

Thanks for your explanation,

They answered my question correctly, but I really dont know which database am I in default when once I log in.

I tried this command,
"SELECT * FROM V$DATABASE;"

It just showed the list of database, not what database am I in.

The rest is fine, Thanks guys

 

[KenCunningham]

Try:

select name from v$database

All I ask of you
Is make my wildest dreams come true

 

[SantaMufasa]

H3nd,

We still don't know whether you want the name of the entire database (which Ken's suggestion provides) or whether you want the name of the user/schema ("non-Oracle database") name. If you are in SQL*Plus, then you can issue the SQL*Plus command, "show user" for that name.

Let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[h3nd]

Hi guys,

Still simple question, what command if I wanna know what tables in particular database.

Thx guys,

 

[BJCooperIT]

select * from all_tables
order by owner, table_name;

The answer to this question has been posted a number of times. There is Search button near the top of the page that could help you get your answer without having to wait for a reply.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus