set up sqlplus environment

set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,
decode(dot,0,length(global_name),dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '
set termout on

if you have database with the same sid and global_name is not setup, you can include hostname in sqlplus prompt.


define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
--column global_name new_value gname
set termout off
define gname=idle
define hostname=idle
column global_name new_value gname
column host_name new_value hostname
select lower(user) || '@' || substr(global_name, 1,
  decode(dot,0, length(global_name), dot - 1) ) global_name
  from (select global_name, instr(global_name, '.') dot from global_name );
select  substr(host_name, 1,
  decode(dot,0, length(host_name), dot - 1) ) host_name
  from (select host_name, instr(host_name, '.') dot from v$instance );
set sqlprompt '&gname..&hostname> '
set termout on

above lines need be put into login.sql file
and the path to the login.sql file need be defined as SQLPATH environment

invlalid number error

When column type is VARCHAR2()

sometimes you can compare the column with a number in one table and sometimes not in another table.

It depends on the data in the column. If all the data stored in the column has only digit characters, you will be able to compare otherwise you will fail.

speed up sql execution

ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10';

ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT OFF';

ORA-10520: recreate package/procedure/view only if definition has changed"

Since untouched package/procedure/view will not be recreated, it save time for processing lots of sql objects

manipulate txt file

replace tab with spaces

expand

or %s/^i/ /g in vi

input ^i by type ctrl +v +i
input ^m by type ctrl +v +m

Check informaiotn in previous time range

following SQL output failed logins in previous 30 minutes.

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0
AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

TO_DSINTERVAL

Syntax

to_dsinterval::=

Description of the illustration to_dsinterval.gif


Purpose

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.

char is the character string to be converted.

The only valid nlsparam you can specify in this function is NLS_NUMERIC_CHARACTERS. This argument can have the form:

NLS_NUMERIC_CHARACTERS = "dg"

where d and g represent the decimal character and group separator respectively.


Examples

The following example selects from the employees table the employees who had worked for the company for at least 100 days on January 1, 1990:

SELECT employee_id, last_name FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
<= DATE '1990-01-01';

EMPLOYEE_ID LAST_NAME
----------- ---------------
100 King
101 Kochhar
200 Whalen

dbms_output.putline()

in order to see the lines on screen of sqlplus.

use SET SERVEROUTPUT on SIZE 100000

delete subdirecotry and files from a directory but not the directory

sometimes, people need delete only subdirectory and files in a direcotry but not the directory.

for example, we have following directory structure

tti - aa.log
- tt1 - ab.log
tti and tt1 are directories; aa.log and ab.log are files.

if all but tti are required to be deleted.
issue following command:
find tti/* -exec rm -rf {} \;


ATTEN:
find tti -exex tm -rf {} \; will not work because it delet tti too.

Following is the output test in solaris 10

:->find tti -exec ls -ltr {} \;
total 0
-rw-r--r-- 1 oracle dba 0 Sep 23 11:36 aa.log
drwxr-xr-x 2 oracle dba 96 Sep 23 11:37 tt1
-rw-r--r-- 1 oracle dba 0 Sep 23 11:36 tti/aa.log
total 0
-rw-r--r-- 1 oracle dba 0 Sep 23 11:37 ab.log
-rw-r--r-- 1 oracle dba 0 Sep 23 11:37 tti/tt1/ab.log

:->find tti -exec rm -rf {} \;
:->cd tti
bash: cd: tti: No such file or directory
:->

When tti/* is used


:->find tti/* -exec ls -ltr {} \;
-rw-r--r-- 1 oracle dba 0 Sep 23 11:39 tti/aa.log
total 0
-rw-r--r-- 1 oracle dba 0 Sep 23 11:39 ab.log
-rw-r--r-- 1 oracle dba 0 Sep 23 11:39 tti/tt1/ab.log
:->find tti/* -exec rm -rf {} \;
:->cd tti
:->ls
:->pwd
~/tti