时区
NLS_DATE_FORMAT
v$timezone_names,current_date,dbtimezone,sessiontimezone,localtimestamp,current_timestamp
select dbtimezone from dual; --database zone
select sessiontimezone from dual; --current zone
select * from v$timezone_names;
timestamp
timestamp with time zone
timestamp with local time zone
year -4712 to 9999
timezone_hour -12 to 14
timzone_minute 00 to 59

SQL> create table tt1(d1 date,d2 timestamp,d3 timestamp with time zone,d4 timestamp with local time zone);

Table created.

SQL> insert into tt1 values(sysdate,sysdate,sysdate,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt1;

D1
---------
D2
---------------------------------------------------------------------------
D3
---------------------------------------------------------------------------
D4
---------------------------------------------------------------------------
08-JAN-11
08-JAN-11 09.59.30.000000 AM
08-JAN-11 09.59.30.000000 AM +08:00
08-JAN-11 09.59.30.000000 AM

interval
interval year to month
interval day to second
interval '8 10:44:54' day to second
create table tt2(id number,d1 interval year to month,d2 interval day to second);
insert into tt2 values(1,interval '12' month,interval '8 10:00:12' day to second);
insert into tt2 values(1,interval '10' month,interval '10:22:12' hour to second);
insert into tt2 values(1,interval '9' month,interval '22:12' minute to second);
extract
select extract(year from sysdate) from dual;
tz_offset
timestamp 转时区使用from_tz
select from_tz(to_timestamp(sysdate),'10:00') from dual;
to_timestamp
to_timestamp_tz
to_yminterval
to_dsinterval高级子查询成对比较子查询
select ename,deptno,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
from子句后使用子查询
select b.ename,b.deptno,b.sal from emp b,(select deptno,avg(sal) avg from emp group by deptno) a where b.deptno=a.deptno and b.sal>a.avg;
exists比in性能好
select empno ,ename from emp a where exists ( select '1' from emp b where b.mgr=a.empno);层次查询
select empno,ename,mgr ,level from emp start with ename='KING' connect by prior empno=mgr; --从上级到下属
SQL> select empno,ename,mgr ,level from emp start with ename='KING' connect by prior empno=mgr;

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3

14 rows selected.


select empno,ename,mgr ,level from emp start with ename='JAMES' connect by prior mgr=empno; --从下属到上级
SQL> select empno,ename,mgr ,level from emp start with ename='JAMES' connect by prior mgr=empno;

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7900 JAMES 7698 1
7698 BLAKE 7839 2
7839 KING 3
select empno,ename,mgr ,level from emp where ename!='JONES' start with ename='KING' connect by prior empno=mgr; --去掉结点JONES
SQL> select empno,ename,mgr ,level from emp where ename!='JONES' start with ename='KING' connect by prior empno=mgr;

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7839 KING 1
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
7900 JAMES 7698 3

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7782 CLARK 7839 2
7934 MILLER 7782 3

13 rows selected.

select empno,ename,mgr ,level from emp start with ename='KING' connect by prior empno=mgr and ename!='JONES' ; --去掉JONES以下的分支
SQL> select empno,ename,mgr ,level from emp start with ename='KING' connect by prior empno=mgr and ename!='JONES' ;

EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7839 KING 1
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3

9 rows selected.

select empno,ename,mgr ,level from emp start with ename='JONES' connect by prior empno=mgr;正则表达式
regexp_like
regexp_instr
regexp_substr
regexp_replace
042
grid：分布式＋网络安全
ASM automatic storage management 存储网格
RAC real application cluster 数据库网格
Oracle Streams 应用程序网格，以后用golden gate
EMGC enterprise manager grid control网格控制内存结构：SGA PGA进程结构：后台进程SMON PMON CKPT DBWR LGWR、用户进程 、服务器进程（OS）存储结构：物理文件
OFA(optimal flexible architecture)装载点：/u01 /disk01目录命名：/u01/app/oracle -- $ORACLE_BASE=/u01/app/oracle文件命名：controlN.ctl redoN.log tN.dbf --N 01 to 99
root.sh --set environment variable
/usr/local/bin/coraenv dbhome oraenv
ORACLE_HOME
ORACLE_OWNER
orainstRoot.sh
ORACLE_BASE
/etc/oraInst.loc
/etc/oratab实例
[root@gnd-dataguard oraInventory]# sh orainstRoot.sh
Changing permissions of /u01/product/oraInventory to 770.
Changing groupname of /u01/product/oraInventory to dba.
The execution of the script is complete
修改 /u01/product/oraInventory 目录权限。

[root@gnd-dataguard oracle]# sh root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/product/oracle

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.