oracle基础

第一节
1、sqlplus中：clea scre 清屏；
2、sql语句不区分大小写，可以跨行，但关键词不能跨行；
3、可以进行+-*/运算，可以用()改变顺序；
4、null 是一种没有状态的状态；
5、oracle中的字符串链接用||
 eg:select name||'is id='||id from amgang;
 'is id=' 是oracle中的literal
6、可以用别名a、id alias_id or b、id as alias_id，
 如果要想用大小写，alise用" "括住就ok；
7、用distinct 去掉重复行；
8、isql*plus是个工具，先启动oracle web，输入http://localhost:7777/isqlplus即可；

第二节：
1、c /W%/w%/
2、比较表达式：= > < >= <= <>
 between --- and ---
 in (set)
 like
 is null
   %通所有 _通一个；
   like ‘%x/_y%’ escape ‘/’:显示包括x_y的字符
3、逻辑条件中：先not 、and 、or
4、取反：not in（set）    is not null；

第三节：内部函数
1、single-row 、 mutiple－row function
2、字符函数：
 lower、upper、initcap（单词第一个字母大写）
 concat（||）concat(name1,name2)
 substr(string,start,lenth)start:字串的开始位置;lenth:待匹配字串的长度;
 length(string)
 instr(string,'g') 返回字符的位置
 instr(string,substring,start,occurrence)start:开始的位置，负数从右开始；occurrence：寻找的第几个
 lpad(name,10,'*')从左补齐10个字符
 rpad(name,10,'*')
 trim('H' from 'Hello')=ello//从两端截去相同的字符，两端有多少截去多少，只写字符串就是截去空格。
 ltrim(string,'s')
 rtrim(string,'s')
 ascii(string);如果是一个字符串，该函数将忽略除首字母外的所有字符；该返回值总是以用户使用的字符集为基础的。
 chr(number);
 replace(string,substring[,replace_string]);
3、数字函数
 round(45.926,2)=45.93
 trunc(45.926,2)=45.92
 mod(1600,300)=100
 round(45.926,-1)=50
 round(45.93,-2)=0
 round(55.93,-2)=100
 EXP(exponent) exp(1)=2.71828183
 ln(number)
 abs() absolute
 acos(number)反余弦
 asin(number)反正弦
 atan(number)反正切
 ceil(number)返回结果则是大于等于输入参数的最小整数
 floor(number)返回一个小于或等于给定十进制数的最大整数
 mod(number,divisor)求余，如果除数为0则返回原来的数
 power(number,power)
 sign(number)-1,0,1
 sqrt(number)返回一个数的算术平方根
4、日期函数
 sysdate
 (sysdate-date)/7
 months_between(date1,date2)
 add_months(date,months):months 为正加，为负则减
 next_day(date,weekday)如果weekday是数字，则返回以后几天；如果是星期，则返回那个周几的日期
 last_day(date)
 trunc(date)
 TO_CHAR(SYSDATE,'WW') 该年中第几个星期
 TO_CHAR(SYSDATE,'W') 该月中第几个星期
 to_char(sysdate-1,'d') 一周的周几 decode(to_char(sysdate-1,'d'),1,'一',2,'二',3,'三',4,'四',5,'',6,'六',7,'日')
5、转换函数conversion function
 to_number(string,fmt)
 to_date(string,fmt)
 to_char(date/number,fmt)
 to_char(sysdate,'dd-mm-yyyy')
 to_char(50,'l99.99')=RMB50.00
 TO_CHAR(123.0233,'FM9999999.0099')
6、通用函数
 nvl NVL(EXPR1, EXPR2)   IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1
 nvl2
 nullif
 DECODE(AA﹐V1﹐R1﹐V2﹐R2....defualtvalue) IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 … ELSE RETURN defualtvalue
7、to_char()
 to_char(sysdate,'MONTH')=JANUARY
 to_char(sysdate,'Month')=January
 to_char(sysdate,'month')=january
8、to_lob(long_value)
9、UID函数返回一个整数值。对于当前数据库用户来它是唯一的。
10、位运算 bitand(a,b)

第四节：多表数据访问
1、select t1.name,t2.name from table1 t1,table2 t2 where t1.id=t2.id;
2、jion链接
 不写where子句，即所有都匹配，n1×n2
 左链接：where t1.id(+)=t2.id //把不加＋的都选择上，如果t2没有相应的，就补空格
 右链接：where t1.id=t2.id(+)
3、sqlplus中l显示行，l1显示第一行，ln 显示第n行；
4、input 增加一行
5、a 增加后面的内容

第五节：组函数、多行函数
1、sum、avg、max、min、count、
 count(*) 所有行；
 count(id)当id不为null的所有行
2、 select *
 from table
 where condition
 group by expression
 having expression
 order by colum
第六节：子查询
1、 select * from table
 where colum
 in (select * from table)
2、where and having 里都可以有子查询；

第七节：isqlplus 操作
1、替换变量：用&a 提交时：输入变量信息。
2、define a＝9800
3、undefine
4、重复运用变量：&&a

第八节：数据操作

第九节：创建操作表
1、分系统表和用户表
2、表命名规定：用字母和数字及_$#，但必须以字符为首；
3、create table test(id number(2),name varchar2(255));
4、利用查询创建类似和同意数据表：
 create table test2
 as select * from test;
5、alter
6、add：alter table test add(fname varchar2(255));
7、modify: alter table test modify(fname varchar2(500));
 只能修改类型、长度、default(只影响以后)
8、drop: alter table test drop column fname;
 alter table test set unused column fname;
 alter table test drop unused column;
9、drop table test;
10、rename test to test2;
11、delete from test ;删除时建立日志，并且可以 rollback
12、truncate table test;删除表内容，不建日志，且，不可rollback。
13、注释：comment on table test is 'this is a test';
 comment on column tablename.columnname is 'comments sentence';
 select * from user_tab_comments where comments is not null;
 select * from user_col_comments where comments is not null;
第十节：including constraint
1、constraint 保证表的完整性；
 基于表一级的规则、维护数据表的完整性。
2、约束条件：
 not null；
 unique
 primary key
 foreign key
 check
3、定义约束，是作为表的一部分而存在的。
4、create table test(id int not null,lname varchar2(20),fname varchar2(20),constraint uk_test_1 unique(lname,fname));
5、主键primary key
 alter table test add constraint pk_test primary key(id);
6、外键foreign key
 create table test3 (rid int,name varchar2(20),constraint fk_test3_1 foreign key(rid) references test(id));
 外键只能取主键已经有的值。
7、check 约束，一般where条件的语句都可以用于check约束；
 alter table test3 add contraint ck_test3_1 check(name like 'K%');
8、删除：
 alter table test3 drop constraint pk_test3_1;
 alter table test drop constraint pk_test cascade;(删除主键，同事也删除和他相关的外键用cascade)
9、将不符合要求的数据导入表中；
 A. alter table test disable constraint pk_test;
 B. insert into data
 C. alter table test enable constraint pk_test;
10、cascade constraint
 alter table test drop column id cascade constraint;(删除栏目id及和他相关的约束)
11、查看：
 select constraint_name,constraint_type,search_condition from user_constraints;

第十一节：视图
1、视图是个逻辑结构，并不具有实际的数据；
2、create view testview1 as select * from test;
3、视图作用:
 a、to restrict date access;(没被视图的数据，用视图是看不到的)
 b、to make complex queries easy;
 c、to provide date independence;
 d、to diffrent viewers;
4、create or replace [force] view viewtest2  ……;
 force 可以强制建立一个视图，方便还没有建好的数据先用。
5、set linesize 50 改变在sqlplus中显示的宽度；
6、view 中，如果出现sum(*)可以用别名代替，如：sum(id) as sumid;
7、modify
 create or replace 覆盖修改；
8、with read only 创建的视图只能查看，不能修改。
9、删除：drop view viewtest1；

第十二节：other data object 其他数据对象
1、index、sequence、synonym
2、sequence：自动产生unique numbers
3、create sequence testse1 increment by 1 start with 1;
4、nextval、currval
 select testse1.nextval from dual;
 select testse1.currval from dual;
5、drop sequence testse1;
6、index 索引：书与目录的关系；
 有自己的存储空间；
 加速数据查询、但减慢写的速度。
7、create index testindex1 on test(id);
8、自动建立的索引：包括primary key、unique
 索引字段最好的存储是占表空间的2～4％之间；
 如果大于20%则会急剧加速访问速度。
9、为function_based index 基于函数的索引；
 create index indextest2 on test(upper(lname));
10、drop index indextest1;
11、synonym 同义词：简化对数据对象的访问。
12、create [public] synonym t1 for user2.test;
 select * from t1;
13、drop [public] synonym t1;

第十三节：控制用户存取
1、系统安全、数据安全；schemas 模式；
2、create user amgang identified by  passwd;
3、grant create session to amgang;
 /table/sequence/view/
4、role是一个命了名的权限集合；它可以代表一部分权限的实体。
5、create role testrole1;
 grant create table,create view to testrole1;
 grant select on test to testrole1;
 grant testrole1 to amgang;
6、改变密码:alter user amgang identified by passwd2;
7、只有当前owner才能够授权自己的权限给别人；
8、grant update(id) on test to amgang with grant option;
 with grant option 是说明把自己的权限给了别人，使他人也可以授权此给另外人。
 把自己的权限给第三人。
9、revoke select on test from amgang;
10、database links:
 a database links connection allows local users to access data on a remote database;

第十五节：集合操作
1、union、union all 并集 order by id
2、intersect 交集
3、minus 差集；
4、to_date(null)、to_number(null) 为了匹配；
第十六节：Datetime Functions
1.Objectives
 After completing this lesson,you should be able use the folling datetime functions:
 a.TZ_OFFSET
 b.CURRENT_DATE
 c.CURRENT_TIMESTAMP
 d.LOCALTIMESTAMP
 e.DBTIMEZONE
 f.SESSIONTIMEZONE
 g.EXTRACT
 h.FROM_TZ
 i.TO_TIMESTAMP
 j.TO_TIMESTAMP_TZ
 k.TO_YMINTERVAL
2.Oracle9i Datetime Support
 a.In Oracle9i,you can include the time zone in your date and time data,and provide support for fractional seconds.
 b.Three new data types are added to DATE:
  timestamp
  timestamp with time zone (tstz)
  timestamp with local time zone (tsltz)
 c.Oracle9i proides daylight savings support for datetime data types in the server.
3.TZ_OFFSET
 Display the time zone offset for the time zone 'US/Eastern'
  select tz_offset('US/Eastern') from dual;
 Display the tiem zone offset for the time zone 'Canada/Yukon'
  select tz_offset('Canada/Yukon') from dual;
 Display the time zone offset for the time zone 'Europe/London'
  select tz_offset('Europe/London') from dual;
4.CURRENT_DATE
 Display the current date and time in the session's time zone.
  alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
  alter session set time_zone='-5:0';
  select sessiontimezone,current_date from dual;
 current_date is sensitive to the session time zone.
 The return value is a date in the Gregorian calendar.
5.CURRENT_TIMESTAMP
 Display the current date and fractional time in the session's time zone.
  alter session set time_zone='-5:0';
  select sessiontimezone,current_timestamp from dual;
6.Localtimestamp
 Display the current date and time in the session time zone in a value of timestamp date type.
  alter session set time zone='-5:0';
  select current_timestamp,localtimestamp from dual;
 localtimestamp returns a timestamp value,whereas current_timestamp returns a timestamp with time zone value.
7.dbtimezone and sessiontimezone
 Display the value of the database time zone.
  select dbtimezone from dual;
 Display the value of the session's time zone.
  select sessiontimezone from dual;
8.extract
 Display the YEAR component from the sysdate.
  select extract(YEAR from sysdate) from dual;
 Display the MONTH component from the HIRE_DATE for those employees whose MANAGER_ID is 100.
  select last_name,hire_date,extract(MONTH from HIRE_DATE)
  from employees
  where manager_id=100;
9.TIMESTAMP conversion using from_tz
 Display the timestamp value '2000-03-28 08:00:00' as a timestamp vith time zone value.
  select from_tz(timestamp '2000-03-28 08:00:00','3:00') from dual;
 Display the timstamp value '2000-03-28 08:00:00' as a timestamp with time zone value for the time zone region 'Australia/North'
  select from_tz(timestamp '2000-03-28 08:00:00','Australia/North') from dual
10.string to timestamp conversion using to_timestamp and to_timestamp_tz
 Display the character string '2000-03-28 08:00:00' as a timestamp value.
  select to_timestamp('2000-03-28 08:00:00','yyyy-mm-dd hh:mi:ss') from dual
 Display the character string '2000-03-28 08:00:00 -8:00' as a timestamp with time zone value.
  select to_timestamp_tz('2000-03-28 08:00:00 -8:00','yyyy-mm-dd hh:mi:ss tzh:tzm') from dual;

11.time interval conversion with to_yminterval
 Display a date that is one year two months after the hire date for the employees working in the department with the department_id 20
  select hire_date,hire_date+to_yminterval('01-02') as hire_date_ymininterval
  from employees
  where department_id=20;

第十七节：Enhancements to the GROUP BY Clause 从句，分句
1.After completing this lession,you should be able to do the following:
 a.Use the ROLLUP operation to produce subtotal values
 b.Use the CUBE operation to produce crosstabulation values
 c.Use the GROUPING function to identify the row values created by ROLLUP or CUBE
 d.Use GROUPING SETS to produce a single result set
2.Use the HAVING clause to specify which groups are to be displayed.
  You further restrict the groups on the basis of a limiting condition.
 select stor_id,sum(qty)
 from sales
 group by stor_id
 having sum(qty)>15
3.GROUP BY with ROLLUP and CUBE Operators
 a.Use ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns.
 b.ROLLUP grouping produces a results set containing the regular grouped rows and the subtotal values.
 c.CUBE grouping produces a results set containing the rows from ROLLUP and cross-tabulation rows.
 select stor_id,ord_num,sum(qty)
 from sales
 group by rollup (stor_id,ord_num)
 //按照group by condition 从右向左再聚合，形成超级聚合。

 select stor_id,ord_num,sum(qty)
 from sales
 group by cube(stor_id,ord_num)
 //按照group by condition 从左向右再聚合，形成超级聚合。
4.GROUPING Function
 select [column,] group function(column),grouping(expr)
 from table
 where condition
 group by [rollup][cube] group_by_expression
 having having_expression
 order by column
 
 The grouping function can be used with either the cube or rollup operator.
 Using the grouping function,you can find the groups forming the subtotal in a row.
 Using the grouping function,you can differentiate stored null balues from null values created by rollup or cube.
 The grouping function returns 0 or 1.
7.grouping sets
 grouping sets are a further extension of the group by clause.
 You can use grouping sets to define multiple groupings in the same query.
 The oracle Server computes all groupings specified in the grouping sets clause and combines the results of individual groupings with a union all operation.
 Grouping set efficiency:
  only on pass over the base table is required.
  There is no need to write complex UNION statements.
  The more elements the grouping sets have,the greater the performance benefit.
 select department_id,job_id,manager_id,avg(salary)
 from employees
 group by grouping sets((department_id,job_id),(job_id,manager_id));
 先以department_id，job_id查询结果，然后union 以job_id,manager_id查询结果
8.Composite columns
 A composite column is a collection of columns that are treated as a unit.
  rollup(a,(b,c),d)
 To specify composite columns,use the Group BY clause to group columns within parentheses so that the Oracle server treats them as a unit while computing rollup or cube operations.
 When used with rollup or cube,composite columns would mean skipping aggregation across certain levels.
 select stor_id,ord_num,sum(qty)
 from sales
 group by rollup((stor_id,ord_num));
9.Concatenated Groupings
 Concatenated groupings offer a concise way to generate useful combinations of groupings.
 To specify concatenated grouping sets,you separate multiple grouping sets,rollup,and cube operations with commas so that the oracle server combines them into a single group by clause.
 The result is a cross-product of groupings from each grouping set.
 group by grouping sets(a,b),grouping sets(c,d)
 example:
  select department_id,job_id,manager_id,sum(salary)
  from employees
  group by department_id,rollup(job_id),cube(manager_id);
第十八节：Advanced Subqueries
1.After completing this lesson,you should be able to do the following:
 Write a multiple-column subquery
 a.Write a subquery in a form clause
 b.Use scalar subqueries in SQL
 c.Describe the types of problems that can be solved with correlated subqueries
 d.write correlated subqueries
 f.Update and delete rows using correlated subqueries
 g.Use the EXISTS and NOT EXISTS operators
 h.Use the WITH clause
2.What is a subquery?
 A subquery is a select statement embedded in a clause of another SQL statement.
3.Subqueries
 select select_list
 from table
 where expr operator(select select_list from talbe);
 a.The subquery(inner query) executes once before the main query.
 b.The result of the subquery is used by the main query(outer query).
4.Column Comparisons
 Pairwise comparisons
 Nonpairwise comparisons
5.select t1.stor_id,t1.qty,t2.avgqty
 from sales t1,(select stor_id,avg(qty) from sales group by stor_id) t2
 where t1.stor_id=t2.stor_id
 and t1.qty>t2.avgqty;
6.Scalar Subquery Expressions
 a.A scalar subquery expression is a subquery that returns exactly one column value from one row.
 b.Scalar subqueries were supported in Oracle8i only in a limited set of cases,for example:
  select statement(from and where clauses)
  values list of an insert statement
 c.in oracle9i,scalar subqueries can be used in :
  condition and expression part of decode and case
  all clauses of select except group by
 scalar subqueries in case expressions
  select employee_id,last_name,
  (case
   when department_id=
    (select department_id from departments where location_id=1800)
   then 'Canada'
   else 'USA'
   end
  ) location
  from employees;
 scalar subqueries in order by clause
  select employee_id,last_name
  from employees e
  order by (select department_name
   from departments d
   where e.department_id=d.department_id);
7.Correlated subqueries
 Correlated subqueries are used for row-by-row processing.Each subquery is executed once for every row of the outer query.
8.Using the exists operator
 The exists operator tests for existence of rows in the results set of the subquery.
 if a subquery row value is found:
  the search does not coninue in the inner query
  The condition is flagged true
 if a subquery row value is not found:
  The condition is flagged false
  The search continues in the inner query
8.Correlated update
 Denormalize the EMPLOYEES table by adding a column to store the department name.
 Populate the table by using a correlated update.
  alter table employees
  add(department_name varchar2(14));
  update emplyees e
  set department_name=
   (select department_name
   from departments d
   where e.department_id=d.departtment_id);
9.Correlated DELETE
 delete from table1 alias1
 where column operator
  (select expression
  from table2 alias2
  where alias1.column=alias2.column);

 Use a correlated subquery to delete rows in one table based on rows from another table.
10.The WITH clause
 Using the with clause,you can use the same query block in a select statement when it occurs more than once within a complex query.
 The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace.
 The WITH clause improves performance
  With
   dept_costs as(
    select d.department_name,sum(a.salary) as dept_total
    from employees e,departments d
    where e.department_id=d.department_id
    group by d.department_name),
   avg_cost as(
    select sum(dept_total)/count(*) as dept_avg
    from dept_costs)
  select *
  from dept_costs
  where dept_total>(select dept_avg from avg_cost)
  order by department_name;

第十九节：分层查询(Hierarchical retrieval)
1.select [level],column from table
 [where conditions]
 [start with conditions]//起始条件
 [connect by prior conditions];//连接条件
2.select employeeid,lastname,firstname,reportsto
 from employee
 start with reportsto=5
 connect by prior reportsto=employeeid;
第二十节：oracle9i extensions to DML and DDL Statements
1.DML:insert,delete,update
2.After completing this lesson,you should be able to do the following:
 Describe the features of multitable inserts//基于多表数据插入
 Use the following types of multitable inserts
  unconditional insert//无条件插入
  pivoting insert//旋转插入
  conditional all insert//
  conditional first insert//
 create and use external tables//
 name the index at the time of creating a primary key constraint
 //
3.Overview of multitable insert statements
 The insert...select statement can be used to insert rows into multile tables as part of a single DML statement.
 Multitable insert statement can be used in data warehousing systems to transfer data from one or more operational sources to a set of target tables.
 They provide significant performance improvement over:
  single DML versus multiple Insert...select statements
  single DML versus a procedure to do multiple inserts using IF...THEN syntax
4.Types of multitable INSERT Statements
 unconditional INSERT
 Conditional ALL INSERT
 Conditional FIRST INSERT
 Pivoting INSERT
5.Multitable INSERT Statements
 Syntax
  insert [all] [conditional_insert_clause]
  [insert_into_clause values_clause] (subquery)
 conditional_insert_clause
  [all] [first]
  [when condition then] [insert_into_clause values_clause]
  [else] [insert_into_clause values_clause]
6.insert all
 into emp_name values(emp_id,lname,fname)
 into emp_addr values(emp_id,address)
 select emp_id,lastname lanme,firstname fname,address
 from employees
 where employeeid>3;
 //select语句是必须的
7.insert all
 when sal>10000 then
  into sal_history values(empid,sal)
 when mgr>200 then
  into mgr_history values(empid,mgr)
 select employee_id empid,salary sal,manager_id mgr
 from employees
 where employee_id >200;
8.insert all //把一个大表里的数据分析插入不同的表中。
9.insert first
 when lastname like 'B%' then
 into emp_name values(employeeid,lastname,firstname)
 when lastname like 'W%' then
 into emp_addr values(employeeid,address)
 else
 into emp_name2 values(employeeid,lastname,firstname)
 select employeeid,lastname,firstname,address
 from employees where employeeid >2;
10.列变行(Pivoting insert)
 insert all
 into sales_info values(employee_id,week_id,sales_mon)
 into sales_info values(employee_id,week_id,sales_tue)
 into sales_info values(employee_id,week_id,sales_wed)
 into sales_info values(employee_id,week_id,sales_thur)
 into sales_info values(employee_id,week_id,sales_fri)
 select employee_id,week_id,sales_mon,sales_tue,sales_wen,sales_thur,sales_fri
 from sales_source_date;
11.External tables
 external tables are read-only tables in which the data is stored outside the database in flat files.
 the metadata for an external table is created using a create table statement.
 with the help of external tables,oracle data can be stored or unloaded as flat files.
 the data can be queried using SQL,but you cannot use DML and no indexed can be created.
12.create table test_index_table
 (id int not null primary key using index(create index t_index1
 on test_index_table(id)),
 lname varchar2(255),
 fname varchar2(255));

第一课：oracle architectural Components
1.oralce including : instance,sga
2.sga system global area
 1.shared pool
 2.database buffer cache
 3.redo log buffer cache
 4.larger pool
 5.java pool
3.instance
 1.back ground process
  1.POMN process cleanup
  2.SMON system monitor process
  3.DBWR db writer process
  4.LGWR redo etc.
  5.CKPT checkpoint
  6.others(reco distrivuted recovery)
 2.memory structure
      SGA
  1.shared pool
  2.database buffer cache
  3.redo log buffer cache
  4.larger pool
  5.java pool
      PGA program global area
   an oracle instance:
 1.is a means to access an oracle database
 2.always opens one and only one database
 3.consists of memory and process structures
4.user process--->server process ----database
5.oracle server consists of an oracle instance and an oracle database
6.database:is a collection of data that is treated as a unit
 1.data file
 2.log file
 3.control file
7.connecting to an oracle instance consistes of
 establishing a user connection and creating a session
8.sga_max_size 为总大小
9.share pool
  The shared pool is used to store the most recently excuted SQL statements and the most recently used data definitions.
 it consists of two key perfrmatce-related memory structured:
  library chache
  data dictinary cache＝row cache
 Sized by teh parameter shared_pool_size.
  alter system set shared_pool_size=64m;
10.library cache
   The library cache stores information about the most recently used SQL and PL/SQL statments.the library cache:
 enables the sharing of commonly used statments
 is managed by a least recently used(LRU) algorithm
 consists of two structures:
  shared SQL area
  shared PL/SQL area
 has its size determined by the shared pool sizing
11.data dictionary cache
   The data dictionary cache is a collection of the most recently used definitions in the database.
 it includes information about database files,tables,indexes,columns,privileges,and other database objects.
 during the parse phase,the server process looks at the data dictionary for information to resolve object names and validate access.
 caching the data dictionary information into memory improves response time on queries.
 size is determined by the shared pool sizing.
12.databae buffer cache
   The database buffer cache stores copies of data blocks that have been retrieved from the data files.
 it enables great performance gains when you obtain and update date.
 it is managed throgh a least recently used(LRU) algorithm.
 DB_BLOCK_SIZE determines the primary block size.
 consists of independent sub-caches:
  db_cache_size
  db_keep_cache_size
  db_recycle_cache_size
 database buffer cache can be dynamically resized to grow or shrink using alter system.
  alter system set db_cache_size=96M;
 db_cache_advice can be set to gather statistics for predicting different cache size behavior.
  alter system set db_cache_advice=on
13.redo log buffer cache
   The redo log buffer cache records all changes mad to the database data blocks.
 its primary purpose is recovery.
 changes recorded within are called redo entries.
 redo entries contain information to reconstruct or redo changes.
 size is defined by log_buffer.
14.large pool
   The large pool is an optional area of memory in the SGA configured only in a shared server environment.
 it relieves the burden placed on the shared pool.
 this configured memory area is used for session memory(UGA),I/O slaves,and backup and restore opertions.
 Unlike the shared pool,the large pool does not use an LRU list.
 sized by large_pool_size.
  alter system set large_pool_size=64M;
15.java pool
   the java pool services the parsing requirements for java commands.
 required if installing and using java.
 it is stored much the sam way as PL/SQL in database tables.
 it si sized by the java_pool_size parameter.
第二课：Getting Started With the oracle server (manager tools)
1.tools
 OUI:oracle universal installer
 DBCA:oracle database configuration assistant
 password file utility
 SQL*Plus
 OEM:oracle enterprise manager
2.optimal flexible architecture (OFA)
 oralce's recommended standard database architecture layout
 OFA involes three major rules
  establish a directory structure where any database file can be stored on any disk resource
  separate objects with different behavior into different tablespaces
  maximize database reliability and performance by separating database components across different disk resources
3.database administrator users
 sys:change_on_install Owner of the database data dictionary
 system:manager Owner of additional internal tables and views used by oracle tool.
4.
第三课：managing an oracle instance
1、spfile system parameter file 系统参数文件
2、initsid.ora
3、oracle 启动读取参数文件顺序
 spfilesid.ora----spfile.ora----initsid.ora
 在liunx下存放在：oraclehome/dbs/
4、系统参数查看：
 v$system_parameter
 isses_modifiable 是能否修改session参数的属性
 issys_modifiable 是能否修改system参数的属性
5、oracle启动步骤：
 shutdown--------------nomount------------------mount-------------------open
  start nomount  alter database mount alter database open
  read init parameters read control file open all files
  start instance  opened for instance for instance
6、创建表空间
 create tablespace tablespacename datafile '/home/oracle/oracledata/test.dbf' size 500m;
7、删除表空间
 drop tablespace tablespacename including contents and datafiles;
8、sqlplus>alter session set nls_language='american';
    alter session set nls_language='simplified chinese';
9、oracle的四种关闭
 1)normal 等待所有事务完毕和链接完毕
 2)transactional 等待所有事务完毕，链接自动关闭
 3)immediate 把所有正在做的事务rollback，关闭链接
 4)abort  只关闭instance 最快，但不完全
10、OMF oracle manager file oracle文件结构

第四课：creating a database
1、 managing a database system
 dbca fro creating a database
 oracle database migration assistant 数据移植帮手
2、to create a new database you must have the following:
 1)a privileged account anthonticated in one of the following ways:
  a:By the operating system
  b:Using a password file
 2)sufficient memory to start the instance;
 3)sufficient disk space for the planned database;
3、planning database file location
 1)最少保持2个controlfile的copy，而且存放在不同物理文件上；
 2)多工重做日志组，存放到不同物理设备上。
4、O S Environment
 oracle_base/oralce_home/oracle_sid/ora_nls33/paht/ld_library_path
 ora_nls33：对于不同字符集的设置。
5、全局数据库名称＝SID.DOMAIN
6、手工创建database步骤：
 1)get a instance and database name;
 2)choose character set;
 3)set os variables;
 4)edit/create the initialization parameter file;
 5)start the instance(nomount)
 6)execute the create database command;
 7)run scripts to generate the data dictionary and acomplish post cration steps;

第五课：data dictionary contents and usage
1、data dictionary 是在创建数据库时，oracle创建内部的系统表，read-only,加密的
2、创建时创建两部分：
 1)data dictionary tables;
 2)dynamic performance table；动态性能表、内存表
3、data dictionary
 1)describes the database and its objects
 2)including two types of objects
  base tables;
  data dictionary view
4、select * from dba_users;
   select * from dba_bojects;
5、data dictionary 是为了方便管理database而引入的概念。
6、data dictionary using
 1)oracle server uses it to find information about:
  a.users(关于用户的信息)
  b.schema objects(数据对象的信息)
  c.storage structures(存储结构信息)
 2)the oracle server modifies it when a DDL statement is executed;
 3)users and DBAs can use it as a read-only reference for information about the database;
7、数据字典的类别：
 1)DBA DBA_XXX(访问所有，但必须具有DBA权限的用户才可以运行)
 2)ALL ALL_XXX(访问owner和自己可以访问的数据)
 3)USER USER_XXX(自己owner的)
8、动态性能表：启动后系统自动建立，关闭时就不存在了。
9、查看数据字典：
 select * from dict(dictionary);
10、查看动态性能表：
 select * from v$fixed_table;

第六课：maintaining the control file
1、control file 记录了database结构信息的二进制文件，包括表空间、数据文件、临时数据文件等信息；
2、the control file is a binary file that defines the current state of the physical database
 1)loss of the control file requizes recovery;
 2)is read at nount stage;
 3)is required to operate;
 4)is linked to a single database;
 5)shoud be multiplexed;
 6)maintains integrity of database;(维护database的完整性)
 7)sized mitially by create database;
3、a control file contains the following entries:
 1)database name and identifier;
 2)time stamp of database creation;
 3)tablespace names;
 4)names and locations of datafiles and redolog files;
 5)current redolog file sequence number;
 6)checkpoint information;
 7)begin and end of undo segments;
 8)redo log archive information;
 9)backup inoformation;
4、Mutiplexing the control file using spfile
 1)alter the spfile
  sql>alter system set control_files='/home/oracle/control01.ctl',
         '/home/oracle/control02.ctl'
   scope=spfile;
 2)shutdown database
 3)create additional control files
  cp /home/oracle/control01.ctl /home/oracle/control02.ctl
 4)startup database;
5、Mutiplexing the control file using init.ora
 1)shutdown database
 2)copy the exiting control file to a new name and location
  cp control01.ctl /disk3/control02.ctl
 3)add the new control file to init.ora
 4)startup database;
6、obtaining control file information
 v$controlfile
 v$parameter where name like 'control';
 v$controlfile_record_section
 show parameter control
7、backup
 1)shutdown database then copy controlfile for backup;
 2)alter database backup controlfile to 'c:/amgang/control.bak';
 3)alter database backup controlfile to trance;(文件存放在udump下，是一个文本文件，记录脚本)

第七课：Maintaining Redo Log Files
1.After completing this lesson,you should be able to do the following:
 Explain the purpose of online redo log files
 Outline the structure of online redo log files
 Control log switches and checkpoints
 Multiplex and maintain online redo log files
 Manage online redo logs files with OMF
2.Using Redo Log Files
 Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure.
 Redo log files are organized into groups.
 An Oracle database requires at least two groups.
 Each redo log within a group is called a member.
 archive log list (view the model of oarlce)
 alter system archive log start; (start archive log for auto)
3.How Redo Logs Work
 Redo logs are used in a cyclic fashion.
 When a redo log file is full,LGWR will move to the next log group.
  This is called a log switch
  Checkpoint operation also occurs
  Information is written to the control file
  Information is written to the Data file
 alter system switch logfile; (change the writting log group)
 alter system checkpoint;     (checkpoint for orcle system tongbu)
4.Forcing log Switches and Checkpoints
 Log swithces can be forced using the ALTER SYSTEM SWRICH LOGFILE command.
 Checkpoints can be forced using:
  Setting FAST_START_MTTR_TRAGET parameter
   mttr:mean time to recovery(recovery的间隔时间)
   In oracle system,setting fast_start_mttr_target equalse setting follow three parameter fast_start_io_target and log_checkpoint_interval and log_checkpoint_timeout;(mttr会翻译成其他三个参数)
  ALTER SYSTEM CHECKPINT command
   alter system checkpint;
5.auto-checkpoint command;
 alter tablespace users begin backup;
 alter tablespace users offline;
 drop table;
 truncate talbe;
6.Adding Online Redo Log Groups
 alter database add logfile group 3
 ('$home/oradata/u01/log3a.rdo',
  '$home/oradata/u02/log3b.rdo')
 size 1m;
 oralce 建议 每个组的member相同，且大小相同
7.Adding Online Redo Log Members
 alter database add logfile memeber
 '$home/oradata/u04/log1c.rdo' to group 1,
 '$home/oradata/u04/log2c.rdo' to group 2,
 '$home/oradata/u04/log3c.rdo' to group 3;
 member的大小是第一次确定的，以后再加member时默认为第一次的member大小
8.Dropping Online Redo log Groups
 alter database drop logfile group 3;
 文件系统中的文件并没有被删除掉
 restricted:
  current log group not be del
  active log group not be del
  not archived log group not be del
  at least one member per group
9.Dropping Online Redo Log Members
 alter database drop logfile member
 '$home/oradata/u04/log3c.rdo';
10.Clearing,Relocating,or Renaming Online Redo Log Files
 Clearing online redo log files:(reinit log file)
  alter database clear logfile '$home/oradata/u01/log2a.rdo';
  alter database clear unarchived logfile group 5;(清空没有归档的日志)
   when u do this, u must do full db backup!because it have some breakpoint and no for recovery for db;
 Relocating or renaming online redo log files can be accomplished by adding new members and dropping old members.
 move or rename logfile--os level
 alter database rename file '***' to '***'
 alter database rename file 'd:/oracle/oradata/fox/redo05.ora'
 to 'd:/oracle/oradata/fox/redo05a.ora';
11.Managing Online Redo Logs with OMF
 A complete group can be added with no file specification:
  alter database add logfile;
 If a group is dropped,all the corresponding OMF files are deleted at the OS level:
  alter database drop logfile group 3;
12.Obtaining group and Member Information
 Information about group and members can be obtained by querying the data dictionary.
 V$log
  log group information
  status:current/active/inactive/unused
 V$logfile
  status:blank(in use)/stale(imcomplete)/delete
13.Archived Redo Log Files
 Filled online redo log files can be archived.
 Two advantages exist to archiving redo logs:
  Recovery:A database backup,together with online and archived redo log files can quarantee recovery off all committed transactions.
  Backup:Can be performed while the database is open.
 By default a database is created in noarhivelog mode.
14.Archived Redo Log Files
 Archiving redo log files is accomplished by ARCn(Archiver) or manually through SQL statements.
  archive log start 启动自动归档模式
  alter system archive log all
 An entry in the control file recording the archive log name,log sequence number,and high and low SCN number is made whenever a redo log is successfully archived.
 A filled redo log file cannot be reused until a checkpoint has taken place and the redo log file has been backed up the ARCn process.
 Archived redo log files can be multiplexed.
 Archived redo log files must be maintained by the DBA.
15.archivelog--noarchivelog
 1) shutdown immediate
 2)startup mount:startup pfile=d:/oracle/ora90/database/initfox.ora mount
 3)alter database archivelog/noarchivelog
 4)alter database open;
 5)full db backup
16.日志挖掘 logmnr
 1)set directory 设置日志目录 utl_file_dir
 2)restartup
 3)create directory file dbms_logmnr_d.bulid
 4)add log file dbms_logmnr.add logfile
   dbms_logmnr.removefile
 5)start logmnr
  dbms_logmnr.start_logmnr
 6)v$logmnr_content  sqlredo/sqlundo

 desc dbms_logmnr_d
 select * from v$log;
 execute dbms logmnr_d.bulid('foxdict.ora',utl_file_dir);
 execute dbms_logmnr.add logfile('d:/oracle/oradata/fox/redo02.log',dbms_logmnr.new);
 execute dbms logmnr.start logmnr(dictfilename=>'d:/oracle/admin/fox/cdump/fnxdict.oar');

第八课：managing tablespaces and datafiles
1、logical storage
 tablespace--------logical area
     |
 segment-----------specific logical structure occupied space collection
     |    table/index/temp/rollback 段
 extent------------分区，分配单位，allocate unit 是block的整数倍
     |
 block-------------use&store unit
2、控制用户空间使用：
 alter user amgang quota 10m on users;
3、创建表空间：
 create tablespace ***
 [datafile '***']
 size ***mb[kb]
 extend management [local/dictionary]
 default storage(***)
4、undo segment
 create undo tablespace test_undo
 datafile '/home/oracle/oradata/test_undo01.dbf' size 10m
 extend management local;
5、temporary tablespace
 create temporary tablespace test_temp
 tempfile '/home/oracle/oradata/test_temp01.dbf' size 10m
 extend management loacl;
 排序，如果内存不够用，则用temp空间；
6、restrictions on default temporary tablespace
 1)it cannt be dropped until after a new default is made available;
 2)it cannt be take offline;
 3)you cannt alter the default temporary tablespace to a permanent tablespace;
7、offline status
 alter tablespace amgang offline;
 alter tablespace amgang online;
 为了部分数据修复、部分数据移动时；
 cannt offline tablespace:
  1)system
  2)undo tablespace
  3)default temporary tablespace
8、read-only tablespace
 alter tablespace amgang read only;
 可以读、可以drop表(因为系统表可以被修改)、可以把read only的数据移到cd－rom上；
9、alter tablespace1 rename tablespace2;
10、drop tablespace
 drop tablespace amgang
 including contents and datafiles;contents:删除内容、datafiles：删除数据文件；
11、resizing a tablespace
 1)automatically
  alter database datafile '/home/oracle/oradata/test.dbf'
   autoexend on;
 2)manually
  alter database datafile '/home/oracle/oradata/test.dbf'
   resize 50m;(可以改大，也可以改小)
  alter database amgang
   add datafile '/home/oracle/oradata/test2.dbf' size 50m;(增加方式)
12、moving data files
 1)the tablespace must be offline;
 2)the target data files must exist;
 alter tablespace amgang
 rename datafile '/home/oracle/oradata/test3.dbf'
 to '/home/oracle/oradata/test4.dbf';
13、对于system表空间
 1)the database must be mounted;
 2)the target datafile must exist;
 alter database rename
 file '/home/oracle/system01.dbf'
 to '/home/oracle/system02.dbf';
14、查看
 DBA_tablespaces、v$tablespace
 DBA_data_files、v$datafile
 DBA_temp_files、v$tempfile

第九课：storage structure and relationships
1、oracle9i支持不同表空间使用不同块大小；
2、type of segments:
 1)table;
 2)table partition;(大表分放在物理上的小表)
 3)cluster(组合在一起放在一个物理段上)
 4)index
 5)index-organized table
 6)index partition
 7)undo segment (存放old value)
 8)temporary segment (支持sort)
 9)lob segment
 10)nested table(嵌套表)
 11)bootstrap segment(创建数据库时创建的，初始化时使用，用完drop掉)
3、storage clause preadence(存储参数优先级)
 oracle default
 table space
 segment          下面的会覆盖上面的参数
 create table kong.test10(id int) tablespace kong storage(initial 100k next 100k);
4、extent allocation and deallocation
 allocated when the segment is:
  created
  extended
  altered
 deallocated when the segment is:
  dropped
  altered
  truncated
 alter table kong.test
 allocate extent(size 1m datafile '/home/oracle/oradata/kong.dbf);
 分配时，必须是段位于的那个表空间；
 alter table kong.test deallocate unused;
5、database block
 minimum unit of i/o (i/o读写的最小单位)
 consists of one of more os blocks
 set at tablespace creation
 DB_BLOCK_SIZE is the default block size;
 指定对块大小的缓存区大小
 alter system set db_8k_cache_size=10m;
 header:top down
 freedate
 date:bottom up
 row migration:行迁移，当当前块放不下该行时，进行。
6、view
 dba_extents dba_free_space
 dba_segments dba_data_files
第十课：managing undo data
1.undo =rollback
 1)automatic undo management
 2)manual undo management
2.undo segment purpose
 1)transaction rollback
 2)read consistency(读一致性)
 3)transaction recovery(没有commit时，掉电重起是恢复)
3.types of undo segment
 1)system
  used for objects in the system tablespace
 2)non-system
  used for objects in other tablespace
 3)deferred
  used when tablespace are take offline immidiate,temporay,or for recovery.
 recover datafile '/home/oracle/oradata/oradate01.dbf'
4.Automatic Undo managment concepts
 1)undo data is managed using an undo tablespace
 2)you allocate one undo tablespace per instance with enough space
 3)the oracle server automatically maintains undo data within the undo tablespace
5.automatic undo manaement configuration
 configure two parameters in the initializatioon file:
  undo_management:auto or manual
  undo_table(任何时候只能有一个被激活)
6. create undo tablespace undotbs1
 datafile '/home/oracle/oradata/undotbs1.dbf' size 10m;
 alter tablespace undotbs1
 add datafile '/home/oracle/oradata/undotbs2.dbf' size 10m;
 drop tablespace undotbs1;(undotbs1必须不能被使用)
7.undo_retention:保存旧数据时间
 execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm);
 alter session set nls_language=american;
 execute dbms_flashback.disable;
8.view
 dba_rollback_segs
 v$rollname
 v$rollstat(统计信息)
 v$undostat
 v$session
 V$transaction
第十一课：Managing Tables
1.Objectives
 After completing this lesson,you should be able to do the following:
 Identify the various methods of storing data
 Outline Oracle data types
 Distinguish between an extended versus a restricted ROWID
 Outline the structure of a row
 Create regular and temporary tables
 Manager storage structures within a table
 Reorganize,truncate,drop a table
 Drop a column with a table
2.

第十三课：maintaining data integrity
1.After completing this lesson,you should be able to do the following:
 a.Implement data integrity constraints
 b.Maintain integrity constraints
 c.Obtain constraint information from the data dictinary
2.完整性规则：
 1)域domain－－column
 2)实体entity－－row
 3)参照reference
3.实现完整性的三种方式：
 1)利用application code 保证数据的完整性
 2)利用database trigger
 3)利用database constraints
4.Types of Constraints
 constraint description
 NOT NULL Specifies that a column cannot contrain null values
 UNIQUE  Designates a column or combination of columns as unique
 PRIMARY KEY Designates a column or combination of columns as the table's primary key
 FOREIGN KEY Designates a column or combination of columns as the foreign key in a referential integrity constraint
 CHECK  Specifies a conditin that each row of the table must satisfy
   check 约束不可以用子查询和系统参数
5.Constraint States
 1)disable novalidate 新旧数据都不进行检测
 2)disable validate 新数据不检测，检测旧数据
 3)enable novalidate 检测新数据，不检测旧数据
 4)enable validate 同时检测新旧数据
6.deferred(COMMIT) and nodeferred(immediate)(DML)
 何时进行验证constraint
 deferred是commit后才验证，nodeferred是执行即验证
 alter table sales
 add constraint ck_sales_1 check(qty>8)
 initially deferred;
 --initially immediate;
7.Defining Constraints as Immediate or Deferred
 Use the set constraints statement to make constraints either dererred or immediate
 The alter session statement also has clauses to set constraints to deferred or immediate
 alter session set constraints deferred
 alter session set constraints immediate
8.Foreign key considerations
 Desired Action   Appropriate Solution
 Drop parent table  Cascade constraints
 Truncate parent table  Disable or drop foreign key
 Drop tablespace containing parent table  Use the cascade constraints clause
 Perform DML on child table Ensure the tablespace containing the parent key key is online
9.Guidelines for defining constraints
 Primary and unique constraints:
  Place indexes in a separate tablespace
  Use nonunique indexes if bulk loads are grequent
 Self-frferencing foreign keys:
  Define or enable foreign keys after initial load
  Defer constraint checking
  allowable NULL
 No locks on talbe
 primary and unique keys must use nonunique indexes
  alter table hr.departments
  enable novalidate constraint dept_pk;
 Locks table
 Can use unique or nonunique indexes
 Needs valid talbe data
  alter table hr.employees
  enable validate constraint emp_dept_fk;
10.Using the EXCEPTIONS table
 Create the EXCEPTIONS table by runing the utlexcpt.sql script
 Execute the alter table statement with exceptions option
 Use subquery on Exceptions to locate rows with invalid data
 Rectify the errors
 Reexecute alter table to enable the constraint.
11.Obtaining Constraint Information
 Data Dictionary Views
 DBA_CONSTRAINTS
 DBA_CONS_COLUMNS
 

第十七课：managing roles
1.role是命了名的权限的集合。
2.benefits of roles
 1)easier privilege management
 2)dynamic privilege management(set role role1)
 3)selective availability of privileges
 4)can be granted through the operating system
 5)improved performance
3.create role role1
 [identified by password]
4.predefined roles
 connect,resource,dba
 exp_full_database
 imp_full_database
 delete_catalog_role 对于系统表
 execute_catalog_role 运行标准的数据包
 select _catalog_role 访问数据字典表
5.modifying roles
 alter role roletest identified by password
 alter role roletest identified externally(外边系统验证)
 alter role roletest not identified
6.assigning roles
 grant role1 to amgang [with admin option];(具有可以给别人权限的人)
 system:
 grant create table to role1;
 grant create any table to role1;
 object:
 grant select ,update on tablename to role1;
 grant update(id,name) on test to role1;
 任何一个角色都不具有拥有者；
7.establishing default roles
 alter user amgang default role role1,[role2,...];
 alter user amgang default role all
 alter user amgang default role all except role1;
 alter user amgang defualt role none;
8.application roles
 application roles can be enabled only by authorized pl/sql package
 the using package clause creates an application role.
9.enabling and disabling roles
 disable a role to revoke the role from a user temporarily
 enable a role to grant it temporaryity
 the set role command enables and disable roles;
 default roles are enabled for a user at login.
 a password may be required to enable a role.
10.enaling and diabling roles
 set role role1;
 set role role1 identified by password;
 set roll all;
 set roll all except role1;
 set roll none;
11.removing roles from users
 revoke role1 from amgang;
12.removing role
 drop role role1;
13.view
 dba_roles
 dba_role_privs
 role_role_privs
 dba_sys_privs
 role_sys_privs
 role_tab_privs
 session_roles
第18课：using globalization suppert
1.os,system; for client view
  database; for storage
2.NLS:nation language support
3.globalization support features
 1)language support
 2)territery support
 3)character set support
 4)linguistic sorting
 5)message support
 6)date and time formats
 7)numeric formats
 8)monetary formats
4.al16utf16 性能优势
  utf8  控件优势
5. initialization parameter
 enviroment parameter
 session parameter
6.specitying language-dependent behavior for the server
 NLS_language sepecifies
  the language for messages;
  day and month names;
  symbols for A.D,B.C,A.M,P.M
  the default sorting mechanism
 nls_territery specifies:
  day and week number
  default date format,decimal character,group separaor,and the default ISO and local currency symbols
 nls_instance_parameters
7.sepcifying language-dependent behavior for the session
 enviroment variable
  nls_lang=French_France.UTF8
 additional enviroment varibles:
  nls_date_format
  nls_date_language
  nls_sort
  nls_numeric_characters
  nls_currency
  nls_iso_currency
  nls_calendar
8.specitying language-dependent behavor for the session
 alter session set nls_date_format='dd.mm.yyyy';
 dbms_session.set_nls('nls_date_fromat','''dd.mm.yyyy''');
9.obtaining information about charater set
 nls_database_parameters
  parameter(nls_characterset,nls_nchar_characterset)
  value
10.obtaining information about nls settings
 nls_instance_parameters
 nls_session_parameters
 v$nls_valid_values
  parameter(language,sort,territory,characterset)
  value
 v$nls_parameters
  parameter(nls session parameters,nls_characterset)
  value
 

other:
1.user_source
 显示所有触发器、过程、函数；
2.user_triggers
 显示触发器的相关详细内容；
3.user_procedurces
4.字符串的模糊比较
   ORACLE里用 字段名 like '%字符串%' 但这种方法不能使用索引, 速度不快
   用字符串比较函数 instr(字段名,'字符串')>0 会得到更精确的查找结果
5.date dt=TIMESTAMP '2004-7-20 15:20:07'
6.查看当前数据库的字符集
 select userenv('language') from dual;
 select userenv('lang') from dual;
7.IS NULL和IS NOT NULL
 如果某列存在NULL值，即使对该列建立索引也不会提高性能
8.为不同的工作编写不同的SQL语句块
 为完成不同的工作编写一大块SQL程序不是好方法。它往往导致每个任务的结果不优化。若要SQL完成不同的工作，一般应编写不同的语句块比编写一个要好.
9.IN 和EXISTS
 Select name from employee where name not in (select name from student);
 Select name from employee where not exists (select name from student where t1.login_id=t2.login_id);
 第一句SQL语句的执行效率不如第二句。
 通过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，
 这就节省了时间。
 Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。
 在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。
 这也就是使用EXISTS比使用IN通常查询速度快的原因。
10.NOT 运算符
 Select * from employee where salary<>1000;
 Select * from employee where salary<1000 or salary>1000;
 第一句SQL语句的执行效率不如第二句，因为第二句SQL语句可以使用索引。
11.Order By 语句
 Order By 语句的执行效率很低，因为它要排序。应避免在Order By 字句中使用表达式。
12.列的连接
 select * from employee where name||department='ZYZBIOINFO';
 select * from employee where name='ZYZ' and department='BIOINFO';
 这两个查询，第二句比第一句会快，因为对于有连接运算符’||’的查询,Oracle优化器是不会使用索引的。
13.应尽量避免混合类型的表达式
 假设字段studentno为VARCHAR2类型
 有语句select * from student where studentno>123;
 则Oracle会有一个隐含的类型转换。隐含的类型转换可能会使Oracle优化器忽略索引。
 这时应使用显式的类型转换select * from student where studentno=to_char(123)。
14.DISTINCT
   DISTINCT总是建立一个排序，所以查询速度也慢。
15.对条件字段的一些优化
 采用函数处理的字段不能利用索引
 进行了显式或隐式的运算的字段不能进行索引(ss_df+20>50优化处理：ss_df>30)
 条件内包括了多个本表的字段运算时不能进行索引
16.更新表tests中的name使其等于表test中的name，且表test id＝tests id
 update tests set name=(select name from test where tests.id=test.id)
17.把test中的记录插入tests中；
 insert into tests select * from test [where test.id=5];
18.删除重复的行：
 delete from t_trans_fenshu_log
 where rowid!=(select max(rowid) from t_trans_fenshu_log t
 where t_trans_fenshu_log.send_desc2=t.send_desc2
 and t_trans_fenshu_log.send_yuanid=t.send_yuanid)；
 其中rowid为oracle系统的id；找出原表和你选择的表中相同的行，其rowid不同，根据rowid把其中的一些干掉；这样就可以建立constraint了。
19.分页查询
 method1:利用分析函数row_number()//缺点是第二个集合中不能用*代替，必须用column name 1百万中查找20条时间0秒05
 select cms_id,name from(
 select cms_id,name,row_number() over (order by name) rn from t_cont_dzkw)
 where rn between 10 and 20
 order by cms_id;

 method2:利用minus和rownum//缺点是：不能先排列后查询                   1百万中查找20条时间0秒07
   select cms_id ,name from(
 select cms_id,name from t_cont_dzkw order by name)
 where rownum<20
 minus
 select cms_id ,name from(
 select cms_id,name from t_cont_dzkw order by name)
 where rownum<10
 order by cms_id;

 method3:                  好慢1百万中查找20条时间 53秒.03
  select * from test
 where rowid not in
 (select rowid from test where rownum<=2)
 and rownum<=2;
20.rownum只能用符号(<、<=、!=),不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录.
21.备份:exp system/manager@oa owner=nxqw file=c:/amgang/working/dbbak/oa20050125.dmp
   恢复:imp test/test@oa full=y file=c:/1.dmp log=d:/1.log  IGNORE=Y

22.select * from nls_database_parameters
select COD_COMMISION_KIND_CODE
from WORK_COMMISION_KIND_RELATION
connect by prior COD_COMMISION_KIND_CODE=COMMISION_KIND_CODE
start with COMMISION_KIND_CODE='10'

23.select * from dba_source
24.数据库字符集更改
 select * from nls_database_parameters;
 select * from nls_instance_parameters;
 Alter database character set zhs16gbk;
25.RANK
 dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过；rank则跳过。

 TABLE：A （科目，分数）
 数学，80
 语文，70
 数学，90
 数学，60
 数学，100
 语文，88
 语文，65
 语文，77
 
 现在我想要的结果是：（即想要每门科目的前3名的分数）
 数学，100
 数学，90
 数学，80
 语文，88
 语文，77
 语文，70
 那么语句就这么写：
 select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
 where t.rk<=3;

26.imp user/password@a  file=dm.dmp log=dw.log from newagent to abc ignore=y commit=y buffer=40960000

27.利用表b的值更新tablea两个列
 update tablea set (a,b)=(select c,d from tableb where a.n=b.n)

28.如下语句需要system用户执行：
  数据池命中率
 db_pool命中率(要求：95%左右)：
 select 100 - 100*( (select value from  v$sysstat where name ='physical reads')) /((select value from  v$sysstat where name ='consistent gets')+(select value from v$sysstat where name = 'db block gets'))  from dual
 
 share_pool命中率(要求：95%左右)：
 select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
 
 如下语句可以查看PGA和SGA的分配情况
 select * from V$PARAMETER where name in ('sga_max_size','db_cache_size','shared_pool_size','pga_aggregate_target')
 db_cache_size:可能的大些
 shared_pool_size:200MB～350MB,超过500M比较危险
 log_buffer:3MB～5MB
 java_buffer:建议设置为30M
 large_pool_size:10MB～30MB
 pga_aggregate_target:默认10m; (RAM*0.8)*0.2 for OLTP
 在oracle 中单个进程的PGA的使用遵循的原则是小于min(pga_aggregate_target *5%,100MB)

29.查找前十条性能差的sql
 SELECT * FROM  (SELECT PARSING_USER_ID
  EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
 sql_text FROM  v$sqlarea ORDER BY disk_reads DESC) 
  WHERE ROWNUM<10 ;

30.根据unix上Top命令看到的PID,查找对应的SQl
 SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
 S.osuser osname,P.serial# S_#,P.terminal,P.program  program,
 P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL
 FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
 AND S.sql_address = a.address (+)  AND P.spid LIKE '%CPU最高的进程对应的PID%';

31.察看IO情况：
 select
     df.name 文件名,
     fs.phyrds 读次数,
     fs.phywrts 写次数,
     (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 读时间,
     (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 写时间
 from  v$datafile df,
     v$filestat fs
 where df.file#=fs.file#
 order by df.name

32.表空间察看
 SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
 D.TOT_GROOTTE_MB "表空间大小(M)",
        D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比", F.TOTAL_BYTES "空闲空间(M)",
        F.MAX_BYTES "最大块(M)"  FROM (SELECT TABLESPACE_NAME,
 ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
 ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
 FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
  GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;

33.产生kill会话的Sql语句
 select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
 'orakill '||sid||' '||spid HOST_COMMAND,
 'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
 from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6;
 
 select t2.username, t2.sid, t2.serial#, t2.logon_time
 from v$locked_object t1, v$session t2
 where t1.session_id = t2.sid order by t2.logon_time;
 
 alter system kill session 'sid,serial#';

34.捕获运行时间很长的SQL
 select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,
  time_remaining,sql_text from v$session_longops , v$sql
 where time_remaining <> 0 and sql_address=address
 and sql_hash_value = hash_value;

35.重做表的统计信息
 analyze table g_og_departinfo compute statistics for table

36.限制ip地址访问
 不过AFTER LOGON ON DATABASE触发器对有DBA权限的用户不起作用,还好客户说,数据库中拥有DBA权限的用户就几个,并且密码就一个人知道.
 CREATE OR REPLACE TRIGGER log_control
 AFTER logon ON DATABASE
 declare
   oname number(2);
   pragma autonomous_transaction;
 begin
 if SYS_CONTEXT('USERENV','IP_ADDRESS') is not null
 then
 select count(*) into oname from logcontrol
 where trim(IPDET)=SYS_CONTEXT('USERENV','IP_ADDRESS');
   if oname<1 then
   insert into yclogin(ipdet) values(SYS_CONTEXT('USERENV','IP_ADDRESS'));
   commit;
    Raise_application_error(-20999, 'ip访问限定，有需要请及时向系统管理员书面申请!!!');
    EXECUTE IMMEDIATE 'DISCONNECT';
   end if;
 end if;
 end;

37.使用ordered提示改变SQL执行计划
 SELECT /*+ ordered */ COUNT (*)  2    FROM t_middle, t_small, t_max  3 
 WHERE t_small.object_id = t_middle.object_id  4 
 AND t_middle.object_id = t_max.object_id;

38.select segment_name,sum(bytes/1024/1024) from user_extents
group by segment_name

39.select * from sys.v_$version
 select * from sys.v_$option
 select * from nls_database_parameters

40.ALTER USER username ACCOUNT UNLOCK;
lsnrctl start
41.
 sqlplus sys /passwd as sysdba
 connect /as sysdba;
 startup nomount,mount,open
 alter database open 
 啟動      lsnrctl start
 狀態      lsnrctl status
 關閉      lsnrctl  stop

42、session限制
 create   profile   limit_1   limit   sessions_per_user   2;  
 alter   system   set   resource_limit=true;  
 ALTER   USER   xxx_user   profile   limit_1;
 ALTER   USER   xxx_user   profile   default;   ------取消
 
 实例的线程 process
 select count(*) from v$session  #连接数
 Select count(*) from v$session where status='ACTIVE'　#并发连接数
 show parameter processes  #最大连接
 alter system set processes = value scope = spfile;重启数据库  #修改连接
43、查看表空间大小
 SELECT X.TABLESPACE_NAME,
                          TOTAL || 'MB' total,
                          NOW || 'MB' now,
                          TO_CHAR(DECODE(TOTAL, 0, 0, ROUND( NOW / TOTAL * 100, 2)),
                                  '990.99') || '%' unused
                     FROM (SELECT T.TABLESPACE_NAME,
                                  TO_CHAR(ROUND(SUM(T.BYTES) / 1024 / 1024, 2), '999990.99') TOTAL
                             FROM DBA_DATA_FILES T
                            GROUP BY T.TABLESPACE_NAME) X,
                          (SELECT A.TABLESPACE_NAME,
                                  TO_CHAR(ROUND(SUM(A.BYTES) / 1024 / 1024, 2), '999990.99') NOW
                             FROM DBA_FREE_SPACE A
                            GROUP BY A.TABLESPACE_NAME) Y
                    WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME