设为首页收藏本站

Botang唐波's Oracle Station

查看: 768|回复: 0

课程第58次课程全部结束:2016-07-24星期日晚上

[复制链接]

719

主题

1081

帖子

7844

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7844
发表于 2016-7-25 09:05:38 | 显示全部楼层 |阅读模式
课程第58次:2016-07-24星期日晚上

【上完1Z0-053的第16章】资源计划
【上完1Z0-053的第18章】行链接和行迁移、压缩、SHRINK SPACE和SUSPEND TRIGGER
【上完1Z0-051】:共12章0 1 2 3 4 5 6 7 8 9 10 11
上完1Z0-052】:共19章(0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
【上完1Z0-053】:共21章(0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
表示已经上过的,表示还没上的。

参考别期的帖子:
http://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

rsrc.sql:
  1. select  * from dba_users u
  2. where u.username  in ('SYS','SYSTEM');

  3. select  * from dba_rsrc_plans;

  4. select  * from dba_rsrc_plan_directives  r
  5.    where r.plan='PLAN1';

  6.   select  * from dba_rsrc_plans  rp
  7.    where rp.plan='PLAN3';
  8.    
  9.    ---
  10.    
  11.    BEGIN
  12. dbms_resource_manager.clear_pending_area();
  13. dbms_resource_manager.create_pending_area();
  14. dbms_resource_manager.create_plan( plan => 'PLAN3',
  15.                                                            comment => 'PLAN3',cpu_mth => 'RATIO'                                                            
  16.                                                              );
  17. dbms_resource_manager.create_plan_directive(
  18.     plan => 'PLAN3',
  19.     group_or_subplan => 'OTHER_GROUPS',
  20.     comment => 'OTHER_GROUPS',
  21.    
  22.     mgmt_p1 => 1
  23. );
  24. dbms_resource_manager.submit_pending_area();
  25. END;

  26. ---

  27. begin
  28.    dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN4',
  29.    consumer_group1 => 'GROUP1',
  30.    group1_cpu => 60,
  31.    consumer_group2 => 'GROUP2',group2_cpu => 40 );
  32.    end;
  33. ---

  34. select s.RESOURCE_CONSUMER_GROUP
  35.   from v_$session s where s.TERMINAL='pts/5';
  36.   
  37.    select  * from dba_rsrc_plan_directives  r
  38.    where r.plan='PLAN1';
  39.   
  40.   select s.RESOURCE_CONSUMER_GROUP, s.STATUS
  41.   from v_$session s where s.TERMINAL='pts/5'  
  42.   and s.RESOURCE_CONSUMER_GROUP='GROUP1';
  43.   
  44.   select  * from v$transaction;
  45.   
  46.     select s.RESOURCE_CONSUMER_GROUP, s.STATUS  , s.TERMINAL
  47.   from v_$session s  where s.USERNAME='HR'
  48.    and  s.RESOURCE_CONSUMER_GROUP='GROUP1';
  49. ---

  50.      select s.RESOURCE_CONSUMER_GROUP, s.STATUS
  51.   from v_$session s where s.TERMINAL='pts/5'  ;

复制代码

rsrc2.sql:
  1. SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
  2. FROM v$rsrcmgrmetric_history
  3. ORDER BY begin_time;

  4. SELECT name, consumed_cpu_time, cpu_wait_time
  5. FROM v$rsrc_consumer_group;

  6. select  s.RESOURCE_CONSUMER_GROUP
  7. from v_$session s where s.TERMINAL='pts/1';

  8. BEGIN
  9. dbms_resource_manager.clear_pending_area();
  10. dbms_resource_manager.create_pending_area();
  11. dbms_resource_manager.set_consumer_group_mapping_pri(explicit => 1,
  12. oracle_user => 8,service_name => 3,client_os_user => 2,client_program => 9,
  13. client_machine => 10,module_name => 5,module_name_action => 4,service_module => 6,
  14.    service_module_action => 7
  15.    
  16.    
  17.    );
  18.    dbms_resource_manager.submit_pending_area();
  19. END;
复制代码
Screenshot.png
space.sql:


  1. create table
  2.   t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));
  3.   
  4.   
  5.   insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');


  6. update t05318_chain  set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';


  7. update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';


  8. select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;

  9. select  * from CHAINED_ROWS;

  10. analyze table t05318_chain list chained rows;

  11. update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';


  12. select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;


  13. analyze table t05318_chain list chained rows;

  14. select  * from CHAINED_ROWS;

  15. ----

  16. truncate table CHAINED_ROWS;

  17. alter system set memory_Target=0;

  18. alter system set db_cache_size=1G;
  19. alter system set db_16k_Cache_size=1M;

  20. create tablespace tbs16k datafile size 10M blocksize 16384;

  21. alter table hr.t05318_chain  move tablespace tbs16k;

  22. ---

  23.   create table t05318_migrate( a varchar2(2000))  pctfree 0;
  24.   
  25.   begin
  26.    for i in 1..733
  27.    loop
  28.       insert into  t05318_migrate values('A');
  29.     end loop;
  30.     commit;
  31.    end;

  32. select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  33.      group by dbms_rowid.rowid_block_number(rowid);
  34.      
  35.      
  36.      --
  37.      analyze table t05318_migrate  list chained rows;
  38.      
  39.       select  * from CHAINED_ROWS;
  40.       
  41.       ---
  42.       update t05318_migrate set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' where rownum=1;
  43.      
  44.       analyze table t05318_migrate  list chained rows;
  45.       
  46.       
  47.       alter table t05318_migrate pctfree 20;
  48.       
  49.       truncate table CHAINED_ROWS;
  50.       
  51.       alter table  t05318_migrate  move tablespace  users ;
  52.       ----
  53.       
  54.   create tablespace tbs_nocompression datafile size 10M autoextend on;
  55. create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
  56. create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
  57. create tablespace tbs_query datafile size 10M autoextend on default compress for query;
  58. create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
  59. select  t.tablespace_name, t.def_tab_compression, t.compress_for  from dba_tablespaces t
  60. where t.tablespace_name in ('TBS_NOCOMPRESSION',
  61.                                                   'TBS_BASIC', 'TBS_OLTP',
  62.                                                    'TBS_QUERY',
  63.                                                    'TBS_ARCHIVE');
  64.       
  65. -----
  66. create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
  67. create table t_basic (a varchar2(200)) tablespace tbs_basic;
  68. create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
  69. ----

  70. begin
  71.    for i in 1..400
  72.    loop
  73.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
  74.    end loop;
  75.    commit;
  76. end;
  77. --hr--
  78. begin
  79.    for i in 1..400
  80.    loop
  81.       insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
  82.    end loop;
  83.    commit;
  84. end;
  85. --hr--
  86. begin
  87.    for i in 1..400
  88.    loop
  89.       insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
  90.    end loop;
  91.    commit;
  92. end;  

  93. ---
  94. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  95. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');

  96. ---
  97. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
  98. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );      
  99. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp  group by  substr(rowid, 10, 6 );  

  100. ----
  101. --sys--
  102. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  103.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
  104. --sys--
  105. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  106.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  107. --sys--      
  108. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
  109.       from hr.T_OLTP group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
  110. ---
  111. create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
  112. create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;

  113. ---

  114. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  115. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
  116. ---
  117. --hr--
  118. begin
  119.    for i in 1..400
  120.    loop
  121.       insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
  122.    end loop;
  123.    commit;
  124. end;
  125. --hr--
  126. begin
  127.    for i in 1..400
  128.    loop
  129.       insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
  130.    end loop;
  131.    commit;
  132. end;
  133.      --
  134.      --hr--
  135. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic2  group by  substr(rowid, 10, 6 );
  136. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp2  group by  substr(rowid, 10, 6 );
  137.      
  138. ---
  139. alter table t_nocompression compress for oltp;

  140. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  141. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
  142. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );   

  143. begin
  144.    for i in 1..400
  145.    loop
  146.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
  147.    end loop;
  148.    commit;
  149. end;

  150. alter table t_nocompression move tablespace tbs_nocompression;
  151. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );

  152. ---
  153. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  154.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);

  155. ---

  156. insert /*+ append */ into t_basic select  * from  t_basic;
  157. commit;


  158. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );

  159. ---

  160. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  161.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  162.       
  163.    ---
  164.       alter table t_basic move tablespace  TBS_NOCOMPRESSION;
  165. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );

  166. ---

  167. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  168.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  169.   
  170. alter  tablespace users add datafile '/home/oracle/users2.dbf' size 100M;
  171.    
  172.       ---
  173.       declare
  174.   v_blkcnt_cmp number;
  175.   v_blkcnt_uncmp  number;
  176.   v_row_cmp number;
  177.   v_row_uncmp number;
  178.   v_cmp_ratio number;
  179.   v_comptype_str  varchar2(200);
  180. BEGIN
  181. DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
  182.                                                                                              ownname =>'HR',
  183.                                                                                              tabname =>'T_BASIC_BIG',
  184.                                                                                              partname =>null,
  185.                                                                                              comptype => 2,
  186.                                                                                              blkcnt_cmp => v_blkcnt_cmp,
  187.                                                                                              blkcnt_uncmp =>  v_blkcnt_uncmp,
  188.                                                                                              row_cmp =>v_row_cmp,
  189.                                                                                              row_uncmp => v_row_uncmp,
  190.                                                                                              cmp_ratio =>  v_cmp_ratio,
  191.                                                                                              comptype_str =>v_comptype_str);                                                  
  192. DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
  193. DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
  194. DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
  195. DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
  196. DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
  197. DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
  198. end;

  199. ---
  200. create table t_basic_col( a  number , b varchar2(20))  compress ;
  201. insert into t_basic_col values ( 1,'A') ;
  202. commit;
  203. create table t_oltp_col( a  number , b varchar2(20))  compress  for oltp;
  204. insert into t_oltp_col values ( 1,'A') ;
  205. commit;
  206. select  * from  t_basic_col;
  207. select  * from t_oltp_col;
  208. alter table  t_basic_col drop column b;
  209. alter table  t_basic_col drop ( b);
  210. alter table t_oltp_col drop column b;
  211. select  * from t_oltp_col;

  212. ---

  213. create tablespace  tbssmall  datafile  '/home/oracle/tbssmall.dbf'    size 5M  autoextend off;
  214. create table t04311_b( a number )  tablespace tbssmall storage ( initial 1M);

  215. CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
  216. AFTER SUSPEND
  217. ON DATABASE
  218. declare
  219. v_size number;
  220.   pragma AUTONOMOUS_TRANSACTION;
  221. begin
  222.   select  bytes into v_size from dba_data_files where  file_name='/home/oracle/tbssmall.dbf';
  223.   v_size := v_size + 5242880;
  224.   execute immediate 'alter database datafile  ''/home/oracle/tbssmall.dbf''  resize '||v_size;
  225. commit;
  226. end;

  227. select  * from user_errors;


复制代码

随堂输出:
  1. [oracle@station90 admin]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 24 21:26:18 2016

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn hr/botang123
  5. Connected.
  6. SQL> @utlchain.sql

  7. Table created.

  8. SQL> create table hr.t_basic_big compress as select  * from dba_source;
  9. create table hr.t_basic_big compress as select        * from dba_source
  10.                                                        *
  11. ERROR at line 1:
  12. ORA-00942: table or view does not exist


  13. SQL> conn / as sysdba
  14. Connected.
  15. SQL> create table hr.t_basic_big compress as select  * from dba_source;

  16. Table created.

  17. SQL> select  bytes/1024/1024  from dba_segments where segment_name='T_BASIC_BIG';

  18. BYTES/1024/1024
  19. ---------------
  20.              57

  21. SQL> delete from t04209_uname ;
  22. delete from t04209_uname
  23.             *
  24. ERROR at line 1:
  25. ORA-00942: table or view does not exist


  26. SQL> conn hr/botang123
  27. Connected.
  28. SQL> delete from t04209_uname ;

  29. 400000 rows deleted.

  30. SQL> commit;

  31. Commit complete.

  32. SQL> select  bytes/1024/1024 from user_segments where segment_name='T04209_UNAME';

  33. BYTES/1024/1024
  34. ---------------
  35.               8

  36. SQL> alter table t04209_uname  enable row movement ;

  37. Table altered.

  38. SQL> alter table t04209_uname shrink space compact;

  39. Table altered.

  40. SQL> select  bytes/1024/1024 from user_segments where segment_name='T04209_UNAME';

  41. BYTES/1024/1024
  42. ---------------
  43.               8

  44. SQL> alter table t04209_uname shrink space ;

  45. Table altered.

  46. SQL> select  bytes/1024/1024 from user_segments where segment_name='T04209_UNAME';

  47. BYTES/1024/1024
  48. ---------------
  49.           .0625

  50. SQL> select  bytes/1024/1024 from user_segments where segment_name='TBIG';

  51. BYTES/1024/1024
  52. ---------------
  53.              88

  54. SQL> delete from TBIG;




  55. 633207 rows deleted.

  56. SQL> SQL> SQL> SQL> commit;

  57. Commit complete.

  58. SQL> select  bytes/1024/1024 from user_segments where segment_name='TBIG';

  59. BYTES/1024/1024
  60. ---------------
  61.              88

  62. SQL> select  bytes/1024/1024 , tablespace_name  from user_segments where segment_name='TBIG';

  63. BYTES/1024/1024 TABLESPACE_NAME
  64. --------------- ------------------------------
  65.              88 USERS

  66. SQL> alter table TBIG  allocate extent (size 200M) ;
  67. alter table TBIG  allocate extent (size 200M)
  68. *
  69. ERROR at line 1:
  70. ORA-01653: unable to extend table HR.TBIG by 1024 in tablespace USERS


  71. SQL> exit
  72. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  73. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  74. and Real Application Testing options
  75. You have new mail in /var/spool/mail/oracle
  76. [oracle@station90 admin]$ sqlplus /nolog

  77. SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 24 22:33:21 2016

  78. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  79. SQL> conn / as sysdba
  80. Connected.
  81. SQL> alter database datafile '/home/oracle/users2.dbf' resize   300M;

  82. Database altered.

  83. SQL> conn hr/botang123
  84. Connected.
  85. SQL> alter table TBIG  allocate extent (size 200M) ;

  86. Table altered.

  87. SQL> alter session enable resumable ;
  88. ERROR:
  89. ORA-01031: insufficient privileges


  90. SQL> alter session enable resumable  ;

  91. Session altered.

  92. SQL> alter session enable resumable timeout 10  ;

  93. Session altered.

  94. SQL> create table t05318 ( a number )  storage ( inital  500M ) ;
  95. create table t05318 ( a number )  storage ( inital  500M )
  96.                                             *
  97. ERROR at line 1:
  98. ORA-02143: invalid STORAGE option


  99. SQL> create table t05318 ( a number )  storage ( initial  500M ) ;

  100. Table created.

  101. SQL> insert into t05318 values (1) ;
  102. insert into t05318 values (1)
  103.             *
  104. ERROR at line 1:
  105. ORA-30032: the suspended (resumable) statement has timed out
  106. ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace USERS


  107. SQL> alter session enable resumable timeout 100  ;

  108. Session altered.

  109. SQL> create table t04311_b( a number )  tablespace tbssmall storage ( initial 6M);

  110. Table created.

  111. SQL> insert into t04311_b values (1) ;

  112. 1 row created.

  113. SQL> commit;

  114. Commit complete.

  115. SQL>


  116. --------------------
  117. [root@station90 桌面]# su - oracle
  118. [oracle@station90 ~]$ sqlplus /nolog

  119. SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 24 22:37:54 2016

  120. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  121. SQL> conn / as sysdba
  122. Connected.
  123. SQL> grant resumable to hr ;

  124. Grant succeeded.

  125. SQL>
  126. ------------------



















复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-18 22:13 , Processed in 0.202585 second(s), 27 queries .

快速回复 返回顶部 返回列表