您查看的文章来源于http://www.oklinux.cn
NAME COMPATIBILITY DATABASE_COMPATIBILI
--------------- ------------- --------------------
DG1 11.1.0.0.0 11.1.0.0.0
RECOVERYDEST 11.1.0.0.0 11.1.0.0.0
6. 探索 Oracle 数据库 11g
此部分将简要描述 Oracle 数据库 11g 的几个新特性。新特性的详细描述不在本指南的讨论范围之内。
自动内存管理 — 在 Oracle 数据库 11g 中，已经使用动态参数 memory_target 进一步自动化了内存管理。您只需指定总的实例内存大小，数据库将自动管理 SGA 与 PGA 之间的内存分配。视图 v$memory_target_advice 将提供内存调整建议。
间隔分区通过在插入行超出分区范围时自动创建新表分区，提高了分区表的可管理性。
按整数值分区
SQL> create table patients (
2 patientid number not null,name varchar2(10),address varchar2(15)
3 )
4 partition by range (patientid)
5 interval (100)
6 (partition p1 values less than (100))
7 /
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SYS_P81 200
SQL> select count(*) from patients partition (SYS_P81);
COUNT(*)
----------
1
按日期分区
SQL> create table userlogs (
2 transid number,
3 transdt date,
4 terminal varchar2(10)
5 )
6 partition by range (transdt)
7 interval (numtoyminterval(1,'YEAR'))
8 (
9 partition p1 values less than (to_date('01-01-2007','mm-dd-yyyy'))
10 );
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> insert into userlogs values (1,'11-AUG-07','XAV0004');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select count(*) from userlogs partition (sys_p42);
COUNT(*)
----------
1
引用分区根据父表的分区模式对子表进行分区。
SQL> create table patients (
2 patientid number not null,name varchar2(10), address varchar2(15)
3 )
4 partition by range (patientid)
5 (partition p1 values less than (100),
6 partition p2 values less than (200))
7 /
Table created.
SQL> alter table patients
2 add constraint patients_pk primary key (patientid);
Table altered.
SQL> create table invoices (
2 invoiceno number,amount number, patientid number not null,
3 constraint invoices_fk
4 foreign key (patientid) references patients
5 )
6 partition by reference (invoices_fk);
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','INVOICES','VCHAN') from dual;
DBMS_METADATA.GET_DDL('TABLE','INVOICES','VCHAN')
-----------------------------------------------------------------------
CREATE TABLE "VCHAN"."INVOICES"
( "INVOICENO" NUMBER,
"AMOUNT" NUMBER,
"PATIENTID" NUMBER NOT NULL ENABLE,
CONSTRAINT "INVOICES_FK" FOREIGN KEY ("PATIENTID")
REFERENCES "VCHAN"."PATIENTS" ("PATIENTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY REFERENCE ("INVOICES_FK")
(PARTITION "P1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
SQL> insert into patients values (1,'TOBY','88 Palace Ave');
1 row created.
SQL> insert into invoices values (150,262.12,1);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
0
SQL> insert into patients values (110,'GILY','512 HILE STREET');
1 row created.
SQL> insert into invoices values (151,500.01,110);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
1
Oracle 数据库 11g 中的表压缩支持传统的 DML 和删除列操作。压缩数据在读取期间不需要解压缩，这样，因为读取的数据块较少，查询压缩数据的速度将明显加快。
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/db11g/tbs1_01.dbf' size 500M;
Tablespace created.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/db11g/tbs2_01.dbf' size 500M;
Tablespace created.
SQL> create table mytable_compress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs1 compress for all operations;
Table created.
SQL> create table mytable_nocompress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs2;
Table created.
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set timing on
SQL> insert into mytable_nocompress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:8.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> insert into mytable_compress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:41.79
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%';
SEGMENT_NAME EXTENTS
------------------------------ ----------
MYTABLE_COMPRESS 53
MYTABLE_NOCOMPRESS 88
SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%';
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TBS1 461.9375
TBS2 363.9375
SQL> alter table mytable_compress drop column col2;
Table altered.
Elapsed: 00:00:21.04
结论：
正如本指南中所述，每个数据库版本的升级过程都变得更加简单高效。衷心希望本指南为您提供了一个升级 RAC 环境的简明方法。现在，您已经升级到 Oracle 数据库 11g，可以开始尽情使用新一代数据库的功能了。
