Author Archives: bigspoon

Sybase sp_freedevice procedure

Posted on by
1

Only the short info about Sybase database devices.
Parameters:
min_space – minimum of required space (i.e. 40 will display the devices with >=40M free space)
the_name – the template for the device name (i.e. %mast% will display only master device)

Warning! It was not tested for all possible configurations, so the result could be incorrect on some OS.

create procedure sp_freedevice
@min_space int = null,
@the_name varchar(40) = null
as
begin

declare @numpgsmb integer /* Number of 'virtual' Pages per Megabytes */
select @numpgsmb = (1048576. / @@pagesize)

select "phyname"=convert(varchar(40), d.phyname),
"name"=convert(varchar(28),d.name),
"d_size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"d_used"=convert(varchar(6), sum(u.size / @numpgsmb)) ,
"d_free"=((1 + d.high - d.low) / @numpgsmb) - sum(u.size / @numpgsmb),
vdevno=d.low/power(2,24) & 255
into #free_device_all_tbl
from master..sysusages u, master..sysdevices d
where u.vstart between d.low and d.high
and d.status & 2 = 2
group by d.name

select
vdevno=low/power(2,24) & 255,
"Physical Name"=convert(varchar(40), d.phyname),
"Device Name"=convert(varchar(28),d.name),
"Size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"Used"=convert(varchar(6),0),
"Free"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb)
from master..sysdevices d
where d.name not in (select tmp.name from #free_device_all_tbl tmp)
and d.status & 2 = 2
and ( @min_space is null or ((1 + d.high - d.low) / @numpgsmb) >= @min_space )
and ( @the_name is null or d.phyname like @the_name or d.name like @the_name)
union
select vdevno , phyname , name , d_size , d_used , convert(varchar(6), d_free)
from #free_device_all_tbl
where ( @min_space is null or d_free >= @min_space )
and ( @the_name is null or phyname like @the_name or name like @the_name)
order by 1

end
go

Rev function and comma-separated output

Posted on by
Reply

Here is the shell command snippet to display comma-separated output:

ls -lrt | rev | sed 's/\\([0-9][0-9][0-9]\\)/\\1,/g' | rev | sed 's/\\([\^0-9]\\),\\([0-9]\\)/\\1\\2/g;s/\^,\\([0-9]\\)/\\1/g'
Example:
-rw-r—– 1 sybase dba 1,572,872,192 Feb 2 07:09 master.dbf

Rev in awk

#!/bin/ksh
nawk '{ l=length($0) ; for(i=l;i>0;i--) { printf "%s", substr($0,i,1) } ; print "" }'

Rev function (absent on SunOS) :

(Warning! Avoid spaces before #define – SunOS cc compiler doesn’t like them)


#include <stdio.h>
#ident "@(#)rev 1.0 02/01/2006"
#define MAX_STRING_SIZE 2000char version[] = "rev v1.0 02/01/2006";

main(argc,argv)
int argc;
char *argv[];
{
char my_src[MAX_STRING_SIZE] ;
char my_dst[MAX_STRING_SIZE] ;
char *retCode ;

char *tmp_s ;
int i;

char *filename;
FILE *fSource ;

/* check command line arguments */
if (argc > 1) {
if( argv[1][0]=='-' || argv[1][0]=='/' ) {
fprintf(stderr, "Usage: %s [file] \n", argv[0]);
exit(1);
}
filename=argv[1] ;
fSource = fopen( filename, "r" ) ;
if ( fSource == NULL ) {
fprintf(stderr, "Can't open %s for reading\n", filename );
exit(1);
}
} else {
/* Using the standard input */
fSource=stdin ;
} /* if (argc > 1) */

while( (retCode = fgets( my_src, MAX_STRING_SIZE, fSource ) )!=NULL ) {

tmp_s=my_dst+MAX_STRING_SIZE-1;
*tmp_s=0;

for( i=0 ; i < MAX_STRING_SIZE && my_src[ i ] != 0 ; i++ ) {
if( my_src[ i ] == '\n' ) {
continue ;
}
tmp_s--;
*tmp_s=my_src[ i ];
}

fprintf( stdout, "%s\n", tmp_s);
}

fclose( fSource ) ;

fflush( stdout );

exit(0);
} /* main */

Sybase simplified sp_who procedure

Posted on by
Reply

The procedure displays only the necessary fields from sysprocesses:


create procedure sp_who_all as
select PR.spid ,
PR.fid ,
substring(suser_name(PR.suid),1,10) Login_name,
substring(PR.program_name,1,10) Program ,
PR.status ,
cmd ,
substring(db_name(dbid),1,19) db_name,
substring(tran_name,1,22) tran_name,
blocked,
physical_io
from master..sysprocesses PR
go

Print current Oracle character set

Posted on by
Reply

Short PL/SQL block to print the characters in range [033..256]

set serveroutput on

declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000')) || ':' || chr(k) || ' ' );
if k mod 8 = 0 then
dbms_output.put_line('');
end if;
end loop;
end loop;
end;

Process all databases in Sybase

Posted on by
Reply


#!/usr/bin/ksh
echo "Started..."

print -u2 -n "Enter password:"
stty -echo
read PASS
stty echo
print

rm -f databases.lst
isql -U sa -S $DSQUERY -w 999< databases.lst
$PASS
select 'DBLABEL' "XXX", name from master..sysdatabases
go
exit
EOF

for DB in $(cat databases.lst) ; do

print -u2 "Processing $DB ..."
isql -U sa -S $DSQUERY -w 999 -D $DB <

Find the Oracle object by name

Posted on by
Reply

If You don’t remember exactly the name of the view or table, so this query is for You:


col SHORT_OBJECT_NAME for a30
select
substr( ob.OBJECT_NAME,1,30)"SHORT_OBJECT_NAME",
substr( ob.OWNER,1,15)"SHORT_OWNER" ,
ob.OBJECT_TYPE,ob.CREATED
from dba_objects ob
where object_name like upper('%&template%') escape '\'
order by 1,2;

Start sqlplus in Windows .BAT script

Posted on by
Reply

Here is the example of emulating “HERE-documents” in Windows shell.


@ECHO OFF

FIND "/*%none% some_label" <%0 |sqlplus USER/PASS@SERVER GOTO end : ----------- embedded SQL------------------------- /* some_label */ select count(*) from dba_free_space; /* some_label */ select tablespace_name from dba_tablespaces; : ----------- end of embedded SQL------------------------- :end

It's also possible to use procedures in .bat files.
%none% is replaced by empty string.
Spaces are mandadory in "/* some_label */" string.

Another possibility:
[cc lang="dos"]
@(
echo select global_name from global_name ;
) | sqlplus USER/PASS@SERVER
[/cc]

[amazon-product]0735622620[/amazon-product]

Show hidden parameters in Oracle

Posted on by
Reply

Check the hidden underscore parameters…


col "Parameter" for a50
col "Session Value" for a15
col "Instance Value" for a15

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and lower(a.ksppinm) like lower('%&param%')
order by a.ksppinm;

Setting the parameter:

alter system set "&param." = value scope = &scope. ;

scope is one of the following: “both”, “memory”, “spfile”

To remove completely the hidden parameter from the spfile:

alter system reset "&param." scope=spfile SID='*';