SQL PLUS system parameters usage (set/show)

To show parameter in detail
SQL> SHOW DEFINE;

To escape from substitution variable (&):
SQL> SET DEFINE OFF;

To enable DBMS_OUTPUT:
SQL> SET SERVEROUTPUT ON;

To see old and new values of variables:
SQL> SET VERIFY ON;

To calculate total execution time of SQL:
SQL> SET TIMING ON;

To print time on the SQL terminal:
SQL> SET TIME ON;

To see table columns name and types for a table:
SQL> DESC hr.employees@XE;

To see currently logged-in user:
SQL> SHOW user;

How to know $ORACLE_HOME path via sqlplus in Windows ?

CMD> sqlplus  / as sysdba

SQL> 
set serveroutput on;

SQL>
DECLARE
 Result_Val VARCHAR2(100);
BEGIN
  dbms_system.get_env('ORACLE_HOME', Result_Val);
  dbms_output.put_line(Result_Val);
END;
/

How to configure display format of sqlplus ?

e.g.
set linesize 200
set pagesize 100
column object_name format a30
column object_type format a106 -- optional
column owner format a30 -- optional

select object_name, object_type, owner from dba_objects where status = 'INVALID' order by owner, object_type, object_name;

Note that "30" is limit of object_name and owner size. In addition,
"106" is limit of object_type size.

linesize; line limit of each row
pagesize; total row limit of display

How to define new db link?

Add a new connection node into tnsnames.ora file.
vi $ORACLE_HOME/network/admin/tnsnames.ora
e.g.

ALPER_DB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))

Check remote host via tnsping

$ tnsping ALPER_DB

Define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

If you desire all users use this link you should add "public" into create statement

For All users (public):

SQL> CREATE PUBLIC DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

Alternative option: define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))';

Observe the new link and check it

For specific users:

SQL> select * from user_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

For All users (public):

SQL> select * from all_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

Extra
Connect without db link:
$ sqlplus "hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))"
or
CMD> sqlplus hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))

How to use sqlcl?

Recently, Oracle announced that we have a new SQL Plus command line anymore! Its name is sqlcl that seems to very useful and powerful tool. So, let's take a look at it at below.

First, we need to download it from Oracle site. After that we may extract the file under C:\ directory then add setup path to "Path" environment variable to reach it from CMD. (e.g. C:\sqlcl-4.2.0.16.131.1023-no-jre\sqlcl)

Now we are able to connect our database by sqlcl.

Here are the commands:
sql hr@//localhost:1521/xe
show all
host cls
select last_name, job_id, salary from employees order by salary desc fetch first 10 rows only; 
set sqlformat ansiconsole
set sqlformat csv
history
history 100

Happy coding! :)

How to login without any connection in sqlplus?

CMD> sqlplus /nolog
SQL> conn hr/hr

- nolog parameter is using for just entering to sqlplus

How to know the current connected user in sqlplus?

SQL> SHOW USER;

USER is "ALPER"

How to run .sql file in sqlplus?

Press Windows button + R and press cmd then click "OK".

Write down "below command" and press enter

Full path approach
echo exit | sqlplus hr/hr @C:\sql\example.sql

Oracle path approach
echo exit | sqlplus hr/hr @?/example.sql
? -> $ORACLE_HOME

Example sql: select * from employees;
Used Oracle Version and OS: Oracle XE and Win7

How to clear screen in sqlplus?

SQL> clear screen;

How to login sqlplus on linux command window in Linux?

$ sqlplus scott/tiger@orcl

@orc1 -> tnsnames.ora – connection node name
scott -> db username
tiger -> db username password 

$ sqlplus / as sysdba@orcl

Login as sysdba with this.