Author Archives: royalwzy

SQL调优03–执行计划的访问路径

Posted on by

Optimizer operators

  1. 行源操作
    1. Unary Operations:一元运算,即单表的查询;
    2. Binary Operations:二元运算,两表的连接;
    3. N-ary Operations:多元运算;
  2. 主要的结构和访问路径:
    1. 表:
      1. Full Table Scan;
      2. Rowid Scan:很少使用,多用在内部的某一个步骤;
      3. Sample Table Scan:很少使用;
    2. 索引:
      1. Index Scan(Unique);
      2. Index Scan(Range);
      3. Index Scan(Full);
      4. Index Scan(Fast Full);
      5. Index Scan(Skip);
      6. Index Scan(Index Join);
      7. Using Bitmap Indexes;
      8. Combining Bitmap Indexes;
  3. 索引的基本概念:
    1. B-Tree Indexes:平衡树索引,最常见的索引;
      1. 正常索引;                                                                         
      2. 基于函数的索引:
        1. 创建函数索引相当于在表上添加一个伪列;                      
        2. 查看定义;                                             
      3. IOT(Index-Organized Table):将表结构整体放入索引中,而且按照主键进行排序,一定要有主键,非主键的列一定要落在索引条目里;
      4. Bitmap Indexes;
        1. 可以索引空值;
        2. 适当发生转换:TO ROWIDS/FROM ROWIDS/COUNT;
        3. 可以进行的操作:MERGE/AND/OR/MINUS/KEY ITERATION,位运算的速度很快;
        4. 位图索引可以进行SINGLE VALUE/ RANGE SCAN/ FULL SCAN扫描;
        5. 缺点是位图索引不能经常更新,效率很差;
      5. Cluster Indexes;
        1. 如果要做两个表的关联查询则最少查询两个块;
        2. CLUSTER把两个表按照关联的字段把记录存放在同一个块上;这样只用查一个块即可;查找时效率提高一倍;
        3. 用在总是关联查询两个表的情况,一般是不用的;ORACLE内部大量使用;
        4. cluster上的索引不能指定列,必须使用所有的列;
        5. 基于cluster的表没有segment;
    2. 索引的属性:
      1. 键压缩;
      2. 反转键值(考点):可以防止索引块争用(buffer busy wait),只支持等式连接,不支持范围扫描;
      3. 顺序/倒序;
    3. 索引和NULL值:
      1. NULL值与索引的关系:
        1. 基于单列的唯一索引,可以多次插入NULL值(NULL <> NULL),因为索引并不存储NULL值;
        2. 基于多列的符合索引,尽管全为NULL的值可以多次插入([NULL, NULL] <> [NULL, NULL]),索引也不会存储,但不全为NULL的重复行则不能重复插入,;
      2. NULL值与执行计划:
        1. 如果列的属性允许为NULL,条件为IS NULL的话,肯定走全表扫描,因为索引不保存NULL值;
        2. 如果列的属性允许为NULL,条件为IS NOT NULL的话,会走全索引扫描;
        3. 如果列的属性为NOT NULL,条件为IS [NOT] NULL的话,走索引扫描;
        4. 组合索引的话,如果条件中只出现一列的话跟单列索引一样;
        5. 组合索引的话,如果条件中出现两列,会优先选择走索引;
      3. IS NULL使用索引的办法:
        1. 在NULL的列上创建函数索引:nvl(column_name, -1),查询的时候条件指定函数索引: where nvl(column_name, -1) = -1;
        2. 为NULL的列添加默认值;
    4. 索引的管理:
      1. 插入数据后再创建索引,对于DW来言;
      2. 在适当的表和列上加索引;
      3. 注意组合索引的顺序;
      4. 控制索引的数量:每添加一个索引,DML的效率下降3倍,官方推荐最多加7个索引;
      5. 删除不使用的索引;
      6. 为索引指定单独的表空间;
      7. 创建索引时使用并行,NOLOGGING参数;
      8. COALESCING是合并相邻叶子节点,rebuild则可以减少索引树的高度;
    5. 检测索引是否被使用了:
      1. 添加对某个索引的监控:ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE;
      2. 查看监视的对象使用情况:SELECT * FROM v$object_usage;默认是没有任何的监视的;                
      3. 使用此索引后再查看;                                               
      4. 取消索引监控:ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE;                                                   
      5. 缺点:每次只能添加一个索引,而且不记录索引使用的次数;
      6. 不使用索引的原因:
        1. 被检索的列上用了函数;
        2. 数据类型不匹配;发生隐士转换是转化左边的列,而不是右边的列;
        3. 统计信息是否最新;
        4. 列是否是空值;
        5. 索引效率太低;
  4. 各种访问路径的原理及使用场景:
    1. Full Table Scan:
      1. 会执行Multiblock Reads,参考初始化参数:db_file_multiblock_read_count;                   
      2. 会读取HWM(High-Water Mark)以下所有被格式化的块;
      3. 过程中可能会过滤某些记录;
      4. 用在要获得大量记录的时候,比索引扫描更快;
      5. 使用的场景:
        1. 没有合适的索引;
        2. 过滤条件不好,甚至是没有过滤条件;
        3. 表太小,记录数很少;
        4. 需要并行扫描,并行扫描一定不走索引,如果确定是全表的话可以考虑并行:SELECT /*+ PARALLEL(d 4) */ * FROM departments d;                 
        5. 加全表扫描的hint时:SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;                             
        6. IS NULL的操作;                                           
    2. ROWID Scan:
      1. 根据记录的rowid查询,最快的访问方式,但不经常使用,可能会出现在执行计划的某个步骤中;
      2. 使用的方法:                                                
    3. Sample Table Sacns:基本不用,SELECT * FROM departments SAMPLE BLOCK (10) SEED (1);
    4. Index Unique Scan:条件中指定了主键列或者唯一键的列就走唯一键扫描;                    
    5. Index Range Sacn:
      1. 过滤的条件上不是主键/唯一索引,就会走索引范围扫描;                                        
      2. 如果对有索引的列排倒序就会有索引倒序扫描;(因为索引本身是排序的,所以执行计划中不会有排序的步骤,按照索引列排序效率会高;)                          
      3. 走函数索引的例子,也是索引范围扫描的一种;                                    
    6. Index Full Sacn vs Index Fast Full Sacn:
      1. 出现的条件:
        1. SELECT与WHERE子句出现的所有的列必须存在索引,而且为非空列,因为索引不存放NULL值;
        2. 返回的数据总行占据索引的10%以上的比例;
      2. Index Full Sacn:
        1. 完全按照索引存储的顺序依次访问整个索引树,当访问到叶子节点时,按照双向链表方式读取相连的节点值;
        2. 使用Single Read,会产生db file sequential reads事件;
        3. 对于索引列上的排序,总是会使用Index Full Scan;
        4. 索引列上is not null的操作,会走全索引扫描;
      3. Index Fast Full Sacn:
        1. 对于索引的分支结构只是简单的获取,然后扫描所有的叶节点,导致索引结构没有访问,获得的数据没有根据索引键的顺序排序,读取效率高.但是如果SQL语句中有排序操作的话,还要额外多做一次排序;
        2. 在使用Index Fast Full Sacn时,使用Multiblock Read,会产生db file scattered reads,db_file_multiblock_read_count参数的设置很重要;
        3. 统计行数,如count(*)的操作总是会使用Index [Fast] Full Scan的;
        4. 会使用大量的内存和CPU资源;
      4. Index [Fast] Full Scan的例子;                                                        
    7. Index Skip Scan:
      1. 创建了复合索引,但是条件中只有复合索引中的第二列,而且当第一列的distinct值不多时,会发生跳跃扫描;
      2. 创建一个测试表,和一个联合索引,当第一列可选值少而条件中只查找第二列时,发生跳越扫描;                     
      3. 如果第一列的可选值很多,条件中查找第二列的话,发生全表扫描;                                               
    8. Index Join Scan:查询的列都不为空,而且都有索引才会出现联合扫描;               
    9. AND-EQUAL操作:两列都有索引,分别扫描两列获得记录的rowid,然后再取rowid的交集;
    10. Bitmap Index:
      1. Bitmap的单值扫描;                                                   
      2. Bitmap的范围扫描;                                                  
      3. Bitmap的迭代操作操作;                                         
      4. Bitmap的AND操作;                                                
  5. 排序操作:
    1. Sort Operator:
      1. AGGREGATE:在group操作用会用到,统计结果;
      2. UNIQUE:评估是否重复;
      3. JOIN:做合并操作;
      4. GROUP BY,ORDER BY:在group by和order by的时候使用;
    2. Hash Operator:
      1. GROUP BY:在group by操作时使用;
      2. UNIQUE:跟SORT UNIQUE一样;
    3. 10g之后结果默认不排序,如果想要排序后的结果,应该总是使用ORDER BY字句;
  6. Buffer Sort:
    1. BUFFER SORT不是一种排序,而是一种临时表的创建方式;
    2. BUFFER表示在内存中存放了一张临时表;
    3. SORT来修饰BUFFER表示具体再内存的什么地方:在PGA的SQL工作区的排序区;
    4. BUFFER SORT的例子:                                                 
  7. INLIST ITERATOR:
    1. 是由于IN操作引起的,要关注迭代的次数,一次迭代就要有一次访问,如果没有索引可能性能问题会很严重;
    2. 可以使用UNION ALL操作代替;
    3. INLIST ITERATOR的例子;                                     
  8. 视图的操作:
    1. Merge View:是将View的定义和外部查询合并,高效的方式;
    2. No Merge View:先将View的数据取出来再做外部条件的过滤,效率低;
  9. 执行计划中的Count和Count Stopkey:oracle数据库的优化关于rownum操作;
    1. 在查询中有时使用到伪列rownum,对使用伪列rownum的查询,优化器要么使用count操作,要么使用count stopkey操作来对rownum计数器进行增量(注意:这里的count操作和count stopkey操作与count函数没有任何关系).如果对rownum伪列应用一个限定条件,如:where rownum<10,则使用count stopkey操作;如果不为Rownum伪列指定限定条件,则是使用count操作;
    2. 不在Rownum伪列上使用限定条件:SELECT employee_id, ROWNUM FROM employees;(employee_id是主键)为了完成这个查询,优化器执行一个全索引扫描(主键索引),后跟一个count操作生成每个行的rownum值,count操作不需要等待得到整个记录集,随着从employee表中返回记录,rownum计数器进行增量,从而确定每个记录的rownum;
    3. 在rownum伪列上使用一个限定:SELECT employee_id, ROWNUM FROM employees WHERE ROWNUM < 10;为了实施限定条件,优化器用count stopkey操作代替count操作,它将rownum伪列的增量值与限定条件中指定的值进行比较,如果rownum伪列的值大于限定条件中指定的值,则查询不再返回更多的行;
    4. 在where子句中不能使用rownum>10这样的操作,只能使用rownum<10这样的操作;
  10. Min/Max and First Row操作:当使用MAX/MIN函数时发生;                           
  11. 连接的方式:
    1. 一个连接定义了两个行源的关系,也是合并两个行源间数据的方法;
    2. 主要由连接的谓词所控制,定义了对象间的关系;                                                   
    3. 连接的方法:
      1. Nested Loops:
        1. 对于被连接的数据子集较小的情况,嵌套循环是个较好的选择;
        2. 返回第一条记录最快的方式;
        3. 这种情况下,内表被外表驱动,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(eg:<1w);
        4. 要把返回子集较小的表作为驱动表,而且内标的连接字段上一定要有索引;
        5. 使用USE_NL(table_name1 table_name2)可是强制CBO执行嵌套循环连接;
      2. Sort-Merge Join:
        1. 通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接;
        2. 可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接;
        3. Sort Merge join使用的情况:
          1. 用在没有索引;
          2. 数据已经排序的情况;
          3. 不等价关联;
          4. HASH_JOIN_ENABLED=FALSE;
      3. Hash Join:
        1. 散列连接是CBO做大数据集连接时常用的方式,优化器使用两个表中较小的表(行源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行;
        2. 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和,但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O的性能;
        3. 也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接,如果使用散列连接HASH_AREA_SIZE初始化参数必须足够的大,如果是10g以后,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可;
    4. 连接方式的比较:
      1. Hash join的工作方式是将一个表(通常是小一点的那个表)做hash运算,将列数据存储到hash列表中,从另一个表中抽取记录,做hash运算,到hash列表中找到相应的值,做匹配;
      2. Nested loops工作方式是从一张表中读取数据,访问另一张表(通常是索引)来做匹配,nested loops适用的场合是当一个关联表比较小的时候,效率会更高;
      3. Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配,因为merge join需要做更多的排序,所以消耗的资源更多,通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能,Merge Join太消耗PGA;
    5. 连接的类型:
      1. [不]等值连接和自然连接;
      2. 外连接:全连接,左外连接,右外连接;(外连接:+号放那边,哪边记录少;)
      3. 半连接:EXISTS子句;
      4. 反连接:NOT IN字句;
  12. 多行源的操作
    1. FILTER;
    2. CONCATENATION;
    3. UNION [ALL]
    4. INTERSECT;
    5. MINUS;
— Full Table Scan;
SELECT * FROM departments WHERE manager_id = 100;
SELECT /*+ PARALLEL(d 4) */ * FROM departments d;
SELECT * FROM departments d WHERE department_id = 10;
SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;
— ROWID Sacn;
SELECT * FROM departments WHERE ROWID = ‘AAAMiZAAFAAAAA4AAI’;
SELECT * FROM departments WHERE ROWID = (
SELECT rowid FROM departments  WHERE manager_id = 100);
— 函数索引的例子;
CREATE INDEX idx_employees_fun_firstname ON employees (upper(first_name));
SELECT * FROM user_tab_cols WHERE table_name = ‘EMPLOYEES’;
SELECT * FROM user_ind_expressions WHERE index_name = ‘IDX_EMPLOYEES_FUN_FIRSTNAME’;
— Index Skip Scan的例子;
CREATE TABLE skip_test AS
SELECT object_id, object_name, decode(object_type, ‘VIEW’, ‘VIEW’, ‘TABLE’) AS object_flag, object_type
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX idx_skip_test ON skip_test(object_flag, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id = 100;
— 如果联合索引第一列的候选值太多,则发生全表扫描;
DROP INDEX idx_skip_test;
CREATE INDEX idx_skip_test ON skip_test(object_type, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id=100;
— 位图索引的例子;
CREATE TABLE bitmap_test AS
SELECT ROWNUM rn, MOD(ROWNUM, 4) bit,
CASE MOD(ROWNUM, 2) WHEN 0 THEN ‘M’ ELSE ‘F’ END gender
FROM dual CONNECT BY ROWNUM < 1000;
CREATE BITMAP INDEX bmp_bitmap_bit on bitmap_test (bit);
CREATE BITMAP INDEX bmp_bitmap_gender on bitmap_test (gender);
EXEC dbms_stats.gather_table_stats(USER, ‘bitmap_test’, CASCADE => TRUE);
ALTER SESSION optimizer_mode = ‘FIRST_ROWS_1000’;
SELECT * FROM bitmap_test WHERE bit = 3;
SELECT * FROM bitmap_test WHERE bit > 2;
SELECT * FROM bitmap_test WHERE bit IN (2, 3);
SELECT * FROM bitmap_test WHERE bit IN (2, 3) AND gender = ‘M’;

SQL调优02–CBO优化器的原理

Posted on by

Intorduction to the CBO Optimizer

  1. 介绍CBO优化器的书:Jonathan Lewis写的<<Cost-Based Oracle Fundamentals>>;
  2. 结构化查询语言的类型:
    1. DML(Data Manipulation Language):INSERT, UPDATE, DELETE, MERGE, SELECT;
    2. DDL(Data Definition Language):CREATE, DROP, ALTER, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT;
    3. ESS(Environmental Sustainability Statement):DECLARE, CONNECT, OPEN, CLOSE, DESCRIBLE, WHENEVER, PREPARE, EXECUTE, FETCH;
    4. TCS(Transactoin Control Statement):COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION;
    5. SystemCS(System Control Statement):ALTER SYSTEM;
    6. SessionCS(Session  Control Statement):ALTER SESSSION, SET ROLE;
  3. SQL语句的实现过程:相同的SQL语句可以使用相同的执行计划;                                     
  4. Cursor
    1. PL/SQL中的cursor:记录行的rowid,用来表示结果集;
    2. sql解析过程中的cursor:是SHARED POOL中的内存块;
  5. SQL语句的处理过程:
    1. Create a cursor:
      1. Cursor是private SQL area的句柄或者名称;
      2. 其中包含了语句执行的信息;
      3. Cursor的结构与它包含SQL语句是互相独立的;
    2. Parse the statement:
      1. SQL语句通过用户进程发送到Oracle实例;
      2. 在PGA中检查语法和语义,然后检查权限,分配private SQL area,然后检查是否已经在Library Cache中存在,如果没有的话,会把sql放入到Shared SQL area中,产生硬解析;
      3. 如果SQL的执行计划已经存在的话可以被重用;
    3. Describe query results:
      1. 提供了SELECT后面的要查找的列的信息,与动态执行SQL有关;
      2. 9i时使用DBMS_SQL包,之后使用EXECUTE IMMEDIATE来动态执行SQL;
    4. Define query output:定义要查找的列的位置,大小,数据类型信息;
    5. Bind variables:
      1. 开启内存地址来保存数据的值;
      2. 允许共享sql;
    6. Parallelize the statement:
      1. 可以并行的语句:SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE, ALTER;
      2. 11g中自动判断是否需要并行;
    7. Execute the statement:执行SQL语句,产生想要的结果;
    8. Fetch rows of a query:以表格的形式返回数据;
    9. Close the cursor:关闭游标,PGA中指向cursor的指针关闭,但是内存区域还是被占用,之后可以被覆盖;
    10. 使用DBMS_SQL包可以看到完整的SQL执行的过程;
  6. SQL语句解析的过程;                                                                            
  7. 需要优化器的理由:它可以选择一种资源消耗最小的方式;
  8. 硬解析的操作步骤:                                                                                         
  9. Transformer
    1. 优化器首先会把语句分成一个一个的查询块,然后进行转换;
    2. 虽然进行了转换操作以提高效率,但是在内存中保存的还是原来的SQL语句;
    3. OR转换为UNION ALL操作;                                                               
    4. IN转换为内连接操作(11g);                                                
    5. IN改写为exists;
    6. NOT IN改写为外连接+IS NULL,11g中自动转换,10g中需要修改;
    7. IN改写为外连接+IS NOT NULL;
    8. 视图合并:查询的时候直接查询视图中的基表,非常适合于视图的记录数很大,查询视图的记录数小的情况;               
    9. 视图不合并:查询时把制图当成基表,这样效率比较低.CBO会自动合并第一层的视图,所以不要使用嵌套视图;
    10. Predicate Pushing:把条件推到最查询的最低端;                                                                        
    11. 条件的传递性:employees的department_id列没有索引,department表department_id列是主键,转换过之后就会先走主键扫描;                                
  10. Cost-Based Optimizer
    1. 由Estimator和Plan Generator组成;
    2. Estimator决定执行计划的成本消耗的建议;
      1. 它是基于概率论的,理论依据是数据是均匀分布的;
      2. 它的基础数据是定期收集并存放在数据字典的统计信息;
    3. Plan Generator:
      1. 产生各种不同的执行计划;
      2. 使用Estimator计算各个执行计划的成本;
      3. 基于成本选择最好的优化建议;
      4. 生成最优的执行计划;
    4. OPTIMIZER_MODE的两个参数:ALL_ROWS, FIRST_ROWS_n:
      1. FIRST_ROWS_n:
        1. CBO优先考虑将结果集中的前N条记录以最快的速度返回,而其它的结果集并不需要同时返回;
        2. 可以使用在BBS的分页上:SELECT /*+ first_rows(10) */ FROM tbname;
        3. 这种执行计划对于SQL整体的执行时间不是最快的,但是在返回前N条记录的处理上绝对是最快的;
        4. 使用的排序字段必须有索引,否则CBO会忽略FIRST_ROWS(n),而使用ALL_ROWS;
      2. ALL_ROWS:
        1. CBO考虑最快的速度返回所有的结果集,和FIRST_ROWS_n相反;
        2. 在OLAP系统中使用较多,总体效率高;
  11. Estimator
    1. Selectivity:选择度;
      1. Selectivity is the estimated proportion of a row set retrieved by a particular predicate or combination of predicates;选择度是由一个特定的谓词或者组合谓词检索行集的估计比例;
      2. 计算公式:Selectivity=满足条件的记录数/总记录数;
      3. 它的取值在0.0-1.0之间:
        1. High Selectivity:得到大比例的记录数;
        2. Low Selectivity:得到小比例的记录数;
      4. 如何获得Selectivity:
        1. 如果没有统计信息则采用动态采样(Dynamic Sampling);
        2. 如果没有直方图信息则采用行分布;
      5. 存放统计信息的视图:
        1. dba_tables;
        2. dba_tab_statistics(NUM_ROWS, BLOCKS, AVG_ROW_LEN);
        3. dba_tab_col_statistics(NUM_DISTINCT, DENSITY, HIGH/LOW_VALUE);
    2. Cardinality:基数;
      1. 通过执行计划期望能检索出来的记录数;
      2. 计算公式:Cardinality=Selectivity*总记录数;
      3. 对于join, filters和sort的成本是重要的指标;
    3. 举例:SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;
      1. employees表的job_id列的取值个数为:19;                                  
      2. employees表中的记录数:107;                                                               
      3. Selectivity=1/19=0.0526315789473684,即DENSITY的值;
      4. Cardinality=(1/19)*107=5.63,向上取整为6;
    4. Cost:
      1. Cost是执行特定的语句所花费的标准I/Os数量的最优评估;
      2. Cost的单位是一个标准块的随机读取:1 cost unit = 1 SRds(Standardized Random Reads);
      3. 执行计划中Cost(%CPU):一次IO读取一个IO块需要的时间;
      4. Cost的值由三部分组成;                                                           
  12. 控制优化器的初始化参数:
    1. CURSOR_SHARING:SIMILAR|EXACT(default)|FORCE,控制可以共享Cursor的SQL语句类型;
    2. DB_FILE_MULTIBLOCK_READ_COUNT:它是一个可以在表扫描时最小化IO的参数,指定了在顺序扫描时一次IO操作可以读取的最大的块数;(在OLTP系统中一般指定4-16,在DW系统中可以尽量设置的大一点);
    3. PGA_AGGREGATE_TARGET:PGA自动管理时指定server processes可以使用的PGA内存的总和;
    4. STAR_TRANSFORMATION_ENABLED:参数设置为TRUE时使用CBO可以使用位图索引的特性,不过貌似现在这个参数不重要;
    5. RESULT_CACHE_MODE:MANUAL,FORCE,11g
    6. RESULT_CACHE_MAX_SIZE:11g;
    7. RESULT_CACHE_MAX_RESULT:11g;
    8. RESULT_CACHE_REMOTE_EXPIRATION:11g;
    9. OPTIMIZER_INDEX_CACHING:在Buffer Cache中缓存索引的比例,默认为0;
    10. OPTIMIZER_INDEX_COST_ADJ:索引扫描/全表扫描的成本,默认为100%,即索引扫描成本等于全表扫描成本;优先会选择全表扫描;比较悲观的配置;
    11. OPTIMIZER_FEATURES_ENABLE:希望启用哪个版本的CBO;
    12. OPTIMIZER_MODE:ALL_ROWS|FIRST_ROWS|FIRST_ROWS_n
      1. 默认是all_rows:资源消耗比较小;
      2. first_rows_n:n的取值为1|10|100|1000,速度优先,但是消耗很大的资源;
    13. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;
    14. OPTIMIZER_USE_SQL_PLAN_BASELINES;
    15. OPTIMIZER_DYNAMIC_SAMPLING:动态采样的特性,10g后默认为2;
    16. OPTIMIZER_USE_INVISIBLE_INDEXES;
    17. OPTIMIZER_USE_PENDING_STATISTICS;
  13. OPTIMIZER_INDEX_COST_ADJ参数设置的例子:
    1. 首先创建表,索引,并收集统计信息;                                       
    2. 打开执行计划,执行查询语句,默认的值为100%,即索引扫描成本等于全表扫描成本,则执行计划走全表扫描;                      
    3. 修改参数值为50,即索引扫描成本是全表扫描成本的1/2,查看执行计划;                      
  14. Selectivity值的例子:
    1. 因为CBO是基于数据均匀的概率分布的,所以它估计的Selectivity是一个理论值;
    2. 创建一个1200条记录的表,里面的值分布是1-12,代表1-12月出生的人;                             
    3. 如果要查找某个月份出生的人,那么在不明白任何情况下,每个月份出生的人的概率都是1/12,即有100个人,CBO也是这么思考问题的;                            
    4. 实际值往往跟理论值不相符,但是数据量越大,越接近;                               
  15. 10053事件测试:
    1. 开启10053事件,并执行一条sql语句;                                       
    2. 查看当前的session_id和process_id来确定生成的内容是放在哪个udump文件中;                         
    3. 查看udump文件,里面包含了一些缩写的含义和当前生效的优化参数的值;                                
    4. 关闭10053事件;                                                      
— Estimator例子的脚本;
SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;
SELECT COUNT(DISTINCT job_id) FROM hr.employees;
SELECT owner, table_name, column_name, num_distinct, density
FROM dba_tab_col_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’ AND column_name = ‘JOB_ID’;
SELECT owner, table_name, num_rows, blocks, avg_row_len
FROM dba_tab_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’;
— OPTIMIZER_INDEX_COST_ADJ参数的例子;
CREATE TABLE t1 AS
SELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX t_i1 ON t1(n1);
EXEC dbms_stats.gather_table_stats(USER, ‘t1’, CASCADE=>TRUE);
SET autotrace traceonly exp;
SELECT * FROM t1 WHERE n1 = 50;
ALTER SESSION SET optimizer_index_cost_adj = 50;
SELECT * FROM t1 WHERE n1 = 50;
— Selectivity的例子;
CREATE TABLE t2(ID, month_id) AS
SELECT ROWNUM, trunc(dbms_random.value(1, 13))
FROM dba_objects WHERE ROWNUM <= 1200;
EXEC dbms_stats.gather_table_stats(USER, ‘t2’, CASCADE => TRUE);
SET autotrace traceonly exp;
SELECT * FROM t2 WHERE month_id = 5;
SELECT COUNT(*) FROM t2 WHERE month_id = 5;
— 10053时间测试例子;
ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context forever, level 8’;
SELECT * FROM employees WHERE employee_id = 100;
ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context off’;
SELECT s.sid, p.spid FROM v$session s
INNER JOIN v$process p ON s.paddr = p.addr AND
s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);

ORACLE中工具的使用06–ORADEBUG

Posted on by
oradebug工具的使用:

  1. oradebug主要是给oracle支持人员使用的,从8i开始,它是个sql*plus命令行工具,有sysdba的权限就可以使用,无需特别设置,主要用途为:
    1. 追踪进程,自己的或者是外部的;
    2. 确定进程写入哪个trc文件;
    3. 转储:数据文件头,内部oracle结构等;
    4. 暂时挂起进程;
    5. 确定实例使用了哪些共享内存块和信号量;
    6. 找出RAC实例使用了哪些互联地址和协议;
    7. 修改SGA中的数据结构;
  2. oradebug使用步骤:
    1. 启动sql*plus并以sysdba身份登入;
    2. 连接到一个进程;
    3. 设置一个事件或者进行诊断转储;
    4. 查看trc文件名;
    5. 与连接到的进程断开;
  3. oradebug的语法;
  4. 连接到一个进程的方法:
    1. oradebug setmypid:连接到当前的进程:
    2. oradebug setorapid pid:根据pid,连接到其它进程(v$process.pid);
    3. oradebug setospid spid:根据spid,连接到其它进程(v$process.spid);
  5. 查看共享信息内存方法:ipcs [-m|-s|-q],默认会列出共享内存,信号量,队列信息;
    1. 清除命令是ipcrm [-m|-s|-q] id;
    2. 参数:
      1. -m:列出共享内存信息;
      2. -s:列出共享信号量信息;
      3. -q:列出共享队列信息;
    3. 例子;
  6. 如果一个程序挂起,那么程序调用栈就可以显示它在调用路径中的哪一步挂起的:oradebug short_stack;                        
  7. 文件转储:
    1. 可以转储的类型:oradebug dmplist;
    2. 转储控制文件:oradebug dump controlf 10;
    3. 实现10046事件,例子;
    4. oradebug dump events的级别和范围:
      1. level 1,会话级别,对应alter session命令;
      2. level 2,进程级别,对应oradebug event命令;
      3. level 4,实例级别,对应alter system命令;
  8. oradebug对于解决问题和诊断性能是必不可少的工具;
——————————– oradebug语法 ——————————–
sqlplus / as sysdba
SQL> oradebug help
HELP           [command]                 Describe one or all commands
SETMYPID                                 Debug current process
SETOSPID       <ospid>                   Set OS pid of process to debug
SETORAPID      <orapid> [‘force’]        Set Oracle pid of process to debug
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
DUMPSGA        [bytes]                   Dump fixed SGA
DUMPLIST                                 Print a list of available dumps
EVENT          <text>                    Set trace event in process
SESSION_EVENT  <text>                    Set trace event in session
DUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
SETVAR         <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable
PEEK           <addr> <len> [level]      Print/Dump memory
POKE           <addr> <len> <value>      Modify memory
WAKEUP         <orapid>                  Wake up Oracle process
SUSPEND                                  Suspend execution
RESUME                                   Resume execution
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G             <Inst-List | def | all>   Parallel oradebug command prefix
-R             <Inst-List | def | all>   Parallel oradebug prefix (return output
SETINST        <instance# .. | all>      Set instance list in double quotes
SGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names
WATCH          <address> <len> <self|exist|all|target>  Watch a region of memory
DELETE         <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints        Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
CORE                                     Dump core without crashing process
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]…[argn]  Invoke function with arguments
——————————– oradebug语法 ——————————–
——————————– 查看共享信息内存的例子 ——————————–
— 1.生成信息到trace文件;
1.操作系统层面查看信息:host ipcs -m | grep ora
2.设置进程为当前进程:oradebug setmypid
3.导出信息到trace文件:oradebug ipc
4.查看trace文件的路径:oradebug tracefile_name
— 2.查看相应的trace文件;
Processing Oradebug command ‘ipc’
Dump of unix-generic skgm context
areaflags            000000f7
realmflags           0000001f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00400000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdc6af20
stackdir                   -1
mode                      640
magic                acc01ade
Handle:              0xf66058 `/u01/app/oracle/product/11.2.0/db_1ORCL’
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0/db_1ORCL’, flags = 00
000000
Area #0 `Fixed Size’ containing Subareas 0-0
Total size 0000000000149294 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
0        0   196608 0x00000020000000 0x00000020000000
Subarea size     Segment size
000000000014a000 0000000000800000
Area #1 `Variable Size’ containing Subareas 4-4
Total size 0000000032400000 Minimum Subarea size 00400000
Area  Subarea    Shmid      Stable Addr      Actual Addr
1        4   229377 0x00000020800000 0x00000020800000
Subarea size     Segment size
0000000032400000 0000000032400000
Area #2 `Redo Buffers’ containing Subareas 1-1
Total size 00000000004e3000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
2        1   196608 0x0000002014a000 0x0000002014a000
Subarea size     Segment size
00000000004e3000 0000000000800000
Area #3 `Base Allocator Control’ containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
3        3   196608 0x000000207fe000 0x000000207fe000
Subarea size     Segment size
0000000000002000 0000000000800000
Area #4 `Slab Allocator Control’ containing Subareas 2-2
Total size 00000000001d1000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
4        2   196608 0x0000002062d000 0x0000002062d000
Subarea size     Segment size
00000000001d1000 0000000000800000
Area #5 `skgm overhead’ containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
5        5   262146 0x00000052c00000 0x00000052c00000
Subarea size     Segment size
0000000000002000 0000000000400000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec        0
used region        0: start 0000000012000000 length 0000000000400000
used region        1: start 0000000020000000 length 0000000033000000
used region        2: start 00000000af800000 length 0000000010800000
Maximum processes:               = 1000
Number of semaphores per set:    = 125
Semaphores key overhead per set: = 4
User Semaphores per set:         = 121
Number of semaphore sets:        = 9
Semaphore identifiers:           = 9
Semaphore List=
491520
524289
557058
589827
622596
655365
688134
720903
753672
————– system semaphore information ————-
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 196608     oracle    640        4096       0
0x00000000 229377     oracle    640        4096       0
0x671360a4 262146     oracle    640        4096       0
—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x4c1391ac 491520     oracle    640        125
0x4c1391ad 524289     oracle    640        125
0x4c1391ae 557058     oracle    640        125
0x4c1391af 589827     oracle    640        125
0x4c1391b0 622596     oracle    640        125
0x4c1391b1 655365     oracle    640        125
0x4c1391b2 688134     oracle    640        125
0x4c1391b3 720903     oracle    640        125
0x4c1391b4 753672     oracle    640        125
—— Message Queues ——–
key        msqid      owner      perms      used-bytes   messages
——————————– 查看共享信息内存的例子 ——————————–
——————————– oradebug实现10046事件 ——————————–
— 1.设置某个进程;
session 1:
sqlplus / as sysdba
conn hr/hr
SELECT pid, spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1));
— 29 22505;
session 2:
sqlplus / as sysdba
oradebug setorapid 29;
— 2.设置trace文件的大小:oradebug unlimit;
— 3.打开10046事件:oradebug event 10046 trace name context forever, level 12;
— 4.执行查询:SELECT COUNT(*) FROM hr.employees;(在session 1中)
— 5.查看trace文件的位置:oradebug tracefile_name;
— 6.关闭10046事件:oradebug event 10046 trace name context off;
— 6.查看trace文件,使用tkprof工具格式化;
——————————– oradebug实现10046事件 ——————————–

ORACLE中工具的使用05–TKPROF

Posted on by
tkprof工具的使用

  1. tkprof工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),用于格式化trace文件(也可以使用第三方的工具,格式化出来的更加美观和全面),从而可以非常方便的跟踪和诊断sql语句的执行效率;
  2. tkprof的语法;
    1. table:手动生成explain plan时,存储中间信息的临时表,默认为PROF$PLAN_TABLE;
    2. explain:手动生成explain时,连接数据库的用户名和密码;
    3. print:仅仅处理前integer数量的sql语句,如果我们需要生成脚本,该参数对脚本中包含的sql数量是不影响的;
    4. insert:生成脚本,该脚本会创建表,并把相关统计信息插入表,从而可以在数据库中查看;
    5. record:生成不包含递归sql的脚本文件;
    6. sys:是否包含sys用户执行的sql,大多数是递归sql;
    7. aggregate=no:如果设置为yes的话,会合并相同的sql语句,一般设置为no,分别查看每次的执行;
    8. waits:是否记录等待事件;
    9. sort:对sql语句排序的规则;
    10. 常用的语法:tkprof tracefiles outputfile sys=no aggregate=no;
  3. SQL Trace文件的内容:
    1. Parse, execute, and fetch counts:解析,执行,获取三个动作的执行次数;
    2. CPU and elapsed times:消耗的cpu时间和总时间,单位是秒;
    3. Physical reads and logical reads:物理读和逻辑读的次数;
    4. Number of rows processed:处理的记录数;
    5. Misses on the library cache:没有命中缓存的次数;
    6. Username under which each parse occurred:执行sql语句的用户;
    7. Each commit and rollback:每次的提交和回滚操作(tkprof不会处理这些信息);
    8. Wait event and bind data for each SQL statement:针对每条sql语句的等待事件和绑定变量信息;
    9. Row operations showing the actual execution plan of each SQL statement:sql语句的实际执行计划;
    10. Number of consistent reads, physical reads, physical writes, and time elapsed for each operation on a row;
  4. Sql Trace与执行计划:
    1. 在sql trace期间,如果sql语句的游标已经关闭,则在sql trace中会包含相应的执行计划,Example 1;
    2. 在sql trace期间,如果sql语句的游标没有关闭,则在sql trace中不会包含相应的执行计划;
    3. 如果之前sql语句已经执行过,则会包含执行计划;
    4. 如果在trace文件中不存在执行计划的相关信息,可以通过tkprof的explain参数来登陆数据库,并执行explain plan命令,把执行计划写入到trace文件中;
  5. 指定aggregate=yes,tkprof会汇总相同的sql语句信息,并在文件的最后汇总所有语句的相关信息;
  6. 使用insert和records参数的例子,Example 2;
  7. 注意一些陷阱:
    1. Avoiding the Argument Trap:如果在运行时不注意绑定变量的问题,很可能会陷入参数陷阱,EXPLAIN PLAN命令不会检查SQL语句中绑定变量的类型,总是认为是VARCHAR类型;所以,如果绑定变量如果实际为number或者date类型的话,tkprof会进行一个隐式转换,导致生成错误的执行计划;为了避免这种情况,需要自己执行转换;
    2. Avoiding the Read Consistency Trap:如果要查询的数据被更新了,而且没有提交,那么很可能就会进入一致性读的陷阱,因为如果有很多相同的查询的操作的话,会一直构建CR块;
    3. Avoiding the Schema Trap:当看到一个只有少量数据返回却发现扫描了非常多的块的统计是,有可能是一下原因:
      1. 表被经常的更新/删除,造成了水位线很高;
      2. 可能tkprof分析的trace文件记录的是之前没有在表上创建索引情况,而之后加上了索引;
    4. Avoiding the Time Trap:当执行一个简单的sql,但是看到elapsed time时间特别长时,可能是再等待其它的锁;
————————————— tkprof的语法 —————————————
[oracle@singleton11g ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename   Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
print=integer    List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename  List SQL statements and data inside INSERT statements.
sys=no           TKPROF does not list SQL statements run as user SYS.
record=filename  Record non-recursive statements found in the trace file.
waits=yes|no     Record summary for any wait events found in the trace file.
sort=option      Set of zero or more of the following sort options:
prscnt  number of times parse was called
prscpu  cpu time parsing
prsela  elapsed time parsing
prsdsk  number of disk reads during parse
prsqry  number of buffers for consistent read during parse
prscu   number of buffers for current read during parse
prsmis  number of misses in library cache during parse
execnt  number of execute was called
execpu  cpu time spent executing
exeela  elapsed time executing
exedsk  number of disk reads during execute
exeqry  number of buffers for consistent read during execute
execu   number of buffers for current read during execute
exerow  number of rows processed during execute
exemis  number of library cache misses during execute
fchcnt  number of times fetch was called
fchcpu  cpu time spent fetching
fchela  elapsed time fetching
fchdsk  number of disk reads during fetch
fchqry  number of buffers for consistent read during fetch
fchcu   number of buffers for current read during fetch
fchrow  number of rows fetched
userid  userid of user that parsed the cursor
————————————— tkprof的语法 —————————————
————————————— Example 1 —————————————
— 1.使用hr用户登录,并查看sql语句的执行计划;
sqlplus / as sysdba
conn hr/hr
EXPLAIN PLAN FOR SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
— 2.打开sql trace功能,然后设置标识符为E1,并执行sql语句;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E1′;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
— 3.格式化trace文件,然后查看执行计划:tkprof *_E1.trc e1.out sys=no aggregate=no;
————————————— Example 1 —————————————
————————————— Example 2 —————————————
— 1.使用hr用户登录,打开sql trace功能,然后设置标识符为E2,并执行sql语句;
sqlplus / as sysdba
conn hr/hr
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E2′;
SELECT USER FROM DUAL;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200;
SELECT COUNT(*) FROM HR.DEPARTMENTS;
— 2.格式化trace文件,然后查看执行计划:tkprof *_E2.trc e2.out sys=no insert=insert.sql record=record.sql;
— 3.查看record.sql文件;
[oracle@singleton11g trace]$ less record.sql
SELECT USER FROM DUAL ;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200 ;
SELECT COUNT(*) FROM HR.DEPARTMENTS ;
— 4.查看insert.sql文件;
REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
date_of_insert                       DATE
,cursor_num                           NUMBER
,depth                                NUMBER
,user_id                              NUMBER
,parse_cnt                            NUMBER
,parse_cpu                            NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                          NUMBER
,parse_current                        NUMBER
,parse_miss                           NUMBER
,exe_count                            NUMBER
,exe_cpu                              NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                            NUMBER
,exe_current                          NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                          NUMBER
,fetch_cpu                            NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                          NUMBER
,fetch_current                        NUMBER
,fetch_rows                           NUMBER
,ticks                                NUMBER
,sql_statement                        LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 2000, 2136, 0, 0, 0, 1
, 1, 0, 46, 0, 0, 0, 0, 0
, 2, 0, 26, 0, 0, 0, 1, 12753692
, ‘SELECT USER FROM DUAL
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 0, 356, 0, 0, 0, 0, 0
, 10, 0, 448, 0, 40, 0, 10, 50095736
, ‘select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spa
re2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 2, 0, 267, 0, 8, 0, 2, 1674
, ‘select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluc
ols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blk
cnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.proper
ty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.tri
gflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cacheh
it,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj#
(+)
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 118, 0, 0, 0, 0, 0
, 10, 2000, 2037, 0, 16, 0, 8, 3642
, ‘select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pct
free$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clu
fac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1
),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.def
errable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.p
ctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist
.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(t
o_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from
cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 6, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 1000, 301, 0, 0, 0, 0, 0
, 17, 0, 410, 0, 34, 0, 9, 2435
, ‘select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 7, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 200, 0, 0, 0, 0, 0
, 17, 1000, 402, 0, 6, 0, 15, 1796
, ‘select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(s
cale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,sc
ale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property,
nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where o
bj#=:1 order by intcol#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 8, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 999, 293, 0, 0, 0, 0, 0
, 10, 0, 459, 0, 30, 0, 10, 3519
, ‘select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(li
sts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(
bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 9, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 0, 199, 0, 0, 0, 0, 0
, 8, 0, 330, 0, 24, 0, 8, 2328
, ‘select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$
o where o.obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 10, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 1000, 149, 0, 0, 0, 0, 0
, 2, 0, 119, 0, 4, 0, 0, 1135
, ‘select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ wher
e obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 11, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 65, 0, 0, 0, 0, 0
, 5, 0, 179, 0, 6, 0, 3, 762
, ‘select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$
where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 12, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 51, 0, 0, 0, 0, 0
, 9, 1000, 269, 0, 18, 0, 7, 1321
, ‘select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj
#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 13, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 16, 0, 290, 0, 32, 0, 14, 2173
, ‘select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),r
owid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 14, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 14, 0, 336, 0, 0, 0, 0, 0
, 28, 0, 499, 0, 56, 0, 14, 4305
, ‘select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 1, 0, 1, 1999, 1376, 0, 0, 0, 1
, 1, 2000, 2335, 0, 0, 0, 1, 0
, 1, 0, 26, 0, 2, 0, 1, 4365
, ‘select condition from cdef$ where rowid=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 16, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 3, 0, 71, 0, 0, 0, 0, 0
, 3, 0, 102, 0, 9, 0, 3, 905
, ‘select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample
_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, av
gcln from hist_head$ where obj#=:1 and intcol#=:2
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 17997, 16136, 0, 0, 0, 1
, 1, 0, 23, 0, 0, 0, 0, 0
, 2, 0, 78, 0, 2, 0, 1, 1010
, ‘SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 0, 84, 1, 8999, 9177, 0, 0, 0, 1
, 1, 1000, 113, 0, 0, 0, 0, 0
, 2, 0, 254, 0, 1, 0, 1, 2161
, ‘SELECT COUNT(*) FROM HR.DEPARTMENTS
‘)
/
COMMIT;
set sqlterminator on
————————————— Example 2 —————————————

ORACLE中工具的使用04–TRCSESS

Posted on by
trcsess工具的使用

  1. trcsess工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),它可以把USER_DUMP_DEST目录下多个trc文件根据一定规则合并成一个trc文件,然后使用tkprof工具进行格式化;
  2. 语法:trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>;                                                                               
  3. 可以根据session,clientid,service,action,module和trace file的文件名(支持通配符)等不同的维度来合并USER_DUMP_DEST目录下的trc文件;相关的信息都可以从v$session视图中查看的到:SELECT sid, client_identifier, service_name, module, action, sql_trace FROM v$session;同样的可以从trace文件中查看的到;(其中sid是[sid.serial#]格式)
  4. trace文件的命名规则是<ORACLE_SID>_ora_<spid>_<TRACEFILE_IDENTIFIER>.trc,默认的TRACEFILE_IDENTIFIER是空,可以通过ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’命令来修改trace文件的标示符;
  5. 可以通过DBMS_SESSION.SET_IDENTIFIER(‘<TRACEFILE_IDENTIFIER>’)过程来设置client id;
  6. 该工具主要应用于共享服务器模式或者采用连接池模式,我们很难针对某用户进行跟踪的情况下;
  7. 用例:根据tracefile_identifier,service/module来合并trace文件;
———————————- 根据tracefile_identifier来合并trace文件 ———————————-
— 1.清空user_dump_dest目录下的trace文件(仅仅为了测试);
— 2.打开session 1做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 1’ FROM DUAL;
— 3.打开session 2做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 2’ FROM DUAL;
— 4.查看生成的trace文件;

— 5.合并trace文件;
根据service_name合并:trcsess output=ti1_service.out service=’SYS$USERS’ *_ti1.trc
根据module_name合并:trcsess output=ti1_module.out module=’sqlplus@singleton11g.snda.com (TNS V1-V3)’ *_ti1.trc
———————————- 根据tracefile_identifier来合并trace文件 ———————————-

ORACLE中工具的使用03–LogMiner

Posted on by
LogMiner工具的使用:

  1. 用来分析Oracle数据库运行过程中产生的redo logfile和archived logfile来获取对数据库操作的DML语句;
  2. 以SYS用户安装LogMiner工具:
    1. 安装DMBS_LOGMNR包:@?/rdbms/admin/dbmslm.sql;                                 
    2. 安装DMBS_LOGMNR_D包:@?/rdbms/admin/dbmslmd.sql;                                 
  3. LogMiner包的介绍:
    1. DMBS_LOGMNR包:包含了初始化LogMiner工具,打开和关闭LogMiner会话的子程序;
      1. DMBS_LOGMNR.ADD_LOGFILE(LogFileName, options):开启一个LogMiner的会话,并添加redo logfile文件到这个队列里面;
        1. LogFileName:指定要分析的redo logfile;
        2. options:打开一个新的LogMiner会话并添加一个redo logfile(DMBS_LOGMNR.NEW);添加一个redo logfile到现存的LogMiner会话(DBMS_LOGMNR.ADDFILE);
      2. DMBS_LOGMNR.REMOVE_LOGFILE(LogFileName):通过指定名称,从被分析的redo logfile队列中移除一个redo logfile;
      3. DMBS_LOGMNR.START_LOGMNR(startScn, endScn, startTime, endTime, DictFileName, options):加载LogMiner数据字典并启动LogMiner来分析redo logfiles,最后填充动态视图;
        1. startScn:LogMiner返回大于此SCN的redo记录(分析后可以查询select filename, low_scn, next_scn from v$logmnr_logs来查询每个redo logfile包含的SCN范围);
        2. endScn:LogMinfer返回小于此SCN的redo记录;
        3. startTime:默认为1988-01-01,如果指定了startScn,则忽略此参数;
        4. endTime:默认为2110-12-31,如果指定了endScn,则忽略此参数;
        5. DictFileName:,默认为空,指定包含LogMiner字典的文本文件.用来生成v$logmnr_contents视图.必须指定DBMS_LOGMNR_D.BUILD过程中的全路径;
        6. options:分析日志时的操作选项,查看帮助文档;
      4. DMBS_LOGMNR.END_LOGMNR:结束LogMiner的会话,当退出Database Session的时候会自动调用此过程;
    2. DMBS_LOGMNR_D包:用来创建LogMiner的数据字典;
      1. DMBS_LOGMNR_D.BUILD(dictionary_filename, dictionary_location, options):即导出数据库的数据字典到一个文本中,数据字典发生变化都要重新创建一次;;
        1. dictionary_filename:指定LogMiner字典的文件名;
        2. dictionary_location:指定LogMiner字典文件的路径;
        3. options:指定LogMiner字典写的位置,文本文件(STORE_IN_FLAT_FILE,默认)或者是在线日志文件(STORE_IN_REDO_LOGS);
        4. 指定文本文件,则必须设置UTL_FILE_DIR初始化参数(ALTER SYSTEM SET UTL_FILE_DIR=’\tmp’ SCOPE=spfile;然后重启数据库服务使其生效):
          1. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’);
          2. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’, options => dbms_logmnr_d.store_in_flat_file);
        5. 指定在线日志文件,不常用,影响性能:EXECUTE dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
      2. DBMS_LOGMNR_D.SET_TABLESPACE(new_tablespace);
        1. 默认情况下,LogMiner的表会被创建到SYSAUX表空间,使用此函数可以修改LogMiner表存放的表空间;
        2. DBMS_LOGMNR_D.SET_TABLESPACE(‘tablespace_name’);
  4. 打开数据库的附加日志:ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;                   
  5. 创建数据字典:
    1. 创建表空间:CREATE TABLESPACE LOGMINER DATAFILE ‘/u01/app/oracle/oradata/AUX/logminer01.dbf’ SIZE 100M AUTOEXTEND ON;                     
    2. 设置LogMiner表的表空间:EXEC dbms_logmnr_d.set_tablespace(‘LOGMINER‘);                                     
    3. 设置UTL_FILE_DIR参数,然后重启数据库使之生效:alter system set utl_file_dir=’/u01/app/oracle/oradata/AUX/’ scope=spfile;                      
    4. 生成数据字典:EXEC dbms_logmnr_d.build(‘dictionary.lm’, ‘/u01/app/oracle/oradata/AUX/’);                             
  6. 模拟一个人为的错误;                                                                    
  7. 打开LogMiner的session并添加日志文件,只是在当前的session有效(查询归档日志的路径:select name from v$archived_log);              
  8. 分析日志,指定生成的LogMiner数据字典:EXEC dbms_logmnr.start_logmnr(DictFileName=>’/u01/app/oracle/oradata/AUX/dictionary.lm’);                 
  9. 此时就可以查询LogMiner的视图了,可以在IDE环境下去做,可以更好的过滤想要的结果;
  10. 退出LogMiner的会话:EXEC dbms_logmnr.end_logmnr;                                      
  11. LogMiner的视图:
    1. V$LOGMNR_CONTENTS:用来存放LogMiner分析日志后的结果;
      1. SELECT scn, TIMESTAMP, log_id, operation, sql_redo, sql_undo FROM v$logmnr_contents WHERE username = ‘HR’;
      2. 分析结果仅在运行过程dbms_logmrn.start_logmnr这个会话的生命期中存在.因为所有的LogMiner存储都在PGA内存中,所有其他的进程是看不到它的,同时随着进程的结束,分析结果也随之消失;
    2. V$LOGMNR_DICTIONARY:使用的数据字典的信息;
    3. V$LOGMNR_LOGS:包含所分析的日志的信息,SELECT log_id, filename, db_name, low_scn, next_scn, low_time, high_time, status FROM v$logmnr_logs;
    4. V$LOGMNR_SESSION:包含LogMiner当前的Session信息;
  12. 注意事项:
    1. 利用LogMiner工具来分析其它数据库实例产生的redo logfiles,使用LogMiner分析其它数据库实例时的注意事项;
    2. LogMiner必须使用被分析数据库实例产生的字典文件,而不是安装LogMiner的数据库产生的字典文件,另外必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同;
    3. 被分析数据库平台必须和当前LogMiner所在数据库平台一样,也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 8i产生的,那么也必须在一个运行在UNIX平台上的Oracle实例上运行LogMiner,而不能在其它如Microsoft NT上运行LogMiner,当然两者的硬件条件不一定要求完全一样;
    4. LogMiner日志分析工具仅能够分析Oracle 8以后的产品,不过它可以分析Oracle8的日志,对于8以前的产品,该工具也无能为力.另外,Oracle8i只能对DML操作进行分析,从Oracle9i开始不仅可以分析DML操作,而且也可以分析DDL操作;
  13. Oracle11g中EM继承了LogMiner工具(需要开启附加日志):
    1. LogMiner的路径:Avaliability->Manage->View and Manage Transactions;            
    2. 可以通过时间/SCN,查询某些表/用户下的所有的事务/DDL;                             
    3. 结果页面;                                                                         
— 模拟人为错误,并切换归档;
conn hr/hr
create table lm tablespace logminer as select * from employees;
delete from lm where employee_id < 200;
commit;
conn / as sysdba
alter system switch logfile;
— 打开LogMiner的session并添加日志文件;
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_3_85j72xlx_.arc’, options=>dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_4_85j76znp_.arc’, options=>dbms_logmnr.addfile);

ORACLE中工具的使用02–DBVERIFY

Posted on by
DBVERIFY工具的使用

  1. DBVERIFY工具介绍:
    1. DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems;
    2. DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs;
    3. 只支持镜像备份,不支持备份集验证;测试发现控制文件可以验证,但是在线日志不可以;
  2. DBVERIFY的语法:dbv key=value;
    1. USERID=Username/Password:指定用户名密码,如果检查的文件是ASM文件系统,需要提供验证信息;
    2. FILE=file_name:要检查的datafile文件名;
    3. START=start_block:要检查的文件的开始块号,默认是文件第一个块;
    4. END=end_block:要检查文件的结束的块号,默认是文件最后一个块;
    5. BLOCKSIZE=size:指定块的大小,默认是8k,如果使用的不是8k的块需要指定此参数;
    6. LOGFILE=log_file:指定日志文件,如果不指定就显示在终端;
    7. FEEDBACK=n:返回检查的进度,n个单位打一个点;
    8. PARFILE=parameter_file:指定一个参数文件;
    9. SEGMENT_ID:指定段的id(由三部分组成:tablespace_id.header_file.header_block),可以通过sql语句查询;
  3. DBVERIFY的用法:
    1. 对datafile文件进行检查;
    2. 对segment进行检查;
    3. 对备份集的检查;
    4. 对控制文件的检查;
  4. 检查datafile中的block:
    1. 这种模式下,会扫描一个datafile中的一个或者多个blocks,并生成一份检查结果;
    2. 如果datafile是ASM系统文件的话,需要提供USERID连接到这个ASM文件;
    3. 检查system表空间:dbv file=$ORACLE_BASE/oradata/PROD/system01.dbf feedback=1000;
  5. 检查segment:
    1. 在这种模式下,可以用来检查一个对象(表/索引)使用的segment的可用性,指定segment的所有链表都会被检查;
    2. 被检查的相应的对象会被加锁,如果是索引的话,则引用的表会被加锁;
    3. 需要提供sysdba的身份验证,因为需要读取segment的数据字典;
    4. 检查scott用户下emp表的segment:dbv userid=scott/tiger segment_id=4.4.2003 feedback=10;                    
  6. 验证控制控制文件:
    1. 常规验证,发现错误,因为块大小不对,修改验证语法:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl feedback=100;     
    2. 验证:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl blocksize=16384 feedback=100;
— 查询某个SEGMENT的id;
SELECT a.ts# || ‘.’ || b.header_file || ‘.’ || b.header_block
FROM v$tablespace a, dba_segments b
WHERE a.NAME = b.tablespace_name AND b.segment_name = ‘segment_name’ AND owner = ‘owner’;
— 块损坏的几种检查;
————————————————————————————–
特性                      坏块侦测类型                    能否修复损坏块
————————————————————————————————
DBVERIFY                  物理                         否
ANALYZE                   逻辑                         否
DB_BLOCK_CHECKING         逻辑                         否
DB_BLOCK_CHECKSUM         物理                         否
exp                       物理                         否
FlashBack                 逻辑                         是
DBMS_REPAIR               逻辑                         是
Block media recovery      未知                         是

ORACLE中工具的使用01–DBNEWID

Posted on by
DBNEWID工具的使用

  1. DBNEWID工具介绍:
    1. 手动创建一个数据库的拷贝时,在重建控制文件时可以指定一个新的DBNAME,但是不能修改DBID.DBID是内部唯一标示一个数据库的标示符;RMAN是使用DBID区分数据库的,所以就不能把这两个数据库注册到同一个RMAN的资源库中了;
    2. DBNEWID工具可以:
      1. 只修改DBID(无法控制修改为多少);
      2. 只修改DBNAME;
      3. 同时修改DBID和DBNAME;
    3. 如果修改了DBID,之前所有的备份,归档日志都会失效,而且打开数据库时需要指定RESETLOGS参数;
    4. 强烈建议在修改DBID之前对数据库进行备份;
    5. 使用DBNEWID工具不会影响global_name,它只能通过ALTER DATABASE语法进行修改,由DB_NAME和域组成:ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db_domain;
    6. DBNEWID工具也不会修改INSTANCE_NAME,但是会修改DB_NAME,DB_UNIQUE_NAME和SERVICE_NAMES;
  2. DBNEWID的语法:nid key=value;
    1. TARGET=user/pwd@tns_name:登陆目标数据库;
    2. DBNAME=newname:指定要修改的新的数据库的名称,如果不指定此参数,则只修改DBID;
    3. SETNAME=YES/NO:默认为NO,如果指定为YES,表示值修改数据库名称;
    4. REVERT=YES/NO:默认为NO,如果为YES,表示如果失败了回退到之前的状态;
    5. LOGFILE=log_file:指定日志文件;
    6. APPEND=YES/NO:指定是追加日志还是覆盖日志;
    7. 例子:
      1. 只修改DBID:nid target=user/pwd@tns_name logfile=nid.log;
      2. 只修改DBNAME:nid target=user/pwd@tns_name dbname=newname setname=yes logfile=nid.log;
      3. 同时修改DBID和DBNAME:nid target=user/pwd@tns_name dbname=newname logfile=nid.log;
      4. 发生错误回退到之前的状态:nid target=user/pwd@tns_name revert=yes logfile=nid.log
  3. 修改DBID和DBNAME:
    1. 对数据库做一次全备;
    2. 配置好监听(静态注册数据库)和网络名,如果是在本机操作则不需要此步骤;
    3. 查看当前的DBID和DBNAME:SELECT dbid, name FROM v$database;             
    4. 正常关闭数据库,并启动到mount状态;                                       
    5. 调用nid工具,使用sysdba登陆:nid target=sys/oracle dbname=OMR,修改完成后数据库处于shutdown状态;                  
    6. 修改spfile文件中的DB_NAME参数(如果只修改DBID的话,不用这个步骤);               
    7. 重置密码文件(如果只修改DBID或者没有修改ORACLE_SID的话,不用这个步骤,因为密码文件只跟ORACLE_SID有关):
    8. 启动数据库到mount状态,然后以resetlogs方式打开数据库:ALTER DATABASE OPEN RESETLOGS;(如果只是修改了DB_NAME的话,不用这个步骤,之前的日志还可以使用)
    9. 查看新的DBID和DB_NAME;                                               

导入导出工具02–SQL*Loader工具与外部表

Posted on by
SQL*Loader工具与外部表

  1. SQL*Loader:
    1. 概念:
      1. 可以在服务器端和客户端操作,数据可以存放在服务器端或者客户端;
      2. 可以导入固定格式的TXT文件的数据;
    2. INSERT INTO … SELECT …的两种插入数据的方法:
      1. conventional INSERT操作:通用的插入方法,数据库会优先重新利用HWM(High Water Mark)以下的数据块,然后再使用HWM以上的数据块,使得新旧数据交叉存放,同时还要维护数据的完整性(主外键,CHECK约束等);
      2. direct-path INSERT操作:直接路径插入方法,数据库把数据直接插入到高水位之后的数据块中,数据不经过buffer cache,直接写入数据文件,忽略数据的完整性,使得性能最大的提升;
        1. 顺序插入分区/非分区表:只有一个线程把数据写到HWM之后;
        2. 并行插入分区表:每个进程可以被分配到一个或者多个分区,但是一个分区最多只有一个进程负责,推荐使用跟分区数量相同的并行度;数据同样只在每个分区的HWM之后的数据块写入;
        3. 并行插入非分区表:每个进程分配一个临时的段,然后把所有临时段合并;
      3. SQL*Loader的convention-path load方式和direct-path load方式跟这个相似;
      4. 可以通过使用/*+ APPEND */的hint,来使用直接导入数据,例子;
      5. conventional方式使用会产生redo日志,而direct-path方式始终不产生日志,与表本身是否是LOGGING模式无关,如果数据库是FORCE LOGGING模式,则无论那种方式都会记录日志,例子;
    3. SQL*Loader的介绍;
      1. Input data files:要导入的数据文件;
        1. 以文本形式存在;
        2. 可以有多个数据文件;
        3. 支持三种数据格式:
          1. Fixed-Record Format:每个列有固定的长度,效率高单灵活性差,如:数据文件中每11个字节是一个字段infile ‘example.dat’ “fix 11”;
          2. Variable-Record Format:每个列长度不固定,在最前面n位来记录它的长度,如:前三个字符表示记录的长度infile ‘example.dat’ “var 3”;
          3. Stream-Record Format:流记录,以某个符号区分列,最常用的方式;
        4. 记录的种类:
          1. Physical Record:即数据文本中的一行记录;
          2. Logical Record:在数据库中表的一行记录,可以合并几行物理记录组成一个逻辑记录;
      2. Loader control file:导入数据的控制文件;
        1. 指定导入数据的目录;
        2. 数据的格式化;
        3. 配置信息:内存,拒绝记录的规则,异常终止后的处理;
        4. 如何操作数据;
        5. 注释使用[–]符号;
        6. 不能使用CONSTANT和ZONE关键字;
      3. Parameter file:可选,可以把sqlldr命令后面的参数保存起来,只需要指定参数文件即可;
      4. Discard file:可选,被抛弃的数据,不满足过滤器条件的记录;
        1. 可以控制打开或者关闭此功能;
        2. 可以在控制文件中定义记录选择的标准;
        3. 可以规定当discard file中记录超过一定数量的话就终止;
      5. Bad file:可选,被拒绝的数据;
        1. SQL*Loader拒绝的数据,比如输入数据的格式不对;
        2. Oracle数据库拒绝的数据,比如违反约束的记录;
      6. Log file:可选,日志信息,如果没有指定就会生成以.log结尾的跟数据文件同名的文件;
        1. 头信息;
        2. 全局信息;
        3. 表信息;
        4. 数据文件信息;
        5. 表加载的信息;
        6. 统计信息;
        7. 附加信息,比如花费的时间等待;
      7. 架构图;                                                                                 
    4. Conventional和Direct-Path Load方法的对比:
      1. Conventional Load:
        1. 读取一条记录,解析,插入,提交;
        2. 总是会产生REDO日志;
        3. 强制检查所有约束;
        4. 会激活触发器;
        5. 可以插入到簇表;
        6. 其它用户可以修改表;
      2. Direct-Path Load:
        1. 把记录构造成块,直接进行块拷贝;
        2. 只有在数据库为FORCE LOGGING模式下才会产生REDO日志;
        3. 只检查主键,唯一键,NOT NULL约束;
        4. 忽略触发器;
        5. 不能对簇表加载数据;
        6. 其它用户不能修改表;
      3. 可以使用并行操作来加快速度,但是需要手动指定并行度;
    5. SQL*Loader的语法:sqlldr keyword=value,命令后面是键值对的组合;
      1. userid:username/password;
      2. control:控制文件名称;
      3. log:日志文件名称;
      4. bad:bad文件名称;
      5. data:数据文件名称;
      6. discard:discard文件名称;
      7. discardmax:允许最大的discard的记录数,默认为所有记录;
      8. skip:跳过的逻辑记录数,默认为0;
      9. load:加载的逻辑记录数,默认为所有记录;
      10. errors:允许错误的记录数,默认为50;
      11. silent:不提示header/feedback/errors/discards/partitions等信息,默认为FALSE;
      12. direct:使用直接路径,默认为FALSE;
      13. multithreading:直接数据导入时使用多线程;
      14. parfile:使用的参数文件的名称;
      15. parallel:使用并行加载,默认是FALSE;
      16. skip_unusable_indexes:跳过unusable的索引,默认为FALSE;
      17. skip_index_maintenance:不维护索引,标记索引为unusable,默认为FALSE;
      18. commit_discontinued:当加载数据终止时,提交已经加载了的数据,默认为FALSE;
      19. streamsize:使用直接路径加载时的缓存区大小,默认是256000;
      20. external_table:使用外部表导入数据;
        1. NOT_USED:不使用外部表;
        2. GENERATE_ONLY:只生成外部表的创建语法,生成在log文件中;
        3. EXECUTE:使用外部表导入数据;
      21. date_cache:导入数据的cache大小;
      22. _display_exitcode:显示sqlloader退出时的退出码;
    6. SQL*Loader的退出码,用在SHELL编程中:
      1. EX_SUCC(0):所有的记录加载成功;
      2. EX_WARN(2):一些记录被放到bad file/discard file/异常终止;
      3. EX_FAIL(1):语法错误,操作错误;
      4. 如果状态码大于0的话,就有问题;
    7. SQL*Loader的例子:
      1. 在9i的数据库的$ORACLE_HOME/rdbms/demo/目录下ul*的11个例子(.ctl是控制文件,.sql是修改表的脚本,.dat是数据,在scott用户下完成);
      2. EXAMPLE1:加载变长的数据,数据列之间以逗号分隔,可能还以引号包围,数据和控制文件在一起;
      3. EXAMPLE2:加载固定格式的数据,数据和控制文件分开;
      4. EXAMPLE3:加载有定界符的,随意格式的文件,数据与控制文件在一起;
      5. EXAMPLE4:加载合并的物理记录,合并多个物理记录到一个逻辑记录中;
      6. EXAMPLE5:加载数据到多个表中;
      7. EXAMPLE6:使用直接路径的方式加载数据;
      8. EXAMPLE7:从一个格式化的报表中抽取数据并加载;
      9. EXAMPLE8:加载分区表;
      10. EXAMPLE9:加载CLOB列;
      11. EXAMPLE10:加载REF列和ARRAYs列;
      12. EXAMPLE11:转换字符集,使用Unicode编码加载数据;
  2. DIRECTORY对象:
    1. 只有SYS用户可以拥有DIRECTORY对象,但是它可以授权其它用户创建的权限;
    2. DIRECTORY对象只是一个文件系统目录的映射/别名(如果oracle用户没有这个目录的权限怎么办?);
    3. 具有DBA角色或者CREATE ANY DIRECTORY权限的用户可以创建DIRECTORY对象:CREATE DIRECTORY ext_tab_dir AS ‘/u01/datafiles’;
    4. 查看系统中存在的DIRECTORY对象:SELECT * FROM dba_directories;
    5. 创建者/DBA可以对其它用户/角色授权:GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;
    6. 查看某个DIRECTORY对象的权限:SELECT * FROM dba_tab_privs WHERE table_name = ‘EXT_TAB_DIR’;
    7. 创建者和DBA自动具有RW权限;
    8. 删除DIRECTORY对象:DROP DIRECTORY ext_tab_dir;
  3. 外部表(External Tables):
    1. 外部表的概念:
      1. 10g之前,外部表是只读的;10g之后,外部表可读可写;
      2. 外部表是对SQL*Loader功能的补充,可以像访问数据库中表一样访问数据库外的资源;
      3. 只能在服务器端操作,数据要存放在服务器端,因为需要使用DIRECTORY;
      4. 可以导入固定格式的TXT文件的数据;
      5. 在ETL中尽量多用外部表,而少用SQL*Loader;
    2. 创建外部表:
      1. 使用CREATE TABLE table_name(…) ORGANIZATION EXTERNAL语法,需要指定的属性:
        1. TYPE:指定外部表使用的访问引擎;
          1. ORACLE_LOADER:默认值,只能进行数据加载操作,而且数据必须是文本文件;
          2. ORACLE_DATAPUMP:可以进行数据加载/卸载的操作,数据必须是二进制的dump文件;
        2. DEFAULT DIRECTORY:指定外部表所使用的默认目录,是一个DIRECTORY对象,而不是一个路径;
        3. ACCESS PARAMETERS:指定外部表数据对应的表中的列和某一列的格式化信息,与使用的引擎有关;
        4. LOCATION:指定外部表的位置,是目录和文件名的组合(directory:file),如果没有指定目录则使用默认的目录;
      2. 创建的步骤:
        1. 创建DIRECTORY对象;
        2. 创建外部表;
        3. 其它操作:可以再创建一个表,然后使用INSERT INTO … SELECT …语法导入数据;
    3. 加载和卸载数据:
      1. 加载数据:把外部表中的数据导入到数据库的表中;
      2. 卸载数据:把数据库表中的内容导入到外部不表中,只能使用ORACLE_DATAPUMP引擎;
    4. 使用外部表时的数据类型转换,LOB字段:CREATE TABLE LONG_TAB_XT (LONG_COL CLOB) ORGANIZATION EXTERNAL…SELECT TO_LOB(LONG_COL) FROM LONG_TAB;
    5. 并行查询外部表:
      1. ORACLE_LOADER引擎:
        1. 如果加载文件时顺序指定了多个文件;
        2. 记录使用VAR格式化;
      2. ORACLE_DATAPUMP引擎:
        1. 加载数据时:
          1. 加载时使用了多个文件;
          2. 使用一个大的文件时也会并行,因为会记录文件读取的偏移量;
        2. 卸载数据时:
          1. LOCATION参数指定多个文件时可以发生并行;
          2. 如果并行度等于文件的个数的话,每个进程写一个文件;
          3. 如果并行度大于文件的个数的话,并行度降低为文件的个数;
          4. 如果并行度小于文件的个数的话,外部文件将不会被使用;
      3. 自己决定并行度;
    6. 外部表的限制:
      1. 不支持加密列;
      2. 不支持LONG类型的列;
      3. 特殊字符需要用双引号转换;
      4. 不能是临时表;
      5. 不能添加约束;
    7. SQL*Loader和外部表的不同:
      1. 在sql*loader中可以使用反斜杠来转义一个字符:FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\”;
      2. 在外部表中使用反斜杠会报错,可以使用引号来分割字段:TERMINATED BY ‘,’ ENCLOSED BY “‘”;
    8. 外部表的例子:
      1. ORACLE_LOADER引擎:使用脚本导入的例子;
      2. ORACLE_LOADER引擎:使用sql*loader生成外部表脚本的例子;
      3. ORACLE_DATADUMP引擎:导出数据的例子;
      4. ORACLE_DATADUMP引擎:导入数据的例子;
————————- Direct-Path INSERT的例子 ————————-
— 创建一个空表;
CREATE TABLE bt AS SELECT * FROM dba_objects WHERE 1 = 0;
— 正常插入数据;
INSERT INTO bt SELECT * FROM dba_objects;
— 在提交之前就可以查询到表的数量;
SELECT COUNT(*) FROM bt;
COMMIT;
— 使用直接路径的方式;
INSERT /*+ APPEND */ INTO bt SELECT * FROM dba_objects;
— 提交之前查询表报错,从一方面说明了直接路径插入;
SELECT COUNT(*) FROM bt;
SELECT COUNT(*) FROM bt
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
————————- Direct-Path INSERT的例子 ————————-
————————- Direct-Path INSERT NOLOGGING的例子 ————————-
— 一,表处于LOGGING模式,数据库不是FORCE LOGGING模式,观察几种插入方式产生的日志;
— 1.传统插入,LOGGING模式,会产生日志;
— 2.传统插入,NOLOGGING模式,同样会产生日志;
— 3.直接插入,LOGGING模式,不会产生日志;
— 4.直接插入,NOLOGGING模式,也不会产生日志;
— 二,表处于NOLOGGING模式,数据库处于非FORCE LOGGING模式:传统方式都会产生日志,直接插入方式都不产生日志;
— 三,数据库处于FORCE LOGGING模式,无论表是什么模式,无论哪种方式都会记录日志;
————————- Direct-Path INSERT NOLOGGING的例子 ————————-
————————- SQL*Loader的例子 ————————-
— EXAMPLE1:加载变长的数据,数据列之间以逗号分隔,可能还以引号包围,数据和控制文件在一起;
— 创建测试表;
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT
(
DEPTNO NUMBER(2) not null,
DNAME  VARCHAR2(14),
LOC    VARCHAR2(13)
);
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
— 控制文件和数据;
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,”SARATOGA”
10,”ACCOUNTING”,CLEVELAND
11,”ART”,SALEM
13,FINANCE,”BOSTON”
21,”SALES”,PHILA.
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo1.ctl;
— 验证数据;
— EXAMPLE2:加载固定格式的数据,数据和控制文件分开;
— 控制文件;
LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo2.dat’
INTO TABLE EMP
( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
ENAME    POSITION(06:15) CHAR,
JOB      POSITION(17:25) CHAR,
MGR      POSITION(27:30) INTEGER EXTERNAL,
SAL      POSITION(32:39) DECIMAL EXTERNAL,
COMM     POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO   POSITION(50:51) INTEGER EXTERNAL)
— 数据文件;
7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
7566 JONES      MANAGER   7839  3123.75          20
7499 ALLEN      SALESMAN  7698  1600.00   300.00 30
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo2.ctl;
— 验证数据;
— EXAMPLE3:加载有定界符的,随意格式的文件,数据与控制文件在一起;
— 修改表结构:alter table emp add (projno number, loadseq number);
— 控制文件;
LOAD DATA
INFILE *
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
(empno, ename, job, mgr,
hiredate DATE(20) “DD-Month-YYYY”,
sal, comm,
deptno   CHAR TERMINATED BY ‘:’,
projno,
loadseq  SEQUENCE(MAX,1))
BEGINDATA
7782, “Clark”, “Manager”, 7839, 09-June-1981, 2572.50,, 10:101
7839, “King”, “President”, , 17-November-1981, 5500.00,, 10:102
7934, “Miller”, “Clerk”, 7782, 23-January-1982, 920.00,, 10:102
7566, “Jones”, “Manager”, 7839, 02-April-1981, 3123.75,, 20:101
7499, “Allen”, “Salesman”, 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, “Martin”, “Salesman”, 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, “Chan”, “Analyst”, 7566, 03-May-1982, 3450,, 20:101
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo3.ctl;
— 验证数据;
— EXAMPLE4:加载合并的物理记录,合并多个物理记录到一个逻辑记录中;
— 修改表结构;
DROP TABLE EMP;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
CREATE UNIQUE INDEX UNQ_EMPNO ON EMP(EMPNO);
— 控制文件;
LOAD DATA
INFILE “/u01/sqlldrdemo/demo4.dat”
DISCARDFILE “/u01/sqlldrdemo/demo4.dis”
DISCARDMAX 999
REPLACE
CONTINUEIF (1) = ‘*’
INTO TABLE EMP
( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
ENAME    POSITION(06:15) CHAR,
JOB      POSITION(17:25) CHAR,
MGR      POSITION(27:30) INTEGER EXTERNAL,
SAL      POSITION(32:39) DECIMAL EXTERNAL,
COMM     POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO   POSITION(50:51) INTEGER EXTERNAL,
HIREDATE POSITION(52:60) INTEGER EXTERNAL)
— 数据文件;
*7782 CLARK      MA
NAGER   7839  2572.50   -10    2512-NOV-85
*7839 KING       PR
ESIDENT       5500.00          2505-APR-83
*7934 MILLER     CL
ERK     7782   920.00          2508-MAY-80
*7566 JONES      MA
NAGER   7839  3123.75          2517-JUL-85
*7499 ALLEN      SA
LESMAN  7698  1600.00   300.00 25 3-JUN-84
*7654 MARTIN     SA
LESMAN  7698  1312.50  1400.00 2521-DEC-85
*7658 CHAN       AN
ALYST   7566  3450.00          2516-FEB-84
*     CHEN       AN
ALYST   7566  3450.00          2516-FEB-84
*7658 CHIN       AN
ALYST   7566  3450.00          2516-FEB-84
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo4.ctl;
— 验证数据;
— 检查不满足条件的记录;
— EXAMPLE5:加载数据到多个表中;
— 创建测试表;
DROP TABLE emp;
DROP TABLE proj;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
CREATE UNIQUE INDEX unq_empno ON EMP (EMPNO);
CREATE TABLE PROJ
(
EMPNO  NUMBER,
PROJNO NUMBER
);
— 控制文件;
LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo5.dat’
BADFILE ‘/u01/sqlldrdemo/demo5.bad’
DISCARDFILE ‘/u01/sqlldrdemo/demo5.dis’
REPLACE
INTO TABLE EMP
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
ENAME    POSITION(6:15)  CHAR,
DEPTNO   POSITION(17:18) CHAR,
MGR      POSITION(20:23) INTEGER EXTERNAL)
INTO TABLE PROJ
— PROJ has two columns, both not null: EMPNO and PROJNO
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(25:27) INTEGER EXTERNAL)   — 1st proj
INTO TABLE PROJ
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(29:31) INTEGER EXTERNAL)   — 2nd proj
INTO TABLE PROJ
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(33:35) INTEGER EXTERNAL)   — 3rd proj
— 数据文件;
1234 BAKER      10 9999 101 102 103
1234 JOKER      10 9999 777 888 999
2664 YOUNG      20 2893 425 abc 102
5321 OTOOLE     10 9999 321  55  40
2134 FARMER     20 4555 236 456
2414 LITTLE     20 5634 236 456  40
6542 LEE        10 4532 102 321  14
2849 EDDS       xx 4555     294  40
4532 PERKINS    10 9999  40
1244 HUNT       11 3452 665 133 456
123 DOOLITTLE  12 9940         132
1453 MACDONALD  25 5532     200
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo5.ctl;
— 验证数据;
— 检查不满足条件的记录;
— EXAMPLE6:使用直接路径的方式加载数据;
— 创建测试表;
DROP TABLE emp;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
)
CREATE UNIQUE INDEX unq_empno ON emp(empno);
— 控制文件;
LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo6.dat’
REPLACE
INTO TABLE emp
SORTED INDEXES (unq_empno)
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks,
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)
— 数据文件;
7566 JONES      MANAGER   7839  3123.75          20
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20
7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo6.ctl direct=y;
— 验证数据;
— EXAMPLE7:从一个格式化的报表中抽取数据并加载;
— 创建测试表;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
CREATE UNIQUE INDEX UNQ_EMPNO on EMP (EMPNO);
— 添加触发器,这一步是关键;
CREATE OR REPLACE PACKAGE sqlloader AS
last_deptno  NUMBER;
last_job     CHAR(9);
last_mgr     NUMBER;
END sqlloader;CREATE OR REPLACE TRIGGER sqlloader_emp_insert
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :new.deptno IS NOT NULL THEN
sqlloader.last_deptno := :new.deptno;   — save value for later use
ELSE
:new.deptno := sqlloader.last_deptno;   — use last valid value
END IF;
IF :new.job IS NOT NULL THEN
sqlloader.last_job := :new.job;   — save value for later use
ELSE
:new.job := sqlloader.last_job;   — use last valid value
END IF;
IF :new.mgr IS NOT NULL THEN
sqlloader.last_mgr := :new.mgr;   — save value for later use
ELSE
:new.mgr := sqlloader.last_mgr;   — use last valid value
END IF;
END sqlloader_emp_insert;

— 控制文件;
LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo7.dat’
DISCARDFILE ‘/u01/sqlldrdemo/demo7.dis’
APPEND
INTO TABLE emp
WHEN (57)=’.’
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno   POSITION(1:2)  INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job      POSITION(7:14)   CHAR  TERMINATED BY WHITESPACE
NULLIF job=BLANKS  “UPPER(:job)”,
mgr      POSITION(28:31)  INTEGER EXTERNAL TERMINATED BY WHITESPACE
NULLIF mgr=BLANKS,
ename    POSITION (34:41) CHAR  TERMINATED BY WHITESPACE
“UPPER(:ename)”,
empno    INTEGER EXTERNAL  TERMINATED BY WHITESPACE,
sal      POSITION(51)  CHAR  TERMINATED BY WHITESPACE
“TO_NUMBER(:sal,’$99,999.99′)”,
comm     INTEGER EXTERNAL  ENCLOSED BY ‘(‘ AND ‘%’
“:comm * 100”
)
— 数据文件;
               Today’s Newly Hired EmployeesDept  Job       Manager   MgrNo  Emp Name  EmpNo  Salary/Commission
—-  ——–  ——–  —–  ——–  —–  —————–
20    Salesman  Blake      7698  Shepard    8061  $1,600.00 (3%)
Falstaff   8066  $1,250.00 (5%)
Major      8064  $1,250.00 (14%)

30    Clerk     Scott      7788  Conrad     8062  $1,100.00
Ford       7369  DeSilva    8063    $800.00
Manager   King       7839  Provo      8065  $2,975.00

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo7.ctl;
— 验证数据;

— 查看不满足的记录;
— EXAMPLE8:加载分区表;
— 创建测试表;
CREATE TABLE LINEITEM
(
L_ORDERKEY      NUMBER,
L_PARTKEY       NUMBER,
L_SUPPKEY       NUMBER,
L_LINENUMBER    NUMBER,
L_QUANTITY      NUMBER,
L_EXTENDEDPRICE NUMBER,
L_DISCOUNT      NUMBER,
L_TAX           NUMBER,
L_RETURNFLAG    CHAR(1),
L_LINESTATUS    CHAR(1),
L_SHIPDATE      DATE,
L_COMMITDATE    DATE,
L_RECEIPTDATE   DATE,
L_SHIPINSTRUCT  CHAR(17),
L_SHIPMODE      CHAR(7),
L_COMMENT       CHAR(43)
)
PARTITION by RANGE (L_SHIPDATE)
(
PARTITION SHIP_Q1 VALUES LESS THAN (TO_DATE(‘1996-04-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q2 VALUES LESS THAN (TO_DATE(‘1996-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q3 VALUES LESS THAN (TO_DATE(‘1996-10-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q4 VALUES LESS THAN (TO_DATE(‘1997-01-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’))
);
— 控制文件;
LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo8.dat’ “fix 129”
BADFILE ‘/u01/sqlldrdemo/demo8.bad’
TRUNCATE
INTO TABLE lineitem
PARTITION (ship_q1)
(l_orderkey      position    (1:6) char,
l_partkey       position   (7:11) char,
l_suppkey       position  (12:15) char,
l_linenumber    position  (16:16) char,
l_quantity      position  (17:18) char,
l_extendedprice position  (19:26) char,
l_discount      position  (27:29) char,
l_tax           position  (30:32) char,
l_returnflag    position  (33:33) char,
l_linestatus    position  (34:34) char,
l_shipdate      position  (35:43) char,
l_commitdate    position  (44:52) char,
l_receiptdate   position  (53:61) char,
l_shipinstruct  position  (62:78) char,
l_shipmode      position  (79:85) char,
l_comment       position (86:128) char)
— 数据文件;
     1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSON
TRUCK  iPBw4mMm7w7kQ zNPL i261OPP
1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN
MAIL   5wM04SNyl0AnghCP2nx lAi
1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN
REG AIRSQC2C 5PNCy4mM
1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE
AIR    Om0L65CSAwSj5k6k
1 6564  6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSON
MAIL   CB0SnyOL PQ32B70wB75k 6Aw10m0wh
1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE
FOB    C2gOQj OB6RLk1BS15 igN
2 8819 82012441659.44  0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD
AIR    O52M70MRgRNnmm476mNm
3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN
FOB    6wQnO0Llg6y
3 9717  1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN
SHIP   LhiA7wygz0k4g4zRhMLBAM
3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN
REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo8.ctl;
— 验证数据;
— EXAMPLE9:加载CLOB列,为每个CLOB列准备一个文本文件;
— 创建测试表;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2),
  RESUME   CLOB
)
— 控制文件;
LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ‘,’
( EMPNO    INTEGER EXTERNAL,
ENAME    CHAR,
JOB      CHAR,
MGR      INTEGER EXTERNAL,
SAL      DECIMAL EXTERNAL,
COMM     DECIMAL EXTERNAL,
DEPTNO   INTEGER EXTERNAL,
RES_FILE FILLER CHAR,
  “RESUME” LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = ‘NONE’
)
BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,/u01/sqlldrdemo/demo91.dat
7839,KING,PRESIDENT,,5500.00,,10,/u01/sqlldrdemo/demo92.dat
7934,MILLER,CLERK,7782,920.00,,10,/u01/sqlldrdemo/demo93.dat
7566,JONES,MANAGER,7839,3123.75,,20,/u01/sqlldrdemo/demo94.dat
7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,/u01/sqlldrdemo/demo95.dat
7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,/u01/sqlldrdemo/demo96.dat
7658,CHAN,ANALYST,7566,3450.00,,20,NONE
— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo9.ctl;
— 验证数据;

— EXAMPLE10:加载REF列和ARRAYs列;
— EXAMPLE11:转换字符集,使用Unicode编码加载数据;
————————- SQL*Loader的例子 ————————-
————————- 外部表 ORACLE_LOADER引擎的例子 ————————-
— 手动编写外部表语法;
1.创建DIRECTORY对象;
CREATE DIRECTORY ext_tab_dir AS ‘/u01/datafiles’;
SELECT * FROM dba_directories WHERE directory_name = ‘EXT_TAB_DIR’;
2.对用户授权;
GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;
SELECT * FROM dba_tab_privs WHERE table_name = ‘EXT_TAB_DIR’;
3.测试数据/u01/datafiles/emp.dat;
“56”,   “baker”,        “mary”, “f”,    “01-09-2004”, “15-11-1980”
“87”,   “roper”,        “lisa”, “m”,    “01-06-1999”, “20-12-1970”
4.创建外部表;
CREATE TABLE ext_tab_emp
(
ID            INTEGER,
first_name    VARCHAR2(20),
last_name     VARCHAR2(20),
male          CHAR(1),
birthday      DATE,
hire_date     DATE
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE ‘EXT_TAB_DIR’:’emp.bad’
DISCARDFILE ‘EXT_TAB_DIR’:’emp.dis’
LOGFILE ‘EXT_TAB_DIR’:’emp.log’
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
ID            CHAR(20) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
first_name    CHAR(20),
last_name     CHAR(20),
male          CHAR(1),
birthday      CHAR(20) date_format DATE mask “DD-MM-YYYY”,
hire_date     CHAR(20) date_format DATE mask “DD-MM-YYYY”
)
)
LOCATION (’emp.dat’, ’emp1.dat’)
) PARALLEL 2;
5.查看数据;
SELECT * FROM ext_tab_emp;
6.查看日志发现,如果使用并行的话会有多个线程同时工作,提高效率;
— 使用SQL*LOADER工具生成创建外部表的语法;
1.测试数据/u01/datafiles/car.dat;
Talbot,8/18,4,01-MAR-1923,ohv,8,295.00
Talbot,10/23,4,12-MAR-1923,ohv,8.9,375.00
Talbot,12/30,6,23-JAN-1924,ohv,13.4,550.00
Sunbeam,14/40,4,06-MAR-1924,ohv,13.9,895.00
Sunbeam,12/30,4,08-FEB-1924,ohv,11.5,570.00
Sunbeam,20/60,6,24-FEB-1924,ohv,20.9,950.00
Sunbeam,Twin Cam,6,11-MAR-1926,ohv,20.9,1125.00
Sunbeam,20,6,15-JUN-1927,ohv,20.9,750.00
Sunbeam,16,6,10-SEP-1927,ohv,16.9,550.00
Peugeot,172,4,28-SEP-1928,sv,6.4,165.00
Austin,7,4,29-JAN-1922,sv,7.2,225.00
Austin,12,4,01-JAN-1922,sv,12.8,550.00
Austin,20,4,04-JAN-1916,sv,22.4,616.00
Lanchester,40,6,08-JAN-1919,ohv,38.4,1875.00
Lanchester,21,6,16-JAN-1924,ohv,20.6,950.00
Vauxhall,30/98,4,18-JAN-1919,sv,23.8,1475.00
Vauxhall,23/60,4,27-JAN-1919,sv,22.4,1300.00
2.hr用户下的表结构;
3.控制文件/u01/datafiles/car.ctl;
LOAD DATA
INFILE ‘/u01/datafiles/car.dat’
BADFILE ‘/u01/datafiles/car.bad’
DISCARDFILE ‘/u01/datafiles/car.dsc’
APPEND
INTO TABLE car
FIELDS TERMINATED BY “,”
TRAILING NULLCOLS
(maker,
model,
no_cyl,
first_built_date,
engine,
hp,
price)
4.生成创建外部表的脚本;
sqlldr hr/hr control=/u01/datafiles/car.ctl external_table=GENERATE_ONLY;
5.适当修改即可;
conn hr/hr
CREATE TABLE car_ext
(
“MAKER” VARCHAR2(20),
“MODEL” VARCHAR2(20),
“NO_CYL” NUMBER,
“FIRST_BUILT_DATE” DATE,
“ENGINE” VARCHAR2(20),
“HP” NUMBER(10,1),
“PRICE” NUMBER(10,2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXT_TAB_DIR — 必须大写
  ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ‘EXT_TAB_DIR’:’car.bad’
    DISCARDFILE ‘EXT_TAB_DIR’:’car.dsc’
    LOGFILE ‘EXT_TAB_DIR’:’car.log’
    READSIZE 1048576
FIELDS TERMINATED BY “,” LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“MAKER” CHAR(255) TERMINATED BY “,”,
“MODEL” CHAR(255) TERMINATED BY “,”,
“NO_CYL” CHAR(255) TERMINATED BY “,”,
“FIRST_BUILT_DATE” CHAR(255) TERMINATED BY “,”,
“ENGINE” CHAR(255) TERMINATED BY “,”,
“HP” CHAR(255) TERMINATED BY “,”,
“PRICE” CHAR(255) TERMINATED BY “,”
)
)
location
(
‘car.dat’
)
)REJECT LIMIT UNLIMITED;
————————- 外部表 ORACLE_LOADER引擎的例子 ————————-
————————- 外部表 ORACLE_DATAPUMP引擎的例子 ————————-
— 卸载数据;
1.目标:卸载执行查询结果集的数据,并创建外部表;
conn hr/hr;
SELECT e.first_name, e.last_name, e.email, e.phone_number, d.department_name FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.department_id = d.department_id;
2.创建目录并授权;
CREATE DIRECTORY EXT_TAB_DIR AS ‘/u01/datafiles’;
GRANT READ, WRITE ON DIRECTORY EXT_TAB_DIR TO hr;
3.把数据导出为EMP_DEPT.dmp文件,创建外部表;
conn hr/hr;
CREATE TABLE emp_dept_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TAB_DIR LOCATION (‘EMP_DEPT.dmp’)
)
AS
SELECT e.first_name, e.last_name, e.email, e.phone_number, d.department_name FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.department_id = d.department_id;
— 导入数据;
目标:把导出的数据导入到hr用户下t1表中;
conn hr/hr;
CREATE TABLE t1
(
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
DEPARTMENT_NAME VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TAB_DIR
LOCATION (‘EMP_DEPT.dmp’)
);
————————- 外部表 ORACLE_DATAPUMP引擎的例子 ————————-

如何访问其它服务器上的数据库

Posted on by

问题:想要在本机或者一台服务器上访问另外一台服务器数据库中的表,可以通过建立链接服务器方式实现.

步骤:1.建立链接服务器

 

2.设置链接服务器名称(常规->选择SQL Server->输入连接服务器名称)

 

3.设置连接的服务器和密码(安全性->使用此安全上下文建立连接->填写账号密码)

 

4.访问方法

SELECT * FROM 服务器名.数据库.dbo.表名

SELECT * FROM qsbndb3.QSBN.dbo.tb_book

导入导出工具01–EXP/IMP和EXPDP/IMPDP工具的使用

Posted on by
EXP/IMP和EXPDP/IMPDP工具的使用
  1. exp/imp:
    1. 概念:
      1. 早期的oracle数据导入导出工具,效率比较低,可以用在小数据量的情况下;
      2. 可以在服务器端或者客户端操作,数据可以存放在服务器端或者客户端;
      3. 只能导出导出二进制的dump文件;
    2. 使用exp/imp工具的前提:
      1. 运行catexp.sql或者catalog.sql脚本;
      2. 保证有足够的空间;
      3. 保证有足够的权限;
        1. 对于exp:必须有CREATE SESSION权限,如果要导出其它用户下的表,还需要有EXP_FULL_DATABASE角色;
        2. 对于imp:必须有CREATE SESSION权限,如果要导入其它用户创建的dump文件,还需要有IMP_FULL_DATABASE角色;
    3. 调用exp/imp的方法:
      1. 命令行接口,常用;
      2. 交互模式;
      3. 使用参数文件方式,常用;
      4. 使用OEM调用;
    4. EXP工具:
      1. 用途:
        1. 归档历史数据;
        2. 保存表的结构信息;
        3. 可以在不同的服务器,不同的平台上传输数据;
        4. 传输表空间时使用;
      2. 导出的四种模式:
        1. Table Mode:包括表的定义,数据,用户拥有的表和索引,表上的约束;
        2. User Mode:用户下所有的对象;
        3. Tablespace Mode:某一个表空间中所有的对象,主要用于传输表空间;
        4. Full Database Mode:不推荐的方式,可以使用rman替代;
      3. 语法:exp keyword=value1,value2,…,valuen;
        1. USERID=user/pwd:用户名和密码;
        2. BUFFER:指定数据缓冲区的大小;
        3. FILE:导出文件的名称;
        4. FILESIZE:每个导出文件的最大大小,如果导出文件很大,可以限制每个文件的大小,使用多个文件来保存数据;
        5. COMPRESS:是否使用压缩,默认为Y;
        6. GRANTS:是否导出用户的权限,默认为Y;
        7. ROWS:是否导出表的记录,默认为Y;
        8. INDEXES:是否导出索引,默认为Y;
        9. TRIGGERS:是否导出触发器,默认为Y;
        10. CONSTRAINTS:是否导出表上的约束,默认为Y;
        11. DIRECT:是否使用直接路径导出,默认为N,只能使用命令行方式,BUFFER参数无效;
        12. LOG:指定日志文件;
        13. PARFILE:指定参数文件;
        14. FULL:导出整个数据库,全库导出时使用,不常用;
        15. OWNER:列出要导出的用户,导出用户模式;
        16. TABLES:列出要导出的表的名称(schema.table:partition),支持通配符,导出表模式;
        17. TABLESPACES:列出要导出的表空间,导出表空间模式;
        18. TRANSPORT_TABLESPACE:是否导出表空间的元信息,默认为N,传输表空间时使用;
        19. FEEDBACK=n:显示导出的进度,n条记录打一个点;
        20. QUERY:使用查询语句来过滤要导出的记录,eg:exp scott/tiger TABLES=emp QUERY=\”WHERE job=\’SALESMAN\’ and sal \<1600\”;
        21. CONSISTENT:是否在导出开始时设置SET TRANSACTION READ ONLY语句来保证整个导出期间的数据一致性,默认为N,推荐为Y;
        22. OBJECT_CONSISTENT:保证导出某个对象期间,这个对象是只读的,默认为N,推荐为Y;
        23. FLASHBACK_SCN:从指定的SCN开始导出数据;
        24. FLASHBACK_TIME:从指定的时间开始导出数据,内部会转化为SCN号码,可以使用TO_TIMESTAMP函数来转化字符串格式的时间;
        25. STATISTICS:当导出的对象被导入时,是否分析对象,默认为ESTIMATE;
        26. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
        27. RESUMABLE_NAME:指定挂起语句的标识符;
        28. RESUMABLE_TIMEOUT:等待的时间;
        29. RECORDLENGTH:记录的长度;
        30. INCTYPE:增量导出类型;
        31. RECORD:跟踪增量导出,默认为Y;
    5. IMP工具:
      1. 用途:
        1. 可以执行完全导入或者累计导入;
        2. 主要用于恢复用户错误;
      2. 导入介绍:
        1. 先创建表的定义;
        2. 然后从exp的dmp文件中抽取数据;
        3. 创建索引;
        4. 导入触发器;
        5. 开启数据完整性约束检查;
        6. 创建位图索引,序列等对象;
      3. 导入的模式:
        1. 表模式;
        2. 用户模式;
        3. 表空间模式,用户可传输表空间;
        4. 全库模式,不常用;
      4. 语法:imp keyword=value1,value2,…,valuen;
        1. USERID=user/pwd:用户名和密码;
        2. BUFFER:指定数据缓冲区的大小;
        3. FILE:要导入的文件的名称;
        4. FILESIZE:每个导入文件的最大大小;
        5. SHOW:查看要导入的dmp文件中的内容,并不进行导入操作;
        6. IGNORE:是否忽略创建对象时的错误,默认为N,表示出错时输出错误日志;
        7. GRANTS:是否导入用户的权限,默认为Y;
        8. ROWS:是否导入表的记录,默认为Y;
        9. INDEXES:是否导入索引,默认为Y;
        10. TRIGGERS:是否导入触发器,默认为Y;
        11. CONSTRAINTS:是否导入表上的约束,默认为Y;
        12. LOG:指定日志文件;
        13. PARFILE:指定参数文件;
        14. FULL:导出整个数据库,全库导出时使用,不常用;
        15. FROMUSER:在用户模式下使用,指定要导入的用户;
        16. TOUSER:与FROMUSER连用,指定要转变的用户列表,如fromuser=u1 touser=u2,即把u1下的对象创建在u2下;这个用户必须预先存在,否则会报错;
        17. TABLES:列出要导入的表的名称(schema.table:partition);
        18. TABLESPACES:列出要导入的表空间,导出表空间模式;
        19. TRANSPORT_TABLESPACE:是否导入表空间的元信息,默认为N,传输表空间时使用;
        20. FEEDBACK=n:显示导入的进度,n条记录打一个点;
        21. STATISTICS:导入对象时,是否分析对象,默认为AWAYS;
        22. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
        23. RESUMABLE_NAME:指定挂起语句的标识符;
        24. RESUMABLE_TIMEOUT:等待的时间;
        25. RECORDLENGTH:记录的长度;
        26. INCTYPE:增量导入类型;
        27. COMMIT:指定是否导入一组数据后就提交,默认为N,表示每导入一个表提交一次;
        28. DESTROY:是否覆盖表空间的数据文件,默认为N;
        29. INDEXFILE:把表和索引写入指定的文件中;
        30. SKIP_UNUSABLE_INDEXES:跳过不可用索引维护;
        31. TOID_NOVALIDATE:跳过指定类型ID的验证;
        32. COMPILE:是否马上编译导入的包,存储过程和函数,默认为Y;如果为N,表示第一次使用时编译;
        33. STREAMS_CONFIGURATION:导入stream的一般元数据,默认为Y;
        34. STREAMS_INSTANTIATION:导入stream的实例元数据,默认为N;
        35. DATAFILES:当TRANSPORT_TABLESPACE=Y时,使用此参数传输导入与数据库的数据文件;
    6. 字符集转换:
      1. 数据从源数据库导出,传输到目标服务器(ftp/scp/tape),然后导入到目标数据库一共有四个环境的字符集:
        1. 源数据库的字符集和NCHAR字符集;
        2. 源数据库服务器导出时的字符集(通过NLS_LANG环境变量设置);
        3. 目标数据库服务器导入时的字符集(通过NLS_LANG环境变量设置);
        4. 目标数据库的字符集和NCHAR字符集;
        5. 源数据库和目标数据库的字符集相同,可能会发生3次字符集转换(导出,传输,导入),如果不相同,一定会发生转换或者导入错误;
      2. 查看数据库的字符集:select * from nls_database_parameters,可以看得出语言是AMERICAN,区域是AMERICA,字符集是WE8ISO8859P1,NCHAR是AL16UTF16;            
      3. 设置服务器的环境变量:export NLS_LANG=NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET;                    
      4. 查看现有的dmp文件的字符集:首先设置当前服务器的字符集与数据库字符集一致,然后使用imp show=y命令,可以查看到导出时的字符集;                      
      5. 字符集换换的拓扑图;                                                      
    7. 传输表空间的原理:
      1. 如果把数据导出再导出效率太低;
      2. 传输表空间的做法是:只导出表空间的元信息,应用到目标数据库,表空间的数据使用数据块拷贝的方式;
  2. expdp/impdp:
    1. 概念:
      1. 带有数据泵的oracle数据导入导出工具,比较高效;
      2. 只能在服务器端操作,因为需要使用DIRECTORY,数据只能存放在服务器;
      3. 只能导出导出二进制的dump文件;
      4. 主要是调用DBMS_DATAPUMP的包;
    2. expdp:
      1. 语法:expdp user/pwd key=value;
        1. COMPRESSION:压缩数据;
          1. METADATA_ONLY:默认值,只压缩元数据;
          2. NONE:不压缩;
        2. CONTENT:指定要导出的内容;
          1. ALL:导出对象的定义和数据;
          2. DATA_ONLY:导出对象的数据;
          3. METADATA_ONLY:导出对象的定义;
        3. DIRECTORY:用于存放dump文件和日志文件的目录对象;
        4. DUMPFILE:导出到dump文件的列表,格式为:[DIRECTORY:]file_name,如果不指定目录,则使用DIRECTORY参数指定的目录对象;
        5. FILESIZE:导出文件的最大大小,如果导出对象比较大,可以分成多个文件保存;
        6. LOGFILE:指定日志文件的名称;
        7. NOLOGFILE:默认为N,禁止生成日志文件;
        8. PARFILE:指定参数文件;
        9. ENCRYPTION_PASSWORD:指定加密列的密码;
        10. ESTIMATE:指定估算被导出表所占磁盘空间的方法;
          1. BLOCKS:默认值,按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间;
          2. STATISTICS:据最近统计值估算对象占用空间;
        11. ESTIMATE_ONLY:默认值为N,如果为Y,则只估算导出作业占用的磁盘空间而不会执行;
        12. INCLUDE:指定要包含的对象类型和对象,格式:object_type[:name_clause];
          1. name_clause:必须是用双引号引起来的,因为里面的每个对象需要使用单引号;
          2. 使用LIKE子句:include=TABLE:”LIKE ‘B%'”,但是不能使用NOT LIKE;
          3. 使用IN子句:include=TABLE:”IN (‘T1’, ‘T2’)”;
          4. 使用<>子句:include=TABLE:”<> (‘T1’)”,但是不能使用[=];
          5. 只导出存储过程:indlude=PROCEDURE;
        13. EXCLUDE:排除指定的对象类型或者对象,格式与INCLUDE相似;
        14. FLASHBACK_SCN:指定SCN以后的数据;
        15. FLASHBACK_TIME:指定某个时间以后的数据,内部转化为最近的SCN号;
        16. JOB_NAME:把导出的任务创建一个JOB,指定作业的名称;
        17. ATTACH:使当前会话与已存在的导出作业建立联系,然后执行交互命令,eg:expdp user/pwd ATTACH=[schema_name.]job_name;
        18. NETWORK_LINK:指定目标数据库到源数据库的Database Link,用于将远程数据对象导出到本地的情况;
          1. 因为expdp是服务器端工具,如果expdp user/pwd@net_name的话导出的文件存放在服务器端;
          2. 如果expdp user/pwd NETWORK_LINK=dblink_name的话,则数据导出到本地;
          3. 导出的日志文件不能放到ASM上,只能存放数据文件;
        19. PARALLEL:指定导出的并行度,默认为1,推荐并行度等于给定的文件个数的整数倍,而且要小于CPU个数*2;
        20. QUERY:指定过滤导出数据的条件,格式:[schmea].[table_name:]query_clause;
        21. SAMPLE:导出数据的百分比;
        22. FULL:默认为N,数据库全库导出;
        23. SCHEMAS:导出数据的schema列表,默认为当前schema;
        24. TABLES:指定要导出的表的列表,格式为:[schema_name.]table_name[:partition_name];
        25. TABLESPACES:指定要导出的表空间;
        26. TRANSPORT_FULL_CHECK:默认为Y,检查要传输的表空间是否是自包含的;
        27. TRANSPORT_TABLESPACES:指定要要导出元数据的表空间的列表,主要用于传输表空间的情况;
        28. VERSION:指定导出对象的数据库的版本;
          1. COMPATIBLE:默认值;
          2. LATEST:会根据数据库的实际版本生成对象元数据;
          3. version_string用于指定数据库版本字符串;
        29. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      2. 交互式命令:
        1. ADD_FILE:添加一个导出文件;
        2. FILESIZE:指定添加文件的文件大小;
        3. HELP:帮助;
        4. PARALLEL:改变导出作业的并行度,PARALLEL=<number of workers>;
        5. 进入expdp的作业会话:expdp user/pwd attch=schema.job_name;
        6. START_JOB:开始/恢复作业的执行;
        7. STOP_JOB:暂停作业,使用STOP_JOB=IMMEDIATE表示立即停止;
        8. EXIT_CLIENT(CTRL+C):退出当前session,让作业继续运行;
        9. CONTINUE_CLIENT:切换到日志模式,作业在空闲时会重新运行;
        10. KILL_JOB:断开与作业的连接,并停止作业,同时删除dmp文件;
        11. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      3. 导出的步骤:
        1. 创建目录对象:CREATE DIRECTORY ext_tab_dir as ‘/u01/datafiles/’;                          
        2. 授权:GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;                                   
        3. 执行导出:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;(如果不指定JOB_NAME的话,会生成一个系统的名称,推荐指定;)                 
      4. 导出的各种场景:
        1. 按表模式导出:expdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp logfile=expdp_table.log parallel=2 job_name=’job_expdp_table’;
        2. 按查询条件导出:expdp hr/hr directory=ext_tab_dir tables=jobs query='”where rownum<11″‘ dumpfile=expdp_hr_jobs.dmp logfile=expdp_hr_jobs.log job_name=’job_expdp_hr_jobs’;
        3. 按表空间导出:expdp system/oracle directory=ext_tab_dir tablespaces=USERS dumpfile=expdp_ts_users1.dmp,expdp_ts_users2.dmp logfile=expdp_ts_users.log parallel=2 job_name=’job_expdp_ts_users’;
        4. 导出方案:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
        5. 导出整个数据库:expdp hr/hr directory=ext_tab_dir dumpfile=full.dmp full=y logfile=full.log job_name=’job_expdp_full’;
    3. impdp:
      1. 语法:impdp user/pwd key=value;
        1. REMAP_DATAFILE:重新定义DDL语句中引用的数据文件,用于将源数据文件转变为目标数据文件名,在不同平台之间搬移表空间时使用,eg:REMAP_DATAFIEL=source_datafie:target_datafile;
        2. REUSE_DATAFILES:默认是N,指定是否覆盖已存在的数据文件;
        3. REMAP_SCHEMA:将源schema的对象转换到目标schema中,eg:REMAP_SCHEMA=source_schema:target_schema;
        4. REMAP_TABLESPACE:将源表空间的对象导入到目标表空间中,eg:REMAP_TABLESPACE=source_tablespace:target_tablespace;
        5. SKIP_UNUSABLE_INDEXES:跳过不可使用的索引,默认为N;
        6. SQLFILE:把导出的dump文件中所有对象的DDL语句写到sql脚本中,eg:SQLFILE=[directory_object:]file_name;
        7. STREAMS_CONFIGURATION:是否导入流元数据库,默认是Y;
        8. TABLE_EXISTS_ACTION:当表已经存在时,导入作业要执行的操作;
          1. SKIP:默认跳过;
          2. APPEND:追加数据;
          3. REPLACE:删除表重建,然后再插入;
          4. TRUNCATE:截断;
        9. TRANSFORM:指定是否修改建立对象的DDL语句,eg:TRANSFORM=transform_name:value[:object_type];
          1. Transform_name:用于指定转换名:
            1. SEGMENT_ATTRIBUTES用于标识段属性(物理属性,存储属性,表空间,日志等信息);
            2. STORAGE用于标识段存储属性;
            3. OID;
            4. PCTSPACE;
          2. value:用于指定是否包含段属性或段存储属性;
          3. object_type用于指定对象类型;
        10. TRANSPORT_DATAFILES:用户指定传输表空间时被导入表空间对应的数据文件;
        11. TRANSPORT_TABLESPACES:列出传输表空间的元数据的表空间的列表,只是在NETWORK_LINK模式下操作;
      2. 交互式命令:与expdp一致;
    4. 导入的各种场景:
      1. 按表导入,将hr用户下俩个表导入到scott用户下:impdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp remap_schema=hr:scott logfile=impdp_table.log parallel=2 job_name=’job_impdp_table’;
      2. 按用户导入(如果用户存在则直接导入,要有目录对象的权限,如果不存在使用dba导入):impdp system/oracle directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=impdp_hr.log parallel=2 job_name=’job_impdp_hr’;
      3. 在客户端执行expdp命令把导出文件存放在服务器端;
      4. 在客户端执行expdp命令把导入文件存放在客户端;
      5. 源数据库的数据直接导入到目标数据库,中间不产生任何的dump文件;
——————————— 查看DIRECTORY对象的权限 ———————————
SELECT d.directory_name, d.directory_path, p.grantee, p.privilege
FROM user_tab_privs p
INNER JOIN dba_directories d ON p.table_name = d.directory_name AND
d.directory_name = ‘EXT_TAB_DIR’;
——————————— 查看DIRECTORY对象的权限 ———————————
——————————— exp的例子 ———————————
— 导出scott用户下的所有对象:exp scott/tiger owner=scott file=/u01/exp/exp_scott.dmp log=exp_scott.log feedback=1000;(导出的字符集和当前环境的字符集不同,可能发生字符集转换;)
— 导出hr用户下的jobs,countries表:exp hr/hr tables=jobs,countries file=/u01/exp/exp_hr_tables.dmp log=/u01/exp/exp_hr_tables.log feedback=100;(导出的过程中出现警告,其实是因为字符集的问题;)
— 设置字符集导出,没有提示字符集可能发送转换,也没有警告;
——————————— exp的例子 ———————————
——————————— imp的例子 ———————————
— 把某个用户下的数据导入;
— 首先删除用户下所有的对象,然后创建用户(导入时此用户必须存在):drop user scott cascade;;
— 导入数据:imp scott/tiger file=/u01/exp/exp_scott.dmp fromuser=scott touser=scott;
— 把hr用户下的exp_jobs表导入到scott用户下(必须使用dba用户才可以完成):imp system/oracle file=/u01/exp/exp_jobs.dmp fromuser=hr touser=scott tables=exp_jobs feedback=100;
——————————— imp的例子 ———————————
——————————— NETWORK_LINK参数的测试 ———————————
— 1.配置两台数据库的tnsnames.ora,其中orcl_source指向源数据库,orcl_target指向目标数据库;
— 2.在源数据库添加一个目录对象dir_source,并授权给hr用户;
CREATE DIRECTORY dir_source AS ‘/u01/dump_source’;
GRANT READ, WRITE ON DIRECTORY dir_source TO hr;
— 3.使用网络名在客户端调用expdp命令,导出的文件存放在服务器端(用户必须要有CONNECT, RESOURCE, EXP_FULL_DATABASE角色和目录对象的读写权限即可);
expdp hr/hr@orcl_source directory=dir_source schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
— 4.在源数据库添加一个目录对象dir_target,并授权给hr用户;
CREATE DIRECTORY dir_target AS ‘/u01/dump_target’;
GRANT READ, WRITE ON DIRECTORY dir_target TO hr;
— 6.在目标端创建服务器端的Database Link;
CREATE PUBLIC DATABASE LINK dblink_source CONNECT TO hr identified by hr USING ‘ORCL_SOURCE’;
— 5.在目标服务器使用expdp命令,把导出的数据放到目标端;
expdp hr/hr network_link=dblink_source directory=dir_target schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
— 直接从源数据库导入到目标数据库,中间不生成任何的dump文件;
impdp system/oracle network_link=dblink_source directory=dir_target schemas=hr logfile=exp2imp.log parallel=2 job_name=’job_exp2imp’
——————————— NETWORK_LINK参数的测试 ———————————
——————————— INCLUDE/SQLFILE参数的测试 ———————————
— 导出HR用户下所有的PROCEDURE,SEQUENCE,TABLE对象;
expdp hr/hr directory=dir_source schemas=hr include=PROCEDURE,SEQUENCE,TABLE dumpfile=expdp_hr_part_objects.dmp logfile=expdp_hr_part_objects.log job_name=’job_expdp_hr_objects’;
— 导出dump文件中包含对象的DDL语句;
impdp system/oracle directory=dir_source dumpfile=expdp_hr_part_objects.dmp sqlfile=hr_objects.sql;
— 查看DDL语句:less /u01/dump_source/hr_objects.sql;
——————————— INCLUDE/SQLFILE参数的测试 ———————————

脚本工具01–把某一个字符串按照指定的分隔符分隔,并以Table形式返回

Posted on by
CREATE OR REPLACE TYPE t_Table IS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION FN_SplitStringByDelimiter(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN t_Table PIPELINED AS
       v_delimiter_index INTEGER;
       v_start_index     INTEGER := 1;
       v_table           VARCHAR2(4000);
       v_delimiter_length INTEGER := LENGTH(p_delimiter);
/*
  功能:把某一个字符串按照指定的分隔符分隔,并以Table形式返回
*/
BEGIN
     LOOP
         — 查找当前分隔符的位置
         v_delimiter_index := INSTR(p_string || p_delimiter, p_delimiter, v_start_index);
         — 如果在字符串找不到字串则返回,退出循环
         EXIT WHEN v_delimiter_index = 0;
         — 得到分隔符前的值
         v_table := SUBSTR(p_string, v_start_index, v_delimiter_index – v_start_index);
         IF v_table IS NOT NULL THEN
            — 返回该集合的单个元素
            PIPE ROW(v_table);
         END IF;
         — 检索位置设置为当前分隔符的下一个
         v_start_index := v_delimiter_index + v_delimiter_length;
     END LOOP;
     — 必须以一个空的RETURN 语句结束
     RETURN;
END FN_SplitStringByDelimiter;
 
 
 
调用方法:SELECT column_value FROM TABLE(FN_SplitStringByDelimiter (‘a.b.c.d.e’, ‘.’));
SELECT column_value FROM TABLE(FN_SplitStringByDelimiter (‘123-;456-;789-;000’, ‘-;’));

sqlservr 应用程序(转)

Posted on by

sqlservr 应用程序可以在命令提示符下启动,停止,暂停和继续 Microsoft SQL Server 的实例.

语法:

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]

     [-eerror_log_path] [-lmaster_log_path] [-m]

     [-n] [-Ttrace#] [-v] [-x] [-gnumber] [-h]

参数:

-s instance_name

指定要连接到的 SQL Server 实例.如果未指定命名实例,sqlservr 将启动 SQL Server 的默认实例.

重要提示:启动 SQL Server 实例时,必须在该实例的相应目录中使用 sqlservr 应用程序.对于默认实例,从/MSSQL/Binn 目录运行 sqlservr.对于命名实例,在 /MSSQL$instance_name/Binn 目录运行sqlservr.

-c

指示独立于 Windows 服务控制管理器启动 SQL Server 实例.从命令提示符下启动 SQL Server 时可使用此选项,以缩短 SQL Server 的启动时间.

注意:使用此选项时,将无法通过使用 SQL Server 服务管理器或 net stop 命令停止 SQL Server.如果注销计算机,则 SQL Server 将停止.

-dmaster_path

指出 master 数据库文件的完全限定路径.在 -d 和 master_path 之间没有空格.如果没有提供此选项,则使用现有的注册表参数.

-f

以最小配置启动 SQL Server 实例.在配置值的设置(如过度分配内存)妨碍服务器启动时,这非常有用.

-e error_log_path

指示错误日志文件的完全限定路径.如果不指定路径,则默认实例的默认位置是 <Drive>:/Program Files/Microsoft SQL Server/MSSQL/Log/Errorlog,命名实例的默认位置是 <Drive>:/Program Files/Microsoft SQL Server/MSSQL$instance_name/Log/Errorlog.在 -e 和 error_log_path之间没有空格.

-l master_log_path

指示 master 数据库事务日志文件的完全限定路径.在 -l 和 master_log_path 之间没有空格.

-m

指示以单用户模式启动 SQL Server 实例.如果以单用户模式启动 SQL Server,则只有一个用户可以连接.确保将已完成事务定期从磁盘缓存写入数据库设备的 CHECKPOINT 机制将不启动.通常情况下,在遇到需要修复系统数据库这样的问题时才使用该选项.启用 sp_configure allow updates 选项.默认情况下,allow updates 被禁用.

-n

用于启动 SQL Server 的命名实例.如果不设置 -s 参数,则尝试启动默认实例.必须在命令提示符下切换到实例相应的 BINN 目录,然后才能启动 sqlservr.exe.例如,如果 Instance1 为其二进制文件使用/mssql$Instance1,则用户必须位于 /mssql$Instance1/binn 目录中才能启动 sqlservr.exe -s instance1.如果用 -n 选项启动 SQL Server 实例,则最好也使用 -e 选项,否则将不会记录 SQL Server事件.

-T trace#

指示 SQL Server 实例启动时,指定的跟踪标志 (trace#) 应同时生效.跟踪标记用于以非标准行为启动服务器.有关详细信息,请参阅跟踪标志 (Transact-SQL).

重要提示:指定跟踪标志时,请使用 -T 来传递跟踪标志号.SQL Server 接受小写的 t (-t);但是 -t 通常用于设置 SQL Server 支持工程师所需的其他内部跟踪标志.

-v

显示服务器的版本号.

-x

不保留 CPU 时间和高速缓存命中率统计信息.可获得最大性能.

-g memory_to_reserve

指定 SQL Server 为位于 SQL Server 进程中但在 SQL Server 内存池之外的内存分配保留的内存整数量(MB).内存池以外的内存是指 SQL Server 用于加载诸如下列项目的区域：扩展过程 .dll 文件,分布式查询引用的 OLE DB 访问接口以及 Transact-SQL 语句中引用的自动化对象.默认值为 256 MB.

使用此选项可帮助优化内存分配,但仅限于物理内存超过操作系统设置的应用程序可用虚拟内存限制时.如果 SQL Server 的内存使用要求异乎寻常,并且 SQL Server 进程的虚拟地址空间全都在使用,那么对于这样的大内存配置适合使用此选项.对此选项的不当使用会导致 SQL Server 实例无法启动或遇到运行时错误.

除非在 SQL Server 错误日志中看到下列任何警告,否则应使用 -g 参数的默认值：

  • “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>”
  • “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>”

这些消息可能指示 SQL Server 尝试释放部分 SQL Server 内存池空间,以便为扩展存储过程 .dll 文件或自动化对象等项留出空间.在这种情况下,可以考虑增加由 -g 开关保留的内存量.

使用低于默认值的值可以增加缓冲池和线程堆栈可用的内存量;在不使用很多扩展存储过程,分布式查询或自动化对象的系统中,这种方法可提高需要大量内存的工作负荷的性能.

-h

对 32 位 SQL Server 启用 AWE 时为热添加内存元数据保留虚拟内存地址空间.需要为具有 32 位 AWE 的热添加内存设置该选项,但将占用大约 0.5GB 的虚拟地址空间,并会增加内存优化的难度.对于 64 位 SQL Server,不是必需项.

备注:

多数情况下,sqlservr.exe 程序只用于故障排除或主要维护.在命令提示符下使用 sqlservr.exe 启动 SQL Server 时,SQL Server 不作为服务启动,因此无法使用 net 命令停止 SQL Server.用户可以连接到 SQL Server,但 SQL Server 工具将显示服务的状态,以便 SQL Server 配置管理器正确指示服务已停止.SQL Server Management Studio 可以与服务器连接,但它也可以指示服务已停止

分离和附加数据库文件

Posted on by

在分离和附加数据库时可以使用sp_detach_db,sp_attach_db和sp_attach_single_file_db系统存储过程,这三个存储过程对于SQL Server数据库管理员执行以下的任务是非常方便的:

1.使用sp_detach_db将数据库从一个服务器分离;

2.使用sp_attach_db系统存储过程直接将.mdf和.ldf文件附加到数据库服务器;

3.使用sp_attach_single_file_db系统存储过程只附加.mdf文件;

尽管它们对于SQL Server数据库管理员是很有用的,但是在使用这两个存储过程时是有一些限制的,如:

1.不能附加多个日志文件;

2.不能附加16个以上的文件;

在SQL Server 2008中,微软宣布上面的系统存储过程将在未来的版本中被废弃.而在“CREATE DATABASE”SQL语句中添加了一个从句”FOR ATTACH“.并建议应该使用“CREATE DATABASE database_name FOR ATTACH“语句

下面介绍使用”FOR ATTACH“语句的用法,以克服在使用sp_attach_db和sp_attach_single_file_db时要面临的限制.

1.创建实例数据库

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. CREATE DATABASE Test ON  
  4. (NAME = ‘Test’, FILENAME = ‘D:/Test.mdf’, SIZE = 3072KB , FILEGROWTH = 1024KB )  
  5. LOG ON  
  6. (NAME = ‘Test_log’, FILENAME = ‘D:/Test_log.ldf’, SIZE = 1024KB , FILEGROWTH = 10%)  
  7. GO  

 

2.使用sp_detach_db分离该数据库并使用sp_attach_db将它重新附加

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. — 分离数据库  
  4. EXEC sp_detach_db ‘Test’  
  5. GO  
  6. — 附加数据库  
  7. EXEC sp_attach_db ‘Test’, ‘D:/Test.mdf’, ‘D:/Test_log.ldf’  
  8. GO  

 

3.使用“CREATE DATABASE database_name FOR ATTACH”语句附加数据库

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. — 分离数据库  
  4. EXEC sp_detach_db ‘Test’  
  5. GO  
  6. — 使用“CREATE DATABASE database_name FOR ATTACH”附加数据库  
  7. CREATE DATABASE Test ON  
  8. (FILENAME = ‘D:/Test.mdf’),  
  9. (FILENAME = ‘D:/Test_log.ldf’)  
  10. FOR ATTACH  
  11. GO  

 

4.分离数据库并删除日志(.ldf)文件

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. — 分离数据库  
  4. EXEC sp_detach_db ‘Test’  
  5. GO  
  6. — 删除日志文件  
  7. EXEC master..xp_cmdshell ‘del “D:/Test_log.ldf”‘  
  8. GO  
  9. — 如果cmdshell功能不可使用则使用下面语句激活cmdshell功能  
  10. USE master  
  11. GO  
  12. sp_configure ‘show advanced options’, 1  
  13. GO  
  14. RECONFIGURE WITH OVERRIDE  
  15. GO  
  16. SP_CONFIGURE ‘xp_cmdshell’, 1  
  17. RECONFIGURE WITH OVERRIDE  
  18. GO  

 

5.使用sp_attach_single_file_db附加.mdf文件

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. EXEC sp_attach_single_file_db ‘Test’, ‘D:/Test.mdf’  
  4. GO  

 

6.删除日志文件,使用“CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG“附加数据库

[c-sharp] view plaincopy

  1. USE master  
  2. GO  
  3. EXEC sp_detach_db ‘Test’  
  4. GO  
  5. — 删除日志文件  
  6. EXEC master..xp_cmdshell ‘del “D:/Test_log.ldf”‘  
  7. GO  
  8. — 使用“CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG”附加数据库  
  9. CREATE DATABASE Test ON  
  10. (FILENAME = ‘D:/Test.mdf’)  
  11. FOR ATTACH_REBUILD_LOG  
  12. GO  

 

如何收缩数据库日志文件

Posted on by

上网查了一下,很多人都遇到过这样的情况:数据库的数据文件才2G左右,但是日志文件就已经20G多,如何收缩数据库日志文件呢?

大致的方法有以下几种:

1.DUMP TRANSACTION database_name WITH NO_LOG 清空事务日志

2.BACKUP LOG WITH NO_LOG 截断事务日志

3.BACKUP LOG WITH TRUNCATE_ONLY 截断事务日志

4.DBCC SHRINKDATABASE() 收缩指定数据库中的数据文件和日志文件的大小

5.DBCC SHRINKFILE() 收缩当前数据库的指定数据或日志文件的大小

6.删除日志文件

其中前3中方法是SQL Server 2008 中不再可用的数据库引擎功能,使用了第四种方法感觉效果不明显.下面我们介绍一下如何使用后两种方法收缩数据库日志文件.

1.使用DBCC SHRINKFILE() 收缩当前数据库的指定数据或日志文件的大小,以AdventureWorks数据库为例

USE AdventureWorks;

GO

— 设置AdventureWorks数据库的恢复模式为简单

ALTER DATABASE AdventureWorks

SET RECOVERY SIMPLE;

GO

— 收缩数据库日志文件到1M

DBCC SHRINKFILE (AdventureWorks_Log, 1);

GO

— 设置AdventureWorks数据库的恢复模式为完全

ALTER DATABASE AdventureWorks

SET RECOVERY FULL;

GO

 

2.删除日志文件,以AdventureWorks数据库为例

USE master

GO

— 分离AdventureWorks数据库

EXEC sp_detach_db ‘AdventureWorks’

GO

— 删除日志文件

EXEC master..xp_cmdshell ‘del “C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/AdventureWorks_Log.ldf”‘

GO

— 如果cmdshell功能不可使用则使用下面语句激活cmdshell功能

USE master

GO

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure ‘xp_cmdshell’, 1

RECONFIGURE WITH OVERRIDE

GO

 

— 使用sp_attach_single_file_db附加.mdf文件

USE master

GO

EXEC sp_attach_single_file_db ‘AdventureWorks’, ‘C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/AdventureWorks_Data.mdf’

GO

或者

— 使用”CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG”附加数据库(推荐使用)

CREATE DATABASE AdventureWorks ON

(FILENAME = ‘C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/AdventureWorks_Data.mdf’)

FOR ATTACH_REBUILD_LOG

GO

SQL Server 2008 参数化查询

Posted on by

我将讨论如果一个查询可以被参数化,那么SQL Server优化器怎样尝试将其参数化,以及你可以怎样建立你自己的参数化查询.

1.什么是参数化查询?

一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数.通过使用不同的参数,一个参数化查询返回不同的结果.要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准.

有两种不同的方式来创建参数化查询.第一个方式是让查询优化器自动地参数化你的查询.另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询.这篇文章的后面部分将介绍这个方法.

参数化查询的关键是查询优化器将创建一个可以重用的缓存计划.通过自动地或编程使用参数化查询,SQL Server可以优化类似T-SQL语句的处理.这个优化消除了对使用高贵资源为这些类似T-SQL语句的每一次执行创建一个缓存计划的需求.而且通过创建一个可重用计划,SQL Server还减少了存放过程缓存中类似的执行计划所需的内存使用.

2.现在让我们看看使得SQL Server创建参数化查询的不同方式.

参数化查询是怎样自动创建的?

微软编写查询优化器代码的人竭尽全力地优化SQL Server处理你的T-SQL命令的方式.我想这是查询优化器名称的由来.这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个T-SQL语句并确定它们是否可以被参数化.要了解这是如何工作的,让我们看看下面的T-SQL语句:

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader

WHERE SalesOrderID = 56000;

GO

在这里,你可以看到这个命令有两个特点.第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值.查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数(“56000”).因此,查询优化器可以自动地参数化这个查询.

如果你使用下面的SELECT语句来查看一个只包含用于上面语句的缓存计划的,干净的缓冲池,那么你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句:

SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text] AS [plan_text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

GO

当我在一个SQL Server 2008实例上运行这个命令时,我得到下面的输出,(注意,输出被重新格式化了,以便它更易读):

如果你看看上面输出中的plan_text字段,你会看到它不像原来的T-SQL文本.如前所述,查询优化器将这个查询重新编写为一个参数化T-SQL语句.在这里,你可以看到它现在有一个数据类型为(int)的变量(@1),它在之前的SELECT语句中被定义的.另外在plan_text的末尾,值“56000”被替换为变量@1.既然这个T-SQL语句被重写了,而且被存储为一个缓存计划,那么如果未来一个T-SQL命令和它大致相同,只有SalesOrderID字段被赋的值不同的话,它就可以被用于重用.让我们在动作中看看它.

如果我在我的机器上运行下面的命令:

DBCC FREEPROCCACHE

GO

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader

WHERE SalesOrderID = 56000;

GO

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader

WHERE SalesOrderID = 56001;

GO

SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text] AS [plan_text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

GO

我从最后的SELECT语句得到下面的输出,(注意,输出被重新格式化以便它更易读):

在这里,我首先释放过程缓存,然后我执行两个不同、但却类似的非参数化查询来看看查询优化器是会创建两个不同的缓存计划还是创建用于这两个查询的一个缓存计划.在这里,你可以看到查询优化器事实上很聪明,它参数化第一个查询并缓存了计划.然后当第二个类似、但有一个不同的SalesOrderID值的查询发送到SQL Server时,优化器可以识别已经缓存了一个计划,然后重用它来处理第二个查询.你可以这么说是因为“cnt”字段现在表明这个计划被用了两次.

3.数据库配置选项PARAMETERIZATION可以影响T-SQL语句怎样被自动地参数化.对于这个选项有两种不同的设置,SIMPLE和FORCED.当PARAMETERIZATION设置被设置为SIMPLE时,只有简单的T-SQL语句才会被参数化.要介绍这个,看下下面的命令:

SELECT SUM(LineTotal) AS LineTotal

FROM AdventureWorks.Sales.SalesOrderHeader H

JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID

WHERE H.SalesOrderID = 56000

这个查询类似于我前面的示例,除了在这里我添加了一个额外的JOIN标准.当数据库AdventureWorks的PARAMETERIZATION选项被设置为SIMPLE时,这个查询不会被自动地参数化.SIMPLE PARAMETERIZATION设置告诉查询优化器只参数化简单的查询.但是当选项PARAMETERIZATION被设置为FORCED时,这个查询将被自动地参数化.

当你设置数据库选项为使用FORCE PARAMETERIZATION时,查询优化器试图参数化所有的查询,而不仅仅是简单的查询.你可能会认为这很好.但是在某些情况下,当数据库设置PARAMETERIZATION为FORCED时,查询优化器将选择不是很理想的查询计划.当数据库设置PARAMETER为FORCED时,它改变查询中的字面常量.这可能导致当查询中涉及计算字段时索引和索引视图不被选中参与到执行计划中,从而导致一个无效的计划.FORCED PARAMETERIZATION选项可能是改进具有大量类似的、传递过来的参数稍有不同的查询的数据库性能的一个很好的解决方案.一个在线销售应用程序,它的客户对你的产品执行大量的类似搜索, 产品值不同,这可能是一个能够受益于FORCED PARAMETERIZATION的很好的应用程序类型.

不是所有的查询从句都会被参数化.例如查询的TOP、TABLESAMPLE、 HAVING、GROUP BY、ORDER BY、OUTPUT…INTO或FOR XML从句不会被参数化.

4.使用sp_execute_sql来参数化你的T-SQL

你不需要依赖于数据库的PARAMETERIZATION选项来使得查询优化器参数化一个查询.你可以参数化你自己的查询.你通过重新编写你的T-SQL语句并使用”sp_executesql”系统存储过程执行重写的语句来实现.正如已经看到的,上面包括一个“JOIN”从句的SELECT语句在数据库的PARAMETERIZATION设置为SIMPLE时没有被自动参数化.让我重新编写这个查询以便查询优化器将创建一个可重用的参数化查询执行计划.

为了说明,让我们看两个类似的、不会被自动参数化的T-SQL语句,并创建两个不同的缓存执行计划.然后我将重新编写这两个查询使得它们都使用相同的缓存参数化执行计划.

让我们看看这个代码:

DBCC FREEPROCCACHE

GO

SELECT SUM(LineTotal) AS LineTotal

FROM AdventureWorks.Sales.SalesOrderHeader H

JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID

WHERE H.SalesOrderID = 56000

GO

SELECT SUM(LineTotal) AS LineTotal

FROM AdventureWorks.Sales.SalesOrderHeader H

JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID

WHERE H.SalesOrderID = 56001

GO

SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text] AS [plan_text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

GO

在这里,我释放了过程缓存,然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句.然后我将检查缓存计划.这是这个使用DMV 的SELECT语句的输出(注意,输出被重新格式化了,以便它更易读):

正如你从这个输出看到的,这两个SELECT语句没有被查询优化器参数化.优化器创建了两个不同的缓存执行计划,每一个都只被执行了一次.我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的SELECT语句创建一个参数化执行计划.

下面是上面的代码被重新编写来使用sp_executesql 系统存储过程:

DBCC FREEPROCCACHE

GO

EXEC sp_executesql N’SELECT SUM(LineTotal) AS LineTotal

FROM AdventureWorks.Sales.SalesOrderHeader H

JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID

WHERE H.SalesOrderID = @SalesOrderID’, N’@SalesOrderID INT’, @SalesOrderID = 56000

GO

EXEC sp_executesql N’SELECT SUM(LineTotal) AS LineTotal

FROM AdventureWorks.Sales.SalesOrderHeader H

JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID

WHERE H.SalesOrderID = @SalesOrderID’, N’@SalesOrderID INT’, @SalesOrderID = 56001

GO

SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text] AS [plan_text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

GO

如同你所看到的,我重新编写了这两个SELECT语句,使它们通过使用”EXEC sp_executesql”语句来执行.对这些EXEC语句中的每一个,我都传递三个不同的参数.第一个参数是基本的SELECT语句,但是我将SalesOrderID的值用一个变量(@SalesOrderID)替代.在第二个参数中,我确定了@SalesOrderID的数据类型,在这个例子中它是一个integer.然后在最后一个参数中,我传递了SalesOrderID的值.这个参数将控制我的SELECT根据SalesOrderID值所生成的结果.sp_executesql的每次执行中前两个参数都是一样的.但是第三个参数不同,因为每个都有不同的SalesOrderID值.

现在当我运行上面的代码时,我从DMV SELECT语句得到下面的输出(注意,输出被重新格式化了,以便它更易读):

从这个输出,你可以看出,我有一个参数化缓存计划,它被执行了两次,为每个EXEC语句各执行了一次.

使用参数化查询来节省资源和优化性能

在语句可以被执行之前,每个T-SQL语句都需要被评估,而且需要建立一个执行计划.创建执行计划会占用宝贵的CPU资源.当执行计划被创建后,它使用内存空间将它存储在过程缓存中.降低CPU和内存使用的一个方法是利用参数化查询.尽管数据库可以被设置为对所有查询FORCE参数化,但是这不总是最好的选择.通过了解你的哪些T-SQL语句可以被参数化然后使用sp_executesql存储过程,你可以帮助SQL Server节省资源并优化你的查询的性能.

DBCC 语句总结

Posted on by

Transact-SQL 编程语言提供 DBCC 语句作为 SQL Server 的数据库控制台命令

数据库控制台命令语句可分为以下类别

命令类别

执行

维护

对数据库、索引或文件组进行维护的任务

杂项

杂项任务，如启用跟踪标志或从内存中删除 DLL

信息

收集并显示各种类型信息的任务

验证

对数据库、表、索引、目录、文件组或数据库页的分配进行的验证操作

 

— I.信息语句

— 1.DBCC INPUTBUFFER 根据session_id显示从客户端发送到 Microsoft SQL Server 实例的一个语句

SELECT @@SPID        — 获得当前会话ID

SELECT * FROM sys.dm_exec_requests WHERE session_id = @@spid        — 跟据当前会话ID获得此次请求信息

DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS        — 显示从客户端发送到 Microsoft SQL Server 实例的一个语句

 

— 2.DBCC SHOWCONTIG 显示指定的表或视图的数据和索引的碎片信息,建议使用 sys.dm_db_index_physical_stats

USE AdventureWorks

GO

DBCC SHOWCONTIG (‘Person.Address’) WITH NO_INFOMSGS        — 显示Person.Address表的数据和索引的碎片信息

GO

— 返回所有数据库中所有对象的信息

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

— 返回AdventureWorks数据库中Person.Address表的信息

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N’AdventureWorks’), OBJECT_ID(N’Person.Address’), NULL, NULL , ‘DETAILED’);

GO

 

— 3.DBCC OPENTRAN 确定打开的事务是否存在于事务日志中

DBCC OPENTRAN (0)        — 当前数据库

DBCC OPENTRAN (N’AdventureWorks’) — AdventureWorks数据库

— 创建数据库和表并打开一个事务

USE master

GO

CREATE DATABASE Test

GO

USE Test

GO

CREATE TABLE T1

(col1 INT,

 col2 VARCHAR(10))

GO

BEGIN TRAN

INSERT INTO T1 VALUES (1, ‘Kobe’);

GO

DBCC OPENTRAN;        — 查看此数据库中打开的事务

ROLLBACK TRAN;

GO

DROP TABLE T1;

GO

USE master

GO

DROP DATABASE Test

GO

 

— 4.DBCC SQLPERF 提供所有数据库的事务日志空间使用情况统计信息,也可以用于重置等待和闩锁的统计信息.

DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;        — 显示所有数据库的日志空间信息

GO

DBCC SQLPERF(“sys.dm_os_latch_stats”,CLEAR) WITH NO_INFOMSGS;        — SQL Server 实例重置闩锁统计信息

GO

DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR) WITH NO_INFOMSGS;        — SQL Server 实例重置等待统计信息

GO

SELECT * FROM sys.dm_os_latch_stats        — 返回按类组织的所有闩锁等待的相关信息

GO

SELECT * FROM sys.dm_os_wait_stats        — 返回执行的线程所遇到的所有等待的相关信息

GO

 

— 5.DBCC OUTPUTBUFFER 以十六进制和 ASCII 格式返回指定 session_id 的当前输出缓冲区

SELECT @@SPID

DBCC OUTPUTBUFFER (@@SPID) WITH NO_INFOMSGS        — 返回当前进程缓冲区内容

 

— 6.DBCC TRACESTATUS 显示跟踪标志的状态

— 在 SQL Server 中,有两种跟踪标志:会话和全局.会话跟踪标志对某个连接是有效的,只对该连接可见;

— 全局跟踪标志在服务器级别上进行设置,对服务器上的每一个连接都可见.

DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;        — 显示当前全局启用的所有跟踪标志的状态

GO

DBCC TRACESTATUS (2528, 3205) WITH NO_INFOMSGS;        — 显示跟踪标志 2528 和 3205 的状态

GO

DBCC TRACESTATUS (3205, -1) WITH NO_INFOMSGS;        — 以下示例显示跟踪标志 3205 是否是全局启用的

GO

DBCC TRACESTATUS() WITH NO_INFOMSGS;        — 列出针对当前会话启用的所有跟踪标志

GO

 

— 7.DBCC PROCCACHE 以表格格式显示有关过程缓存的信息

— 使用过程缓存来缓存已编译计划和可执行计划,以加快批处理的执行速度.过程缓存中的项处于批处理级别.过程缓存包括以下项:

— A.已编译计划;B.执行计划;C.Algebrizer 树;D.扩展过程

DBCC PROCCACHE WITH NO_INFOMSGS

 

— 8.DBCC USEROPTIONS 返回当前连接的活动(设置)的 SET 选项

DBCC USEROPTIONS WITH NO_INFOMSGS;

GO

 

— 9.DBCC SHOW_STATISTICS 显示索引,统计信息或列的当前查询优化统计信息.根据统计信息对象中存储的数据,显示的相应统计信息包括标题,直方图和密度

USE AdventureWorks;

GO

— 以下示例显示 Person.Address 表的 AK_Product_Name 索引的所有统计信息

DBCC SHOW_STATISTICS (“Person.Address”, AK_Address_rowguid) — WITH NO_INFOMSGS, STAT_HEADER, DENSITY_VECTOR, HISTOGRAM;

GO

DBCC SHOW_STATISTICS (“Person.Address”, PK_Address_AddressID)

GO

 

— II.验证语句

— 1.DBCC CHECKALLOC 检查指定数据库的磁盘空间分配结构的一致性

DBCC CHECKALLOC;        — 不指定此参数或指定了 0 值,则默认值为当前数据库

GO

DBCC CHECKALLOC (N’AdventureWorks’);        — 检查AdventureWorks数据库

GO

— 显示当指定所有其他选项时运行 DBCC CHECKALLOC 所需的估计 tempdb 空间大小

DBCC CHECKALLOC WITH ALL_ERRORMSGS, NO_INFOMSGS, TABLOCK, ESTIMATEONLY

GO

 

— 2.DBCC CHECKFILEGROUP 检查当前数据库中指定文件组中的所有表和索引视图的分配和结构完整性

USE AdventureWorks;

GO

— 不指定此参数或指定了 0 值,则默认值为主文件组

DBCC CHECKFILEGROUP;

GO

— 通过指定主文件组的标识号并指定 NOINDEX,对 AdventureWorks 数据库主文件组(不包括非聚集索引)进行检查

DBCC CHECKFILEGROUP (1, NOINDEX);

GO

— 检查 AdventureWorks 数据库主文件组并指定选项 ESTIMATEONLY,所需的估计 tempdb 空间大小

DBCC CHECKFILEGROUP (1) WITH ESTIMATEONLY;

GO

 

— 3.DBCC CHECKCATALOG 检查指定数据库内的目录一致性.数据库必须联机

DBCC CHECKCATALOG;        — Check the current database.

GO

DBCC CHECKCATALOG (AdventureWorks);        — Check the AdventureWorks database.

GO

 

— 4.DBCC CHECKIDENT 检查指定表的当前标识值,如有必要,则更改标识值.还可以使用 DBCC CHECKIDENT 为标识列手动设置新的当前标识值

USE AdventureWorks;

GO

DBCC CHECKIDENT (“HumanResources.Employee”);        — 据需要重置 AdventureWorks 数据库中 Employee 表的当前标识值

GO

DBCC CHECKIDENT (“HumanResources.Employee”, NORESEED);– 报告 AdventureWorks 数据库的 Employee 表中的当前标识值,但如果该标识值不正确,不会进行更正

GO

DBCC CHECKIDENT (“HumanResources.Employee”, RESEED, 30);– 将 AdventureWorks 数据库的 Employee 表中的当前标识值强制设置为值 30

GO

 

— 5.DBCC CHECKCONSTRAINTS 检查当前数据库中指定表上的指定约束或所有约束的完整性

— 例检查 AdventureWorks 数据库中的 Table1 表的约束完整性

USE AdventureWorks;

GO

CREATE TABLE Table1 (Col1 int, Col2 char (30));

GO

INSERT INTO Table1 VALUES (100, ‘Hello’);

GO

ALTER TABLE Table1 WITH NOCHECK ADD CONSTRAINT chkTab1 CHECK (Col1 > 100);

GO

DBCC CHECKCONSTRAINTS(Table1);

GO

DROP TABLE Table1

GO

DBCC CHECKCONSTRAINTS (“Production.CK_ProductCostHistory_EndDate”);– 检查 CK_ProductCostHistory_EndDate 约束的完整性

GO

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;        — 检查当前数据库中所有表上的所有启用和禁用约束的完整性

GO

 

— 6.DBCC CHECKTABLE 检查组成表或索引视图的所有页和结构的完整性

— 若要对数据库中的每个表执行 DBCC CHECKTABLE,请使用 DBCC CHECKDB-

— 对于指定的表,DBCC CHECKTABLE 将检查以下内容:

— A.是否已正确链接索引,行内,LOB 以及行溢出数据页;

— B.索引是否按照正确的顺序排列;

— C.各指针是否一致;

— D.每页上的数据是否合理(包括计算列);

— E.页面偏移量是否合理;

— F.基表的每一行是否在每个非聚集索引中具有匹配的行,以及非聚集索引的每一行是否在基表中具有匹配的行;

— G.已分区表或索引的每一行是否都位于正确的分区中;

— H.使用 FILESTREAM 将 varbinary(max) 数据存储在文件系统中时，文件系统与表之间是否保持链接级一致性;

USE AdventureWorks;

GO

DBCC CHECKTABLE (“HumanResources.Employee”);– 检查 AdventureWorks 数据库中的 HumanResources.Employee 表的数据页完整性

GO

DBCC CHECKTABLE (“HumanResources.Employee”) WITH PHYSICAL_ONLY;– 将以较低的开销检查 AdventureWorks 数据库中的 Employee 表

GO

 

DECLARE @indid int;

SET @indid = (SELECT index_id

              FROM sys.indexes

              WHERE object_id = OBJECT_ID(‘Production.Product’)

                    AND name = ‘AK_Product_Name’);

DBCC CHECKTABLE (“Production.Product”, @indid);        — 将检查通过访问 sys.indexes 获得的特定索引

 

— 7.DBCC CHECKDB

— 通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性:

— A.对数据库运行 DBCC CHECKALLOC;

— B.对数据库中的每个表和视图运行 DBCC CHECKTABLE;

— C.对数据库运行 DBCC CHECKCATALOG;

— D.验证数据库中每个索引视图的内容;

— E.使用 FILESTREAM 在文件系统中存储 varbinary(max) 数据时,验证表元数据和文件系统目录和文件之间的链接级一致性;

— F.验证数据库中的 Service Broker 数据;

— 这意味着不必从 DBCC CHECKDB 单独运行 DBCC CHECKALLOC,DBCC CHECKTABLE 或 DBCC CHECKCATALOG 命令

 

— Check the current database.

DBCC CHECKDB;

GO

— Check the AdventureWorks database without nonclustered indexes.

DBCC CHECKDB (AdventureWorks, NOINDEX);

GO

DBCC CHECKDB WITH NO_INFOMSGS;        — 检查当前数据库,取消信息性消息

GO

 

— III.维护语句

— 1.DBCC CLEANTABLE 回收表或索引视图中已删除的可变长度列的空间

— DBCC CLEANTABLE 用于在删除可变长度列之后回收空间,可变长度列可以属于下列数据类型之一:varchar,nvarchar,varchar(max),nvarchar(max),varbinary,varbinary(max),text,ntext,image,sql_variant 和 xml.该命令不回收删除固定长度列后的空间.

— 如果删除的列存储在行内,则 DBCC CLEANTABLE 将从表的 IN_ROW_DATA 分配单元回收空间;如果列存储在行外,则将根据已删除列的数据类型从 ROW_OVERFLOW_DATA 或 LOB_DATA 分配单元回收空间;如果从 ROW_OVERFLOW_DATA 或 LOB_DATA 页回收空间时产生空页,DBCC CLEANTABLE 将删除该页

— DBCC CLEANTABLE 作为一个或多个事务运行。如果未指定批大小,则该命令将在一个事务中处理整个表,并在操作过程中以独占方式锁定该表.对于某些大型表,单个事务的长度和所需的日志空间可能太大.如果指定批大小,则该命令将在一系列事务中运行,每个事务包括指定的行数.DBCC CLEANTABLE 不能作为其他事务内的事务运行

— 该操作将被完整地记入日志。

— 系统表或临时表不支持使用 DBCC CLEANTABLE。

— 不应将 DBCC CLEANTABLE 作为日常维护任务来执行.而应在对表或索引视图中的可变长度列进行重要更改之后并且需要立即回收未使用空间时使用 DBCC CLEANTABLE.或者,也可以重新生成表或视图的索引;但是,此操作会耗费更多资源

DBCC CLEANTABLE (AdventureWorks,”Person.Address”, 0) WITH NO_INFOMSGS;

GO

— 创建一个表并用几个可变长度列填充该表.然后删除其中两列,并运行 DBCC CLEANTABLE 以回收未使用空间.在执行 DBCC CLEANTABLE 命令之前和之后,运行查询以验证页计数和已用空间值

USE AdventureWorks;

GO

IF OBJECT_ID (‘dbo.CleanTableTest’, ‘U’) IS NOT NULL

    DROP TABLE dbo.CleanTableTest;

GO

— 创建测试表 CleanTableTest

CREATE TABLE dbo.CleanTableTest

    (DocumentID int Not Null,

    FileName nvarchar(4000),

    DocumentSummary nvarchar(max),

    Document varbinary(max)

    );

GO

— Populate the table with data from the Production.Document table.

INSERT INTO dbo.CleanTableTest

    SELECT DocumentID,

           REPLICATE(FileName, 1000),        — 返回多次复制后的字符表达式

           DocumentSummary,

           Document

    FROM Production.Document;

GO

— Verify the current page counts and average space used in the dbo.CleanTableTest table.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N’AdventureWorks’);

SET @object_id = OBJECT_ID(N’AdventureWorks.dbo.CleanTableTest’);

SELECT alloc_unit_type_desc,

       page_count,

       avg_page_space_used_in_percent,

       record_count

FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘Detailed’);

GO

— Drop two variable-length columns from the table.

ALTER TABLE dbo.CleanTableTest

DROP COLUMN FileName, Document;

GO

— Verify the page counts and average space used in the dbo.CleanTableTest table

— Notice that the values have not changed.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N’AdventureWorks’);

SET @object_id = OBJECT_ID(N’AdventureWorks.dbo.CleanTableTest’);

SELECT alloc_unit_type_desc,

       page_count,

       avg_page_space_used_in_percent,

       record_count

FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘Detailed’);

GO

— Run DBCC CLEANTABLE.

DBCC CLEANTABLE (AdventureWorks,”dbo.CleanTableTest”);

GO

— Verify the values in the dbo.CleanTableTest table after the DBCC CLEANTABLE command.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N’AdventureWorks’);

SET @object_id = OBJECT_ID(N’AdventureWorks.dbo.CleanTableTest’);

SELECT alloc_unit_type_desc,

       page_count,

       avg_page_space_used_in_percent,

       record_count

FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘Detailed’);

GO

 

— 2.DBCC INDEXDEFRAG 指定表或视图的索引碎片整理.下一版本的 Microsoft SQL Server 将删除该功能.建议使用 ALTER INDEX

— DBCC INDEXDEFRAG 对索引的叶级进行碎片整理,以便页的物理顺序与叶节点从左到右的逻辑顺序相匹配,因此可提高索引扫描性能

— 对 AdventureWorks 数据库的 Production.Product 表中的 PK_Product_ProductID 索引的所有分区进行碎片整理。

DBCC INDEXDEFRAG (AdventureWorks, “Production.Product”, PK_Product_ProductID)

GO

— 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理

— 该方法可用于对数据库中碎片数量在声明的阈值之上的所有索引进行碎片整理

— Declare variables

SET NOCOUNT ON;

DECLARE @tablename varchar(255);

DECLARE @execstr   varchar(400);

DECLARE @objectid  int;

DECLARE @indexid   int;

DECLARE @frag      decimal;

DECLARE @maxfrag   decimal;

 

— Decide on the maximum fragmentation to allow for.

SELECT @maxfrag = 30.0;

 

— Declare a cursor.

DECLARE tables CURSOR FOR

   SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME

   FROM INFORMATION_SCHEMA.TABLES

   WHERE TABLE_TYPE = ‘BASE TABLE’;

 

— Create the table.

CREATE TABLE #fraglist (

   ObjectName char(255),

   ObjectId int,

   IndexName char(255),

   IndexId int,

   Lvl int,

   CountPages int,

   CountRows int,

   MinRecSize int,

   MaxRecSize int,

   AvgRecSize int,

   ForRecCount int,

   Extents int,

   ExtentSwitches int,

   AvgFreeBytes int,

   AvgPageDensity int,

   ScanDensity decimal,

   BestCount int,

   ActualCount int,

   LogicalFrag decimal,

   ExtentFrag decimal);

 

— Open the cursor.

OPEN tables;

 

— Loop through all the tables in the database.

FETCH NEXT

   FROM tables

   INTO @tablename;

 

WHILE @@FETCH_STATUS = 0

BEGIN;

— Do the showcontig of all indexes of the table

   INSERT INTO #fraglist

   EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)

      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’);

   FETCH NEXT

      FROM tables

      INTO @tablename;

END;

 

— Close and deallocate the cursor.

CLOSE tables;

DEALLOCATE tables;

 

— Declare the cursor for the list of indexes to be defragged.

DECLARE indexes CURSOR FOR

   SELECT ObjectName, ObjectId, IndexId, LogicalFrag

   FROM #fraglist

   WHERE LogicalFrag >= @maxfrag

      AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;

 

— Open the cursor.

OPEN indexes;

 

— Loop through the indexes.

FETCH NEXT

   FROM indexes

   INTO @tablename, @objectid, @indexid, @frag;

 

WHILE @@FETCH_STATUS = 0

BEGIN;

   PRINT ‘Executing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,

      ‘ + RTRIM(@indexid) + ‘) – fragmentation currently ‘

       + RTRIM(CONVERT(varchar(15),@frag)) + ‘%’;

   SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@objectid) + ‘,

       ‘ + RTRIM(@indexid) + ‘)’;

   EXEC (@execstr);

 

   FETCH NEXT

      FROM indexes

      INTO @tablename, @objectid, @indexid, @frag;

END;

 

— Close and deallocate the cursor.

CLOSE indexes;

DEALLOCATE indexes;

 

— Delete the temporary table.

DROP TABLE #fraglist;

GO

— ALTER INDEX index_name ON talbe_name REORGANIZE;

USE AdventureWorks;

GO

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE;        `– 重新组织单个聚集索引

GO

 

— 3.DBCC DBREINDEX 对指定数据库中的表重新生成一个或多个索引,下一版本的 Microsoft SQL Server 将删除该功能.建议使用 ALTER INDEX。

— DBCC DBREINDEX 重新生成表的一个索引或为表定义的所有索引.通过允许动态重新生成索引,可以重新生成强制 PRIMARY KEY 或 UNIQUE 约束的索引,而不必删除并重新创建这些约束.这意味着无需了解表的结构或其约束,即可重新生成索引.这可能在将数据大容量复制到表中以后发生

USE AdventureWorks;

GO

— 使用填充因子 80 对 AdventureWorks 数据库中的 Employee 表重新生成 Employee_EmployeeID 聚集索引

DBCC DBREINDEX (“HumanResources.Employee”, PK_Employee_EmployeeID,80);

GO

— ALTER INDEX index_name ON talbe_name REBUILD;

USE AdventureWorks;

GO

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD; — 在 Employee 表中重新生成单个索引

GO

 

— 4.DBCC SHRINKDATABASE  收缩指定数据库中的数据文件和日志文件的大小

— 若要收缩特定数据库的所有数据和日志文件,请执行 DBCC SHRINKDATABASE 命令;若要一次收缩一个特定数据库中的一个数据或日志文件,请执行 DBCC SHRINKFILE 命令

— 若要查看数据库中当前的可用(未分配)空间量,请运行 sp_spaceused

EXEC sp_spaceused

DBCC SHRINKDATABASE (AdventureWorks, 10);

GO

 

— 5.DBCC DROPCLEANBUFFERS 从缓冲池中删除所有清除缓冲区

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

 

— 6.DBCC SHRINKFILE 收缩当前数据库的指定数据或日志文件的大小,或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件,以允许从数据库中删除该文件.文件大小可以收缩到比创建该文件时所指定的大小更小.这样会将最小文件大小重置为新值

— 将日志文件收缩到指定的目标大小,将 AdventureWorks 数据库中的日志文件收缩到 1 MB.若要允许 DBCC SHRINKFILE 命令收缩文件,首先需要通过将数据库恢复模式设置为 SIMPLE 来截断该文件.

— 将 AdventureWorks 用户数据库中名为 DataFile1 的数据文件的大小收缩到 1 MB。

USE AdventureWorks;

GO

DBCC SHRINKFILE (DataFile1, 1);

GO

 

USE AdventureWorks;

GO

— Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks

SET RECOVERY SIMPLE;

GO

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_Log, 1);

GO

— Reset the database recovery model.

ALTER DATABASE AdventureWorks

SET RECOVERY FULL;

GO

 

— 清空文件以便从数据库中将其删除的步骤

USE AdventureWorks;

GO

— Create a data file and assume it contains data.

ALTER DATABASE AdventureWorks

ADD FILE (

    NAME = Test1data,

    FILENAME = ‘C:/t1data.ndf’,

    SIZE = 5MB

    );

GO

— Empty the data file.

DBCC SHRINKFILE (Test1data, EMPTYFILE);

GO

— Remove the data file from the database.

ALTER DATABASE AdventureWorks

REMOVE FILE Test1data;

GO

 

— 7.DBCC FREEPROCCACHE 删除计划缓存中的所有元素,通过指定计划句柄或 SQL 句柄从计划缓存删除特定计划,或者删除指定资源池中的所有工作负荷组

— 通过指定查询计划句柄从计划缓存中清除查询计划.为了确保示例查询在计划缓存中,首先执行该查询.将查询 sys.dm_exec_cached_plans 和 sys.dm_exec_sql_text 动态管理视图以返回查询的计划句柄.然后,将结果集中的计划句柄值插入 DBCC FREEPROCACHE 语句,以从计划缓存中仅删除该计划

USE AdventureWorks;

GO

SELECT * FROM Person.Address;

GO

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N’SELECT * FROM Person.Address%’;

GO

— Remove the specific plan from the cache.

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

GO

 

— 清除计划缓存中的所有计划

— 小心使用 DBCC FREEPROCCACHE 清除计划缓存.释放计划缓存将导致系统重新编译存储过程,而不重用缓存中的存储过程.这会导致查询性能暂时性地突然降低.对于计划缓存中每个已清除的缓存存储区

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

 

— 8.DBCC UPDATEUSAGE 报告目录视图中的页数和行数错误并进行更正

DBCC UPDATEUSAGE (0);        — 为当前数据库中的所有对象更新页数或行数,或同时更新两者

GO

 

USE AdventureWorks;

GO

DBCC UPDATEUSAGE (AdventureWorks) WITH NO_INFOMSGS; — 为 AdventureWorks 更新页数和行数,或同时更新两者,并禁止显示信息性消息

GO

DBCC UPDATEUSAGE (AdventureWorks,”HumanResources.Employee”);– 报告 AdventureWorks 数据库中 Employee 表的已更新页数或行数信息

GO

DBCC UPDATEUSAGE (AdventureWorks, “HumanResources.Employee”, IX_Employee_ManagerID);– 为表中的特定索引更新页数或行数，或同时更新两者

GO

 

— IV.杂项语句

— 1.DBCC dllname (FREE) 从内存中释放指定的扩展存储过程 DLL

— 执行扩展存储过程时,DLL 仍保持由 SQL Server 的实例加载,直到服务器关闭为止.此语句允许从内存中卸载 DLL,而不用关闭 SQL Server

DBCC xpstar ( FREE ) WITH NO_INFOMSGS

 

— 存储过程 sp_helpextendedproc 报告当前定义的扩展存储过程以及该过程(函数)所属的动态链接库(DLL)的名称,后续版本的 Microsoft SQL Server 将删除该功能

USE master;

GO

EXEC sp_helpextendedproc;        — 对所有扩展存储过程进行报告

GO

EXEC sp_helpextendedproc xp_cmdshell;        — 对 xp_cmdshell 扩展存储过程进行报告

GO

 

— 2.DBCC TRACEOFF  禁用指定的跟踪标记

DBCC TRACEOFF (3205) WITH NO_INFOMSGS;        — 禁用跟踪标记 3205

GO

DBCC TRACEOFF (3205, -1) WITH NO_INFOMSGS;        — 全局禁用跟踪标记 3205

GO

DBCC TRACEOFF (3205, 260, -1) WITH NO_INFOMSGS;        — 全局禁用跟踪标记 3205 和 260

GO

 

— 3.DBCC TRACEON 启用指定的跟踪标记

— 在生产服务器上,为了避免意外行为,建议您使用下列方法之一,仅在服务器范围内启用跟踪标记:

— A.使用 Sqlservr.exe 的 -T 命令行启动选项.这是推荐的最佳实践,因为这样可确保将所有语句运行时使用已启用的跟踪标志.这些语句包括启动脚本中的命令

— B.仅在用户或应用程序未对系统以并行方式运行语句时,才使用 DBCC TRACEON ( trace# [, ….n], -1 )

— 跟踪标记用于通过控制 SQL Server 的运行方式来自定义某些特征.启用的跟踪标记将在服务器中一直保持启用状态,

— 直到执行 DBCC TRACEOFF 语句将其禁用为止.在 SQL Server 中,有两种跟踪标志:会话和全局.会话跟踪标志对某个连接是有效的,只对该连接可见;

— 全局跟踪标志在服务器级别上进行设置,对服务器上的每一个连接都可见.若要确定跟踪标记的状态,请使用 DBCC TRACESTATUS.若要禁用跟踪标记,请使用 DBCC TRACEOFF

DBCC TRACEON (3205) WITH NO_INFOMSGS;        — 打开跟踪标记 3205,禁用磁带驱动程序的硬件压缩功能.仅为当前连接打开此标记

GO

DBCC TRACEON (3205, -1) WITH NO_INFOMSGS;        — 全局方式打开跟踪标记 3205

GO

DBCC TRACEON (3205, 260, -1) WITH NO_INFOMSGS;        — 全局方式打开跟踪标记 3205 和 260

GO

 

— 4.DBCC HELP 返回指定的 DBCC 命令的语法信息

DBCC HELP (‘?’);        — 返回可查看其帮助信息的所有 DBCC 语句

GO

DBCC HELP (‘checkdb’);        — 返回 DBCC CHECKDB 的语法信息

GO

 

— V.其它语句

— 1.DBCC FREESESSIONCACHE 刷新针对 Microsoft SQL Server 实例执行的分布式查询所使用的分布式查询连接缓存

USE AdventureWorks;

GO

DBCC FREESESSIONCACHE WITH NO_INFOMSGS;        — 将刷新分布式查询缓存

GO

 

— 2.DBCC FREESYSTEMCACHE 从所有缓存中释放所有未使用的缓存条目.

— SQL Server 数据库引擎会事先在后台清理未使用的缓存条目,以使内存可用于当前条目.但是,可以使用此命令从所有缓存中手动删除未使用的条目

— Clean all the caches with entries specific to the resource pool named “default”.

DBCC FREESYSTEMCACHE (‘ALL’,’default’)        — 清除特定于某个资源调控器资源池的缓存

 

— 3.DBCC PINTABLE 将表标记为驻留,这表示 Microsoft SQL Server 不从内存中刷新表页

— DBCC PINTABLE 不会导致将表读入到内存中.当表中的页由普通的 Transact-SQL 语句读入到高速缓存中时,这些页将标记为内存驻留页.

— 当 SQL Server 需要空间以读入新页时,不会清空内存驻留页.SQL Server 仍然记录对页的更新,并且如有必要,将更新的页写回到磁盘.

— 然而,在使用 DBCC UNPINTABLE 语句使该表不驻留之前,SQL Server 在高速缓存中一直保存可用页的复本.

— DBCC PINTABLE 最适用于将小的,经常引用的表保存在内存中.将小表的页一次性读入到内存中,将来对其数据的所有引用都不需要从磁盘读入.

— 驻留 AdventureWorks 数据库中的 Person.Address 表

DECLARE @db_id int, @tbl_id int

USE AdventureWorks

SET @db_id = DB_ID(‘AdventureWorks’)

SET @tbl_id = OBJECT_ID(‘Person.Address’)

DBCC PINTABLE (@db_id, @tbl_id)

GO

 

— 4.DBCC UNPINTABLE 将表标记为不在内存驻留.将表标记为不在内存驻留后,可以清空高速缓存中的表页

— DBCC UNPINTABLE 不会导致立即将表从数据高速缓存中清空.而指定如果需要空间以从磁盘中读入新页,高速缓存中的表的所有页都可以清空

— 不驻留 AdventureWorks 数据库中的 Person.Address 表

DECLARE @db_id int, @tbl_id int

USE AdventureWorks

SET @db_id = DB_ID(‘AdventureWorks’)

SET @tbl_id = OBJECT_ID(‘Person.Address’)

DBCC UNPINTABLE (@db_id, @tbl_id)

GO

SQL Server 2008 更改跟踪

Posted on by

与SQL SERVER 2008 CDC 异步捕获数据变更的不同,更改跟踪是同步进程,是DML(INSERT/UPDATE/DELETE)事务的一部分,它可以使用最小的C盘存储开销来侦测数据行的净变更.那么它也就不能像CDC那样可以提供用户表的历史更改信息.更改是使用异步进程捕获的,此进程读取事务日志,并且对系统造成的影响很小.

更改跟踪捕获更改了表行这一事实,但不会捕获更改的数据.这样,应用程序就可以确定使用从用户表中直接获取的最新行数据更改的行.因此,与变更数据捕获相比,更改跟踪可以解答的历史问题比较有限.但是,对于不需要历史信息的那些应用程序,更改跟踪产生的存储开销要小得多,因为它不需要捕获更改的数据(不需要触发器和表时间戳).它使用同步跟踪机制来跟踪更改.此功能旨在最大限度地减少DML 操作开销.

总的来说有以下几点:

1.减少了开发时间:由于SQL Server 2008 中提供了更改跟踪功能,因此无需开发自定义解决方案.

2.不需要架构更改:使用更改跟踪不需要执行以下任务:添加列;添加触发器;如果无法将列添加到用户表,则需要创建要在其中跟踪已删除的行或存储更改跟踪信息的端表.o

3.内置清除机制:更改跟踪的清除操作在后台自动执行.不需要端表中存储的数据的自定义清除.

4.提供更改跟踪功能的目的是获取更改信息:使用更改跟踪功能可使信息查询和使用更方便.列跟踪记录提供与更改的数据相关的详细信息.

5.降低了DML 操作的开销:同步更改跟踪始终会有一些开销.但是,使用更改跟踪有助于使开销最小化.开销通常会低于使用其他解决方案,对于需要使用触发器的解决方案,尤其如此.

6.更改跟踪是基于提交的事务进行的:更改的顺序基于事务提交时间.在存在长时间运行和重叠事务的情况下,这样可获得可靠的结果.必须专门设计使用timestamp

值的自定义解决方案,以处理这些情况.

7.配置和管理更改跟踪的标准工具:SQL Server 2008 提供标准的DDL 语句、SQL Server Management Studio,目录视图和安全权限.

 

具体步骤:

1.建立测试数据库

IF NOT EXISTS (SELECT name FROM SYS.databases WHERE name = N’CHANGE_TRACK_DB’)

BEGIN

CREATE DATABASE CHANGE_TRACK_DB

END

要启用数据库更改跟踪功能,需要配置CHANGE_TRACKING数据库选项.也可以配置跟踪的数据在数据库保留多久,以及是否启用自动清除.配置保留期将会影响到需要维护的跟踪数据的大小.该值过高可能会影响存储.太低的话在远程应用程序同步不够的情况下,会引发通另一应用程序的同步问题.

2.配置更改跟踪

ALTER DATABASE CHANGE_TRACK_DB

SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 36HOURS, AUTO_CLEANUP = ON)

使用更改跟踪时的最佳实践是为数据库启用快照隔离.不使用快照隔离会引发事务不一致的变更信息.对有显著DML活动的数据库和表,以一致的方式捕获更改跟踪的信息很重要(抓取最新版本并使用该版本号来获取适当的数据)由于行版本的生成,启用快照隔离会在tempdb中增加额外的使用空间.会带来I/O开销的增加.

3.启用快照隔离

ALTER DATABASE CHANGE_TRACK_DB

SET ALLOW_SNAPSHOT_ISOLATION ON

GO

4.通过查询sys.change_tracking_databases来确认数据库是否以正确启用更改跟踪.

SELECT DB_NAME(DATABASE_ID) AS [DB_NAME], IS_AUTO_CLEANUP_ON, RETENTION_PERIOD, RETENTION_PERIOD_UNITS_DESC

FROM sys.change_tracking_databases

GO

5.创建测试表

USE CHANGE_TRACK_DB

GO

CREATE TABLE CHANGE_TRACKING_USER

(USERID  INT NOT NULL PRIMARY KEY  IDENTITY(1,1),

NAME VARCHAR(20) NOT NULL,

ADDRESS  VARCHAR(100) NOT NULL)

GO

对于要打开更改跟踪以及要跟踪哪些列被跟新了的表,需要打开表的CHANGE_TRACKING选项和TRACK_COLUMNS_UPDATED选项.

ALTER TABLE CHANGE_TRACKING_USER

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED= ON)

6.查询sys.change_tracking_tables目录视图可以获得启用跟踪更改的详细信息.

SELECT OBJECT_NAME(OBJECT_ID) AS [TB_NAME], IS_TRACK_COLUMNS_UPDATED_ON

FROM sys.change_tracking_tables

GO

7.对表进行插入数据来捕获更改跟踪.

INSERT CHANGE_TRACKING_USER(NAME, ADDRESS)

VALUES(‘Kobe’,’Lakers’),

(‘Jordon’, ‘Bull’),

(‘Wade’, ‘Heat’),

(‘Howard’, ‘Magic’)

GO

8.查看正在同步的是一个函数CHANGE_TRACKING_CURRENT_VERSION(),返回的是最后提交的事务的版本号.所有发生在启用更改跟踪表中的DML操作都会照成版本号的增长.版本号用来确定更改.

SELECT CHANGE_TRACKING_CURRENT_VERSION()

9.函数CHANGE_TRACKING_MIN_VALID_VERSION()可以获得表的最小可用版本号.如果断开连接的程序不同步的时间超过了更改跟踪保留期限.那么就要对应用程序的数据进行彻底的刷新.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘CHANGE_TRACKING_USER’))

10.对于更改的侦测我们可以用函数CHANGETABLE.该函数有种用法:使用CHANGES关键字来检测从指定的同步版本以来发生的更改;或者使用VERSION关键字来返回最新的更改跟踪版本.

SELECT USERID                  –返回的是主键

,SYS_CHANGE_OPERATION    –I 代表INSERT, U代表UPDATE, D代表DELETE

,SYS_CHANGE_VERSION      –返回的是版本号,因为这条数据是在同一个INSERT中添加的,所以下面的结果版本号相同  

FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 0) A — 此函数返回的是自版本以来的更改.第一个参数是表名称

11.当收集同步信息时,使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT 和BEGIN TRAN..COMMIT TRAN来封装收集的更改信息和相关的当前更改跟踪版本以及最小的可用版本.使用快照隔离允许更改跟踪的数据具有事务一致性的形式.

UPDATE CHANGE_TRACKING_USER

SET NAME = ‘Kobe Bryant’

WHERE USERID = 1

UPDATE CHANGE_TRACKING_USER

SET ADDRESS = ‘Lakers’

WHERE USERID = 4

DELETE FROM CHANGE_TRACKING_USER WHERE USERID = 2

–检查最新的版本号

SELECT CHANGE_TRACKING_CURRENT_VERSION()

12.当程序收集了自数据版本后的数据.下面可以检测自版本起发生的所有更改

SELECT USERID, SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS

FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 1) AS T

SYS_CHANGE_COLUMNS列式包含从最新版本开始更新过的列的VARBINARY值,可以使用CHANGE_TRACKING_IS_COLUMN_IN_MASK函数来解释它.该函数接受个参数:表的列ID和VARBINARY值.

13.下面使用这个函数来检查NAME列和ADDRESS列是否被修改过.

SELECT USERID,        –该函数返回对应的列ID

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘CHANGE_TRACKING_USER’),’NAME’,’COLUMNID’),

SYS_CHANGE_COLUMNS) NAME_IS_CHANGED,CHANGE_TRACKING_IS_COLUMN_IN_MASK(

COLUMNPROPERTY(OBJECT_ID(‘CHANGE_TRACKING_USER’),’ADDRESS’,’COLUMNID’)

,SYS_CHANGE_COLUMNS) ADDRESS_IS_CHANGED

FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,1) AS T

WHERE SYS_CHANGE_OPERATION = ‘U’        –确定修改的列

14.CHANGETABLE 通过VERSION 参数来返回最新的版本.

SELECT A.USERID, NAME, ADDRESS, SYS_CHANGE_VERSION

FROM CHANGE_TRACKING_USER A

CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER, (USERID), (A.USERID)) T

15.下面再演示一个UPDATE来演示版本的不同.

UPDATE CHANGE_TRACKING_USER

SET ADDRESS = ‘MIAMI HEAT’

WHERE USERID = 3

SELECT CHANGE_TRACKING_CURRENT_VERSION() –检查最新的版本号

SELECT A.USERID, NAME, ADDRESS, SYS_CHANGE_VERSION

FROM CHANGE_TRACKING_USER A

CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER, (USERID), (A.USERID)) T

可以看到USERID=3的版本号为5,这是因为版本号是一致递增的(第11步版本号已经到4),所以现在最新的版本号位.没有修改的行版本号不变.

16.最后测试如何通过DML操作提供更改跟踪应用程序上下文信息,可以确定是哪一应用程序对那些行进行了数据修改.它的作用是如果有多个应用程序对数据源进行数据同步,这将会是有用的信息.使用CHANGE_TRACKING_CONTEXT函数来查询,函数只有一个输入参数CONTEXT,它是VARBINARY数据类型.

首先要保存上下文信息的变量,然后在CHANGE_TRACKING_CONTEXT函数中使用变量,再向更改跟踪表中插入一条新行

DECLARE @CONTEXT VARBINARY(128) = CAST(‘DS_ALEX’ AS VARBINARY(128));

WITH CHANGE_TRACKING_CONTEXT(@CONTEXT)

INSERT CHANGE_TRACKING_USER(NAME,ADDRESS)

VALUES(‘James’, ‘Heat’)

–现在查询从版本发生的所有更改.

SELECT USERID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, CAST(SYS_CHANGE_CONTEXT AS VARCHAR(50)) AppContext

FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 5) AS T

 

 

以上主要涉及了建库建表,

ALTER DATABASE ….ENABLE CHANGE_TRACKING. 启用数据库更改跟踪

CHANGE_RETENTION 和AUTO_CLEANUP 指定更改跟踪保留期限和自动清除.

查询SYS.CHANGE_TRACKING_DATABASES目录视图检查数据库更改跟踪的状态.

ALTER TABLE …ENABLE CHANGE_TRACKING

TRACK_COLUMNS_UPDATED 指定列级别更改也会被跟踪.

SYS.CHANGE_TRACKING_TABLES目录视图确认表的更改跟踪状态

一些检测更改跟踪数据的不同函数:

CHANGE_TRACKING_CURRENT_VERSION() 返回最后提交的事务版本号

CHANGE_TRACKING_MIN_VALID_VERSION() 返回更改跟踪表的最小可用版本号

CHANGETABLE:VERSION 返回最新的更改版本

CHANGES 检测自指定同步版本以来的更改

CHANGE_TRACKING_IS_COLUMN_IN_MASK 检测更改跟踪表中那些列被更新

CHANGE_TRACKING_CONTEXT 通过DML操作存储更改上下文,从而可以跟踪哪一应用程序修改了什么数据.

填充因子

Posted on by

提供填充因子选项是为了优化索引数据存储和性能.当创建或重新生成索引时,填充因子值可确定每个叶级页上要填充数据的空间百分比,以便保留一定百分比的可用空间供以后扩展索引.例如,指定填充因子的值为 80 表示每个叶级页上将有20% 的空间保留为空,以便随着在基础表中添加数据而为扩展索引提供空间.在每个页上的索引行之间(而不是在页的末尾)保留空白区域.

填充因子值是 1 到 100 之间的百分比值,服务器范围的默认值为 0,这表示将完全填充叶级页.

注意:填充因子值 0 和 100 意义相同.

可以使用 CREATE INDEX 或 ALTER INDEX 语句来设置各个索引的填充因子值.若要修改服务器范围的默认值,请使用 sp_configure 系统存储过程.若要查看一个或多个索引的填充因子值,请使用 sys.indexes 目录视图.

重要提示:只有在创建或重新生成了索引后,才会应用填充因子.SQL Server 数据库引擎并不会在页中动态保持指定的可用空间百分比.如果试图在数据页上保持额外的空间,将有背于使用填充因子的本意,因为随着数据的输入,数据库引擎将不得不在每个页上进行页拆分,以保持填充因子所指定的可用空间百分比.

性能注意事项

1.页拆分

正确选择填充因子值可提供足够的空间以便随着向基础表中添加数据而扩展索引,从而降低页拆分的可能性.

如果向已满的索引页添加新行,数据库引擎将把大约一半的行移到新页中,以便为该新行腾出空间.这种重组称为页拆分.页拆分可为新记录腾出空间,但是执行页拆分可能需要花费一定的时间,此操作会消耗大量资源.此外,它还可能造成碎片,从而导致 I/O 操作增加.如果经常发生页拆分,可通过使用新的或现有的填充因子值来重新生成索引,从而重新分发数据.有关详细信息,请参阅重新组织和重新生成索引.

尽管采用较低的填充因子值(非 0)可减少随着索引增长而拆分页的需求,但是索引将需要更多的存储空间,并且会降低读取性能.即使对于面向许多插入和更新操作的应用程序,数据库读取次数一般也超过数据库写入次数的 5 到 10 倍.因此,指定一个不同于默认值的填充因子会降低数据库的读取性能,而降低量与填充因子设置的值成反比.例如,当填充因子的值为 50 时,数据库的读取性能会降低两倍.读取性能降低是因为索引包含较多的页,因此增加了检索数据所需的磁盘I/O 操作.

2.将数据添加到表的末尾

如果新数据在表中均匀分布,则非零填充因子对性能有利.但是,如果所有数据都添加到表的末尾,则不会填充空的空间.例如,如果索引键列是 IDENTITY 列,则新行的键将总是增加,并且行在逻辑意义上将添加到表的末尾.在这种情况下,页拆分将不会导致性能下降,因此您应当使用默认填充因子 0,或者指定填充因子 100,以便在叶级进行填充.

Stop Words

Posted on by

为节省存储空间和提高搜索效率,搜索引擎在索引页面或处理搜索请求时会自动忽略某些字或词,这些字或词即被称为Stop Words(停用词).

通常意义上,大致为如下两类:

1,这些词应用十分广泛,在Internet上随处可见,比如“Web”一词几乎在每个网站上均会出现,对这样的词搜索引擎无法保证能够给出真正相关的搜索结果,难以帮助缩小搜索范围,同时还会降低搜索的效率;

2,这类就更多了,包括了语气助词,副词,介词,连接词等,通常自身并无明确的意义,只有将其放入一个完整的句子中才有一定作用,如常见的“的“,”在“之类.举个例子来说,象“IT技术点评“,虽然其中的“IT”从我们的本意上是指“Information Technology”,事实上这种缩写也能够为大多数人接受,但对搜索引擎来说,此“IT”不过是“it”,即“它“的意思,这在英文中是一个极其常见同时意思又相当含混的词,在大多数情况下将被忽略.我们在IT技术点评中保留“IT”更多地面向“人“而非搜索引擎,以求用户能明了IT技术点评网站涉及的内容限于信息技术,虽然从SEO的角度这未必是最佳的处理方式.

了解Stop Words,在网页内容中适当地减少Stop Words出现的频率,可以有效地帮助我们提高关键词密度,而在网页Title中避免出现Stop Words往往能够让我们优化的关键词更突出.

Google stop words list:比如 I,a,about,an,are,as,at,be,by,com,de,of,on,or,that,what,when,where,who…

中文停止词:”在“,”里面“,”也“,”的“,”它“,”为“这些词都是停止词.这些词因为使用频率过高,几乎每个网页上都存在,所以搜索引擎开发人员都将这一类词语全部忽略掉.