Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

oracle olapº¯Êý

/*sum()over()*/
--ĬÈϼÆËãËùÓÐÐеĺϼÆ
select t.empno,t.ename,t.sal,t.deptno,sum(t.sal)over()
from scott.emp t;
--partition by·Ö×éºÏ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(partition by t.deptno)
from scott.emp t
order by t.deptno,t.sal;
--partition by order by deptno·Ö×éÀÛ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(partition by t.deptno order by t.sal)
from scott.emp t;
--rows n preceding È¡µ±Ç°ÐÐ+ǰnÐÐ=(n+1)ÐÐ
--ͨ¹ýorder by desc¿ÉÒÔÈ¡ºónÐÐ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(order by t.deptno,t.sal rows 1 preceding)
from scott.emp t;
--rows 2n+1 È¡µ±Ç°ÐÐ+ǰnÐÐ+ºónÐÐ=(2n+1)ÐÐ
select t.empno,t.ename,t.sal,t.deptno,
       sum(t.sal)over(order by t.deptno,t.sal rows between 1 preceding and 1 following)
from scott.emp t;
/*first_value() over()*/
select deptno,ename,sal,hiredate,
¡¡¡¡first_value(ename) over(partition by deptno order by sal asc rows 5 preceding) first_ename
¡¡¡¡from emp  order by hiredate asc;
/*avg()over  count() over()  max()over() min()over()*/
select deptno,sal,
    sum(sal)over(partition by deptno) as sumsal,
¡¡¡¡avg(sal)over(partition by deptno) as avgsal,
¡¡¡¡count(*)over(partition by deptno) as count,
¡¡¡¡max(sal)over(partition by deptno) as maxsal
from emp;
/*rank()over() dese_rank()over() row_number()over()*/
select empno, deptno, sal,
    rank()   over (order by deptno desc nulls last) as rank,
    dense_rank()  over (partition by deptno order by sal desc nulls last) as dense_rank,
    row_number()  over(partition by deptno order by sal desc nulls last) as row_number
from emp;
/*stddev() over()*±ê×¼²î/
select empno, deptno, sal,stddev(sal) over(order by sal)
from emp;


Ïà¹ØÎĵµ£º

Oracle±í¿Õ¼ä¹ÜÀí

extent--×îС¿Õ¼ä·ÖÅ䵥λ --tablespace management
block --×îСi/oµ¥Î»      --segment    management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M ¡¡¡¡¡¡¡¡¡¡¡¡--³õʼµÄÎļþ´óС¡¡
autoextend On¡¡¡¡¡¡¡¡ --×Ô¶¯Ôö³¤
next 10M¡ ......

Oracle¼¼ÇÉ£ºÓÃv$session_longops¸ú×ÙDDLÓï¾ä

OracleÊý×Ö×Öµä°üº¬Ò»¸öÏÊΪÈËÖªµÄv$session_longopsÊÓͼ¡£v$session_longopsÊÓͼ¿ÉÒÔʹOracleר¼Ò¼õÉÙÔËÐÐʱ¼äºÜ³¤µÄDDLºÍDMLÓï¾äµÄÔËÐÐʱ¼ä¡£
¡¡¡¡
¡¡¡¡
¡¡¡¡
¡¡¡¡ÀýÈçÔÚÊý¾Ý²Ö¿â»·¾³ÖУ¬¼´Ê¹Ê¹Óò¢ÐÐË÷Òý´´½¨¼¼Êõ£¬¹¹½¨Ò»¸öºÜ¶àG×Ö½Ú´óµÄË÷ÒýÐèÒªºÄ·ÑºÜ¶à¸öСʱ¡£ÕâÀïÄã¾Í¿ÉÒÔ²éѯv$session_longopsÊÓͼ¿ìËÙÕÒ³öÒ»¸ ......

oracleº¯Êý´óÈ«


SQLÖеĵ¥¼Ç¼º¯Êý    
1.ASCII    
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;    
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;&nb ......

ORACLE Êý¾Ý±í´´½¨²½Öè

1.´´½¨ÁÙʱ±í¿Õ¼ä
CREATE TEMPORARY TABLESPACE Test_temp
TEMPFILE 'E:\Test\TEST_temp.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
2.´´½¨Óû§±í¿Õ¼ä
create tablespace TEST
datafile 'E:\TEST\TEST.dbf'
size  100M
autoextend on maxsize     ......

oracleÖв鿴Óû§È¨ÏÞ

ORACLEÖÐÊý¾Ý×ÖµäÊÓͼ·ÖΪ3´óÀà,     ÓÃÇ°×ºÇø±ð£¬·Ö±ðΪ£ºUSER£¬ALL ºÍ DBA£¬Ðí¶àÊý¾Ý×ÖµäÊÓͼ°üº¬ÏàËÆµÄÐÅÏ¢¡£
USER_*:ÓйØÓû§ËùÓµÓеĶÔÏóÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóÐÅÏ¢
ALL_*£ºÓйØÓû§¿ÉÒÔ·ÃÎʵĶÔÏóµÄÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóµÄÐÅÏ¢¼ÓÉÏÆäËûÓû§´´½¨µÄ¶ÔÏ󵫸ÃÓû§ÓÐȨ·ÃÎʵÄÐÅÏ¢
DBA_* ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ