0

I like to know the query to get all database in oracle server 9i. Actually my requirement is to select all database in oracle and next to get all table , views , procedures , function in each database. Is their any way to get all these information? I am using oracle spl plus developer.

Aswathy
  • 83
  • 3
  • 11
  • You really want to get all database names or all users in a database? – Dba Dec 06 '13 at 10:33
  • Do you mean all schemas, rather than all databases? Have you looked at the `ALL_*` and `DBA_*` data [dictionary views](http://docs.oracle.com/cd/B19306_01/server.102/b14237/toc.htm) to get the information? [Here's a starter for tables](http://stackoverflow.com/q/205736/266304). (Also SQL*Plus and SQL Developer are different thing, though it shouldn't really matter which you're using). – Alex Poole Dec 06 '13 at 10:34
  • i want all database name. Then i need all tables, views, procedures in each database – Aswathy Dec 06 '13 at 10:42
  • 2
    An Oracle instance only has one database. The database may contain multiple schemas (aka users), some of which will be system users. – David Aldridge Dec 06 '13 at 11:21
  • @DavidAldridge You should add "usually" or "in most cases" to your statement because technically it is possible for a database to be mounted by multiple instances simultaneously. – Yaroslav Shabalin Dec 07 '13 at 13:48
  • 1
    Sure, in a RAC system, but what I said was the other way round -- that an instance cannot mount multiple databases. Now that might be changing logically in 12c, of course. – David Aldridge Dec 07 '13 at 15:02

1 Answers1

0

The database names you can get from server file system or check running Oracle Processes.

Then check select * from DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PACKAGE','PROCEDURE')

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Not true. There is also v$database view. – Yaroslav Shabalin Dec 06 '13 at 11:20
  • Actually i need to list all database names first. Based on the database i need table, view, functions and all.. Actually i am writing a c# code to display all database in oracle. Based on selection of database from that list i need to display table, view , functions and all.. – Aswathy Dec 06 '13 at 11:32
  • v$database shows the current database where you are connected on. But it does not show any other DB hosted by the same server - at least not in any case. – Wernfried Domscheit Dec 06 '13 at 13:07