Script Help on Line Selection

[Steven Chen_1]

I would like to get advise on how to get certain lines in the whole sql result, e.g.

==================================

SQL> select tablespace_name,sum(bytes) free from dba_free_space group by tablesp
ace_name;
TABLESPACE_NAME FREE
------------------------------ ----------
DRSYS 16637952
INDX 20963328
PRODDATA 2791825408
PRODINDEX 592568320
RBS 633856000
SYSTEM 318390272
TEMP 1048567808
TOOLS 5234688
USERS 65265664

9 rows selected.
==========================================

if I cat the spool file, I have all lines. How to only get the lines without head and tail, or only for tablespace_name listings like:

TABLESPACE_NAME FREE
------------------------------ ----------
DRSYS 16637952
INDX 20963328
PRODDATA 2791825408
PRODINDEX 592568320
RBS 633856000
SYSTEM 318390272
TEMP 1048567808
TOOLS 5234688
USERS 65265664

Any help is appreciated.

Steven

[Mel Burslan · Accepted solution]

for simplicity, let's assume you directed this output to a file and exit out of the sql> prompt. You are at the unix command prompt.

assuming first line you want is the

TABLESPACE_NAME FREE

and you do not want including line

9 rows selected

and anything after that

# you placed the sql output is in outfile
(( startline=`grep -n TABLESPACE_NAME outfile | cut -d: -f1` - 1 ))
sed -e "1,${startline}d" outfile >interimfile
endline=`grep -n "rows selected" outfile | cut -d: -f1`
sed -e "${endline},\$d" outfile | sed -e "1,${startline}d" > final_output

you can use command substitutions and combine all these 3 lines into one, gigantic, ugly sed command but I preferred to put it down this way, so what I was telling was obvious.


Hope this helps

[Mel Burslan]

sorry, the interimfile line should not have existed. correct script is as follows:

(( startline=`grep -n TABLESPACE_NAME outfile | cut -d: -f1` - 1 ))
endline=`grep -n "rows selected" outfile | cut -d: -f1`
sed -e "${endline},\$d" outfile | sed -e "1,${startline}d" > final_output

[Stefan Schulz]

Hi,

i would suggest not to filter the output but to configure everything so that only the desired lines are in the output. Thats much easier to maintain.

If you use some Oracle set commands you can configure your output pretty easy.

For example this will give you a report without the command itselve, and without the sql feedback (xx rows selected).


set NEWPAGE 0;
set PAGESIZE 0;
set LINESIZE 560;
set COLSEP '';
set FEEDBACK OFF;
set HEADING OFF;
SET ECHO OFF;
set VERIFY OFF;
set TERMOUT OFF;

spool ;

SELECT ;

spool off;
quit;

Put this in a script and give it a try. You might want to set HEADING to ON. But the rest should be pretty close to what you need.

Hope this helps

Regards Stefan

[Muthukumar_5]

You can do it as,


#!/bin/ksh
SPOOL=test.log
line=$(wc -l $SPOOL)
let end=$line-2
start=$(grep -n ";" ${SPOOL} | cut -d":" -f1)
sed -e "$start,$end !d" $SPOOL

hth.

[Hein van den Heuvel]

I'm with Stefan: Best prevent the undesired data lines.
But if you have several files with then already then you could use the following 'cleanup' perl one-liner

perl -e 'while (<>) { $x++ if /^---/; $x-- if /rows select/;print $line if
$x; $line=$_} < old.txt > new.txt

This remembers a prior line in $line.
It looks at the current like whether to start or stop printing that prior line.

fwiw,

Hein.

[Sandman!]

I would agree with Schulz's solution with slight modifications. Create a shell script as follows and run it at the command line:

=============================================
# sqlplus -s /@ <set head on feed off echo off
spool filename.out
select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name
/
spool off
exit
EOF
=============================================

cheers!

[TwoProc]

Stefan's is about the best, except I'd make one modification: (Stefan, I'm "bumming" your steps to mod - hope that's OK). This will keep your select statement from showing up in the spooled output.

Put your select statement in the buffer (and leave it there) BEFORE doing your spool command. Then, put in the spool command, then just do a "/" to run the select. Put a "spool off" immediately after the select runs. This gives a pretty clean output, suitable for sending to lpt once you've played with the newpage, pagesize, etc.

Also call sqlplus in SILENT mode (-S).

set NEWPAGE 0
set PAGESIZE 0
set LINESIZE 560
set COLSEP ''
set FEEDBACK OFF
set HEADING OFF
SET ECHO OFF
set VERIFY OFF
set TERMOUT OFF

SELECT
spool
/
spool off
quit;

[Venkatesh_18]

I think the below script will solve the problem.

lists.lst : This will have the tablespace details.

cat lists.lst | grep -v "rows selected" | grep -v "TABLESPACE_NAME" | grep -v "-"

After executing the above commnad you will get only the table space names.

-- Venkatesh

[Steven Chen_1]

appreciate everyone jumping in to help.

By far, I find 2 solutions that are simply and workable:

1) sql coding....
spool xxx
/

so that it does not show the sqlcode.

2) sed -n 3,10p /tmp/xxx.lst

Again, this is a great forum from where you brain stormed and try all ways for a fix.