Skip to Main Content
  • Questions

  • SQL query to list all SID's in Oracle database?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mayank.

Asked: March 29, 2009 - 12:14 am UTC

Last updated: March 30, 2009 - 6:14 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hello,

1) I am trying to list all the SID's (Databases) in the Oracle. Is there any SQL query in Oracle to do the same like we have "select name from master.dbo.sysdatabases" in MSSQL ? This list all databases in the particular MS SQL Server instance.
I tried with "select name from v$database" but it only lists the SID name you are currently logged in?

Please help me with this. Thanks a lot in advance.


Regards,
Mayank

and Tom said...

The concept of a database in sqlserver is entirely different than a database in Oracle.

sqlserver has three components to their schema names - database.username.object_name

Oracle has username.object_name

That third namespace - database - is a "logical" concept in sqlserver, it divides up their single instance/database into multiple namespaces - each of their databases are not really standalone databases - they are just a namespace.

And in Oracle, there is no concept, hence there is no need of "sysdatabases", we simply do not have that name space.

We use only schemas - so, select * from all_users would be the closest logical equivalent.


When you connect to sqlserver, you connect to an instance (a set of threads/memory). This sqlserver instance is associated with a set of files that contain your tables and indexes. They name their stuff using x.y.z.

When you connect to oracle, you connect to an instance (a set of threads or processes/memory). This oracle instance is associated with a set of files that contain your tables and indexes. We name our stuff using y.z.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sql server databases

A reader, March 30, 2009 - 5:15 pm UTC

"...That third namespace - database - is a "logical" concept in sqlserver, it divides up their single instance/database into multiple namespaces - each of their databases are not really standalone databases - they are just a namespace..."

Unfortunately that's not correct.
Each sql server database has it's own set of transaction logs (redo), it's own users, can be backed up/restored separately and so on. It's a lot more of a physical object than a logical one.

Some times I wish oracle had the ability to restore only a subset of the whole database into an existing database like sql server does...
Tom Kyte
March 30, 2009 - 6:14 pm UTC

We do have the ability to restore only a subset of the whole database???? We have for years and years and years.

It is called TSPITR - tablespace point in time recovery.
http://www.oracle.com/pls/db102/search?remark=quick_search&word=TSPITR&tab_id=&format=ranked


And tell me, if you

update db1.x.y set y = 5;
update db2.a.b set z = 10;
commit;

in sqlserver, are you doing a distributed two phase commit? No? Then you have a single database.

Our tablespaces are very much like their "database", and since their "redo logs" (they store undo and redo together - they do not really have redo logs - they have transaction heaps) are transaction logs and are different in nature, format, use - everything- from Oracles - you cannot really compare them.


In sqlserver, the database has users, users can be granted access to a namespace - the users do not need to be created for EACH 'database' you have, rather the users have a default database and the ability to set database to anything they allowed to access. The users are not specific to a 'database', they may have privileges on 'databases' - a logical entity, a tool to segregate items logically.


We can backup and restore tablespaces separately. Always have.


The closest thing we have to a sqlserver database is what we call a tablespace.

Users can have quotas on tablespaces (the ability to use them or not).
You can back them up.
You can restore them (as of different points in time)
You can transport them from database to database (Oracle database to Oracle database).
Etc.

You just do not reference them in the identifier, you would never say "from tablespace.owner.table", just "owner.table"


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.