Example of using Sed

SED is a stream editor. A stream editor is used to perform basic text transformations on an input stream (a file or input from a pipeline). While in some ways similar to an editor which permits scripted edits (such as ED), SED works by making only one pass over the input(s), and is consequently more efficient. But it is SED's ability to filter text in a pipeline which particularly distinguishes it from other types of editors.

One of sed's most useful commands is the substitution command. Using it, we can replace a particular string or matched regular expression with another string. For example say we have a file (called test.txt) with the following content:

aaa bbb ccc

eee fff ggg

bbb ccc aaa

eee rrr kkk

We want to change all the instances of “aaa” to “zzz”. The following sed command could be used to perform the search and replace:

sed -e 's/aaa/zzz/g' test.txt > out.txt

The contents of the output file (out.txt) now has the following:

zzz bbb ccc

eee fff ggg

bbb ccc zzz

eee rrr kkk

Unix shell script to automate FTP process

I found the following script below from FTP Planet to automate FTP process. It seems to work well but do not recommend leaving your username and password in any script for a extended period of time:

ftp -v -n hostname << EOF
user usuario password
bin
put filename
bye
EOF

SQL query to obtain tablespace usage

Below are some queries to obtain the amount of space available as well as total size:

-- All size
select a.tablespace_name,
ROUND(a.bytes,2) "SIZE(MB)",
ROUND(a.maxbytes,2) "MAX SIZE(MB)",
ROUND(nvl(b.bytes,0),2) "FREE SPACE(MB)",
ROUND((a.bytes - nvl(b.bytes,0)),2) "USED(MB)",
ROUND((a.bytes - nvl(b.bytes,0))/a.bytes*100,2) "PCT USED"
-- ,ROUND((a.bytes - nvl(b.bytes,0))/a.maxbytes*100,2) "PCT MAX USED"
from (
select TABLESPACE_NAME,
sum(BYTES/(1024*1024)) bytes,
sum(decode(MAXBYTES,0,bytes,maxbytes)/(1024*1024)) maxbytes
from dba_data_files
group by TABLESPACE_NAME) a,
(
select TABLESPACE_NAME,
sum(BYTES/(1024*1024)) bytes
from dba_free_space
group by TABLESPACE_NAME) b
where a.tablespace_name=b.tablespace_name(+)
order by 1;

-- tablespace size
select tablespace_name, sum(bytes)/1024/1024 mbytes from user_segments
group by tablespace_name
order by 2 desc;

Determining SQL statements currently being executed

In addition to determine session information, the Oracle data dictionary tables can also be used to determine the current SQL statements being executed. James Koopmann has authored several articles on Database Journal regarding monitoring SQL statements. I strongly recommend reading his article. Below are some examples of extracting the SQL statements currently executed on an instance:

SELECT hash_value,
sql_text
FROM v$sqlarea
WHERE users_executing > 0;

SELECT sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
FROM v$sqlarea sqlarea,
v$session sesion
WHERE sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

SELECT u.sid,
substr(u.username,1,12) user_name,
s.sql_text
FROM v$sql s,
v$session u
WHERE s.hash_value = u.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
ORDER BY u.sid;

Using Oracle’s explain plan

Use the EXPLAIN PLAN statement to determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

If you are using cost-based optimization, then this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted.

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.

The DBMS_XPLAN package provides an easy way to format the output of the EXPLAIN PLAN command. Below is an sample SQL which will generate an explain plan and store in the plan table:

EXPLAIN PLAN FOR

SELECT *

FROM emp e, dept d

WHERE e.deptno = d.deptno

  AND e.ename='su';

Below is example of using the DBMS_XPLAN procedure and the output produced:

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |     1 |    50 |     3 |

|*  1 | HASH JOIN            |             |     1 |    50 |     3 |

|*  2 | TABLE ACCESS FULL    | EMP         |     1 |    32 |     1 |

|   3 | TABLE ACCESS FULL    | DEPT        |     4 |    72 |     1 |

--------------------------------------------------------------------

Predicate Information (identified by operation id)

1 - access("E1"."DEPTNO"="D1"."DEPTNO")

2 - filter("E1"."ENAME"='su')

Oracle SQL query to determine session information

The v$session is one of the most useful Oracle data dictionary tables. This table provides information such as

• The user associated with each session
• The status of a session (active or inactive)
• The program associated with the session
• The machine where the session originated from

This information can be valuable when determining which users or applications are utilizing Oracle. Below is an example of a query that will obtain the number of session associated with each user:

select substr(username,1,10) as username, substr(program,1,30) as program,
status, substr(machine,1,20) as machine, count(*)
from v$session
group by username,status,program, machine
order by status,program,username;

This query will return a result set like the following:

USERNAME	PROGRAM	STATUS	MACHINE	COUNT(*)
-------------	-----------------	--------	----------	--------
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
USER1	sqlplusw.exe	ACTIVE	doug-pc	1
USER1		ACTIVE	appsvr	7
USER1	sqlplus.exe	INACTIVE	doug-pc	3
USER2	sqlplus.exe	INACTIVE	doug-pc	1
USER3	sqlplus.exe	INACTIVE	tony-pc	2
USER4	sqlplus.exe	INACTIVE	jeff-pc	3
USER5	sqlplus.exe	INACTIVE	ann-pc	2
USER6	sqlplus.exe	INACTIVE	ann-pc	6
USER1		INACTIVE	appsvr	2

The sessions with the name "oracle" in the program name are created by Oracle and not from users. In this example there are 6 users with sessions. USER1 has a total of 13 sessions (4 from doug-pc and 9 from appsvr). Of the 13 sessions, 8 sessions are active. Note the empty value for the program name for some of the USER1 sessions. These appear to be related to sessions using JBDC.

Oracle's online documentation is an excellent source for learning more about v$session and other data dictionary tables.

Using the split command to break up large files

The split command allows you to break up a large file into several smaller files. A file can split up based on size or lines. The split command is available on UNIX, Linux, and Windows (through Cygwin). Below is an example of a large file a user may want to split:

/cygdrive/c/temp/split> ls -l
total 1571886
-rw-r--r-- 1 userid group 1609611264 Mar 23 10:39 test.xml

The following command will split up the file into 300 MB chunks:

/cygdrive/c/temp/split> split -b300m test.xml
/cygdrive/c/temp/split> ls -lrt
total 3143772
-rw-r--r-- 1 userid group 1609611264 Mar 23 10:39 test.xml
-rw-r--r-- 1 userid group 314572800 Mar 23 10:43 xaa
-rw-r--r-- 1 userid group 314572800 Mar 23 10:43 xab
-rw-r--r-- 1 userid group 314572800 Mar 23 10:44 xac
-rw-r--r-- 1 userid group 314572800 Mar 23 10:44 xad
-rw-r--r-- 1 userid group 314572800 Mar 23 10:45 xae
-rw-r--r-- 1 userid group 36747264 Mar 23 10:45 xaf

In this example the large file was separated into six smaller files(xaa, xab, xac, xad, xae, xaf). The length of the files names generated by split is configurable with the –a option. Note that the split command will keep the original file intact. For splitting up larger files, the verbose option is useful for monitoring progress.

Generating Java Stack Trace

A Java stack trace displays the current state of each thread and monitor in a Java Virtual Machine. The stack trace displays the current method being executed by a thread. In addition the trace shows the sequence of calls leading up to the execution of the current method.

Analyzing stack traces can be very helpful in determine the causes of run time errors. For example if there is a deadlock detected, a stack trace can be generated to determine where the hang is occurring.

A Java stack trace can be generated by sending a signal to the JVM. On a UNIX environment this can be accomplished by sending a kill -3 to the Java process. It may be necessary to generate stack trace for several Java processes. The following can be used to send the kill signal to several Java processes:

for pid in `ps -u userid | grep java | awk '{print $1}'`;
do kill -3 $pid;
done

On Windows pressing the ctrl-break keys while having focus on the window where the Java process is running will generate the stack trace.

Sun's Java website provides an excellent article on analyzing stack traces.

Removing carriage returns (^M) from a file

When transferring a file from UNIX to Windows in binary mode, you may observe carriage returns (control M characters) in the file when viewing it on UNIX. The control M character(^M) is the end of line character on Windows . When you transfer afile via FTP in binary mode, the file is transferred with the carriage returns intact. The control M character is not the end of line character in UNIX, so as a result the ^M is interpreted as part of the file. In order to avoid this problem the file should be transferred in ASCII mode. If this is not possible there are scripts which can remove the Control M character. Below is an example of using the tr command to remove the control M characters from a file:

tr -d "\015" < $input > $output

The tr command reads in characters from standard input and replaces or deletes characters before writing them back to standard output. The -d option in the example above will delete the carriage returns (specified in octal as o15) from the specified input file ($input) and redirect the results to an output file ($output). Here is in a for loop:

for f in `ls `

do

tr -d "\015" <$f >tmp/$f

done

I Did Not Know That

Welcome to I Did Know That. This is the blog where I will tell you everything I dont know. This may take a while. Enjoy !