{"id":565,"date":"2012-01-11T18:38:22","date_gmt":"2012-01-11T18:38:22","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=565"},"modified":"2012-12-25T22:38:40","modified_gmt":"2012-12-25T22:38:40","slug":"sqlplus-exploring-schemas-and-data","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/sqlplus-exploring-schemas-and-data\/","title":{"rendered":"sqlplus, exploring schemas and data"},"content":{"rendered":"<p>I would like to get information about different schemas and tables in sqlplus. I would like this to be as easy as mysql, it&#8217;s less intuitive but almost as easy.<\/p>\n<p>To get a list of schemas (similar to mysql &#8220;show databases&#8221;), you can run the following,<\/p>\n<pre class=\"sh_oracle\">\r\nfoo_owner@FOO> SELECT username FROM all_users ORDER BY username;\r\n\r\nUSERNAME\r\n------------------------------\r\nBOB_APP\r\nBOB\r\nFOO_OWNER\r\nSYS\r\nSYSTEM\r\n\r\n5 rows selected.\r\n\r\nfoo_owner@FOO> \r\n<\/pre>\n<p>To change to a different schema (similar to mysql &#8220;use <em>database<\/em>&#8220;), you can run the following,<\/p>\n<pre class=\"sh_oracle\">\r\nfoo_owner@FOO> ALTER SESSION SET CURRENT_SCHEMA = foo_owner;\r\n\r\nSession altered.\r\n\r\nfoo_owner@FOO> \r\n<\/pre>\n<p>To view the tables (similar to mysql &#8220;show tables&#8221;), you can run the following,<\/p>\n<pre class=\"sh_oracle\">\r\nfoo_owner@FOO> SELECT table_name FROM tabs;\r\n\r\nTABLE_NAME\r\n------------------------------\r\nFOO_USERS\r\nFOO_GROUPS\r\nFOO_USER_GROUP\r\n...\r\n<\/pre>\n<p>To view the columns of the table, it&#8217;s the same as mysql, i.e.,<\/p>\n<pre class=\"sh_oracle\">\r\nrpt_owner@FOO> DESC foo_user_group\r\n\r\n Name\t\t\t\t\t\t\t\t   Null?    Type\r\n ----------------------------------------------------------------- -------- --------------------------------------------\r\n USER_ID\t\t\t\t\t\t\t   NOT NULL NUMBER(38)\r\n GROUP_ID\t\t\t\t\t\t\t   NOT NULL NUMBER(38)\r\n DATE_ID\t\t\t\t\t\t\t   NOT NULL NUMBER(38)\r\n\r\n<\/pre>\n<p>And all the normal SQL you feel like running, e.g.,<\/p>\n<pre class=\"sh_oracle\">\r\nfoo_owner@FOO> SELECT COUNT(*) FROM foo_users;\r\n\r\n  COUNT(*)\r\n----------\r\n    746202\r\n\r\nfoo_owner@FOO> \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I would like to get information about different schemas and tables in sqlplus. I would like this to be as easy as mysql, it&#8217;s less intuitive but almost as easy. To get a list of schemas (similar to mysql &#8220;show databases&#8221;), you can run the following, foo_owner@FOO> SELECT username FROM all_users ORDER BY username; USERNAME [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/565"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=565"}],"version-history":[{"count":6,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/565\/revisions"}],"predecessor-version":[{"id":701,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/565\/revisions\/701"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}