Category Archives: SQL

Creation and using of Sybase DBCCDB database

Posted on by
Reply

Planning:

sp_plan_dbccdb '{dbname}'

Creation:

create database dbccdb on ... log on ...

isql -U sa -i $SYBASE/$SYBASE_ASE/scripts/installdbccdb

DBCCDB Configuration:

sp_configure "number of worker processes", {value_from_sp_plan}

-- create named cache for dbccdb
sp_cacheconfig "dbccdb_cache", "{cache_size}M"

-- Size is at least 40 * 16K * (number of working processes)
sp_poolconfig "dbccdb_cache", "{pool_size}M", "16K"

Working space configuration:

sp_dbcc_createws dbccdb, "default", scan_{dbname}, scan, '{size_from_sp_plan}'
sp_dbcc_createws dbccdb, "default", text_{dbname}, text, '{size_from_sp_plan}'

sp_dbcc_updateconfig {dbname}, 'max worker processes', '{value_from_sp_plan}'
sp_dbcc_updateconfig {dbname}, 'dbcc named cache','dbccdb_cache', '{size}'
sp_dbcc_updateconfig {dbname}, 'scan workspace','scan_{dbname}'
sp_dbcc_updateconfig {dbname}, 'text workspace','text_{dbname}'

dbcc checking:


dbcc checkstorage ({dbname})
dbcc checkverify ({dbname})

dbcc report:

sp_dbcc_summaryreport
sp_dbcc_configreport
sp_dbcc_statisticsreport
sp_dbcc_faultreport ['short'|'long']

-- all above
sp_dbcc_fullreport

Fixing the errors
Warning: just some tips here. See Sybase documentation for the complete description of the repair action

100035, 100025, 100021 - dbcc checktable ( %s , "fix_spacebits")
forwarded rows errors - reorg compact %s with resume , time = '%d'
Others:
Index code == 0 - dbcc tablealloc ( %s , "full" , "fix")
Index code != 0 - dbcc indexalloc ( %s , %s , "full" , "fix")

System tables require special processing - see Sybase documentation for the details

Usage of the temp tablespace in Oracle

Posted on by
Reply

First find the block size:


select value "BLOCK_SIZE"
from v$parameter
where name='db_block_size' ;

Now check the usage of the tablespace:

select
s.username "USER",s.sid,s.osuser,
u.tablespace "TS" ,
sum(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
from v$session s,v$sort_usage u,v$sqltext x
where s.saddr=u.session_addr
and s.sql_address=x.address
group by
s.sid, s.username, osuser,
tablespace, sql_text, address, piece
order by sid, piece asc;

Invalid objects in Oracle DB (health check)

Posted on by
Reply

Some standard queries to check the state of the Oracle DB

select 'alter '
|| decode(lower(object_type), 'package body','package',lower(object_type))
|| ' ' || owner || '.'
|| object_name
|| decode(object_type,'PACKAGE BODY', ' compile body;',' compile;') INVALID_OBJECTS
from DBA_OBJECTS
where STATUS = 'INVALID';

select 'ALTER INDEX '|| owner || '.' || index_name || ' rebuild online ;'
from dba_indexes where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild partition ' || partition_name || ' online ;'
from dba_ind_partitions
where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild subpartition ' || subpartition_name || ' online ;'
from dba_ind_subpartitions
where status = 'UNUSABLE';

SELECT name, unrecoverable_change# , unrecoverable_time
FROM v$datafile
order by 2;

Search for Oracle errors in the alert log

Posted on by
Reply

Search for Oracle errors in the last $NUM lines.


NUM=1000
SIGNAL_LIST='^(...-|Error|Starting.*instance)|terminating instance'

tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/'"$SIGNAL_LIST"'/ { if ( prev !~ /'"$SIGNAL_LIST"'/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '

On Unix Oracle alert log is normally located in bdump/alert_{ORACLE_SID}.log

Converting timestamp to char

Posted on by
Reply

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))))

Manupulating Oracle LDAP entries

Posted on by
Reply

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)" "*"

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

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