2 queries from Oracle
-
Hello all. If you own an Oracle DB, could you provide me two queries? First, get database_id and name for every database, something like this (in MSSQL):
SELECT database_id, name FROM sys.databases ORDER BY 2
and the second one, that provide every table from a database, something like that (in MSSQL):
SELECT table_name FROM %s.information_schema.tables ORDER BY 1
where %s is the database name taken from the first query. Thank you.
-
Hello all. If you own an Oracle DB, could you provide me two queries? First, get database_id and name for every database, something like this (in MSSQL):
SELECT database_id, name FROM sys.databases ORDER BY 2
and the second one, that provide every table from a database, something like that (in MSSQL):
SELECT table_name FROM %s.information_schema.tables ORDER BY 1
where %s is the database name taken from the first query. Thank you.
In Oracle, the equivalent for SQL Server's information_schema or sys tables would be the DICTIONARY and dynamic performance views - ([https://docs.oracle.com/cd/E11882\_01/server.112/e40540/datadict.htm#CNCPT002\](https://docs.oracle.com/cd/E11882\_01/server.112/e40540/datadict.htm#CNCPT002)) Your first query doesn't make sense in an Oracle context since in Oracle you only connect to one database at a time. The second could be (to list the tables in the connected database): SELECT * FROM ALL_TABLES; SELECT * FROM DBA_TABLES; depending on your rights. See the link for the difference.
-
In Oracle, the equivalent for SQL Server's information_schema or sys tables would be the DICTIONARY and dynamic performance views - ([https://docs.oracle.com/cd/E11882\_01/server.112/e40540/datadict.htm#CNCPT002\](https://docs.oracle.com/cd/E11882\_01/server.112/e40540/datadict.htm#CNCPT002)) Your first query doesn't make sense in an Oracle context since in Oracle you only connect to one database at a time. The second could be (to list the tables in the connected database): SELECT * FROM ALL_TABLES; SELECT * FROM DBA_TABLES; depending on your rights. See the link for the difference.
-
I confess I use SQL Server more these days than Oracle, but the only possible option I can find that would let you see multiple databases seems to be if you're using something like Enterprise Manager Cloud Control - [Extract the list of all databases with server and OS version of Oracle Enterprise Manager Cloud Control repository - My DBA World]([https://www.mydbaworld.com/extract-databases-server-os-version-of-oem-repository/\])