SQLPlus

Connect with SQLPlus to the database via Easy Connect

EZCONNECT is Oracle's easy connect naming method. EZCONNECT eliminates the need for service name lookups in the tnsnames.ora files when connecting to an Oracle database across a TCP/IP network. In fact, no naming or directory system is required when using this method as it provides out-of-the-box connectivity. It extends the functionality of the host naming method by enabling clients to connect to a database with an optional port and service name in addition to the host name of the database.

EZCONNECT format:

CONNECT username/password@[//]host[:port][/service_name]

Example:

CONNECT robdba/robdba@//prod-server:1521/prod.example.com

In the SQL Plus GUI you would use:

USER NAME   : ROBDBA
PASSWORD    : ROBDBA
HOST STRING : prod-server:1521/prod.example.com

Add the database name and time to a spool file

Place the code snippet below at the top of the script

-- spooling settings added by Rob
column timendate new_value spooltime noprint
select 'anonymize_'||SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||to_char(sysdate,'yyyymmdd') timeNdate from dual;
spool &spooltime
-- end spooling settings 

The spoolfile will have a name like : ANONYMIZE_PRODDB_20191107

Use Bind Variables in SQL Plus

SQL> exec :kleur := 'GREEN';

PL/SQL procedure successfully completed.

SQL> print kleur;
GREEN

SQL> select /*+ GATHER_PLAN_STATISTICS */ count(id) from robla.gg_tbl_custom_heartbeat_x1 where colour = :kleur;
      4863

Change the SQLPlus prompt

SQL> set sqlprompt "SQL_01> "
SQL_01>