OCA-UNIT7-SCHEMA

OCA 第七章主要实验

一： 研究number(p,s)
P为精度，s为小数位数
SQL> conn hr/hr
Connected.
SQL> create table test(a number(5,2));
Table created.
SQL> insert into test values(100.00);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
100
1
SQL> insert into test values(1000);
insert into test values(1000)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into test values(10.0);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
100
1
10
SQL> insert into test values(10.000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
100
1
10
10
SQL> insert into test values(1.000000);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test values(1000.00);
insert into test values(1000.00)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
总结： 假设此时number(5,2)，意思是当你的整数+小数位达到5位时，你的小数位数必须至少要有两位。当你的整数+小数位小于5位时，小数位数可以任意长度。当然，整数+小数位数大于5位是不允许的。
二： 数据完整性
（1）
NOT NULL
SQL> conn hr/hr
Connected.
SQL> create table test1a(a number not null);
Table created.
SQL> desc test1a
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
select * from dba_constraints c where c.table_name='TEST1A';
OWNER CONSTRAINT_NAME CON TABLE_NAME
HR SYS_C005425 C TEST1A "A" IS NOT NULL
SQL> insert into test1a values('');
insert into test1a values('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST1A"."A")
SQL> insert into test1a values(null);
insert into test1a values(null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST1A"."A")
（2）
UNIQUE
SQL> create table test1b(a number unique);
Table created.
SQL> desc test1b;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
select * from dba_constraints c where c.table_name='TEST1B';
OWNER CONSTRAINT_NAME CON TABLE_NAME
HR SYS_C005426 U TEST1B
SQL> insert into test1b values('');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1b;
A
----------
SQL> insert into test1b values(null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1b;
A
----------
SQL> insert into test1b values(null);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test1b;
COUNT(*)
----------
3
SQL> insert into test1b values(1);
1 row created.
SQL> insert into test1b values(1);
insert into test1b values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C005426) violated
总结： 每个空值可以都看作是唯一的。
（3）
PRIMARY KEY
SQL> create table test1c(a number primary key);
Table created.
SQL> desc test1c;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
select * from dba_constraints c where c.table_name='TEST1C';
OWNER CONSTRAINT_NAME CON TABLE_NAME
HR SYS_C005427 P TEST1C
SQL> insert into test1c values('');
insert into test1c values('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST1C"."A")
SQL> insert into test1c values(null);
insert into test1c values(null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST1C"."A")
总结： 主键是非空且唯一，空值自然无法插入。
（四） 主外键
SQL> create table testpri(a number primary key);
Table created.
select * from dba_constraints c where c.table_name='TESTPRI';
OWNER CONSTRAINT_NAME CON TABLE_NAME
HR SYS_C005428 P TESTPRI
SQL> create table testfor(b number,foreign key(b) references testpri(a));
Table created.
select * from dba_constraints c where c.table_name='TESTFOR';
OWNER CONSTRAINT_NAME CON TABLE_NAME
HR SYS_C005429 R TESTFOR
SQL> insert into testfor values(1);
insert into testfor values(1)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.SYS_C005429) violated - parent key not
Found
SQL> insert into testpri values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into testfor values(1);
1 row created.
SQL> commit;
Commit complete.
（五） CHECK
SQL> create table testchk(a number check (a>3));
Table created.
select * from dba_constraints c where c.table_name='TESTCHK';
OWNER CONSTRAINT_NAME CON TABLE_NAME SEARCH_CONDITION
HR SYS_C005430 C TESTCHK a>3
SQL> insert into testchk values(4);
1 row created.
SQL> insert into testchk values(3);
insert into testchk values(3)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005430) violated
三： 违反约束条件
（1）
删除引用完整性约束条件子行的父行
利用TESTPRI和TESTFOR这两张表
SQL> CONN HR/HR
Connected.
SQL> show user
USER is "HR"
SQL> delete from testpri where a=1;
delete from testpri where a=1
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.SYS_C005429) violated - child record found
（2）
更新列值后超出了检查约束条件的范围
利用TESTCHK做实验
SQL> select * from testchk;
A
----------
4
SQL> update testchk set a=2;
update testchk set a=2
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005430) violated
四： 约束条件状态
为了更好地处理数据可能暂时违反约束条件的情况，可将约束条件指定为不同的状态。可以启用 (ENABLE) 或禁用 (DISABLE) 完整性约束条件。如果启用约束条件，在数据库中输入或更新数据时就会检查数据。此时，禁止输入不符合约束条件规则的数据。如果禁用约束条件，则可以在数据库中输入不符合规则的数据。
（1）
DISABLE NOVALIDATE
不检查新数据和现有数据，因此这些数据可能不符合约束条件。当数据来自验证过的源，而且表是只读表时，通常会使用此状态。因此，不会将新数据输入表中。（这个你想想看，什么都不检查，还要约束做什么，比较没意义）
SQL> create table test1(a number);
Table created.
SQL> insert into test1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1;
A
----------
1
SQL> insert into test1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1;
A
----------
1
1
SQL> alter table test1 add unique(a) disable novalidate;
Table altered.
SQL> insert into test1 values(1);
1 row created.
SQL> commit;
Commit complete.（看吧，不生效，写跟没写一样）
（2）
DISABLE VALIDATE
如果约束条件处于此状态，则不允许对有约束条件的列进行任何修改。因为如果在验证现有数据后，又允许将未经检查的数据输入表中，就会出现不一致的情况。当必须验证现有数据，但不需要修改数据，而且不需要另外为性能而建立索引时，通常会使用此状态。
SQL> create table test2(a number);
Table created.
SQL> insert into test2 values(1);
1 row created.
SQL> insert into test2 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test2;
A
----------
1
1
SQL> alter table test2 add unique(a) disable validate;
alter table test2 add unique(a) disable validate
*
ERROR at line 1:
ORA-02299: cannot validate (HR.SYS_C005432) - duplicate keys found
SQL> truncate table test2;
Table truncated.
SQL> insert into test2 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test2;
A
----------
1
SQL> alter table test2 add unique(a) disable validate;
Table altered.
SQL> insert into test2 values(1);
insert into test2 values(1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (HR.SYS_C005433)
disabled and validated（额这个功能比较无语 可以说是没有用的一个功能）
（3）
ENABLE NOVALIDATE
新数据符合约束条件，但现有数据处于未知状态。当可以更正现有的约束条件违规情况，同时又不允许将新的违规数据输入系统时，常常会使用此状态。
SQL> create table test3(a number);
Table created.
SQL> insert into test3 values(1);
1 row created.
SQL> insert into test3 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test3;
A
----------
1
1
SQL> alter table test3 add unique(a) enable novalidate;
alter table test3 add unique(a) enable novalidate
*
ERROR at line 1:
ORA-02299: cannot validate (HR.SYS_C005434) - duplicate keys found
（这里我实验做出来就是这样，感觉跟书上说的不一样）
（4）
ENABLE VALIDATE
新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。
SQL> create table test4(a number);
Table created.
SQL> insert into test4 values(1);
1 row created.
SQL> insert into test4 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test4;
A
----------
1
1
SQL> alter table test4 add unique(a) enable validate;
alter table test4 add unique(a) enable validate
*
ERROR at line 1:
ORA-02299: cannot validate (HR.SYS_C005436) - duplicate keys found
SQL> truncate table test4;
Table truncated.
SQL> insert into test4 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table test4 add unique(a) enable validate;
Table altered.
SQL> insert into test4 values(1);
insert into test4 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C005438) violated
SQL> insert into test4 values(2);
1 row created.
SQL> commit;
Commit complete.
五： 检查约束条件的时机
（1）
initially immediate(初始化立即执行)
（2）
initially deffered(初始化延迟执行)
非延迟约束条件又称即时约束条件，是在每个 DML 语句结束时强制执行的。违反约束条件会导致回退语句。如果约束条件导致诸如delete cascade 等操作，则认为该操作是导致执行此操作的语句的一部分。定义为“非延迟”的约束条件不能更改为延迟约束条件。
延迟约束条件是提交事务处理时才检查的约束条件。如果在提交时检测到任何违反约束条件的情况，则会回退整个事务处理。当同时输入外键关系中的父行和子行（如订单录入系统中，这种情况下需要同时录入订单及订单项）时，这些约束条件非常有用。
SQL> create table testdef1(a number(1) constraint check_a check (a>0) deferrable initially immediate,b number(1) constraint check_b check (b>0) deferrable initially deferred);
Table created.
SQL> insert into testdef1 values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into testdef1 values(-1,1);
insert into testdef1 values(-1,1)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHECK_A) violated
SQL> insert into testdef1 values(1,-1);
1 row created.
SQL> select * from testdef1;
A B
---------- ----------
1 1
1 -1（看到没，有了是吧，假象！）
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.CHECK_B) violated
SQL> select * from testdef1;
A B
---------- ----------
1
1（最后被悲剧的回滚了）
SQL> set constraint check_a deferred;
Constraint set.
SQL> set constraint check_a deferred;
Constraint set.
SQL> insert into testdef1 values(-1,-1);
1 row created.
SQL> select * from testdef1;
A B
---------- ----------
1 1
-1 -1（再说一遍，这是假象）
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.CHECK_A) violated
SQL> select * from testdef1;
A B
---------- ----------
1
1
SQL> set constraint check_b immediate;
Constraint set.
SQL> insert into testdef1 values(-1,-1);
insert into testdef1 values(-1,-1)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHECK_B) violated
（我想这个问题，我写得非常清楚了）
再做一个实验，延迟验证在设置为立即验证时，会检验验证条件，但不回滚事务
先恢复到最开始的状态
SQL> set constraint check_a immediate;
Constraint set.
SQL> set constraint check_b deferred;
Constraint set.
SQL> insert into testdef1 values(1,-1);
1 row created.
SQL> insert into testdef1 values(2,2);
1 row created.
SQL> select * from testdef1;
A B
---------- ----------
1 1
1 -1
2 2
SQL> set constraint check_b immediate;
set constraint check_b immediate
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHECK_B) violated
SQL> select * from testdef1;
A B
---------- ----------
1 1
1 -1
2 2
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.CHECK_B) violated
SQL> select * from testdef1;
A B
---------- ----------
1 1（COMMIT才被迫回滚，请注意COMMIT是提交，请不要理解为COMMIT竟然能回滚！）
六： 删除表之CASCADE CONSTRAINTS
利用之前的表TESTPRI与TESTFOR
SQL> select * from testpri;
A
----------
1
2
SQL> select * from testfor;
B
----------
1
2
SQL> create table testp(a number primary key);
Table created.
SQL> create table testf(b number,foreign key(b) references testp(a));
Table created.
SQL> insert into testp values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into testf values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table testp;
drop table testp
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> drop table testp cascade constraints;
Table dropped.
SQL> select * from testp;
select * from testp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from testf;
B
----------
1
这个时候，你查
select * from dba_constraints c where c.table_name='TESTF';
他已经没有任何的约束了，因为都被删了。
七： TRUNCATE DELEE DROP之间的比较
这是一个很有趣的实验
SQL> conn / as sysdba
Connected.
SQL> create table testsource1 as select * from dba_source;
Table created.
SQL> create table testsource2 as select * from dba_source;
Table created.
SQL> set timing on
SQL> truncate table testsource1;
Table truncated.
Elapsed: 00:00:00.27
SQL> delete from testsource2;
292541 rows deleted.
Elapsed: 00:01:24.09（TRUNCATE是如此的快，DELETE是如此的慢，因为DELETE 100万行的数据会产生10万行的日志，但是请不要理解为TRUNCATE就一定比DELETE好，TRUNCATE下去数据就回不来了！因为他日志只记了一条！）
我们再来看看DROP的速度
SQL> create table testsource3 as select * from dba_source;
Table created.
Elapsed: 00:00:17.32
SQL> drop table testsource3;
Table dropped.
Elapsed: 00:00:00.36（它只是将它改名，并放入回收站中）
八： B树索引与 位图索引的比较
select count(*) from dba_source s group by s.type;
COUNT(*)
4820
124476
135509
3173
5650
1400
17513 也就是说类型总共只有7种非常适合建位图索引
SQL> conn / as sysdba
Connected.
SQL> set timing off
SQL> create table testsource as select * from dba_source;
Table created.
SQL> select count(*) from dba_indexes d where d.table_name='TESTSOURCE';
COUNT(*)
----------
0 (OK 没有索引)
首先，建立B树索引
SQL> create index idx_testsource_type on testsource(type);
Index created.
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ index(idx_testsource_type) */ count(*) from testsource where type='PACKAGE';
COUNT(*)
----------
124476
Elapsed: 00:00:00.45
Execution Plan
----------------------------------------------------------
Plan hash value: 3761944022
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 8 | 200 (4
)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 8 |
| |
|* 2 | INDEX FAST FULL SCAN| IDX_TESTSOURCE_TYPE | 164K| 1284K| 200 (4
)| 00:00:03 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='PACKAGE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
977 consistent gets
868 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> drop index idx_testsource_type;
Index dropped.
接下来，我们建立位图索引
SQL> create bitmap index idx_testsource_type on testsource(type);
Index created.
SQL> select /*+ index(idx_testsource_type) */ count(*) from testsource where type='PACKAGE';
COUNT(*)
----------
124476
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 1062179406
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1 | 8 |
5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 |
| |
| 2 | BITMAP CONVERSION COUNT | | 164K| 1284K|
5 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| IDX_TESTSOURCE_TYPE | | |
| |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='PACKAGE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
95 consistent gets
4 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
（看到没，非常明显的区别，位图索引在这里比B树索引的效率高
很多，仅仅是指在本例这样的情况下，具体环境具体分析）
九： 序列SEQUENCE
SQL> conn hr/hr
Connected.
SQL> create sequence seq1 cycle noorder cache 20 maxvalue 100 minvalue 1 increment by 5 start with 10;
Sequence created.
SQL> create table testseq(a number);
Table created.
SQL> insert into testseq values(seq1.nextval);
1 row created.
SQL> insert into testseq values(seq1.nextval);
1 row created.
SQL> commit;
Commit complete.
SQL> select seq1.currval from dual;
CURRVAL
----------
15
模拟掉电（生产环境禁止发这样的命令！）
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 113246864 bytes
Database Buffers 335544320 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> conn hr/hr
Connected.
SQL> select * from testseq;
A
----------
10
15
SQL> insert into testseq values(seq1.nextval);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testseq;
A
----------
10
15
1（看到没，正常的话下一个号应该是20，可是他却从1开始了，这就是你CACHE的代价）
十： 临时表
如果需要为了执行某一任务而存储专用数据，而且要在执行该任务后结束事务处理或会话时清除数据，就可以利用临时表。临时表提供了这种功能，这样您不必在其它会话中隐藏数据，也不必在结束时删除所生成的数据。对于某一会话来说，唯一可见的临时表数据是该会话已经插入的数据。
临时表可专用于事务处理，也可专用于会话。对于专用于事务处理的临时表而言，数据在事务处理期间存在；对于专用于会话的临时表而言，数据在会话期间存在。在这两种情况下，通过会话插入的数据专用于会话。每个会话仅可查看和修改它自己的数据。因此，从不会在临时表的数据上获得 DML 锁。下列子句控制行的生存期：
•
ON COMMIT DELETE ROWS：指定插入行的生存期仅为事务处理的持续时间
•
ON COMMIT PRESERVE ROWS：指定插入行的生存期为会话的持续时间
使用 CREATE GLOBAL TEMPORARY TABLE 语句可创建临时表。在临时表中可以创建索引、视图及触发器，还可以使用“Export and Import（导出和导入）”或“Data Pump（数据泵）”导出和导入临时表的定义。但是，即使使用了 ROWS 选项，也不会导出数据。
（1）
ON COMMIT PRESERVE ROWS
SQL> create global temporary table testtmp(a number) on commit preserve rows;
Table created.
SQL> show user
USER is "SYS"
SQL> desc testtmp;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
SQL> grant select,insert,update on testtmp to hr;
Grant succeeded.
SQL> alter user sh identified by sh account unlock;
User altered.
SQL> grant select,insert,update on testtmp to sh;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> insert into sys.testtmp values(1);
1 row created.
SQL> select * from sys.testtmp;
A
----------
1
SQL> commit;
Commit complete.
SQL> select * from sys.testtmp;
A
----------
1
SQL> conn sh/sh
Connected.
SQL> select * from sys.testtmp;
no rows selected（SH看不到HR的数据）
SQL> insert into sys.testtmp values(2);
1 row created.
SQL> select * from sys.testtmp;
A
----------
2
SQL> commit;
Commit complete.
SQL> select * from sys.testtmp;
A
----------
2
SQL> conn hr/hr
Connected.
SQL> select * from sys.testtmp;
no rows selected
SQL> conn sh/sh
Connected.
SQL> select * from sys.testtmp;
no rows selected（HR之前的数据被SH清掉了）
SQL> conn / as sysdba
Connected.
SQL> select * from sys.testtmp;
no rows selected（就算是SYS也看不到他们的数据）
SQL> conn hr/hr
Connected.
SQL> select * from sys.testtmp;
no rows selected（会话一推出，重新登，数据也就没了）
（2）
ON COMMIT DELETE ROWS
SQL> show user
USER is "SYS"
SQL> create global temporary table testtmp2(a number) on commit delete rows;
Table created.
SQL> grant select,insert,update on testtmp2 to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
SQL> insert into sys.testtmp2 values(1);
1 row created.
SQL> select * from sys.testtmp2;
A
----------
1
SQL> commit;
Commit complete.
SQL> select * from sys.testtmp2;
no rows selected（数据仅存在于事务TRANSACTION阶段，但是请不要理解为COMMIT能回滚，它回滚是因为临时表本身的属性决定的）