|
- SQL> select * from tpart;
- no rows selected
- SQL> desc tpart
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- A NUMBER
- B DATE
- SQL> insert into tpart values (1, to_Date('2018-12-31','YYYY-MM-DD'));
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into tpart values (2, to_Date('2019-01-01','YYYY-MM-DD'));
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from tpart;
- A B
- ---------- -------------------
- 1 2018-12-31:00:00:00
- 2 2019-01-01:00:00:00
- SQL> select * from tpart partition (tpart_a) ;
- A B
- ---------- -------------------
- 1 2018-12-31:00:00:00
- SQL> select * from tpart partition (tpart_b);
- A B
- ---------- -------------------
- 2 2019-01-01:00:00:00
- SQL>
复制代码- SQL> create index ipart on tpart (b ) local ;
- Index created.
复制代码
-------
挪动分区表的一个分区,本地索引就不可用,进而就没有段了:
- SQL>alter table tpart move partition tpart_A tablespace example;
- Table altered.
复制代码- select * from dba_tab_partitions tp
- where tp.table_owner='HR' and tp.table_name='TPART';
-
-
- select * from dba_indexes i where i.table_owner='HR'
- and i.table_name='TPART';
-
- select * from dba_part_tables pt where pt.table_name='TPART';
-
- select * from dba_segments s where s.owner='HR' and
- 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--
- create tablespace tbs_nocompression datafile size 10M
- autoextend on;
- create tablespace tbs_basic datafile size 10M autoextend on default compress
- basic;
- create tablespace tbs_oltp datafile size 10M autoextend on default compress for
- oltp;
- create tablespace tbs_query datafile size 10M autoextend on default compress for
- query;
- create tablespace tbs_archive datafile size 10M autoextend on default compress
- for archive;
复制代码
- select t.tablespace_name,t.def_tab_compression, t.compress_for from dba_tablespaces t
- where t.tablespace_name in ('TBS_NOCOMPRESSION',
- 'TBS_BASIC','TBS_OLTP',
- 'TBS_QUERY',
- 'TBS_ARCHIVE');
复制代码
--hr--
- create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
- create table t_basic (a varchar2(200)) tablespace tbs_basic;
- 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--
- begin
- for i in 1..400
- loop
- insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
复制代码
--hr--
- begin
- for i in 1..400
- loop
- insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- 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;
-----------------------------------------------
- SQL> alter session set resumable_timeout=10;
- Session altered.
- SQL> alter session enable resumable;
- Session altered.
- SQL> create table t05318_c ( a number ) tablespace tbs6 storage ( initial 6M ) ;
- Table created.
- SQL> insert into t05318_c values (1) ;
- insert into t05318_c values (1)
- *
- ERROR at line 1:
- ORA-30032: the suspended (resumable) statement has timed out
- ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS6
- SQL>
复制代码 ---suspend trigger
- create or replace trigger trg_suspend
- after suspend
- on database
- declare
- v_size number;
- pragma AUTONOMOUS_TRANSACTION;
- begin
- select BYTES into v_size from dba_data_files where FILE_name='+DATA/orcl/datafile/tbs6.276.1022136411';
- execute immediate 'alter database datafile ''+DATA/orcl/datafile/tbs6.276.1022136411'' resize '||to_char(v_size+ 5242880);
- commit;
- end;
复制代码
|
|