oracle olapº¯Êý£¨Ðø£©
/*connect byº¯Êý*/
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('6','126','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('7','127','1');
commit;
--´ÓRootÍùÊ÷Ä©Éҵݹé
select * from TBL_TEST
start with id=1
connect by prior id = pid
order by pid,id
--´ÓÄ©ÉÒÍùÊ÷ROOTµÝ¹é
select * from TBL_TEST
start with id=5
connect by prior pid=id
/*leadÏÂÒ»¸ö lagÉÏÒ»¸ö*/
create table LEAD_TABLE
(
CASEID VARCHAR2(10),
STEPID VARCHAR2(10),
ACTIONDATE DATE
);
insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));
insert into LEAD_TABLE valu
Ïà¹ØÎĵµ£º
ʹÓÃ
set
pagesize 1000
set
linesize 132
col
TS_NAME form a24
col
PIECES form 9999
col
PCT_FREE form 999.9
col
PCT_USED form 999.9
select
*
from (select Q2.OTHER_TNAME TS_NAME,
PIECES,
& ......
OracleÊý×Ö×Öµä°üº¬Ò»¸öÏÊΪÈËÖªµÄv$session_longopsÊÓͼ¡£v$session_longopsÊÓͼ¿ÉÒÔʹOracleר¼Ò¼õÉÙÔËÐÐʱ¼äºÜ³¤µÄDDLºÍDMLÓï¾äµÄÔËÐÐʱ¼ä¡£
¡¡¡¡
¡¡¡¡
¡¡¡¡
¡¡¡¡ÀýÈçÔÚÊý¾Ý²Ö¿â»·¾³ÖУ¬¼´Ê¹Ê¹Óò¢ÐÐË÷Òý´´½¨¼¼Êõ£¬¹¹½¨Ò»¸öºÜ¶àG×Ö½Ú´óµÄË÷ÒýÐèÒªºÄ·ÑºÜ¶à¸öСʱ¡£ÕâÀïÄã¾Í¿ÉÒÔ²éѯv$session_longopsÊÓͼ¿ìËÙÕÒ³öÒ»¸ ......
Oracle µÄ 9i/10g ÖÐÒѾ¶ÔÄÚ´æ¹ÜÀíÖð²½×öÁ˺ܴóµÄ¼ò»¯£¬11g Ôò¸ü½øÒ»²½£¬ÒýÈëÁËÒ»¸öеĸÅÄî×Ô¶¯»¯ÄÚ´æ¹ÜÀí(Automatic Memory Management,AMM) . Èç¹û DBA ÕæµÄÏë͵ÀÁµÄ»°£¬Ö»ÐèÒªÉ趨Á½¸ö²ÎÊý¾Í¿ÉÒÔ°Ñ·³ÐĵÄÊÂÇ鶼½»¸ø Oracle ÕÛÌÚÁË(Ö»Òª DBA ×ã¹»ÐÄ¿í)¡£PGA Óë SGA Ò»Æð¸ã¶¨¡£ÕâÁ½¸ö²ÎÊý·Ö±ðÊÇ£º
MEMORY_TARGET--²Ù×÷Ï ......
1£ºÒÔÒ»¸öDBAÉí·ÖµÄÓû§µÇ½£¬´ò¿ªÒ»¸öÃüÁî´°¿Ú¡£Ö´ÐÐÃüÁî
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
from v$session s,v$lock l,dba_objects o
WHERE ......