Monthly Archives: September 2009

Oracle: transpose data table (rows into columns)

Posted on by
Reply


create table MAIN_TBL (
magazine varchar2(10),
region varchar2(5),
quantity int );

insert into MAIN_TBL values ( 'Playboy', 'Nord', 1 );
insert into MAIN_TBL values ( 'Playboy', 'East', 2 );
insert into MAIN_TBL values ( 'AutoWeek', 'Nord', 3 );
insert into MAIN_TBL values ( 'AutoWeek', 'West', 4 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 5 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 6 );
insert into MAIN_TBL values ( 'Wired', 'West', 7 );
insert into MAIN_TBL values ( 'Wired', 'East', 8 );
insert into MAIN_TBL values ( 'Wired', 'South', 9 );

declare
report_exists number;
report_owner varchar(30) := 'FRODO' ;
--
report_name varchar(30) := 'REPORT_TBL' ;
query_main varchar(16000) :=
'create table ' || report_owner || '.'
|| report_name || ' as select MAGAZINE ' ;
--
query_part varchar(1024) ;
my_var varchar2(5);

cursor cur_region is
select distinct REGION
from MAIN_TBL
order by region;

begin

select count(*) into report_exists
from dba_tables
where table_name = report_name and owner = report_owner;

if ( report_exists = 1 ) then
execute immediate 'drop table ' || report_owner || '.' || report_name ;
end if;

open cur_region ;
loop
fetch cur_region into my_var ;
exit when cur_region%NOTFOUND;
query_part := 'select nvl(sum(quantity),0) from MAIN_TBL x where x.magazine = main.magazine and x.region='''||my_var||'''' ;
query_main := query_main || chr(10) || ',(' || query_part || ')"' || my_var || '"';
end loop;

close cur_region ;

query_main := query_main || ' from (select distinct MAGAZINE from MAIN_TBL ) main' ;

execute immediate query_main ;

end;
/
select * from FRODO.REPORT_TBL
/

Output:

MAGAZINE East Nord South West
———- —– —– —— ——-
AutoWeek 0 3 0 4
Playboy 2 1 0 0
Wired 8 11 9 7

There is also discussion of the similar subject on AskTom site.

[amazon-product]0137142838[/amazon-product]

Oracle: find dependent objects

Posted on by
1

This query helps to find the objects, that depend from the specified one

select CONSTRAINT_NAME, OWNER,TABLE_NAME from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME in
( select CONSTRAINT_NAME
from dba_constraints
where
OWNER=upper('&user_dependent_from') and
TABLE_NAME=upper('&object_dependent_from') )
and STATUS='ENABLED';

Example:

create table city ( city_id int ,
name char(10) ,
primary key ( city_id ) ) ;

create table country (
cou_id int,
name char(19),
city_id int
constraint ZZZ references city ( city_id ) on delete cascade,
primary key(cou_id) ) ;

insert into CITY values ( 1, 'Paris' ) ;
insert into CITY values ( 2, 'Berlin') ;

insert into COUNTRY values ( 101, 'France', 1 ) ;
insert into COUNTRY values ( 102, 'Germany', 2 );

Output:

Enter value for user_dependent_from: FRODO
Enter value for object_dependent_from: CITY

CONSTRAINT_NAME OWNER TABLE_NAME
ZZZ FRODO COUNTRY

[amazon-product]1590599683[/amazon-product]

There is also standard script from Oracle, which displays the dependencies: ${ORACLE_HOME}/rdbms/admin/utldtree.sql


execute deptree_fill('table', 'scott', 'emp');
select * from deptree order by seq#;

Write to the alert log from PL/SQL code

Posted on by
Reply

The following undocumented function could be used to write to the alert log:


execute sys.dbms_system.ksdwrt(code, message);

Parameters:

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Example:

execute sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY')||'Hello!' );


create or replace trigger trg_delme
BEFORE UPDATE on frodo.delme
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY') );
end;

Quick calculation of the row count

Posted on by
Reply

Warning! It depends on the statistics and could be not accurate!

Sybase

select user_name( obj.uid), obj.name , rowcnt(doampg)
from sysindexes idx, sysobjects obj
where idx.indid < 2 and idx.id = obj.id and obj.type = 'U'
order by 1,2
go

select object_name( idx.id ), rowcnt(doampg)
from sysindexes idx
where idx.indid < 2
and object_name( idx.id ) in ('object1','object2')
go

Oracle

select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
where TABLE_NAME = upper( '&what_tbl.' );

select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
order by 1,2;

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

Calculating the date of Easter

Posted on by
Reply

The easiest way is to use specialized package (like Date::Calc).
If You need standalone function, the following method could be used.


#!/usr/bin/perl

$year=$ARGV[ 0 ];
$isJulian=(defined($ARGV[ 1 ]) && $ARGV[ 1 ] eq 'julian');
$isOrthodox=(defined($ARGV[ 1 ]) && $ARGV[ 1 ] eq 'orthodox');

my ( $G , $C , $H, $I, $J , $L );

$G = $year % 19 ;
if ( $isJulian || $isOrthodox ) {
print ( $isJulian ? "Julian:" : "Orthodox:" );
$I = ( 19*$G + 15) % 30 ;
$J = ( $year + int($year/4) + $I) % 7 ;
}else{
print "Gregorian:" ;
$C = int( $year/100 );
$H = ($C - int($C/4) - int((8*$C+13)/25) + 19*$G + 15) % 30 ;
$I = $H - int($H/28)*(1 - int($H/28)*int(29/($H + 1))*int((21 - $G)/11)) ;
$J = ($year + int($year/4) + $I + 2 - $C + int($C/4) ) % 7 ;
}
$L = $I - $J ;
$EasterMonth = 3 + int(($L + 40)/44) ;
$EasterDay = $L + 28 - 31*int($EasterMonth/4) ;

if ( $isOrthodox ) {
( $EasterDay, $EasterMonth, $year ) = EasterOrthodox( $EasterDay, $EasterMonth, $year )
}

printf("Day=%d Mon=%d Year=%d \n", $EasterDay, $EasterMonth, $year );

sub EasterOrthodox
{
my ($pDay, $pMonth, $pYear) = @_ ;
my ( $extra , $tmp ) = ( 0, 0 );
my ( $rezDay, $rezMonth) = ( 0, 0 );

if (($pYear > 1582) && ($pYear <= 4099)) { $extra = 10; if ($pYear > 1600) {
$tmp = int($pYear/100) - 16;
$extra = $extra + $tmp - int($tmp/4);
}

$rezDay = $pDay + $extra;
$rezMonth = $pMonth;

if (($rezMonth == 3) && ($rezDay > 31)) {
$rezMonth = 4;
$rezDay = $rezDay - 31;
}

if (($rezMonth == 4) && ($rezDay > 30)) {
$rezMonth = 5;
$rezDay = $rezDay - 30;
}
}
return ( $rezDay, $rezMonth, $pYear )
}

Examples:
perl -w 2006
perl -w 2006 orthodox