第64次：2014-11-13 空间管理

botang

migrate_chain_rows.sql

1. create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));
   
2. 
   
3. insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
   
4. 
   
5. select  * from t05318_chain;
   
6. 
   
7. update t05318_chain  set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
   
8. 
   
9. select  * from t05318_chain;
   
10. 
    
11. update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
    
12. 
    
13. select  * from t05318_chain;
    
14. 
    
15. select  * from CHAINED_ROWS;
    
16. 
    
17. analyze table t05318_chain  list chained rows;
    
18. 
    
19. select  * from CHAINED_ROWS;
    
20. 
    
21. update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
    
22. 
    
23. analyze table t05318_chain  list chained rows;
    
24. 
    
25. select  * from CHAINED_ROWS;
    
26. 
    
27. select  * from dba_tablespaces;
    
28. 
    
29. select  * from dba_Tables t where t.owner='HR' and t.table_name='T05318_CHAIN';
    
30. 
    
31. create tablespace tbs16k  datafile size 5M blocksize  16384;
    
32. 
    
33. alter system set db_16k_cache_size=1M;
    
34. 
    
35. create tablespace tbs16k  datafile size 5M blocksize  16384;
    
36. 
    
37. alter table hr.T05318_CHAIN move tablespace tbs16k;
    
38. 
    
39. select  * from CHAINED_ROWS;
    
40. 
    
41. analyze table t05318_chain  list chained rows;
    
42. 
    
43. 
    
44. select  * from CHAINED_ROWS;
    
45. 
    
46. -----
    
47. 
    
48. create table t05318_migrate( a varchar2(2000))  pctfree 0;
    
49. 
    
50. truncate  table t05318_migrate;
    
51. 
    
52. begin
    
53.    for i in 1..733
    
54.    loop
    
55.       insert into  t05318_migrate values('A');
    
56.     end loop;
    
57.     commit;
    
58. end;
    
59. 
    
60. 
    
61. analyze table t05318_migrate  list chained rows;
    
62. 
    
63. select  * from CHAINED_ROWS;
    
64. 
    
65. select     distinct dbms_rowid.rowid_block_number(rowid)
    
66.    from t05318_migrate;
    
67. 
    
68. 
    
69. select      dbms_rowid.rowid_block_number(rowid)  myrow ,count( dbms_rowid.rowid_block_number(rowid)  )
    
70.    from t05318_migrate  group by    dbms_rowid.rowid_block_number(rowid)     ;
    
71.    
    
72.    insert into    t05318_migrate values('A');
    
73.    
    
74.    
    
75.    update  t05318_migrate set a='AAAAAA';
    
76.    
    
77.    analyze table t05318_migrate  list chained rows;
    
78. 
    
79. select  * from CHAINED_ROWS;
    
80. 
    
81. select     distinct dbms_rowid.rowid_block_number(rowid)
    
82.    from t05318_migrate;
    
83. 
    
84. 
    
85. select      dbms_rowid.rowid_block_number(rowid)  myrow ,count( dbms_rowid.rowid_block_number(rowid)  )
    
86.    from t05318_migrate  group by    dbms_rowid.rowid_block_number(rowid)     ;
    
87.    
    
88.    select * from user_Tables t where t.TABLE_NAME='T05318_MIGRATE';
    
89.    
    
90.    
    
91.    alter table T05318_MIGRATE  move tablespace users;
    
92.    
    
93.    truncate table CHAINED_ROWS;
    
94.    
    
95.    analyze table t05318_migrate  list chained rows;
    
96. 
    
97. select  * from CHAINED_ROWS;
    
98. 
    
99. select     distinct dbms_rowid.rowid_block_number(rowid)
    
100.    from t05318_migrate;
     
101. 
     
102. 
     
103. select      dbms_rowid.rowid_block_number(rowid)  myrow ,count( dbms_rowid.rowid_block_number(rowid)  )
     
104.    from t05318_migrate  group by    dbms_rowid.rowid_block_number(rowid)     ;
     
105.    
     
106.    
     

复制代码

2014-11-13-index.sql

1. create table t05318_d( a number ) ;
   
2. 
   
3. insert into t05318_d values (1) ;
   
4. 
   
5. commit;
   
6. 
   
7. create index i05318_d on t05318_d ( a )  unusable;
   
8. 
   
9. select  * from user_segments s where s.segment_name='I05318_D';
   
10. 
    
11. 
    
12. create table t05318_e( a number ) ;
    
13. 
    
14. insert into t05318_e values (1) ;
    
15. 
    
16. commit;
    
17. 
    
18. create index i05318_e on t05318_e ( a ) ;
    
19. 
    
20. select  * from user_segments s where s.segment_name='I05318_E';
    
21. 
    
22. alter index i05318_e unusable;
    
23. 
    
24. select  * from user_indexes i where i.INDEX_NAME='I05318_E';
    
25. 
    
26. select  * from user_segments s where s.segment_name='I05318_E';
    
27. 
    
28. 
    
29. alter index i05318_e rebuild;
    
30. 
    
31. select  * from user_indexes i where i.INDEX_NAME='I05318_E';
    
32. 
    
33. select  * from user_segments s where s.segment_name='I05318_E';
    
34. 
    
35. ----
    
36. 
    
37. 
    
38. 
    
39. create table t05318_f ( a  number )
    
40. partition by  range ( a )
    
41. ( partition t05318_f_p1 values less than (10)  tablespace users,
    
42.    partition t05318_f_p2 values less than (100)  tablespace users,
    
43.     partition t05318_f_p3 values less than (maxvalue)  tablespace example);
    
44.    
    
45.     select * from user_tables t where t.TABLE_NAME='T05318_F';
    
46.    
    
47.     select * from user_part_tables  ;
    
48.    
    
49.    
    
50.     select * from user_segments s where s.segment_name='T05318_F';
    
51.    
    
52.     insert into t05318_f values (100) ;
    
53.    
    
54.         select * from user_segments s where s.segment_name='T05318_F';
    
55.         
    
56.         insert into t05318_f values (10) ;
    
57.    
    
58.     select * from user_segments s where s.segment_name='T05318_F';
    
59.    
    
60.    
    
61.        insert into t05318_f values (-0.001) ;
    
62.       
    
63.       
    
64.       
    
65.       
    
66.       
    
67.         select * from user_segments s where s.segment_name='T05318_F';
    
68.    
    
69.    
    
70.    
    
71.     create index i05318_f  on t05318_f ( a )  local;
    
72.    
    
73.    
    
74.          select * from user_segments s where s.segment_name='I05318_F';
    
75.          
    
76.          
    
77.          alter table t05318_f move partition T05318_F_P3  tablespace users;
    
78.          
    
79.                  select * from user_segments s where s.segment_name='T05318_F';
    
80.                  
    
81.                    select * from user_segments s where s.segment_name='I05318_F';

复制代码

2014-11-13-deferred.sql

1. 
   
2. select  * from CHAINED_ROWS;
   
3. 
   
4. select      dbms_rowid.rowid_block_number(rowid)  myrow ,count( dbms_rowid.rowid_block_number(rowid)  )
   
5.    from t05318_migrate  group by    dbms_rowid.rowid_block_number(rowid)     ;
   
6.    
   
7. update  t05318_migrate set a='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaA';
   
8. 
   
9. commit;
   
10. 
    
11. -----
    
12. create table t05318_a( a number ) ;
    
13. 
    
14. select * from user_tables t where t.TABLE_NAME='T05318_A';
    
15. 
    
16. select  * from user_segments s where s.segment_name='T05318_A';
    
17. 
    
18. select  * from user_extents e where e.segment_name='T05318_A';
    
19. 
    
20. insert into t05318_a values(1);
    
21. 
    
22. rollback;
    
23. 
    
24. ----
    
25. 
    
26. create table t05318_b( a number ) ;
    
27. 
    
28. select * from user_tables t where t.TABLE_NAME='T05318_B';
    
29. 
    
30. select  * from user_segments s where s.segment_name='T05318_B';
    
31. 
    
32. select  * from user_extents e where e.segment_name='T05318_B';
    
33. 
    
34. ----
    
35. 
    
36. create table t05318_c ( a number )
    
37. segment creation  immediate  tablespace example;
    
38. 
    
39. select * from user_tables t where t.TABLE_NAME='T05318_C';
    
40. 
    
41. select  * from user_segments s where s.segment_name='T05318_C';
    
42. 
    
43. select  * from user_extents e where e.segment_name='T05318_C';
    
44. 
    
45. create index  i05318_c on   t05318_c(a);
    
46. 
    
47. select  * from user_segments s where s.segment_name='I05318_C';

复制代码

2014-11-13-comp.sql

1. --sys--
   
2. create tablespace tbs_nocompression datafile size 10M autoextend on;
   
3. 
   
4. create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
   
5. 
   
6. create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
   
7. 
   
8. 
   
9. create tablespace tbs_query datafile size 10M autoextend on default compress for query;
   
10. 
    
11. 
    
12. create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
    
13. 
    
14. 
    
15. select  t.tablespace_name, t.def_tab_compression, t.compress_for  from dba_tablespaces t
    
16. where t.tablespace_name in ('TBS_NOCOMPRESSION',
    
17.                                                   'TBS_BASIC', 'TBS_OLTP',
    
18.                                                    'TBS_QUERY',
    
19.                                                    'TBS_ARCHIVE');
    
20.                                                    
    
21.    --hr--                                                   
    
22. create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
    
23. 
    
24. create table t_basic (a varchar2(200)) tablespace tbs_basic;
    
25. 
    
26. create table t_oltp (a varchar2(200)) tablespace tbs_oltp;                                                
    
27.                                                    --hr--
    
28. begin
    
29.    for i in 1..400
    
30.    loop
    
31.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
    
32.    end loop;
    
33.    commit;
    
34. end;
    
35. --hr--
    
36. begin
    
37.    for i in 1..400
    
38.    loop
    
39.       insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
    
40.    end loop;
    
41.    commit;
    
42. end;
    
43. --hr--
    
44. begin
    
45.    for i in 1..400
    
46.    loop
    
47.       insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
    
48.    end loop;
    
49.    commit;
    
50. end;  
    
51. --hr--
    
52. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
    
53. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
    
54. 
    
55. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
    
56. 
    
57. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );      
    
58. 
    
59. 
    
60. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp  group by  substr(rowid, 10, 6 );  
    
61. 
    
62. --sys--
    
63. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
64.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
65. --sys--
    
66. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
    
67.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
    
68. --sys--      
    
69. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
    
70.       from hr.T_OLTP group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
    
71.       
    
72.      
    
73. --hr--      
    
74. create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
    
75. create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
    
76. --hr-error--
    
77. create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
    
78. create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;     
    
79. --hr--
    
80. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
    
81. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
    
82.   --hr--
    
83. begin
    
84.    for i in 1..400
    
85.    loop
    
86.       insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
    
87.    end loop;
    
88.    commit;
    
89. end;
    
90. --hr--
    
91. begin
    
92.    for i in 1..400
    
93.    loop
    
94.       insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
    
95.    end loop;
    
96.    commit;
    
97. end;   
    
98. 
    
99. --hr--
    
100. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic2  group by  substr(rowid, 10, 6 );
     
101. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp2  group by  substr(rowid, 10, 6 );
     
102. 
     
103. 
     
104. --sys--
     
105. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
     
106.       from hr.T_BASIC2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
     
107. --sys--      
     
108. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
     
109.       from hr.T_OLTP2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
     
110.       
     
111.    --hr--
     
112. alter table t_nocompression compress for oltp;
     
113. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
     
114. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
     
115. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );   
     
116.   --sys--
     
117. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
     
118.    from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
     
119.    
     
120. --hr--
     
121. begin
     
122.    for i in 1..400
     
123.    loop
     
124.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
     
125.    end loop;
     
126.    commit;
     
127. end;
     
128. --hr--
     
129. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );  
     
130. 
     
131. --sys--
     
132. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
     
133.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
     
134.    --hr--
     
135. alter table t_nocompression move tablespace tbs_nocompression;
     
136. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );      
     
137. 
     
138. --sys--
     
139. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
     
140.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
     
141.       
     
142.     --hr--
     
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.       
     
151. --sys--
     
152. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
     
153.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
     
154.       
     
155.       
     
156.   --hr--
     
157. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );  
     
158. insert /*+ append */ into t_basic select  * from  t_basic;
     
159. commit;
     
160. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
     
161. 
     
162. --sys--
     
163. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
     
164.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
     
165.       
     
166.       
     
167.       --hr--
     
168. alter table t_basic move tablespace  TBS_NOCOMPRESSION;
     
169. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
     
170. 
     
171. --sys--
     
172. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
     
173.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
     
174.       
     
175.       
     
176.    
     
177.       
     
178.       

复制代码