课程第15次

botang

1. SQL> select  * from tpart;
   
2. 
   
3. no rows selected
   
4. 
   
5. SQL> desc tpart
   
6. Name                                      Null?    Type
   
7. ----------------------------------------- -------- ----------------------------
   
8. A                                                  NUMBER
   
9. B                                                  DATE
   
10. 
    
11. SQL> insert into tpart values (1, to_Date('2018-12-31','YYYY-MM-DD'));
    
12. 
    
13. 1 row created.
    
14. 
    
15. SQL> commit;
    
16. 
    
17. Commit complete.
    
18. 
    
19. SQL>  insert into tpart values (2,  to_Date('2019-01-01','YYYY-MM-DD'));
    
20. 
    
21. 1 row created.
    
22. 
    
23. SQL> commit;
    
24. 
    
25. Commit complete.
    
26. 
    
27. SQL> select  * from tpart;
    
28. 
    
29.          A B
    
30. ---------- -------------------
    
31.          1 2018-12-31:00:00:00
    
32.          2 2019-01-01:00:00:00
    
33. 
    
34. 
    
35. SQL> select  * from tpart  partition (tpart_a) ;
    
36. 
    
37.          A B
    
38. ---------- -------------------
    
39.          1 2018-12-31:00:00:00
    
40. 
    
41. SQL>  select  * from tpart  partition (tpart_b);
    
42. 
    
43.          A B
    
44. ---------- -------------------
    
45.          2 2019-01-01:00:00:00
    
46. 
    
47. SQL>

复制代码

1. SQL> create index  ipart on tpart (b )  local ;
   
2. 
   
3. Index created.
   

复制代码

-------
挪动分区表的一个分区，本地索引就不可用，进而就没有段了：

1. SQL>alter table tpart   move  partition tpart_A  tablespace example;
   
2. 
   
3. Table altered.
   

复制代码

1. select  * from dba_tab_partitions  tp
   
2. where tp.table_owner='HR' and tp.table_name='TPART';
   
3. 
   
4. 
   
5. select  * from dba_indexes i where i.table_owner='HR'
   
6.   and i.table_name='TPART';
   
7. 
   
8. select  * from dba_part_tables pt where pt.table_name='TPART';
   
9. 
   
10. 
    
11. select  * from dba_segments s where s.owner='HR' and
    
12. s.segment_name='IPART';

复制代码

Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式：
·  支持直接路径加载的Basic压缩方式（10x）
·  支持针对所有DML操作的OLTP压缩方式（2-4x）
·  Exadata专属的Hybrid columnar compression压缩方式

    Oracle公司建议我们采用以上三种不同的压缩方式，针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值，通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。

先从图形界面入手，我们发现在新建表空间时Oracle11gR2的界面出现压缩选项：

在 下面各个部分所有的实验中我们都创建两个表：一个表在TBS_NOCOMPRESSION表空间，另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。

--sys--

1. 
   
2. 
   
3. create tablespace tbs_nocompression datafile size 10M
   
4. autoextend on;
   
5. 
   
6. create tablespace tbs_basic datafile size 10M autoextend on default compress
   
7. basic;
   
8. 
   
9. create tablespace tbs_oltp datafile size 10M autoextend on default compress for
   
10. oltp;
    
11. 
    
12. create tablespace tbs_query datafile size 10M autoextend on default compress for
    
13. query;
    
14. 
    
15. create tablespace tbs_archive datafile size 10M autoextend on default compress
    
16. for archive;
    

复制代码

1. 
   
2. select  t.tablespace_name,t.def_tab_compression, t.compress_for from dba_tablespaces t
   
3. where t.tablespace_name in ('TBS_NOCOMPRESSION',
   
4.                                                   'TBS_BASIC','TBS_OLTP',
   
5.                                                   'TBS_QUERY',
   
6.                                                   'TBS_ARCHIVE');

复制代码

--hr--                                                   

1. create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
   
2. create table t_basic (a varchar2(200)) tablespace tbs_basic;
   
3. create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
   

复制代码

--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive;  
--hr--

1. begin
   
2.    for i in 1..400
   
3.    loop
   
4.       insert into t_nocompression  values('AAAAAAAAAAAAAAAAAAAA');
   
5.    end loop;
   
6.    commit;
   
7. end;
   

复制代码

--hr--

1. begin
   
2.    for i in 1..400
   
3.    loop
   
4.      insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
   
5.    end loop;
   
6.    commit;
   
7. end;

复制代码

--hr--
begin
   for i in 1..400
   loop
      insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;  
--hr--
select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION, t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME   in('T_NOCOMPRESSION','T_BASIC','T_OLTP');
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by substr(rowid, 10, 6 );      
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp  group by substr(rowid, 10, 6 );  
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid);
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid);
--sys--      
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_OLTP',row_id => rowid)
      from hr.T_OLTP group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_OLTP',row_id => rowid);
--hr--      
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;     
--hr--
select  t.TABLE_NAME, t.PCT_FREE ,t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME   in('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
--hr--
begin
   for i in 1..400
   loop
      insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;
--hr--
begin
   for i in 1..400
   loop
      insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;
--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic2  group by substr(rowid, 10, 6 );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp2  group by substr(rowid, 10, 6 );
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC2',row_id => rowid)
      from hr.T_BASIC2 group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC2',row_id => rowid);
--sys--      
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_OLTP2',row_id => rowid)
      from hr.T_OLTP2 group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_OLTP2',row_id => rowid);
--hr--
alter table t_nocompression compress for oltp;
select  t.TABLE_NAME, t.PCT_FREE ,t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME   in('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );     
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid)
   from hr.T_NOCOMPRESSION  group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid);  
--hr--
begin
   for i in 1..400
   loop
      insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;
--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by substr(rowid, 10, 6 );
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid);
--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select  count(*)  , substr(rowid, 10, 6 ) fromhr.t_nocompression  group by  substr(rowid, 10, 6 );   
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_NOCOMPRESSION',row_id => rowid);
--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by substr(rowid, 10, 6 );  
insert /*+ append */ into t_basic select * from  t_basic;
commit;
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by substr(rowid, 10, 6 );
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid);
--hr--
alter table t_basic move tablespace TBS_NOCOMPRESSION;
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by substr(rowid, 10, 6 );      
--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by    dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC',row_id => rowid);
--sys-evolution--
drop table hr.t_basic_big;
create table hr.t_basic_big compress as select * from dba_source;
--sys-advisor--
declare
  v_blkcnt_cmp number;
  v_blkcnt_uncmp  number;
  v_row_cmp number;
  v_row_uncmp number;
  v_cmp_ratio number;
  v_comptype_str  varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
                                                                                             ownname=>'HR',
                                                                                            tabname =>'TBIG',
                                                                                             partname=>null,
                                                                                            comptype => 2,
                                                                                            blkcnt_cmp=> v_blkcnt_cmp,
                                                                                            blkcnt_uncmp => v_blkcnt_uncmp,
                                                                                            row_cmp=>v_row_cmp,
                                                                                            row_uncmp => v_row_uncmp,
                                                                                            cmp_ratio => v_cmp_ratio,
                                                                                            comptype_str =>v_comptype_str);                                                
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' ||v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
--输出--
Blk count compressed = 1785
Blk count uncompressed = 2340
Row count per block compressed = 68
Row count per block uncompressed = 51
ratio: 1.31092436974789915966386554621848739496
Compression type = "Compress For OLTP"
--OLTP压缩一下表--
验证表大小

--sys--
select   count(*)  ,   dbms_compression.get_compression_type(ownname => 'HR',tabname =>'T_BASIC_BIG',row_id => rowid)
      from hr.T_BASIC_BIG group by     dbms_compression.get_compression_type(ownname=> 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);  
--hr--
create table t_basic_col( a  number , bvarchar2(20))  compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a  number , bvarchar2(20))  compress  for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select  * from  t_basic_col;
select  * from t_oltp_col;
alter table  t_basic_col drop columnb;
alter table  t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select  * from t_oltp_col;
-----------------------------------------------

1. grant resumable to hr;

复制代码

1. SQL>  alter session set resumable_timeout=10;
   
2. 
   
3. Session altered.
   
4. 
   
5. SQL> alter session enable resumable;
   
6. 
   
7. Session altered.
   
8. 
   
9. SQL> create table t05318_c ( a number )  tablespace tbs6 storage ( initial 6M ) ;
   
10. 
    
11. Table created.
    
12. 
    
13. SQL> insert into  t05318_c values (1) ;
    
14. insert into  t05318_c values (1)
    
15.              *
    
16. ERROR at line 1:
    
17. ORA-30032: the suspended (resumable) statement has timed out
    
18. ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS6
    
19. 
    
20. 
    
21. SQL>

复制代码

---suspend trigger

1. create or replace trigger trg_suspend
   
2. after suspend
   
3. on database
   
4. declare
   
5.   v_size number;
   
6.   pragma AUTONOMOUS_TRANSACTION;
   
7. begin
   
8.   select BYTES into v_size from dba_data_files where FILE_name='+DATA/orcl/datafile/tbs6.276.1022136411';
   
9.   execute immediate 'alter database datafile ''+DATA/orcl/datafile/tbs6.276.1022136411'' resize '||to_char(v_size+ 5242880);
   
10.   commit;
    
11. end;
    
12. 
    

复制代码