How to list all database names in Oracle

Question

0.00/5 (No votes)

See more:

Oracle

Hi I want to list all database names, but not getting any command to do so, please help...

thanks in advance

Posted 28-Mar-12 21:42pm

djrocks0101

Updated 18-Feb-18 10:55am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pratikv20 · Answer 1 · 2012-03-28T22:05:00

SQL

1) To view database

select * from v$database;

2) To view instance

select * from v$instance;

3) To view all users

select * from all_users;


4) To view table and columns for a particular user

select tc.table_name Table_name
,tc.column_id Column_id
,lower(tc.column_name) Column_name
,lower(tc.data_type) Data_type
,nvl(tc.data_precision,tc.data_length) Length
,lower(tc.data_scale) Data_scale
,tc.nullable nullable
FROM all_tab_columns tc
,all_tables t
WHERE tc.table_name = t.table_name;

Jörgen Andersson · Answer 2 · 2012-04-03T00:42:00

I think you need to read up on the Oracle concepts[^], concentrating on Instance, Database and Schema.

To find out which instances is running on your server: /bin/ps -ef | grep ora_pmon | grep -v grep
If you have a Windows server you'll get the same information at Administrative Tools > Services.

SQL

select owner from dba_tables
union
select owner from dba_views;

Will get you schemas/users who actually have existing tables/views.

SQL

select username from dba_users;

Will give all the existing users/schemas that could potentially own database objects.

Member 13684218 · Answer 3 · 2018-02-18T09:36:00

Solution 3

To check databases with location execute this command from OS prompt $cat /etc/oratab

Posted 18-Feb-18 9:36am

Member 13684218

Comments

CHill60 18-Feb-18 15:54pm

Stick to answering new questions where the OP still needs help. Nearly 6 years after the question was posted you've added nothing to the thread

How to list all database names in Oracle

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0