9i 很少了解新特点(28页).ppt

My Favorite Little Known Features of Oracle9i Matt Adams What you will NOT Hear about Today * Multiple Block Sizes Automatic Undo External Tables DataGuard LogMiner Resumable Space Management List Based Partitioning Oracle Managed Files Test! Test! Test! SQL create table T ( x, y, z) 2 as select 1,2,3 from dual; SQL create index T_IX on T (x,y); SQL alter table T add constraint C primary key (x,y); SQL alter index T_IX rebuild compress 1; SQL insert into t values (1,2,3); ERROR at line 1: ORA-08102: index key not found, obj# 69517, dba ? “Fixed” Export exp tablespace=(user_data1, user_data2) exports all tables at least partially in a tablespace, and all associated indexes New features of sqlplus only one chance to log in sqlplus -l scott/tiger New features of sqlplus New environmental substitution variable is set to the SID. Set sqlprompt ‘_I CAN’T REMEMBER’ (go back and run ‘define’ inside of sqlplus and you’ll see it) WITH clause for complex subqueries SQL WITH dept_salaries AS ( 2 SELECT dname, SUM(sal) dept_sal 3 FROM emp e, dept d 4 WHERE e.deptno = d.deptno 5 GROUP BY dname), 6 avg_sal AS ( SELECT AVG(dept_sal) asal 7 FROM dept_salaries) 8 9 SELECT * FROM dept_salaries d, avg_sal a 10 WHERE d.dept_sal a.asal 11 ORDER BY d.dname; Row Locking - then SQL select delivery_date from invoice 2 where invoice = 11236 3 for update of delivery date; If the row is locked this waits forever. SQL select delivery_date from invoice 2 where invoice = 11236 3 for update of delivery date NOWAIT; If row is locked, this returns an error. Locking -now SQL select delivery_date from invoice 2 where invoice = 11236 3 for update of delivery date wait 10; If the row is locked, this waits for 10 seconds, then returns error. ERROR at line 1: 30006: resource busy; acquire with WAIT timeout expired Object Level Grants (HOW LONG HAVE WE WAITED FOR THIS ONE, FOLKS!!!) THEN - SQL connect system/password connected SQL create table FRE