设为首页收藏本站

Botang唐波's Oracle Station

查看: 248|回复: 0

课程第60次(2018-06-11星期一)课程结束

[复制链接]

709

主题

1071

帖子

7764

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7764
发表于 2018-6-11 19:56:45 | 显示全部楼层 |阅读模式
  1. select  count(*) from hr.t04209_uname;

  2. select count(distinct uvalue) from  hr.t04209_uname;

  3. select  t.num_rows  from cdb_tables t
  4. where t.owner='HR' and t.table_name='T04209_UNAME';

  5. select  * from cdb_tab_col_statistics tcs where tcs.owner='HR'
  6. and tcs.table_name='T04209_UNAME';


  7. begin
  8.    dbms_stats.gather_table_stats('HR','T04209_UNAME');
  9. end;

  10. begin
  11.    dbms_stats.gather_table_stats('HR','T04209_UNAME',
  12.    estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
  13.    method_opt => 'for all columns size auto'   
  14.    );
  15. end;

  16. begin
  17.    dbms_stats.gather_table_stats('HR','T04209_UNAME',
  18.    estimate_percent => 100,
  19.    method_opt => 'for columns uvalue size 254 for all columns size auto'   
  20.    );
  21. end;

  22. select  * from cdb_tab_col_statistics tcs where tcs.owner='HR'
  23. and tcs.table_name='T04209_UNAME';

  24. select  * from CDB_TAB_HISTOGRAMS  cth
  25. where cth.owner='HR' and cth.table_name='T04209_UNAME';


  26. select i.index_name, i.last_analyzed from cdb_indexes i where i.owner='HR'
  27.   and i.table_name='T04209_UNAME';
  28.   
  29.   
复制代码
  1. [root@station90 桌面]# su - oracle
  2. [oracle@station90 ~]$ . oraenv
  3. ORACLE_SID = [orcl] ? cdb2
  4. The Oracle base remains unchanged with value /u01/app/oracle
  5. [oracle@station90 ~]$ sqlplus /nolog

  6. SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 11 19:40:16 2018

  7. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  8. SQL> conn hr/oracle_4U@pdb2
  9. Connected.
  10. SQL> update t04209_uname set uvalue=99999  where rownum <=99000;

  11. 99000 rows updated.

  12. SQL> commit;

  13. Commit complete.

  14. SQL> create index i04209_uname_uvalue on t04209_uname (uvalue) ;

  15. Index created.

  16. SQL> drop index i04209_uname;

  17. Index dropped.

  18. SQL>
  19. SQL>
  20. SQL>
  21. SQL>
  22. SQL> set autot on
  23. SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
  24. SP2-0611: Error enabling STATISTICS report
  25. SQL> conn sys/oracle_4U@pdb2 as sysdba
  26. Connected.
  27. SQL> @?/sqlplus/admin/plustrce.sql
  28. SQL>
  29. SQL> drop role plustrace;
  30. drop role plustrace
  31.           *
  32. ERROR at line 1:
  33. ORA-01919: role 'PLUSTRACE' does not exist


  34. SQL> create role plustrace;

  35. Role created.

  36. SQL>
  37. SQL> grant select on v_$sesstat to plustrace;

  38. Grant succeeded.

  39. SQL> grant select on v_$statname to plustrace;

  40. Grant succeeded.

  41. SQL> grant select on v_$mystat to plustrace;

  42. Grant succeeded.

  43. SQL> grant plustrace to dba with admin option;

  44. Grant succeeded.

  45. SQL>
  46. SQL> set echo off
  47. SQL> grant plustrace to hr;

  48. Grant succeeded.

  49. SQL> conn hr/oracle_4U@pdb2
  50. Connected.
  51. SQL> set autot  traceonly
  52. SQL> select  * from t04209_uname where uvalue=99999;

  53. 99000 rows selected.


  54. Execution Plan
  55. ----------------------------------------------------------
  56. Plan hash value: 3641033916

  57. --------------------------------------------------------------------------------
  58. --

  59. | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
  60. |

  61. --------------------------------------------------------------------------------
  62. --

  63. |   0 | SELECT STATEMENT  |                 | 99016 |  1160K|    70   (2)| 00:00:01
  64. |

  65. |*  1 |  TABLE ACCESS FULL| T04209_UNAME | 99016 |  1160K|    70   (2)| 00:00:01
  66. |

  67. --------------------------------------------------------------------------------
  68. --


  69. Predicate Information (identified by operation id):
  70. ---------------------------------------------------

  71.    1 - filter("UVALUE"=99999)


  72. Statistics
  73. ----------------------------------------------------------
  74.           1  recursive calls
  75.           0  db block gets
  76.        6836  consistent gets
  77.           0  physical reads
  78.           0  redo size
  79.     2437959  bytes sent via SQL*Net to client
  80.       73140  bytes received via SQL*Net from client
  81.        6601  SQL*Net roundtrips to/from client
  82.           0  sorts (memory)
  83.           0  sorts (disk)
  84.       99000  rows processed

  85. SQL> select  * from t04209_uname where uvalue=9;

  86. no rows selected


  87. Execution Plan
  88. ----------------------------------------------------------
  89. Plan hash value: 82881757

  90. --------------------------------------------------------------------------------
  91. ---------------------------

  92. | Id  | Operation                            | Name                  | Rows  | Byte
  93. s | Cost (%CPU)| Time          |

  94. --------------------------------------------------------------------------------
  95. ---------------------------

  96. |   0 | SELECT STATEMENT                    |                          |        1 |    1
  97. 2 |        2   (0)| 00:00:01 |

  98. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T04209_UNAME          |        1 |    1
  99. 2 |        2   (0)| 00:00:01 |

  100. |*  2 |   INDEX RANGE SCAN                    | I04209_UNAME_UVALUE |        1 |
  101.   |        1   (0)| 00:00:01 |

  102. --------------------------------------------------------------------------------
  103. ---------------------------


  104. Predicate Information (identified by operation id):
  105. ---------------------------------------------------

  106.    2 - access("UVALUE"=9)


  107. Statistics
  108. ----------------------------------------------------------
  109.           1  recursive calls
  110.           0  db block gets
  111.           2  consistent gets
  112.           0  physical reads
  113.           0  redo size
  114.         409  bytes sent via SQL*Net to client
  115.         540  bytes received via SQL*Net from client
  116.           1  SQL*Net roundtrips to/from client
  117.           0  sorts (memory)
  118.           0  sorts (disk)
  119.           0  rows processed

  120. SQL> L| T04209_UNAME | 99016 |  1160K|    70   (2)| 00:00:01
  121. SP2-0224: invalid starting line number
  122. SQL>
复制代码

试错(begin
   dbms_stats.gather_table_stats('HR','T04209_UNAME',
   estimate_percent => 100,
   method_opt => 'for columns uvalue size 1 for all columns size auto'   
   );
end;)


  1. SQL> select  * from t04209_uname where uvalue=99999;

  2. 99000 rows selected.


  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 82881757

  6. --------------------------------------------------------------------------------
  7. ---------------------------

  8. | Id  | Operation                            | Name                  | Rows  | Byte
  9. s | Cost (%CPU)| Time          |

  10. --------------------------------------------------------------------------------
  11. ---------------------------

  12. |   0 | SELECT STATEMENT                    |                          |   100 |  120
  13. 0 |        2   (0)| 00:00:01 |

  14. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T04209_UNAME          |   100 |  120
  15. 0 |        2   (0)| 00:00:01 |

  16. |*  2 |   INDEX RANGE SCAN                    | I04209_UNAME_UVALUE |   100 |
  17.   |        1   (0)| 00:00:01 |

  18. --------------------------------------------------------------------------------
  19. ---------------------------


  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  22.    2 - access("UVALUE"=99999)


  23. Statistics
  24. ----------------------------------------------------------
  25.           0  recursive calls
  26.           0  db block gets
  27.        6836  consistent gets
  28.           0  physical reads
  29.           0  redo size
  30.     2437959  bytes sent via SQL*Net to client
  31.       73140  bytes received via SQL*Net from client
  32.        6601  SQL*Net roundtrips to/from client
  33.           0  sorts (memory)
  34.           0  sorts (disk)
  35.       99000  rows processed

  36. SQL>
复制代码
个性化:
  1. select  dbms_stats.get_prefs(pname => 'STALE_PERCENT') from dual;

  2. select  * from cdb_tab_stat_prefs tsp where tsp.owner='HR'
  3.     and tsp.table_name='EMPLOYEES';
  4.    
  5. select  dbms_stats.get_prefs(pname =>  'STALE_PERCENT',
  6. ownname => 'HR',
  7. tabname => 'EMPLOYEES') from dual;

  8. begin
  9. dbms_stats.set_table_prefs('HR','EMPLOYEES','STALE_PERCENT','13');
  10. end;
复制代码
  1. SQL> conn sys/oracle_4U@pdb2 as sysdba
  2. Connected.
  3. SQL> show parameter optimizer

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. optimizer_adaptive_features             boolean         TRUE
  7. optimizer_adaptive_reporting_only    boolean         FALSE
  8. optimizer_capture_sql_plan_baselines boolean         FALSE
  9. optimizer_dynamic_sampling             integer         2
  10. optimizer_features_enable             string         12.1.0.2
  11. optimizer_index_caching              integer         0
  12. optimizer_index_cost_adj             integer         100
  13. optimizer_inmemory_aware             boolean         TRUE
  14. optimizer_mode                             string         ALL_ROWS
  15. optimizer_secure_view_merging             boolean         TRUE
  16. optimizer_use_invisible_indexes      boolean         FALSE

  17. NAME                                     TYPE         VALUE
  18. ------------------------------------ ----------- ------------------------------
  19. optimizer_use_pending_statistics     boolean         FALSE
  20. optimizer_use_sql_plan_baselines     boolean         FALSE
  21. SQL> conn hr/oracle_4U@pdb2
  22. Connected.
  23. SQL> alter session set optimizer_use_pending_statistics=true;

  24. Session altered.

  25. SQL>
复制代码

  1. select  t.num_rows  from cdb_tables t
  2. where t.owner='HR' and t.table_name='T04209_UNAME';

  3. select * from cdb_tab_pending_stats tps
  4.   where tps.owner='HR' and tps.table_name='T04209_UNAME';

  5.     select  dbms_stats.get_prefs(pname => 'PUBLISH') from dual;

  6.     select  * from cdb_tab_stat_prefs tsp where tsp.owner='HR'
  7.         and tsp.table_name='EMPLOYEES';
  8.       
  9.     select  dbms_stats.get_prefs(pname =>  'PUBLISH',
  10.     ownname => 'HR',
  11.     tabname => 'EMPLOYEES') from dual;

  12.     begin
  13.     dbms_stats.set_table_prefs('HR','T04209_UNAME','PUBLISH','FALSE');
  14.     end;


  15. begin
  16.    dbms_stats.gather_table_stats('HR','T04209_UNAME',
  17.    estimate_percent => 100,
  18.    method_opt => 'for columns uvalue size 254 for all columns size auto'   
  19.       );
  20. end;

  21. select * from cdb_tab_pending_stats tps
  22.   where tps.owner='HR' and tps.table_name='T04209_UNAME';
  23.   
  24.   begin
  25.      dbms_stats.publish_pending_stats(ownname => 'HR',
  26.      tabname => 'T04209_UNAME');
  27.    end;
  28.      
复制代码

代码KEEP:
  1. begin
  2.    dbms_shared_pool.keep('HR.PROC1');
  3. end;

  4. select    oc.kept  from v_$db_object_cache  oc
  5. where oc.owner='HR' and oc.name='PROC1';
复制代码

SAA怎么用:
a.png

SQL Access Advisor的捷径:

  1. create table hr.tbig as select * from cdb_source;

  2. begin
  3.    dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
  4.    task_name => 'MY1',
  5.    attr1 => 'select count(*)  from hr.tbig group by line',
  6.    template =>dbms_advisor.SQLACCESS_WAREHOUSE);
  7. end;


  8. begin
  9.    dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
  10.    task_name => 'MY2',
  11.    attr1 => 'select count(*)  from hr.tbig group by line',
  12.    template =>dbms_advisor.SQLACCESS_OLTP);
  13. end;
复制代码

行迁移,行链接:
  1. [root@station90 ~]# su - oracle
  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 30 15:13:48 2015

  4. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  5. SQL> conn hr/oracle_4U
  6. ERROR:
  7. ORA-28002: the password will expire within 5 days


  8. Connected.
  9. SQL> create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));

  10. Table created.

  11. SQL> insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

  12. 1 row created.

  13. SQL> commit;

  14. Commit complete.

  15. SQL> update t05318_chain  set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  16. 1 row updated.

  17. SQL> commit;

  18. Commit complete.

  19. SQL> update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  20. 1 row updated.

  21. SQL> commit;

  22. Commit complete.

  23. SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;

  24. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  25. ------------------------------------
  26.                                  559

  27. SQL> select  * from CHAINED_ROWS;
  28. select        * from CHAINED_ROWS
  29.                *
  30. ERROR at line 1:
  31. ORA-00942: table or view does not exist


  32. SQL> @?/rdbms/admin/utlchain.sql

  33. Table created.

  34. SQL> set linesize 1000
  35. SQL> select  * from CHAINED_ROWS;

  36. no rows selected

  37. SQL> analyze table t05318_chain list chained rows;

  38. Table analyzed.

  39. SQL> select  * from CHAINED_ROWS;

  40. no rows selected

  41. SQL> update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  42. 1 row updated.

  43. SQL> commit;

  44. Commit complete.

  45. SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;

  46. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  47. ------------------------------------
  48.                                  559

  49. SQL> analyze table t05318_chain list chained rows;

  50. Table analyzed.

  51. SQL> select  * from CHAINED_ROWS;

  52. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  53. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  54. HR                               T05318_CHAIN                                                                                    N/A                            AAASOPAAEAAAAIvAAA 30-MAY-15

  55. SQL> truncate table CHAINED_ROWS;

  56. Table truncated.

  57. SQL> select  * from CHAINED_ROWS;

  58. no rows selected

  59. SQL> analyze table t05318_chain list chained rows;

  60. Table analyzed.

  61. SQL> select  * from CHAINED_ROWS;

  62. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  63. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  64. HR                               T05318_CHAIN                                                                                    N/A                            AAASOPAAEAAAAIvAAA 30-MAY-15

  65. SQL> truncate table CHAINED_ROWS;

  66. Table truncated.

  67. SQL> select  * from CHAINED_ROWS;

  68. no rows selected

  69. SQL> alter table t05318_chain  move tablspace tbs16k;
  70. alter table t05318_chain  move tablspace tbs16k
  71.                                *
  72. ERROR at line 1:
  73. ORA-14133: ALTER TABLE MOVE cannot be combined with other operations


  74. SQL> alter table t05318_chain  move tablespace tbs16k;

  75. Table altered.

  76. SQL> analyze table t05318_chain list chained rows;

  77. Table analyzed.

  78. SQL> select  * from CHAINED_ROWS;

  79. no rows selected

  80. SQL> create table t05318_migrate( a varchar2(2000))  pctfree 0;

  81. Table created.

  82. SQL> show user
  83. USER is "HR"
  84. begin
  85.    for i in 1..733
  86.    loop
  87.       insert into  t05318_migrate values('A');
  88.     end loop;
  89.     commit;
  90.   7  end;
  91.   8  /

  92. PL/SQL procedure successfully completed.

  93. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  94.   2   group by dbms_rowid.rowid_block_number(rowid);

  95. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  96. ------------------------------------ ----------
  97.                                  559            733

  98. SQL> begin
  99.   2    for i in 1..100
  100.   3    loop
  101.   4      insert into  t05318_migrate values('B');
  102.   5    end loop;
  103.   6    commit;
  104.   7  end;
  105.   8  /

  106. PL/SQL procedure successfully completed.

  107. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  108.   2    group by dbms_rowid.rowid_block_number(rowid);

  109. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  110. ------------------------------------ ----------
  111.                                  555            100
  112.                                  559            733

  113. SQL> delete from t05318_migrate where A='B';

  114. 100 rows deleted.

  115. SQL> commit;

  116. Commit complete.

  117. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  118.   2    group by dbms_rowid.rowid_block_number(rowid);

  119. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  120. ------------------------------------ ----------
  121.                                  559            733

  122. SQL> analyze table t05318_migrate  list chained rows;

  123. Table analyzed.

  124. SQL> select  * from chained_rows;

  125. no rows selected

  126. SQL> update t05318_migrate set a=''aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
  127. ERROR:
  128. ORA-00972: identifier is too long


  129. SQL> update t05318_migrate set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' where rownum=1;

  130. 1 row updated.

  131. SQL> commit;

  132. Commit complete.

  133. SQL> analyze table t05318_migrate  list chained rows;

  134. Table analyzed.

  135. SQL> select  * from chained_rows;

  136. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  137. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  138. HR                               T05318_MIGRATE                                                                                    N/A                            AAASOUAAEAAAAIvAAA 30-MAY-15

  139. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  140.   2   group by dbms_rowid.rowid_block_number(rowid);

  141. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  142. ------------------------------------ ----------
  143.                                  559            733

  144. SQL> alter table T05318_MIGRATE move tablespace users;

  145. Table altered.

  146. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  147.   2   group by dbms_rowid.rowid_block_number(rowid);

  148. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  149. ------------------------------------ ----------
  150.                                  588            185
  151.                                  587            548

  152. SQL> alter table T05318_MIGRATE  pctfree 20;

  153. Table altered.

  154. SQL>  alter table T05318_MIGRATE move tablespace users;

  155. Table altered.

  156. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  157.   2   group by dbms_rowid.rowid_block_number(rowid);

  158. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  159. ------------------------------------ ----------
  160.                                  555            402
  161.                                  556            331

  162. SQL> truncate table chained_rows;

  163. Table truncated.

  164. SQL> analyze table T05318_MIGRATE list chained rows;

  165. Table analyzed.

  166. SQL> select  * from chained_rows;

  167. no rows selected

  168. SQL>















复制代码

空间不够的问题:

  1. SQL> conn hr/oracle_4U@pdb2
  2. Connected.
  3. SQL> create table t1 ( a number ) storage ( extent 11M )  tablespace tbs1;
  4. create table t1 ( a number ) storage ( extent 11M )  tablespace tbs1
  5.                                        *
  6. ERROR at line 1:
  7. ORA-02143: invalid STORAGE option


  8. SQL> create table t1 ( a number ) storage ( initial extent 11M )  tablespace tbs1;
  9. create table t1 ( a number ) storage ( initial extent 11M )  tablespace tbs1
  10.                                                *
  11. ERROR at line 1:
  12. ORA-02218: invalid INITIAL storage option value


  13. SQL> create table t1 ( a number ) storage ( initial 11M )  tablespace tbs1;

  14. Table created.

  15. SQL> insert into t1 values (1) ;
  16. insert into t1 values (1)
  17.             *
  18. ERROR at line 1:
  19. ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace TBS1


  20. SQL>
复制代码
SYS:
  1. grant resumable to hr;
复制代码

HR:
  1. SQL> insert into t1 values (1)  timeout 5  ;
  2. insert into t1 values (1)  timeout 5
  3.                            *
  4. ERROR at line 1:
  5. ORA-00933: SQL command not properly ended


  6. SQL> insert into t1 values (1)   ;
  7. insert into t1 values (1)
  8.             *
  9. ERROR at line 1:
  10. ORA-30032: the suspended (resumable) statement has timed out
  11. ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace TBS1


  12. SQL>
复制代码
自治事务加上动态SQL的悬挂触发器:
  1. grant resumable to hr;


  2. select   file_name,  bytes, bytes/1024/1024  from cdb_data_files
  3. where tablespace_name='TBS1';

  4. create or replace trigger trgocp11_limit
  5. after suspend
  6. on database
  7.   declare
  8.   v_size number;
  9.    pragma AUTONOMOUS_TRANSACTION;
  10. begin
  11.   select  BYTES into v_size from cdb_data_files
  12.   where FILE_name='/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf';
  13.   
  14.   execute immediate 'alter database datafile ''/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf'' resize
  15.      '||to_char(v_size+5242880);
  16.   commit;
  17. end;


  18. select  BYTES from cdb_data_files
  19.   where FILE_name='/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf';
复制代码






回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-8-21 00:51 , Processed in 0.207316 second(s), 27 queries .

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