Oracle之DBA入门四

Chapter 9 storage structure
Database block contents:header,free space, data

HWM: High-Water Mark

dba_extents
dba_segments
dba_tablespaces
dba_data_files
dba_free_space

---------------
Chapter 10 Managing undo data
// undo segment needs max blocks
select max(undoblks/((end_time-begin_time)*24*3600))
from v$undostat;
// undo segment's average blocks
select sum(undoblks) / sum((
end_time -begin_time)*24*3600) from v$undostat;

========================
Chapter 11 Managing Tables
Regular Table(Hip table):
表最大可以有1000列,但不要超过254列,会影响到效率.
一个表空间可以包含1022个文件.

RowId:快速定位一条记录最快的方法.
Rowid是数据库中一行的惟一标志.


create tablespace assm datafile 'd:/oracle/tt/mssm_1.dbf'
size 100M extent management
local uniform size 128K segment space management manual

create tablespace mssm datafile 'd:/oracle/tt/mssm_1.dbf'
size 100M extent management
local uniform size 128K segment space management manual

create user as1 identified by as1 default tablespace assm;
grant connect ,resource to ms1;

pctfree 相当重要,会控制row migration
initrans:影响并发量

create global temporary table....
临时表有两个级别:session级别,transaction级别

Row Migration & Chaining

查看表的详细信息:
select dbms_metadata.get_ddl('TABLE','T') from dual;

=================
Chapter 12 Managing Index
B-tree index
Bitmap index

select object_name, object_type from user_objects;
drop index my_bit_index;

To Monitor a index
v$object_usage动态性能视图里保存了监控信息
alter index JOB_ID_PK monitoring usage;
alter index JOB_ID_PK nomonitoring usage;
select * from v$object_usage;

===============
Chapter 13 Maintaining Data Integerity
Constraint:
not null
列级约束
alter table t modify id not null;

check
可以在column和table级别定义.
alter table tt add constraint age_ct check(age>0);
check的condition不能使用查询,伪列,系统函数.

unique
单行惟一或多行组合惟一.
会自动创建一个索引
允许有NULL
alter table t add constraint uniquue_id unique(id);
drop index unique_id // 不能drop掉

primary key
unique + null
a table can have only one primary key.
被引用的表相应的字段必须有惟一性或主键约束.
foreign key
alter table dept add primary key (dept_id);

alter table emp add constaint fk_emp1
foreign key(dept_id) references dept(dept_id)
on delete cascade;

约束高级知识点:
Create Disabled Constraint
alter table emp add primary key(emp_id) disable;
如此创建的约束不会立即生效
alter table emp enable primary key;
alter table emp disable primary key;
alter table emp drop constraint xxx;
alter table emp drop primary key cascade;

Constraint States
disable novalidate:不启动不检查
disable VALIDATE:将表变为只读状态.
enable NOVALIDATE:启动约束,不检查表中已存数据.
enable validate:启动还检查表中的数据

推荐启动顺序:disable novalidate->enable novalidate->
enable validate 这样不会将表锁死,提高效率.

alter table t add primary key(id) disable;
// 此时还不会创建索引,user_objects看不到
alter table t enable novalidate primary key;
truncate table t;

Constraint Checking
dml statement -> check nondeferred constraints
-> commit -> check deferred constraints

alter table t drop primary key;
alter table t add primary key(id) deferrable;
alter table t disable primary key;
alter table t disable validate primary key;
set constraints all deferred;
set constraint constraint_name deferred;