SQLPlus
From Robs_Wiki
Contents
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>