Connect to Oracle database using command line on Ubuntu 16.04

1. Download Oracle client package (instantclient-basiclite-linux.x64-19.8.0.0.0dbru.zip) and sqlplus commandline client package (instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip) from here
Your may have latest versions, download them.

2. Now run commands

mkdir -p /opt/oracle
cd /opt/oracle

3. Unzip downloaded package instantclient-basiclite-linux.x64-19.8.0.0.0dbru.zip

unzip instantclient-basiclite-linux.x64-19.8.0.0.0dbru.zip

and move folder instantclient_19_8 in /opt/oracle

4. Install libaio package

sudo apt install libaio-dev

5. Run Commands

sudo sh -c "echo /opt/oracle/instantclient_19_8 > /etc/ld.so.conf.d/oracle-instantclient.conf"
  
sudo ldconfig

or

export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_8:$LD_LIBRARY_PATH

6. Unzip downloaded package instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

unzip instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

Copy all .so files of these extracted zip into /opt/oracle/instantclient_19_8

mv *.so /opt/oracle/instantclient_19_8

7. Now connect to oracle db on command line.

./sqlplus username/password@domain:port/service_name

Example :

./sqlplus root/strongpassword@54.24.xxx.xxx:1532/ORCAD

8. After connect to sql prompt, list all databases using query

select * from v$database;

Query to view the schema name by

select * from dba_users;

To display the columns available in the Oracle view v$database, we can execute the below statement.

desc v$database;

Error : pip OSError: mysql_config not found

While installing mysqlclient package from pip, you may get above error.

Package name could be like this in requirements.txt.

mysqlclient @ git+https://github.com/PyMySQL/mysqlclient-python.git@ca630c01fb39f252a4c91a525e440beea4ac4447

Solution :

sudo apt-get install libmysqlclient-dev

For recent versions of debian/ubuntu (as of 2018) it is

sudo apt install default-libmysqlclient-dev

Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s)

While importing mysql database if you get the above error, it means the Definer is not same as the user which you are using to import the database.

If Definer is set as root in you sql file, either import the database using root user or you need to change the Definer in sql file with the username which you are using to import database.

You can find a line like this in your sql file

DEFINER = 'root'@'%'

or

DEFINER = 'root'@'localhost'

or

DEFINER = 'root'@'your-mysql-hostname'

You need to change root with your mysql user.