Oracle 11g笔记
Oracle 11g笔记
*开头的是我们可以考虑用到的，#是
没有用到新功能，没有符号是我暂时
不明白部分----------------------
2009-3-11 ***
*分区
间隔分区：
特点：自动添加分区（自动分区删除暂时没有做出来）
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values less than (to_date('2009-02-01','yyyy-mm-dd'))
);
--插入数据
insert into sales6 values (1,to_date('2009-04-01','yyyy-mm-dd'));
insert into sales6 values (1,to_date('2009-05-01','yyyy-mm-dd'));
insert into sales6 values (1,to_date('2009-01-07','yyyy-mm-dd'));
insert into sales6 values (2,to_date('2009-09-07','yyyy-mm-dd'));
--查询数据
select * from sales6 partition for (to_date('2009-04-05','yyyy-mm-dd'));
select * from sales6 partition for (to_date('2009-05-07','yyyy-mm-dd'));
select * from sales6 partition for (to_date('2009-01-07','yyyy-mm-dd'));
--自动添加可以
select *
from user_tab_partitions
where table_name = 'SALES6';
虚拟分区：
特点：使用不存在的列进行分区
create table sales5
(
sales_id number,
sales_dt varchar2(14),
sales_dt1 date
generated always as
(
to_date(sales_dt,'yyyymmddhh24miss')
) virtual
)
partition by range (sales_dt1)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);
insert into sales5(sales_id,sales_dt) values (1,'20090401000000');
insert into sales5(sales_id,sales_dt) values (1,'20090501000000');
insert into sales5(sales_id,sales_dt) values (1,'20090701000000');
引用分区：
特点：使用外健分区
create table customers
(
cust_id number primary key,
cust_name varchar2(200),
rating varchar2(1) not null
)
partition by list (rating)
(
partition pA values ('A'),
partition pB values ('B')
);
/*create table sales
(
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01
foreign key (cust_id)
references customers
);*/
create table sales
(
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01
foreign key (cust_id)
references customers
)
partition by reference (fk_sales_01);
系统分区：
特点：自动分区，但是没有发现其优点
create table sales3
(
sales_id number,
product_code number,
state_code number
)
partition by system
(
partition p1 tablespace users,
partition p2 tablespace users
);
select partition_name from user_segments where segment_name = 'SALES3';
insert into sales3 partition (p1) values (1,101,1);
insert into sales3 values (1,101,1);--会有问题的！
总结：
间隔分区和虚拟分区联合使用可以满足个别要求！
*不可见的索引
特点：相当于特殊索引，避免执行计划的影响
create table sales ( sales_id number, cust_id number, sales_amt number, sale_category varchar2(6) generated always as ( case when sales_amt <= 10000 then 'LOW' when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM' when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH' else 'ULTRA' end ) virtual );
insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);select * from sales; create index in_sales_cat on sales (sale_category);
alter index in_sales_cat invisible;
总结：
这个功能在以后可能会很有用的！！！！！！！！！！！！！！！！！！！
*只读表
特点：可以固定一个特定的表
alter table sales read only;
delete sales
alter table sales read write;
select read_only from user_tables where table_name = 'SALES';
可以用来测试环境！·N
*井井有条的序列
特点：序列的使用将会变的更加简单
以前，如果您需要在 PL/SQL 程序中使用序列，则在该版本推出之前，您需要使用 SELECT
declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
现在不再需要这样了。您可以将序列的下一个值直接赋值给一个变量：
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
总结：
可以更加简化代码的开发！
*添加具有默认值的列
特点：Oracle 将从数据字典获取默认值并将其返回给用户尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL：
alter table sales add tax_code varchar2(20) default 'XX' not null;
但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充还原段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。
Oracle 数据库 11g 中有更好的方法吗？
当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL，是吗？
实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和还原开销。真棒！
很不错的选择！！！！！！
总结：
没有测试！
*嵌套表的新功能
这个没有做测试，可以直接对嵌套表进行比较处理！
*透明数据加密
特点：可以透明个别数据
11G测试没有通过
万能密钥存储在数据库外一个称为“钱夹”的地方 — 默认位置在 $ORACLE_BASE/admin/$ORACLE_SID/wallet。在概念上，它类似于下图。
在配置 TDE 之后 — 或者更明确地说是配置了钱夹和万能密钥之后 — 您可以使用它来保护数据值。要为表的一列加密，需要使用以下 SQL
alter system set encryption key authenticated BY "topSecret"; 运行老是出问题！ create table accounts
(
acc_no number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
SSN varchar2(9) ENCRYPT USING 'AES128',
acc_type varchar2(1) not null,
folio_id number ENCRYPT USING 'AES128',
sub_acc_type varchar2(30),
acc_open_dt date not null,
acc_mod_dt date,
acc_mgr_id number
)
在这里，您在列 SSN 和 FOLIO_ID 上使用了 TDE，它们现在以加密方式存储在表本身。但是，当用户从表中选择时，她看到以明文表示的数据，因为在检索过程中已经完成了解密。如果磁盘被盗，则包含在表段中的信息仍然保持加密状态。盗窃者需要表密钥才能看到加密的值，但是要获得表密钥，他需要万能密钥，而万能密钥存储在外部，因此无法获得。
注意列 SSN 和 FOLIO_ID 后面的子句，这些子句指定 ENCRYPT 使用 128 位高级加密标准。
这个功能可能会用到！
总结：
这个功能没有做出来！
#PL/SQL 中的条件编译：一次编写，多次执行
特点：???
这个我们暂时用不到！！
你们中很多人曾经使用过 C 语言，它支持编译器指令的概念。在 C 程序中，根据相关编译器的版本，特定变量的值有可能不同。
在 Oracle 数据库 10g 第 2 版中，PL/SQL 有一个类似的特性：现在可以提供预处理器指令，它们在编译期间而不是在运行时进行求值。例如，让我们创建一个非常简单的返回字符串的函数。
create or replace function myfunc
return varchar2
as
begin
$if $$ppval $then
return 'PPVAL was TRUE';
$else
return 'PPVAL was FALSE';
$end
end;
注意第 5 行，您已经使用预处理器指令为变量 ppval 求值。因为 ppval 是一个预处理器变量，而不是常规的 PL/SQL 变量，所以使用 $$ 标志来指定它。同样，为了编译器能分辨自己只需在编译期间处理这些行，你要用特殊的 $ 标志来指定求值项，例如用 $if 代替 if。现在，利用变量 ppval 的不同值来编译这个函数。
SQL> alter session set plsql_ccflags = 'PPVAL:TRUE';
Session altered.
现在编译该函数并执行它。
SQL> alter function myfunc compile;
Function altered.
SQL> select myfunc from dual;
MYFUNC
-------------------------------------
PPVAL was TRUE
在编译期间 ppval 的值被设为 false。现在更改该变量的值并重新执行该函数。
SQL> alter session set plsql_ccflags = 'PPVAL:FALSE';
Session altered.
SQL> select myfunc from dual;
MYFUNC
---------------------------------------------------------
PPVAL was TRUE
虽然这里 ppval 的值在会话中是 FALSE，但函数没有采用它；而是采用了在编译期间所设置的值。现在，重新编译该函数并执行它。
SQL> alter function myfunc compile;
Function altered.
SQL> select myfunc from dual;
MYFUNC
---------------------------------------------------
PPVAL was FALSE
在编译期间，ppval 的值是 FALSE，而这就是所返回的值。
#自适应游标：
因此，如您所见，尽管绑定变量在大多数情况下都非常有效，当值的选择性将显著影响计划时（正如在此示例中，值 'CT' 和 'NY' 的选择性分别为 5％ 和 95％），绑定变量并不可靠。如果数据分布均匀，所有值的选择性几乎相同，执行计划将保持不便。因此，聪明的 SQL 编码人员将会选择在何时打破使用绑定变量的基本准则，改用文字值。
select is_bind_sensitive, is_bind_aware, sql_id, child_number
from v$sql
where sql_text = 'select count(1) from customers where state_code = :state_code and times_purchased > 3'
I I SQL_ID CHILD_NUMBER
- - ------------- ------------
Y Y 7cv5271zx2ttg 0
Y N 7cv5271zx2ttg 1
让我们看一看这些列的含义。Oracle 对游标进行观察，并确定值变化的方式。如果不同的值可能会改变计划，则游标标记为 "Bind-Sensitive"，IS_BIND_SENSITIVE 列显示 "Y"。在几次执行后，数据库对游标和值有了更多了解，并确定游标是否应根据值的变化来改变计划。如果情况如此，则游标被称为 "Bind-Aware"，IS_BIND_AWARE 列显示 "Y"。总结：Bind-Sensitive 游标是可能会更改计划的游标，而 Bind-Aware 游标是实际更改计划的游标。
由于自适应游标共享特性根据绑定变量的值使用正确的计划，数据库必须在某处存储这些信息。它通过另一个新视图 V$SQL_CS_SELECTIVITY 显示这些信息，该视图显示传递给绑定变量的不同值的选择性。
select * from v$sql_cs_selectivity
视图显示了大量信息。PREDICATE 列显示了用户使用的各种谓词（WHERE 条件）。LOW 和 HIGH 值显示传递的值的范围。
最后，第三个新视图 V$SQL_CS_STATISTICS 显示了标记为 Bind-Aware 或 Bind-Sensitive 的游标执行的操作。
#复合触发器
我们的系统用不到
create or replace trigger tr_bookings_track
for update of booking_dt on bookings
compound trigger
type ty_bookings_hist is table of bookings_hist%rowtype index by pls_integer;
coll_bookings_hist ty_bookings_hist;
ctr pls_integer := 0;
--------------------------------------------------------------
before statement is
begin
dbms_output.put_line('In before statement');
end before statement;
----------------------------------------------------------------
before each row is
begin
dbms_output.put_line('In before each row');
end before each row;
-------------------------------------------------------------------
after each row is
begin
ctr := ctr + 1;
dbms_output.put_line('In after each row. booking_id=' || :new.booking_id);
coll_bookings_hist(ctr).booking_id := :new.booking_id;
coll_bookings_hist(ctr).mod_dt := sysdate;
coll_bookings_hist(ctr).mod_user := user;
coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
end after each row;
--------------------------------------------------------------------
after statement is
begin
dbms_output.put_line('In after statement');
forall counter in 1 .. coll_bookings_hist.count()
insert into bookings_hist values coll_bookings_hist(counter);
end after statement;
--------------------------------------------------------------------
end tr_bookings_track;
/
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9879835/viewspace-1020731/ ，如需转载，请注明出处，否则将追究法律责任。
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
违法和不良信息举报电话:18101376593