{"id":7602,"date":"2018-06-20T21:21:53","date_gmt":"2018-06-20T13:21:53","guid":{"rendered":"http:\/\/rmohan.com\/?p=7602"},"modified":"2018-06-20T21:21:53","modified_gmt":"2018-06-20T13:21:53","slug":"how-to-list-all-database-names-in-oracle","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7602","title":{"rendered":"How to list all database names in Oracle"},"content":{"rendered":"<pre class=\"notranslate\" lang=\"sql\"><span class=\"code-digit\">1<\/span>) <span class=\"code-keyword\">To<\/span> <span class=\"code-keyword\">view<\/span> <span class=\"code-keyword\">database<\/span>\r\n\r\n<span class=\"code-keyword\">select<\/span> * <span class=\"code-keyword\">from<\/span> v$database;\r\n\r\n<span class=\"code-digit\">2<\/span>) <span class=\"code-keyword\">To<\/span> <span class=\"code-keyword\">view<\/span> instance\r\n\r\n<span class=\"code-keyword\">select<\/span> * <span class=\"code-keyword\">from<\/span> v$instance;\r\n\r\n<span class=\"code-digit\">3<\/span>) <span class=\"code-keyword\">To<\/span> <span class=\"code-keyword\">view<\/span> <span class=\"code-keyword\">all<\/span> users\r\n\r\n<span class=\"code-keyword\">select<\/span> * <span class=\"code-keyword\">from<\/span> all_users;\r\n\r\n\r\n<span class=\"code-digit\">4<\/span>) <span class=\"code-keyword\">To<\/span> <span class=\"code-keyword\">view<\/span> <span class=\"code-keyword\">table<\/span> <span class=\"code-keyword\">and<\/span> columns <span class=\"code-keyword\">for<\/span> a particular <span class=\"code-keyword\">user<\/span>\r\n\r\n<span class=\"code-keyword\">select<\/span> tc.table_name Table_name\r\n,tc.column_id Column_id\r\n,lower(tc.column_name) Column_name\r\n,lower(tc.data_type) Data_type\r\n,nvl(tc.data_precision,tc.data_length) Length\r\n,lower(tc.data_scale) Data_scale\r\n,tc.nullable nullable\r\n<span class=\"code-keyword\">FROM<\/span> all_tab_columns tc\r\n,all_tables t\r\n<span class=\"code-keyword\">WHERE<\/span> tc.table_name = t.table_name;\r\n\r\n\r\n\r\n<\/pre>\n<pre class=\"notranslate\" lang=\"SQL\"><span class=\"code-keyword\">select<\/span> owner <span class=\"code-keyword\">from<\/span> dba_tables\r\n<span class=\"code-keyword\">union<\/span>\r\n<span class=\"code-keyword\">select<\/span> owner <span class=\"code-keyword\">from<\/span> dba_views;\r\n\r\n\r\n<\/pre>\n<pre class=\"notranslate\" lang=\"SQL\"><span class=\"code-keyword\">select<\/span> username <span class=\"code-keyword\">from<\/span> dba_users;\r\n\r\n\r\n<\/pre>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">QL<\/span><span class=\"pun\">&gt;<\/span> <span class=\"kwd\">SELECT<\/span><span class=\"pln\"> TABLESPACE_NAME <\/span><span class=\"kwd\">FROM<\/span><span class=\"pln\"> USER_TABLESPACES<\/span><span class=\"pun\">;<\/span><\/code><\/pre>\n<p>Resulting in:<\/p>\n<blockquote>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">SYSTEM\r\nSYSAUX\r\nUNDOTBS1\r\nTEMP\r\nUSERS\r\nEXAMPLE\r\nDEV_DB<\/span><\/code><\/pre>\n<\/blockquote>\n<p>It is also possible to query the users in all tablespaces:<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">SQL<\/span><span class=\"pun\">&gt;<\/span> <span class=\"kwd\">select<\/span><span class=\"pln\"> USERNAME<\/span><span class=\"pun\">,<\/span><span class=\"pln\"> DEFAULT_TABLESPACE <\/span><span class=\"kwd\">from<\/span><span class=\"pln\"> DBA_USERS<\/span><span class=\"pun\">;<\/span><\/code><\/pre>\n<p>Or within a specific tablespace (using my DEV_DB tablespace as an example):<\/p>\n<pre class=\"lang-sql prettyprint prettyprinted\"><code><span class=\"pln\">SQL<\/span><span class=\"pun\">&gt;<\/span> <span class=\"kwd\">select<\/span><span class=\"pln\"> USERNAME<\/span><span class=\"pun\">,<\/span><span class=\"pln\"> DEFAULT_TABLESPACE <\/span><span class=\"kwd\">from<\/span><span class=\"pln\"> DBA_USERS <\/span><span class=\"kwd\">where<\/span><span class=\"pln\"> DEFAULT_TABLESPACE <\/span><span class=\"pun\">=<\/span> <span class=\"str\">'DEV_DB'<\/span><span class=\"pun\">;<\/span><span class=\"pln\">\r\n\r\nROLES DEV_DB\r\nDATAWARE DEV_DB\r\nDATAMART DEV_DB\r\nSTAGING DEV_DB<\/span><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7602"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7602"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7602\/revisions"}],"predecessor-version":[{"id":7603,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7602\/revisions\/7603"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}