1.DML操作及名称空间
模式与对象名称空间的关系
模式(schema)是一种逻辑结构，它对应于用户，每建一个用户就有一套模式与之对应。
我们通常说对象的唯一标识符是前缀为模式名加上对象名称，如scott.emp
同一模式下的对象是不可以重名的。比如在scott模式里，表emp是唯一的，不能还有另外对象叫emp。但不同的模式下可以重名。
create table emp1 as select * from emp;
create view emp1 as select 1 as A from dual;
create unique index emp1 on emp1(empno);
alter table emp1 add constraint emp1 unique(empno);
名称空间定义了一组对象类型，同一个名称空间里的不同对象不能同名，而不同的名称空间中的不同对象可以共享相同的名称。
表，视图，序列，同义词是不同类型的对象，但他们属于同一名称空间，因此在同一模式下也是不可以重名的。
索引，约束有自己的名称空间，所以在同一模式下，可以由表A,索引A和约束A共存。
使用子查询创建表
create table as subquery
create table emp1 as select * from emp;
表emp的索引不会被复制给表emp1
表emp中的约束只有not null约束能够带到表emp1里来
表emp中的default也不会被复制到emp1
改变表结构的DDL操作
drop和rename语法上要加column关键字。如drop列建议先使用set unused 然后drop unused column
当想要add一列，并约束该列为not null时，如果该表已经有数据了，则会报错。
alter table emp1 add c number not null
alter table emp1 add c number default 0 not null
alter table emp1 set unused column c;
select * from emp1
alter table emp1 drop unused column;
如果set unused 某列，该列上有索引，约束，并定义了视图，引用过序列，结果如何？
索引和约束自动删除，序列无关，视图保留定义。
create table test(id int ,name varchar(10));
create index idx_test_id on test(id);
alter table test add constraint unq_test_id unique(id);
create sequence seq_test start with 1 increment by 1;
create view vi_test as select id from test;
insert into test values(seq_test.nextval,'zzl');
commit;
select * from user_indexes where table_name ='TEST';
select * from user_constraints where table_name ='TEST';
select * from user_sequences ;
select * from user_views;
alter table test drop column id;
select * from user_indexes where table_name ='TEST';
select * from user_constraints where table_name ='TEST';
select * from user_sequences ;
select * from user_views;
select * from vi_test
group by 产生统计报告
group by rollup
group by cube
grouping
grouping_id
grouping set
select job,deptno,sal from emp1;
select job,deptno,sum(sal) from emp1 group by (job,deptno);
select job,deptno,sum(sal) from emp1 group by rollup(job,deptno);
select job,deptno,sum(sal) from emp1 group by cube(job,deptno);
grouping函数可以接受一列，返回0或1，如果列为空，那么grouping返回1，如果列值非空，那么返回0，grouping只能在使用rollup或cube的查询中使用
select job,deptno,sum(sal),grouping(job),grouping(deptno), grouping_id(job,deptno) from emp group by cube(job,deptno);
select deptno,job,mgr, avg(sal) from emp
group by grouping sets((deptno,job),(job,mgr))
select deptno,job,null, avg(sal) from emp
group by deptno,job
union all
select null,job,mgr,avg(sal) from emp
group by job,mgr
oracle分层查询
oracle是一个关系数据库管理系统，它用表的形式组织数据，在某些表中的数据还呈现出树形结构的联系，
例如，emp表，empno和mgr两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种
关系就是一种树结构。
遍历有两个方向
top-down
down_top
select ...
connect by {prior 列名1=列名2|列名1 = prior 列名2}
[start with];
select level,empno,ename,mgr from emp
connect by prior empno = mgr
start with empno =7839 order by level;
select level,empno,ename,mgr from emp
connect by empno = prior mgr
start with empno =7369 order by level;
select lpad(level,level*3,' '),empno,ename,mgr
from emp
connect by prior empno = mgr
start with ename ='KING' order by level
节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用where子句来限定
树形结构中的单个节点，以去掉树中的单个节点，但他却不影响后代节点（top_down）或前辈节点(down_top)
select lpad(level,level*3,' '),empno,ename,mgr
from emp
where ename <>'SCOTT'
connect by prior empno = mgr
start with ename ='KING' order by level
select lpad(level,level*3,' '),empno,ename,mgr
from emp
connect by prior empno = mgr
and ename <> 'SCOTT'
start with ename ='KING' order by level
2.oracle的事务和锁
事务的ACID属性
原子性(atomicity):事务是一个完整的操作，事务的各步操作是不可分的；要么都执行，要么都不执行。
一致性（consistency）：一个查询的结构必须与数据库在查询开始的状态一致（读不等待写，写不等待读）
隔离性（isolation）:对于其他会话来说，未完成的（也就是未提交的）事务必须不可见
持久性（durability）:事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。
事务的开始和结束
事务采用隐形的方式，起始于session的第一条dml语句
事务结束于：
commit或rollback
ddl语句被执行（提交）
dcl语句被执行（提交）
用户退出sqlplus（正常退出是提交，非正常退出是回滚）
机器故障或系统崩溃(回滚)
shutdown immediate(回滚)
oracle的事务保存点功能
savepoint 命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，
即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的dml语句，并使事务本身继续执行，
也就是说回滚到保存点这个动作并不使事务结束。
insert into test values(1,'a');
savepoint A;
insert into test values(2,'b');
savepoint B;
insert into test values(3,'c');
savepoint C;
rollback to A;
commit;
scn的概念
scn：system change number
它是一个不断增长的整数，相当于oracle内部的一个时钟，只要数据库一有变更，这个scn就会+1
oracle通过scn记录数据库里事务的一致性。scn涉及了实例恢复和介质恢复的核心概念，它几乎无处不在，
控制文件，数据文件，日志文件都有scn。包括block上也有scn。
select current_scn from v$database;
select dbms_flashback.get_system_change_number from dual;
共享锁与排他锁的基本原理
排他锁，排斥其他排他锁和共享锁
共享锁，排斥其他排他锁，但不排斥其他共享锁
oracle数据库锁分类
dml锁（data locks 数据锁）用于保护数据的完整性
ddl锁（dictionary locks 字典锁）用于保护数据对象的结构 如表，索引等的结构定义
内部锁和闩（internal locks and latches）保护数据库的内部结构
当一个用户对某表做dml操作时，也会加ddl锁，这样在事务未结束前，可防止另一用户对该表做ddl操作。初始化参数ddl_lock_timeout可以
设定了ddl锁的等待时间。时间过后如果事务仍未结束，则显示资源正忙。
当一个用户对某表做DDL操作时，也会加DML锁，这样可以防止另一个用户对该表做DML操作
oracleDML锁，包括tm和tx两种
tm是面向对象的锁，它表示锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做ddl操作。
tx是面向事务的锁，他表示发起了一个事务，是否有事务产生，这是根据是否使用undo段作为评断标准的。
五种tm表锁的含义
row share 行共享(RS)允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
row exclusive 行排他（RX） 允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
share 共享（S） 不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
share row exclusive(srx) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
exclusive(X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁
sql语句 加锁模式 许可其他用户的加锁模式
selec * from emp1 无 RS,RX,S,SRX,X
insert,update,delete RX RS,RX
select * from emp1 for update RX RS,RX
lock table emp1 in row share mode RS RS,RX,S,SRX
lock table emp1 in row exclusive mode RX RS,RX
lock table emp1 in share mode S RS，S
lock table emp1 in share row exclusive mode SRX RS
lock table emp1 in exclusive mode X 无
加锁模式
自动加锁
做DML操作时，如insert,update,delete 以及select ... for update 由oracle自动完成加锁
scott
select * from emp1 for update
sys
update scott.emp1 set ename ='SCOTT' where empno =7788
scott
select * from emp1 where deptno =10 for update
sys
select * from emp1 for update nowait
select * from scott.emp1 for update wait 5
select * from scott.emp1 for update skip locked
对整个表for udpate是不锁insert语句的
死锁和解锁
plsql
update emp1 set ename ='aaa' where empno =7788;
update emp1 set ename = 'bbb' where empno = 7369;
sqlplus
update emp1 set ename = 'bbb' where empno = 7369;
update emp1 set ename ='aaa' where empno =7788;
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
alter system kill session 'sid,serial#'
3.用户访问控制
创建和管理数据库用户
查看数据库用户
select * from dba_users;
模式 schema
show user
用户缺省表空间
select * from v$tablespace
create user zzl identified by bsoft;
缺省表空间
select * from database_properties
alter database default tablespace tablespace_name
空间配额
配额 (quota)是表空间中为用户的对象使用的空间量
alter user zzl quota 10M on users;
alter user zzl quota
alter user zzl quota 0 on users;
管理概要文件(profile)
作用是对用户访问数据库做一些限制
概要文件（profile）具有两个功能，一个是实施口令限制，另一个是限制会话可以占用的资源
始终要实施口令控制，而对于资源限制，则只有实例参数resoure_limit为true时才会实施，默认是false
系统自动使用概要文件，有一个默认的default profile 限制很松，作用很小
可以使用create profile为用户创建它自己的概要文件，没有说明的参数就从default profile的当前版本中提取
create profile zzl limit failed_login_attempts 2;
alter user zzl1 profile zzl;
select * from dba_users;
zzl1用错误密码尝试登陆3次
drop profile zzl cascade
select * from dba_users;
alter user zzl1 account unlock;
权限的引入
系统安全：用户名和口令，分配给用户的磁盘空间及用户的系统操作，如profile等
数据库安全：对数据库对象的访问及操作
用户具备系统权限才能够访问数据库
具备对象权限才能访问数据库中的对象
系统权限通常由dba授予
典型dba权限
create user
drop user
backup any table
select any table
create any table
典型用户需要的系统权限
create session
create table
create sequence
create view
create procedure
对象权限
对象权限有8种：alter,delete,execute,index,insert,references,select ,udpate
权限的授权
授予系统权限语法
grant sys_privs,[role] to user|role|public [with admin option]
授予对象权限语法
grant object_privs on object to user|role|public [with grant option]
系统权限和对象权限语法格式不同，不能混合使用
grant create table ,select on emp1 to zzl1;
创建和删除角色
create role myrole
drop role myrole
角色的引入：
系统权限太复杂，将系统权限打包成角色，oracle建议通过角色授权权限，目的就是为了简化用户访问管理
权限传递
系统权限级联
create user zzl1 identified by bsoft;
create user zzl2 identified by bsoft;
grant create session to zzl1 with admin option;
--zzl1
grant create session to zzl2;
尝试用zzl2登录
revoke create session from zzl1;
尝试用zzl1和zzl2登录
对象权限级联
grant select on emp1 to zzl1 with grant option;
--zzl1
grant select on emp1 to zzl2;
select * from scott.emp1;
--zzl2
select * from scott.emp1;
--scott
revoke select on emp1 from zzl1;
--zzl1
select * from scott.emp1;
--zzl2
select * from scott.emp1;
与权限有关的数据字典
select * from session_privs;//用户当前会话拥有的系统权限
select * from user_role_privs;//用户被授予的角色
select * from role_sys_privs;//用户当前拥有的角色的系统权限
select * from user_sys_privs;//直接授予用户的系统权限
select * from user_tab_privs;//授予用户的对象权限
select * from role_tab_privs;//授予角色的表的权限
4.逻辑导入导出
传统的导入导出 imp/exp
导出程序exp将数据库中的对象定义和数据备份到一个操作系统二进制文件中
导入程序imp读取二进制导出文件并将对象和数据载入数据库中
传统的导入导出是基于客户端的
exp/imp特点
1.可以按时间保存表结构和数据
2.允许导出指定的表，并重新导入到新的数据库中
3.可以把数据库迁移到另一台异构服务器上
4.在两个不同版本的oracle数据库之间
5.在联机状态下进行备份和恢复
6.可以重新组织表的存储结构，减少链接及磁盘碎片
使用以下三种方法调用导出和导入实用程序
1.交互提示符：以交互的方式提示用户逐个输入参数的值
2.命令行参数：在命令行指定程序的参数和参数值
3.参数文件：允许用户将运行参数和参数值存储在参数文件中，以便重复使用参数。
导出导入数据库对象的四种模式
1.数据库模式：导出和导入整个数据库中的所有对象
2.表空间模式：导出和导入一个或多个指定的表空间中的所有对象
3.用户模式：导出和导入一个用户模式中的所有对象
4.表模式：导出和导入一个或多个指定的表或表分区
导入导出表
exp scott/bsoft file =D:\scott.dmp tables=(emp1,dept1)
drop table emp1 purge;
drop table dept1 purge;
imp scott/bsoft file = D:\scott.dmp
exp sys/bsoft file = D:\scott.dmp tables =(scott.emp1,scott.dept1)
imp scott/bsoft file = D:\scott.dmp
imp 'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott
导入导出用户
exp scott/bsoft file =D:\scott.dmp owner=scott
drop user scott cascade
grant connect,resource to scott identified by bsoft
imp scott/bsoft file =D:\scott.dmp
或者
imp 'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott touser=scott
grant connect,resource to scott identified by bsoft
imp 'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott touser=zzl
导入导出表空间
create tablespace test datafile 'E:\database\test.dbf' size 20 M
create table test (id int) TABLESPACE test
insert into test
select 1 from dual;
commit;
insert into test
select 1 from dual;
commit;
--验证表空间是否可传输
exec dbms_tts.transport_set_check('test', true, true);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
alter tablespace
test
read only
exp '/ as sysdba' tablespaces = test transport_tablespace = y file =e:\database\test.dmp
imp userid ='/ as sysdba' tablespaces=test file =e:\database\test.dmp
transport_tablespace = y
datafiles =e:\database\test.dbf
alter tablespace
test
read write
可传输表空间需要满足几个前提条件
1.原库和目标库字符集要一致，若不一致可以通过转换环境变量NLS_LANG解决
2.字符序有大端和小端之分
select * from v$transportable_platform,如果不一致可以使用rman转换
3.compatible 10.0.0 或更高
4.迁移的表空间要自包含（当前表空间中的对象不依赖于该表空间之外的对象）
test表空间中有表T1，T1上的索引T1_indx在users表空间上，索引依赖于表
test是自包含的，导出丢弃T1_indx.
users不是自包含的，无法导出
检查表空间是否自包含
execute ddms_tts.transport_set_check('USERS');
select * from transport_set_violations;
导出整个数据库的对象
exp 'sys/bsoft as sysdba' file = D:\scott.dmp full=y
数据泵
数据泵优点
1.改进性能（较传统的exp/imp速度提高1到2个数量级）
2.重启作业能力
3.并行执行能力
4.关联运行作业能力
5.估算空间需求能力
6.操作网络方式
数据泵组成
核心部分程序包：DBMS_DATAPUMP
提供元数据的程序包:DBMS_MATADATA
命令行客户机：expdp，impdp
数据泵文件
转储文件：此文件包含对象数据
日志文件：记录操作信息和结果
SQL文件：将导入作业中的DDL语句写入sqlfile指定的参数文件中
数据泵的目录及文件位置
以sys或system完成数据泵的导入导出时，可以使用缺省的目录DATA_PUMP_DIR
select * from dba_directories
若果设置了环境变量ORACLE_BASE,则缺省目录位置是
$oracle_base/admin/database_name/dpdump
否则是
$oracle_home/admin/database_name/dpdump
数据泵的两种操作方式
使用expdp和impdp
create directory dir_dp as 'E:\database';
Grant read,write on directory dir_dp to scott;
expdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp tables=(emp,dept)
impdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp
导出数据但不导出结构
expdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp tables=(emp,dept) content = data_only reuse_dumpfiles=y
impdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp
导出scott用户
expdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp schemas=scott
//与exp的区别owner换成了schemas
impdp scott/bsoft directory =
dir_dp
dumpfile = scott.dmp
使用oem操作数据泵
