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

ISS (internet security systems) IBM

Proventia Management SitProtector Console

client version: 2.8.0.247
server version: 2.8.0.231
http://www.iss.net/support/documentation/docs.php?product=16&family=8

comment multiple lines in shell script

how to comment multiple lines in shell script
1.
Wrap the lines within :<< MARKER.. MARKER like shown below

Instead of MARKER you can use any string which is not used in your script. Preferably a unique string.


Code:
:<< COMMENT
a line in the script.
another line in the script
.
.
yet another line in script.
COMMENT
The above is the easiest thing to do.

2.
Else within vi you could issue


Code:
:10,20s/^/#/g

unix command TOP

top
c #full command line
n ## #disply ## process
s 20 # scan every 20 second

The default scan rate on top is 10 seconds. What this means is that with enough top sessions, and we ALL put them on , top itself starts to interfere with the machine. If you start top then type in “s 20” (only without the double quotes), you can change the scan rate to every 20 seconds. 30 would be even better.

If you type “c”, you should get a wider description of the commands. That will definitely help when every command displays as “oracle”. If this option does not work on some particular machine, please let me know and I will get it fixed.

And finally, if you type “n ##” where ## is some number, you will get that many top processes displayed instead of the default of 10.

time function in T-SQL

GETDATE()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of Server is running. The time zone offset is not included.
SYSDATETIME()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

SQL Loader

if table user varchar2, load file need use char

command: sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl

LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

Time range selection in T-sql statement

select *
from everts
where [opened Date] between #09-09-2009# and #03-03-2010#

Outer join with oracle SQL

An outer join use a (+) on the side of the operator where we want to have nulls returned if no value matches.
Create table data_usage (
c_time date,
m_time date,
V varchar2(20),
name varchar2(20),
host varchar2(20)
)
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+1, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+2, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+ 0.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+1.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘MIKE’, ‘D3’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘DAVID, ‘D3’);
Create table users (
Name varchar2(20),
Host varchar2(20)
)

Insert into users values ( ‘SCOTT’, ‘D1’);
Insert into users values ( ‘TOM’, ‘D2’);
Insert into users values ( ‘SCOTT’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D3’);
Insert into users values ( ‘DAVID’, ‘D1’);
Insert into users values ( ‘SAM’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D1’);

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN

If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1

ATTN: (+) can not be added to both side of an relation for outer-joined table

Let’s put date in the select

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host
And d.m_time > sysdate + 0.2;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
And m_time > sysdate - 1
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table, the m_time need a (+) too.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
And m_time(+) > sysdate
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1