Start the dxdiag tool.
Windows XP:
Start -> Run -> dxdiag
Windows Vista:
Start -> Search -> dxdiag
…and if You could not find dxdiag, probably You do not have DirectX on Your machine.
Are You still using old kind DOS box?
SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid=&which_sid. )
Full text
select x.sql_text
from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid
order by sid, piece asc
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]
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: CITYCONSTRAINT_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#;
Small query to list Oracle tablespaces, that are in backup mode:
select d.tablespace_name, b.time
from dba_data_files d, v$backup b
where
d.file_id = b.FILE#
and b.STATUS = 'ACTIVE' ;
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;
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;
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
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
Add the project to del.icio.us
http://del.icio.us/post?title=www.memosoup.com&url=http://www.memosoup.com
http://digg.com/submit?phase=2&url=http://www.memosoup.com
Add the site to bookmarks:
function add2Bookmarks(title,url) {
if (window.sidebar) {
window.sidebar.addPanel(title, url,"");
} else if( document.all ) {
window.external.AddFavorite( url, title);
} else if( window.opera && window.print ) {
return true;
}
}
