sweetdev useful links
http://espace.sig.echonet/sweetdev/index.htm (French)
http://espace.sig.echonet/sweetdev/sweetbook/2_2_2/Sweetbook-2_2_2.htm (English)
http://wikinet.group.echonet/display/ITPSInetSweetdev/Home (Wiki ıin english)
http://wikinet.group.echonet/display/ITPSInetSweetdev/SweetDEV+-+Components+-+Table (customizatıon of the table tag)
http://www.ideotechnologies.com/documentation/sweetdev-en/help-doc.htm
http://www.ideotechnologies.com/documentation/javadoc/
http://espace.sig.echonet/downloads/
http://espace.sig.echonet/sweetdev/sweetbook/2_2_2/Sweetbook-2_2_2.htm (English)
http://wikinet.group.echonet/display/ITPSInetSweetdev/Home (Wiki ıin english)
http://wikinet.group.echonet/display/ITPSInetSweetdev/SweetDEV+-+Components+-+Table (customizatıon of the table tag)
http://www.ideotechnologies.com/documentation/sweetdev-en/help-doc.htm
http://www.ideotechnologies.com/documentation/javadoc/
http://espace.sig.echonet/downloads/
CrossTab
Hi,
I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)
As always, there's actually a few ways to do this,
here's one approach:
SQL> select * from food;
WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/
SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;
FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2
/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/
/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/
SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;
WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2
/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/
SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);
FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/
Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).
Thanks and regards,
Anthony
-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab
# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933
# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838
Hi, I have the following table...
apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...
john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)
As always, there's actually a few ways to do this,
here's one approach:
SQL> select * from food;
WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/
SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;
FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2
/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/
/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/
SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;
WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2
/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/
SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);
FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/
Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).
Thanks and regards,
Anthony
-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab
# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933
# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838
Hi, I have the following table...
apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...
john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
plsql send mail
declare
CRLF CONSTANT varchar2(10) := utl_tcp.CRLF;
BOUNDARY CONSTANT varchar2(256) := '-----7D81B75CCC90D2974F7A1CBD';
FIRST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||CRLF;
LAST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||'--'||CRLF;
MULTIPART_MIME_TYPE CONSTANT varchar2(256) := 'multipart/mixed; boundary="'||BOUNDARY||'"';
mail_conn utl_smtp.connection;
sender VARCHAR2(200);
mhost VARCHAR2(30);
inRecipient VARCHAR2(100);
inMessage VARCHAR2(100);
mime_type varchar2(255) := 'text/html';
attachment_file_name varchar2(255) := 'abc.txt';
--attachment_file_name varchar2(255) := 'abc.txt';
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(mail_conn, name || ': ' || header || utl_tcp.CRLF);
END;
procedure send_receive_header(name in varchar2, header in varchar2) is
begin
utl_smtp.write_data(mail_conn, name||': '||header||CRLF);
end;
BEGIN
inMessage :='DENEME1';
inRecipient:='ctahtaci@yfas.com.tr';
mhost:='193.193.193.96';
mail_conn := utl_smtp.open_connection(mhost, 25);
sender :='ct_deneme_attach@yfas.com.tr';
utl_smtp.helo(mail_conn, mhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, inRecipient);
utl_smtp.open_data(mail_conn);
send_header('From', 'xxxxx');
send_header('To', '"Recipient"
send_header('Subject', 'Hello1');
send_header('Content-Type',MULTIPART_MIME_TYPE);
--utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');
-- utl_smtp.data(mail_conn, inMessage);
----------------------------------------
-- Send the main message text
----------------------------------------
-- mime header
utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);
send_receive_header('Content-Type',mime_type);
utl_smtp.write_data(mail_conn, CRLF);
utl_smtp.write_data(mail_conn, 'see attachment');
utl_smtp.write_data(mail_conn, CRLF);
-- add the attachment
utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);
send_receive_header('Content-Type',mime_type);
send_receive_header('Content-Disposition','attachment; filename= '||attachment_file_name);
utl_smtp.write_data(mail_conn, CRLF);
utl_smtp.write_data(mail_conn, 'This is the attachment and this text is written in add attachement section');
utl_smtp.write_data(mail_conn, CRLF);
-- Close the message
utl_smtp.write_data(mail_conn, LAST_BOUNDARY);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
null;
END;
used temp tbs per each session
SET termout ON
> > SET heading ON
> > SET PAGESIZE 60
> > SET LINESIZE 126
> >
> > COLUMN tablespace FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN tablespace_name FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN username FORMAT a12 HEADING 'Oracle|Username'
> > COLUMN Client_User FORMAT a20 HEADING 'Client|Username'
> > COLUMN osuser FORMAT a12 HEADING 'O/S|Username'
> >
> > TTITLE CENTER 'Who/What is Using Which SORT Tablespace' -
> > skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~~~' -
> > skip 2
> > repfooter off
> > btitle off
> >
> > SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,
>a.serial#,
> > a.username, a.osuser, a.status
> > FROM v$session a,v$sort_usage b
> > WHERE a.saddr = b.session_addr
> > ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
> > ;
> >
> > select tablespace_name, used_blocks, free_blocks, total_blocks
> > from v$sort_segment;
> >
> > SET heading ON
> > SET PAGESIZE 60
> > SET LINESIZE 126
> >
> > COLUMN tablespace FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN tablespace_name FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN username FORMAT a12 HEADING 'Oracle|Username'
> > COLUMN Client_User FORMAT a20 HEADING 'Client|Username'
> > COLUMN osuser FORMAT a12 HEADING 'O/S|Username'
> >
> > TTITLE CENTER 'Who/What is Using Which SORT Tablespace' -
> > skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~~~' -
> > skip 2
> > repfooter off
> > btitle off
> >
> > SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,
>a.serial#,
> > a.username, a.osuser, a.status
> > FROM v$session a,v$sort_usage b
> > WHERE a.saddr = b.session_addr
> > ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
> > ;
> >
> > select tablespace_name, used_blocks, free_blocks, total_blocks
> > from v$sort_segment;
> >
ref cursor kullanmak
Hello,
RefCursor example:
declare
type TRefCursor is ref cursor;
vc TRefCursor;
vr dual%rowtype;
begin
open vc for 'select * from dual';
loop
fetch vc into vr;
exit when vc%notfound;
dbms_output.put_line( 'dummy='||vr.dummy );
end loop;
if( vc%isopen )then close vc; end if;
end;
The main difference it could be dinamic.
RefCursor example:
declare
type TRefCursor is ref cursor;
vc TRefCursor;
vr dual%rowtype;
begin
open vc for 'select * from dual';
loop
fetch vc into vr;
exit when vc%notfound;
dbms_output.put_line( 'dummy='||vr.dummy );
end loop;
if( vc%isopen )then close vc; end if;
end;
The main difference it could be dinamic.
find invalid object dependencies
SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
WHERE object_id IN (
SELECT object_id
FROM public_dependency
CONNECT BY PRIOR object_id = referenced_object_id
START WITH referenced_object_id =
(SELECT object_id
FROM SYS.dba_objects
WHERE owner = :owner
AND object_name = :NAME
AND object_type = :TYPE))
FROM SYS.dba_objects
WHERE object_id IN (
SELECT object_id
FROM public_dependency
CONNECT BY PRIOR object_id = referenced_object_id
START WITH referenced_object_id =
(SELECT object_id
FROM SYS.dba_objects
WHERE owner = :owner
AND object_name = :NAME
AND object_type = :TYPE))
ftp script
#!/bin/sh
HOST='10.206.145.42'
USER='oracle'
PASSWD='AnGntk06'
FILE='/GANTEKT/mini2.dmp'
ftp -n $HOST <
quote USER $USER
quote PASS $PASSWD
bin
put $FILE /ata031/bscs5/tempo/mini3.dmp
quit
END_SCRIPT
exit 0
disk mount etmek
root@sdpool # mount -F nfs 10.200.165.11:/patches /A
bekleyen session
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
rollback segment
SELECT owner,segment_name,partition_name, sum(bytes)
FROM dba_extents
WHERE tablespace_name='RBS'
GROUP BY owner,segment_name,partition_name
ORDER BY 4 DESC
alter rollback segment rbs13 shrink
--TABLESPACE SPACE CHECK (SPACE MANAGEMENT)
SELECT d.tablespace_name "Name", d.status "Status",
to_char((a.bytes / 1024 / 1024),'99,999,990.900') "Size (M)",
to_char(((a.bytes - decode(f.bytes, null, 0, f.bytes)) / 1024 / 1024),'99,999,990.900') "Used (M)",
to_char((a.bytes - decode(f.bytes, null, 0, f.bytes))*100/a.bytes,'99,99,990') "USED %"
FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f
WHERE d.tablespace_name = a.tablespace_name
AND f.tablespace_name (+) = d.tablespace_name
ORDER by 5 desc
SELECT segs.SEGMENT_NAME "RB NAME ",sta.RSSIZE, p.spid "SYSTEM PID ",s.sid,s.serial#,
NVL (p.username, 'NO TRANSACTION') "OS USER", s.username , action, p.terminal, tablespace_name
FROM v$rollstat sta , dba_rollback_Segs segs, v$process p , v$session s ,v$lock l
WHERE l.sid = s.sid(+)
AND s.paddr = p.addr
AND TRUNC (l.id1(+)/65536) = sta.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
AND segs.segment_id=usn
ORDER BY sta.rssize desc
shrink
SELECT 'ALTER rollback segment '||segment_name||' shrink;'
FROM dba_segments WHERE tablespace_name!='SYSTEM' AND SEGMENT_TYPE LIKE 'R%';
Tru64 Xterm login
Arkadaslar,
Tru64 Unix server’lara (toros,alaca, anadolu, trakya....) Xterm (GUI) “root” login olmak istediginizde “Root login refused on this terminal"
Hatasi alacaksiniz muhtemelen . Bunu kaldirmak icin.
Makinaya telnet olup,
# vi /etc/securettys
Satirin sonuna Laptopunuzun ıpsini yazip sonuna :0 yazmaniz lazim.
Ornek : 10.204.101.121:0 gibi...
Kaydol:
Kayıtlar (Atom)
