Sometimes it’s necessary to convert Sybase timestamp to something more readable
Warning! Timestamp is not date type!
declare @p timestamp
declare @s varchar(16)
declare @t1, @t2 varbinary(4)
//--- from char to timestamp
@t1 = hextoint(substring(lower(@s),1,8))
@t2 = hextoint(substring(lower(@s),9,8))
@p = @t1 + @t2
//--- from timestamp to char
@s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))
Examples for changing the Oracle LDAP entries.
Warning!
Put the space after every ldif command!
Check the version of ldapmodify (it should be Oracle binary, not the OS standard one)
#### modify.sh ####
ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f modify.ldif
#### modify.ldif ####
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
changetype: modify
replace:orclnetdescstring
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))
#### add.sh ####
ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f add.ldif
### add.ldif ###
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))
objectclass: top
objectclass: orclNetService
cn: SOME_ORACLE_SID
orclnetdescname: 000:cn=DESCRIPTION_0
### unload.sh ###
#!/bin/ksh
if [ -z "$1" ] ; then
if [ -z "$CONTEXT" ] ; then
print "Examples of context: ny.company.com or dc=ny,dc=company,dc=com" >&2
printf "Enter context (Enter for default): " >&2
read CONTEXT
fi
CONTEXT=${CONTEXT:-"dc=world"}
else
CONTEXT=${1:-"dc=world"}
fi
case "$CONTEXT" in
"dc="* ) ;;
* ) CONTEXT=$( echo "${CONTEXT}" | sed 's#\([^.]*\)$#dc=\1#;s#\([^.]*\)\.#dc=\1,#g') ;;
esac
echo "# CONTEXT: $CONTEXT" >&2
ldapsearch -h $LDAP_HOST -p $LDAP_PORT -D "cn=orcladmin" -w $LDAP_PASS -L -s sub -b "cn=OracleContext,${CONTEXT}" "(objectclass=orclnetService)" "*"
Quick and dirty parsing procedure for unix shell scripts
parse_command_line ()
{
typeset -i user_opt help_opt password_opt verbose_opt
typeset errmsg
arg_cou=$#
while [ "$#" -gt 0 ]
do
case "$1" in
-@([U]) ) let user_opt=user_opt+1 ;;
-P ) let password_opt=password_opt+1 ; ask_pass=0 ;;
-option1 ) ;;
-option2 ) ;;
-option3 ) ;;
-@([hH?])) help_opt=1;;
-v*(erbose)) let verbose_opt=verbose_opt+1 ;;
*)
if [ user_opt -eq 1 ];then
USER="$1"; user_opt=0
elif [ password_opt -eq 1 ];then
PASS="$1"; password_opt=0
elif [ verbose_opt -eq 1 ];then
verbose_level="$1"; verbose_opt=0
else
### help_opt=1
CMD=$* ; break
fi
;;
esac
shift
done
if [ arg_cou -eq 0 ] ; then
help_opt=1 ; errmsg="Nothing to do"
fi
for opt in user password ; do
if [ ${opt}_opt -ne 0 ] ; then
help_opt=1 ; errmsg="${errmsg}\n${opt} is absent"
fi
done
if [ verbose_opt -ne 0 ] ; then
verbose_level=$verbose_opt
fi
if [ help_opt -eq 1 ]; then
help "$errmsg"
fi
}
print -n "Enter Your password:"
stty_orig=`stty -g`
trap "stty ${stty_orig}; exit" 1 2 3 15
stty -echo >&- 2>&-
read PASS stty ${stty_orig} >&- 2>&-
trap 1 2 3 15 print
trap :catches interruptions. I.e. if the user presses Ctrl+C, the normal stty mode is set before stopping the program
stty -echo :switches off the display echo
>&- 2>&- :helps to avoid “stty: Not a typewriter” message for non-interactive scripts.
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
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 */
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
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;
#!/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<
$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 <
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;