I would like to get information about different schemas and tables in sqlplus. I would like this to be as easy as mysql, it’s less intuitive but almost as easy.

To get a list of schemas (similar to mysql “show databases”), you can run the following,

foo_owner@FOO> SELECT username FROM all_users ORDER BY username;

USERNAME
------------------------------
BOB_APP
BOB
FOO_OWNER
SYS
SYSTEM

5 rows selected.

foo_owner@FOO> 

To change to a different schema (similar to mysql “use database“), you can run the following,

foo_owner@FOO> ALTER SESSION SET CURRENT_SCHEMA = foo_owner;

Session altered.

foo_owner@FOO> 

To view the tables (similar to mysql “show tables”), you can run the following,

foo_owner@FOO> SELECT table_name FROM tabs;

TABLE_NAME
------------------------------
FOO_USERS
FOO_GROUPS
FOO_USER_GROUP
...

To view the columns of the table, it’s the same as mysql, i.e.,

rpt_owner@FOO> DESC foo_user_group

 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 USER_ID							   NOT NULL NUMBER(38)
 GROUP_ID							   NOT NULL NUMBER(38)
 DATE_ID							   NOT NULL NUMBER(38)

And all the normal SQL you feel like running, e.g.,

foo_owner@FOO> SELECT COUNT(*) FROM foo_users;

  COUNT(*)
----------
    746202

foo_owner@FOO>