Using timing in SQL*Plus

Using timing feature on SQL*Plus one can monitor elapsed time. Example: -- start timer with the name monitor_timing, name is optional SQL> timing start monitor_timing SQL> select sysdate from dual; SYSDATE --------- 11-APR-10 -- display time elapsed for current timer SQL> timing show timing for: monitor_timing Elapsed: 00:00:21.35 SQL> select count(1) FROM dba_objects; COUNT(1) ----------... Continue Reading →

How to include instance name in spool filename?

Using the example below one can include instance name in the spool filename when using SQL*Plus SQL> set termout off SQL> set feedback off SQL> undefine curdate dcol SQL> column dcol new_value curdate noprint SQL> column dbcol new_value db noprint SQL> select sys_context(#&39;userenv#&39;,#&39;db_name#&39;) dbcol, to_char(sysdate,#&39;YYYYMMDD_HH24MISS#&39;) dcol from dual; SQL> set termout on SQL> set feedback... Continue Reading →

Clean up script with oratab

A simple script to delete trace files and arc logs from remote location, based on oratab instances. #!/bin/ksh export PATH=/usr/bin:$PATH export USERNAME=`whoami` export HOSTNAME=`hostname` ######################################## #  Copy TNS Names file to local disk   # ######################################## /bin/ksh /work/dba/monora/scripts/update_local_tns.ksh > /tmp/update_local_tns.log 2>&1 # #      Clean up Oracle Export logs # find /work/dba/logs -name '*${HOSTNAME}_*' -mtime +7 -exec... Continue Reading →

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Immediately after a successful RDBMS installation (perhaps even including a sample DB instance creation), sqlplus will not start: [oracle@test ~]$ sqlplus '/as sysdba' sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied The reason is that SELinux is running in "enforcing" mode. You can check it on file /etc/pam.d/login... Continue Reading →

How to enable command line history scrolling on SQL*Plus on unix?

Using the utility rlwrap one can perform command line scrolling on SQL*Plus similar to that is available on windows version of SQL*Plus. The source code for it can be downloaded from the following site: http://utopia.knoware.nl/~hlub/uck/rlwrap/. To build rlwrap executable needs a library readline is needed which can be downloaded from http://directory.fsf.org/project/readline/. One of the requirements... Continue Reading →

How to pass host, port, SID/Service name to connect to a database?

Using the following command one can pass hostname, port, servicename and SID and connect to a database without having an entry in tnsnames.ora $ sqlplus username/password@hostname:port/SERVICENAME OR $ sqlplus username Enter password: password@//hostname:port/SERVICENAME OR $ sqlplus /nolog SQL> connect username/password@hostname:port/SERVICENAME

Create a free website or blog at WordPress.com.

Up ↑