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) ———-…

How to skip blank lines in between SQL statements in SQL*Plus?

If a SQL script file has blank lines in between SQL statements one may see errors when executing the script in SQL*Plus so if one would like to ignore the blanklines in SQL*Plus one can ignore them by set blanklines on. Example: SQL> select object_name 2 SQL> from dba_objects; SP2-0734: unknown command beginning “from dba_o…”…

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…

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…

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…