第 1 页 共 34 页
ORACLE
一. DDL(数据定义语言)
Oracle自动在语句执行前后都加上commit, 则DDL之前的没提交改变被隐式提交, 不能回滚.
数据类型
1. 合法表示符（标示符）
1-30个字符,以字符(可以# $ _ )开始,数据库链接可以包含(@ , )两个特殊符号. 不能包含引号,不能
是保留字.
特殊情况: 数据库名称最多8个字符, 数据库链接最多128个字符, 把列名放入引号可以使用保留字
作为列名.
ORACLE数据库的端口默认是1521
2. NUMBER
<column_name> Number(precision[,scale])
precision: 有效位数总数, 1---38 , 包括小数位
scale: 小数位数,没有小数可以省略小数和小数点; 省略此参数表示一个整数;当 scale> precision,则
会在有效位之前加0
3. CHAR
<column_name> char(size)
size: 1---2000字节, 值不足size时,会自动在值后面补充空格.
<column_name> char(size [CHAR|BYTE]) 指出基于字符或字节,9i以上版本
考虑数据库参数 NLS_LENGTH_SEMATICS
(show parameters nls_length_semantics 可以看当前设置).
4. NCHAR
与char差别在于它规定的是最大字符个数.支持Unicode可变长度的字符集. 最大也是2000字节容量.
5. VarChar2
与char差别仅仅是:不会自动在值后面补充空格.最大4000字节,默认基于字节.
6. NVarChar2
存储可变长度且基于字符数量的数据.
7. RAW
与VarChar2类似,用于可变长度的二进制数据,但最大只有2000字节.
8. LOB
大对象,包括BLOB(二进制字节) CLOB(字符) NCLOB(国家字符) BFILE(二进制文件), 最大可到
4GB的数据. 支持分段访问, 支持Oracle对象, 还可以用PL/SQL的DBMS_LOB包进行处理.
9. Date 精确到秒的时间. 通常被误认为只是到天. 9i之前唯一存在的时间类型.
Date ‘yyyy-mm-dd’ 生成一个日期, date ‘2006-08-15’ . 这里没有小时,分,秒, 也必须yyyy-mm-dd的
格式.
To_date(日期值, 格式串) 生成一个日期, 格式串: yyyy mm dd 分别表示年月日.
使用DD- MON-YYYY, MON-DD-YYYY YYYY-MON-DD-YYYY表示时都应该基于其安装的系统,
中文Oracle需要使用’月’标识月字段.
show parameters nls_date_format(查看默认日期格式),
9i默认的格式: DD-Mon-RR
: DD-Mon-RR
及以下: DD-Mon-YY
如果在windows系统中设置了了注册表中的NLS_LANG, 则所有的NLS_*参数都会被忽略. 这时或
者根本没有设置nls_date_format参数, 使用不加格式串的to_date函数就会失败.
--- insert into Test(event,"data")
--- values('Table Made',TO_DATE('Dec-2-2004','MON-DD-YYYY') ); //Dec无效 ! 中文Oracle需要
使用’月’标识月字段.
insert into Test(event,"data")
values('Table Made',SYSDATE ) ; //SYSDATE生成系统日期
insert into test2(event,"Date")
values('Table Made',TO_DATE('01-12月-04','DD-MON-YY') ) ;
insert into test2(event,"Date")
values ('Insert first value', to_date('12月-21–2004 ', ' MON-DD - YYYY '));
insert into test2(event,"Date")
values ('Insert first value', to_date('2004-12月-31','YYYY-MON -DD') );
insert into test2(event,"Date")
values('Table Made',TO_DATE('01-12月-04','DD-MON-YYYY'));
insert into test2(event,"Date")
values('Table Made',TO_DATE('01-12月-03'))
// 日-月-年 月-日-年 年-月-日 三种顺序有效 !!;
SQL> select to_char("Date",'DD-MON-YY HH12:MI:SS') "Date" from test2;
Date
-------------------
31-12月-04 03:52:39
31-12月-04 12:00:00
21-12月-04 12:00:00
10. TIMESTAMP 对秒提供最多9位小数精度的时间. 9i new!
Sysdate 可以直接产生一个timestamp的.
<column_name> Timestamp [seconds_precision] //精度指出秒的小数位数,默认为6,可选0-9.
11. TIMESTAMP WITH TIME ZONE
<col> Timestamp [seconds_precision] WITH Time Zone
timestamp ‘2004-12-31 15:00:000000 GMT’
timestamp ‘2006-08-14 17:41:000000 –5:00’
12. TIMESTAMP WITH LOCAL TIME ZONE
插入的数据在查询时将考虑时区转换到当前时区.
alter session set time_zone=’-05:00’ //设置时区, 执行查询时将依据设置的时区返回相应的结果.
插入:
timestamp ‘2004-12-31 20:00:000000 GMT’
如果当地时区为-05:00,则查询结果为
31-Dec-04
改时区为’GMT’ 再次查询,结果为:
31-Dec-04
-- Create table
create table TIMECOMP
(
EVENT VARCHAR2(100),
NORMALC DATE,
TIMESTAMPC TIMESTAMP(6),
TIMESTAMPZC TIMESTAMP(6) WITH TIME ZONE,
TIMESTAMPLOCALZC TIMESTAMP(6) WITH LOCAL TIME ZONE
)
insert into timecomp (event,normalc,timestampc,timestampzc,timestamplocalzc)
values('Test 各个日期的区别',sysdate, sysdate,
timestamp '2006-08-14 18:14: -5:00',
timestamp '2006-08-14 18:14: EST')
select * from timecomp;
EVENT Test 各个日期的区别
NORMALC 2006-8-14 18:36:25
TIMESTAMPC 14-8月 -06 下午
TIMESTAMPZC 14-8月 -06 下午 -05:00
TIMESTAMPLOCALZC 15-8月 -06 上午
alter session set time_zone = '-5:00'
select * from timecomp;
EVENT Test 各个日期的区别
NORMALC 2006-8-14 18:36:25
TIMESTAMPC 14-8月 -06 下午
TIMESTAMPZC 14-8月 -06 下午 -05:00
TIMESTAMPLOCALZC 14-8月 -06 下午
alter session set time_zone = '-8:00'
select * from timecomp;
EVENT Test 各个日期的区别
NORMALC 2006-8-14 18:36:25
TIMESTAMPC 14-8月 -06 下午
TIMESTAMPZC 14-8月 -06 下午 -05:00
TIMESTAMPLOCALZC 14-8月 -06 下午
13. INTERVAL YEAR TO MONTH
INTERVAL YEAR [(YEAR_PRECISION)] TO MONTH
//精度默认2,可选0—9
14. INTERVAL DAY TO SECOND
INTERVAL DAY[(DAY_PRCISION)] TO SECOND [(SECOND_ PRECISION)]
//可选0-9, 日精度默认2, 秒精度默认6 .
create table INTERVAL
(
NAME VARCHAR2(20),
EVENT VARCHAR2(100),
BEGIN TIMESTAMP(6) WITH TIME ZONE,
END TIMESTAMP(6) WITH TIME ZONE,
BREAK INTERVAL DAY(4) TO SECOND(2)
)
insert into interval(name,event,begin,end)
values ('AiSee','²âÊÔintervalÊý¾ÝÀàÐÍ', timestamp '2006-08-14 19:12: -5:00' ,
timestamp '2006-08-14 19:13: -6:00' )
select * from interval
AiSee 测试interval数据类型 14-8月 -06 下午 -05:00
14-8月 -06 下午 -06:00
insert into interval select name, event,begin,end,end-begin from interval
select * from interval
AiSee 测试interval数据类型 14-8月 -06 下午 -05:00 14-8月 -06
下午 -06:00
AiSee 测试interval数据类型 14-8月 -06 下午 -05:00 14-8月 -06
下午 -06:00 +0000 01:01:
15. ANSI 数据类型
ORACLE支持工业标准的ANSI数据类型, 以及IBM DB/2 和 SQL/DS数据类型. 出于某种原因选择
它们比较合适:
一些数据库设计工具可以导出能被其他数据库使用的ANSI兼容的SQL.
在其他数据库上使用的SQL教本不需要转换称为ORACLE语法就可以在ORACLE使用,
当使用ANSI, IBM BD/2 , SQL/DS 数据类型建立列的时候, 会被隐式转换位合适的
ORACLE内部类型.
create table ANSITYPE2
(
INTEGER2NUMBER INTEGER,
DEC2NUMBER dec(9,2),
CHARACTER2VARCHAR2 character varying(30),
NATIONALCHAR2NCHAR National CHAR(3)
)
SQL> desc ANSITYPE ;
Name Type Nullable Default Comments
------------------ ------------ -------- ------- --------
INTEGER2NUMBER INTEGER Y
DEC2NUMBER NUMBER(9,2) Y
CHARACTER2VARCHAR2 VARCHAR2(30) Y
NATIONALCHAR2NCHAR NCHAR(3) Y
等价于:
-- Create table
create table ANSITYPE
(
INTEGER2NUMBER INTEGER,
DEC2NUMBER NUMBER(9,2),
CHARACTER2VARCHAR2 VARCHAR2(30),
NATIONALCHAR2NCHAR NCHAR(3)
)
可用SQL数据类型 Oracle等价数据类型
NUMERIC(PRECISION,SCALE) NUMBER(PRECISION,SCALE)
DECIMAL(PRECISION,SCALE) NUMBER(PRECISION,SCALE)
INTEGER NUMBER(38)
INT NUMBER(38)
SMALLINT NUMBER(38)
FLOAT(b) NUMBER
DOUBLEPRECISION NUMBER
REAL NUMBER
CHARACTER(size) CHAR(size)
CHAR(size) CHAR(size)
CHARACTER VARYING(size) VARCHAR(size)
CHAR VARING(size) VARCHAR(size)
NATIONAL CHARACTER(size) NCHAR(size)
NATIONAL CHAR(size) NCHAR(size)
NCHAR(size) NCHAR(size)
NATIONAL CHARACTER
VARYING(size)
NVARCHAR2(size)
NATIONAL CHAR VARYING(size) NVARCHAR2(size)
NCHAR VARYING(size) NVARCHAR2(size)
二. SELECT
Select增值服务
16. 有哪些表,试图呢 ?
select table_name from dba_tables;
17. 当前用户是谁 ?
select user from user_tables group by user ;
show user ; -- The same as above paragraphs
18. 当前用户有哪些表 :
SQL> select table_name from user_tables ;
19. 选择某一用户的表名
select table_name from dba_tables where owner = 'SYSTEM' ;
20. 想知道有多少个用户吗 ?
select owner from dba_tables group by owner ;
21. 想知道表EMP是谁的吗 ?
select owner from dba_tables where table_name = 'EMP' ;
22. 索引增加的描述
SELECT NAME ,HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS, PCT_USED
FROM INDEX_STATS
查询: 表名,索引高度,叶子结点数,行数,标记为删除的行数,索引结点占空间的百分比例(有自由空间
时一定小于100)
由于针对索引表,前期需要:
SQL> create index index_objname_indexTest on indexTest(object_name) pctfree 10;
索引已创建。
SQL> analyze index index_objname_indexTest validate structure ;
索引已分析
SQL> analyze indexTest index_objname_indexTest compute statistics [ for owner ] ;
索引已分析
查询过滤
23. where 限定语句
select <column> from <data_source>
where <condition_expression>
a=b a>b a<b a != b a<>b [ NULL 不等于NULL ]
BETWEEN a AND b
a LIKE b [ 表达式b 使用通配符 %表示0,1,多个字符 _ 代表一个字符 ]
a NOT LIKE <条件>
24. and / or :
在where中实现连接多个子句,形成复合条件,
注意 AND 的优先级高于 OR
注意 可以用（括号）提高优先级
25. DISTINCT 不想要重复的信息 ?
select distinct owner from DBA_TABLES ; // 重复的owner只显示一条
distinct 只支持一个字段的查询，一个SQl语句中只能用一次（注意：通配符 * 会被认为是一个
字段）
26. Like / Not Like
select * from insert_Select where word not like '_ct%';
_代表单字符（必占用）, %代表多个字符（可占可不占）
27. 通配符和转义字符
_ 代表一个任意字符,
% 代表任意数量的任意字符
ESCAPE: 定义一个转义字符, 如 ESCAPE ‘\’ 定义\ 为转义字符, 所谓转义字符指紧跟在它后
面的特殊字符( 上面的 _ % ) 表示普通的字符.
select * from TMP where name not like '\_%' ESCAPE '\' ;
查询name第一个字母不是 _ 的所有记录
28. IN /NOT IN 运算符
select * from insert_Select where word in( 'act','active');
29. Any /ALL
select * from insert_Select where word> All (select word from enstudy where word Like 'a%') ;
select * from insert_Select where word< Any (select word from enstudy where word Like 'a%') ;
30. EXISTS / NOT EXISTS 存在性条件
select * from insert_Select
where word > All (select word from enstudy where word Like 'a%')
and exists(
select * from insert_Select in_S, enstudy ensd where =) ;
31. BETWEEN运算符
select * from insert_Select where word between 'act' and 'zz';
小的值应该放在前面部分.
32. 连接运算符: || 重新组合/命名结果
select WORD || '[' || pronounce || ']' from insert_Select;
//SQL Server, MS Access 使用 + 作连接符
33. order by [desc]排序 可接多个列或表达式+
order by salary desc 按salary降序排列返回值
order by salary, id desc 首先按salary升序排列,再按id降序排列
order by 1 ; 表示按第一列排序
select * from tmp order by 1,2,3 desc 按第一第二升序,第三列降序排列
注意: 参与排序的列不一定会出现在select子句中, 也即是不一定出现在结果中.NULL出现在排序的
最前面.
34. group by 和 having
此时select字句中的每个列都是group by字句的列或列函数.
group by <column_name> 把返回结果按指定列名分组, 同一组只显示一项
having 在使用了group by <column_name>后成为可选, 后面跟一个条件. 与where不同, where是在数
据分组前进行挑选,having是在查询结果中分组之后进行再选.
distinct 只支持一个字段的查询，一个SQl语句中只能用一次（注意：通配符 * 会被认为是一个
字段）
group by 支持多字段的查询，
select event, avg(salary), max(salary) from test2
where event is not NULL
group by avg(salary) having event is not NULL
order by max(salary);
35. 集合函数
SUM 求和
MAX 最大
MIN 最小
AVG 平均数
COUNT 个数
36. 表别名（不可以加as）, 列别名（可以加as）
select _dm,cxdx_mc,zbmc,_dm,
from _zhcx_cxdx cxdx inner join _zhcx_tjzd tjzd
on _dm = _dm
where _dm between 13 and 13
/
select cxdx_dm “Dm”,cxdx_mc AS Mc, zbmc,cxzb_dm,tj
from _zhcx_cxdx natural join _zhcx_tjzd
where cxdx_dm between 13 and 13; //as 关键字可选
对于在不同表中有相同名称的列才会强制加上表别名.
把别名用双引号引用可以避免返回结果用完全大写的列名.（强调为小写）
ANSI 联接, 直接表关联
37. 自然联接 natural join:
自动将两个表相同名称的列联接,用户不需要命名原来联接的列, 这里不允许使用别名.
这将会强制设计者将要联接的列使用相同的名称,而且不能让其他的列有相同的名称,否则oracle将
一起联接他们.
R1(A, B) R2(A, C) R3(A, D) 用sql语句把这三个表连接成一个模式为(A, B, C, D)的表
SELECT AS A, AS B, AS C, AS D FROM R1 INNER JOIN R2 ON =
INNER JOIN R3 ON =
38. 内联接 inner join
select … form t1 inner join t2 on <expression> …用户规定了要联接的各个表的列. 将只返回满足
条件的行,忽略条件一方为NULL的行.
select * from student st inner join score sc on =
//内联接：两个表存在主外键关系时常用内联接查询。
39. 外联接
select * from student st left outer join score sc on =
//左外联接：结果包括左表的所有行，如果左表的行在右表中没有匹配的行，则为空值
select * from student st right outer join score sc on =
//右外联接：结果包括右表的所有行，如果右表的行在左表中没有匹配的行，则为空值
select * from student st full join score sc on =
//完全外联接：返回左表和右表中的所有行
left outer join / right outer join/full outer join
/ /返回内联接的结果以及存在左/右表中, 而另一个表没有相应行的所有行.(同内联接有on… 条件
子句.)
Left时,尽可能多返回第一个表的行,right时尽可能返回第二个表的行.
完全外联接 在where子句后面用带括号的加号(+)表示. 无join 和 on子句.
select _dm,cxdx_mc,zbmc,_dm,
from _zhcx_cxdx cxdx , _zhcx_tjzd tjzd
where _dm = _dm (+)
and _dm between 10 and 12
一种说法是+ 号的另一侧的列可以为空(P37,Oracle9i JDBC 编程).
Conn IC/IC ;
select NVL(,99099) AS ID1,NVL(,'I am NULL') AS name1, ,
NVL(,'I am NULL') AS name2, NVL(,99900) ID2 ,
from temp t2 , tmp t1
where (+) = ;
这里认为 可以为空. 但结果包含了部分为空的记录.
下面的更加直接.
select NVL(,99099) AS ID1,NVL(,'I am NULL') AS name1, ,
NVL(,'I am NULL') AS name2, NVL(,99900) ID2 ,
from temp t2 , tmp t1
where =
OR is null and is NOT NULL
注意 AND 的优先级高于 OR
自联接 某些表可以拥有自引用外键,一个列是其所在表的主键的一个外键.这时需要使用表别名消
除模糊:
select _name “Employee”
_name “Report To”
from employees e1 left outer join employees e2
on _id = _id
order by _id
40. 不想用联接 ?
直接查询两个表
SQL> select _DM,_MC,_DM
from _ZHCX_CXZB a, _zhcx_tjzd b
where CXZB_LX='2' and _DM = _DM ;
// 把where字句的=改为*= 或者=* 就表示左/右外联接
集合操作
不允许使用order, 不能在BLOB, Long 这样的大数据对象上使用集合操作.各个单独的Select表的数
量和数据类型必须完全相同.
41. UNION
取第一个查询的所有行与第二个查询的所有行的并集.
Select employee_id, last_name from employees
where last_name like ‘A%’ or last_name like ‘B%’
UNION
Select employee_id, last_name from employees
where last_name like ‘B%’ or last_name like ‘C%’
将返回last_name以A , B 或C开头的所以employee_id , last_name行
42. UNION ALL
取两次查询的和,而不消除重复的行. A B B C 开头的,注意B两次出现.
43. INTERSECT
取两次的交集. 即B开头
44. MINUS
取在第一次返回而第二此不返回的结果. 即 A 开头
三. Table(表)
Table DDL，Constraint
45. 建立表
create table [schema.] <TABLE_NAME>(
<COLUMN_NAME> <data type> [default <expression>] [<constraint>
[,<COLUMN_NAME> <data type> [default <expression>] [<constraint>] ]…. );
46. 约束语法
alter table [schema.] <table_name> add [constraint [<name>]] < constraint definition >
创建表时的语法:[constraint [<name>]] < constraint definition >
主键 constraint cs_name primary key(columun_name) 任一表最多一个主键, 可以由一个或者
多个列(max=32)共同构成一个主键. 表中任意两行的主键列的值不能为NULL也不能相同.
唯一性 constraint cs_name unique(columun_name)最多由32列构成,多个列即为复合唯一键, 唯一
键约束允许多个NULL.唯一键建立后, oracle会在后台建立一个唯一性索引来强制唯一性.如果已经为唯
一键建立了索引, oracle会使用已有的索引.
外 键 constraint cs_name foreign key(columun_name) references parent_table_name(columun_name)
被参照的父表列为唯一键或者主键,子表插入记录时可以为空. 外键可以自参照. 也可组合多个列成为复
合外键(此时,复合外键所拥有的子表列要与父表列的数量类型都匹配). 当插入一行,外键不为空而父表
中被参照的列没有相应的行, 则插入失败. 外键本身不要求非空.
alter table insert_Select add constraint fk_ insert_Select foreign key (word) references enstudy(word) ;
Check约束 constraint cs_name check(sex in ( ‘MALE’, ’FEMALE’))
47. 建立有约束的表
create table significant_Others(
customer_number int not null primary key,
significant_other int,
information NVARCHAR2(15) ,
foreign key (information) references enstudy) ;
// enstudy 必须有主键,而且主键为NVARCHAR2(15).
create table CONSTRAINTS2
(
ID1 VARCHAR2(10) not null,
ID2 VARCHAR2(10) not null,
NAME VARCHAR2(20),
SEX VARCHAR2(1),
constraint pk_constraints2 primary key(id1,id2)
)
48. create table …as select …where…
create table insert_Select2 as select word, pronounce from enstudy ;
查询一个表以创建另一个表, 这里支持对象(如 约束/索引/触发器) 将不会被建立.
使用where 1=0 可以只复制结构.
49. 删除约束
alter table enstudy drop constraint pk ;
//
ERROR 位于第 1 行:
ORA-02273: 此唯一/主键已被某些外部关键字引用
50. 创建视图
create view cor_View as select word as WORD_Se, pronounce as PRO from enstudy ;
由于视图是一个指定的查询结果集, 所以可以使用符合查询或者嵌套,JOIN,UNION
对视图的改变将反映到原始表 !!
DML (Data Manipulation language)
51. INSERT 插入一行
Insert into <t_name> (<col_name1>[,<col_name2>….])
Values(<value1>[,<value2>…]) ;
52. insert into … select .. . from …where…
insert into copy_tab Select word , pronounce from enstudy where word=’able’ ;
53. UPDATE 修改行
Update employees
Set salary = salary *
Where job_id=’IT_PROG’ ;
54. DELETE
Delete from employees where job_id = ‘IT_PROG’ ;
这里不限使用一个条件,如果不提供条件则会删除表的所有记录.
55. COMMIT
一旦提交就不能回滚回到前面的状态.
56. ROLLBACK
回滚到最近一次commit或者rollback后的状态.
57. 全局临时表
create global temporary table session_tab
on commit preserve rows
as select *
from employees
create global temporary table teansaction_tab
on commit delete rows
as select * from
employees
where 1=0
修改表结构
58. 添加一列 add
alter table people add( cell_number varchar2(10) ) ;
59. 修改一列 Modify
//NOT NULL 约束
Alter table people
Modify ( ssn number(9) not null) ;
60. 删除列 标注不用的列
alter table <table_name> Drop cloumn <cloumn_name1>
alter table <table_name> Drop cloumn (cloumn1,column2….)
//上面语句将重写表到磁盘,并移走旧的数据.
//下面将不重写也不收回空间,对于大表将节约时间.
Alter table <table_name>set unused column
Alter table <table_name>set unused (column,column2…)
//下面删除unused列
alter table <table_name> DROP UNUSED COLUMN
//查找不用的列
select * from user_unused_col_tabs ; // 返回两列(table_name, count)
61. 重命名表
alter table people rename to employee
62. 删除表
drop table <table_name> [cascade constraints]
可选参数选中后将同时删除子表外键.
63. TRUNCATE TABLE 清除数据,保留表
TRUNCATE TABLE [SCHEMA.] <table_name> [DROP STORAGE | REUSE STORAGE ]
// 快速清除表中的数据,保留表,且不会生成回滚数据.
不会激活ON DELETE 触发器
必须先禁用所以子表外键,必须具有DROP TABLE 特权, 是一个DDL语句,所以不能回滚.
DROP STORAGE: 默认选项.只有最初为表分配的盘区(MINEXTENTS)会被保留给新行.
REUSE STORAGE:保留表的所有盘区, 适合快速增长的表.
四. View(视图) (省略了很多细节)
关系视图
64. 建立视图
create view my_v as
select first_name||', '||last_name NAME
from ; --重命名是必须的,当使用列连接时.
-- Group by , order by , having 子句是允许的. 也允许Union两个查询.
65. 我需要什么权限 ?
需要有视图所引用的表的选择的权限.
需要有创建视图的权限::
grant create [any] view to IC ; -- user IC 具有了创建视图的权限
66. 视图的作用 ?
把复杂的查询定义在可重用视图中, 可以隐藏查询的负责性,
限制查询的返回, 比如定义视图时不选择薪资字段, 再把视图的选择权授予public, 而收回表的选择
权, 可以限制表数据的返回, 也可以在视图的查询中增加WHERE子句, 限制行的返回,
通过赋予视图的SELECT权限更加有效的保护表中的数据.
67. 可以建立同义词
create public synonym table_name for _name;
68. 有哪些视图 ?
select view_name from user_views ;
69. 视图的原始定义
select view_name, text from user_views ;
70. 改变视图
create or replace view ms_v as
select * from ms ;
-- 不会损失以前授予对视图操作的特权
71. 删除视图
drop view <view_name>
72. 编译视图compile
alter view view_t compile ; --视图无效后, 手工编译视图
73. 建立强制视图force
create or replace force view golf as
select *
from iAmNoExsit ;
警告: 创建的视图带有编译错误。
74. 视图的哪些列是可更新的? 查数据词典user_updatable_columns
desc user_updatable_columns
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
UPDATABLE VARCHAR2(3)
INSERTABLE VARCHAR2(3)
DELETABLE VARCHAR2(3)
select * from user_updatable_columns
where table_name =’COMPANY_PHONE_BOOK’;
75. WITH READ ONLY 建立只读视图
create or replace force view golf as
select *
from iAmNoExsit WITH READ ONLY;
-- 使得视图只读, 避免用户通过视图改变了表数据.
76. 通过视图更新数据
一般直接从数据表中选择的列可以直接更新, 但一些连接( ||)成的虚拟列或者统计列不能直接更新,
此时可以考虑在视图上建立INSTEAD OF 触发器.
77. WITH CHECK OPTION 约束
create or replace view friend as
select * from workMess where ID=9
with check option ;
由于加上了约束, 则当DML数据时, 只有那些能被视图选中的数据才允许插入或者更新.
update friend set id=10 ;
update friend set id=10
*
ERROR 位于第 1 行:
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句
但是, 此时使用where id=9条件使得照常可以更新.
insert into friend (id) values(11) ;
insert into friend (id) values(11)
*
ERROR 位于第 1 行:
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句
delete friend ;
已删除 1 行。
内嵌视图
内嵌视图基本上就是嵌入到父查询中的查询, 能够在任何可以使用表的地方使用. 内嵌视图是临时
的,它只存在于父查询的运行期间, 但是它可以让你在整个查询的任何部分使用视图结果.
78. 什么样子的
select department_name,count(*),
TO_CHAR((count(*)/)*100,'')||'%' pct
from dep,
emp,
( select count(*) cnt
from ) total_emp
where _id = _id
group by department_name,
total_emp 成为内嵌视图select count(*) cnt from 的别名.
类似
select department_name,count(*),
TO_CHAR((count(*)/( select count(*) from ) )*100,'')||'%' pct
from dep, emp
where _id = _id
group by department_name
79. ROWNUM有用吗?
ROWNUM 是一个反映了从表选取行的次序的伪列, 所要选取的第一行ROWNUM值为1, 第二行
的rownum为2，后面的行依次类推。但是, ROWNUM值会在用ORDER BY子句之前就指定给了每个行.
内嵌视图使ROWNUM真正有用::
select last_name, hire_date
from (select last_name, hire_date
from
order by hire_date )
where rownum<=5 ;
上面只能在rownum小于某一值的条件下使用.
如果要取两个参数之间的数据, 必须使用row_number()分析函数
Select Sub.*
From (Select t.*, row_number() over(Order By Name) r From Workman t) Sub
Where R Between 100 And 120
80. ROWNUM 分析函数和嵌套
select * from (
select t.*, rownum "afterOrderResultNo"
from (select name, salary,rownum "beforeOrderResultNo" from workman where sex = 'M' order by
name) t
) where "afterOrderResultNo">=2 and "afterOrderResultNo"<=3
select * from (
select id,ro r from(
select id, row_number()over(order by id desc) ro
from rownumtest
)
)
where r between 1000 and 1020 ;
五. 数据词典视图
常见的视图(省略了很多细节)
81. 数据词典
数据词典是用户在整个ORACLE数据库的编目, 当建立用户,表, 约束 和 其他数据库对象时,
ORACLE都会自动维护一个在数据库中存储的项编目. 用户通过数据词典可以知道:
所拥有的表的名称和特性
有权查看的所有表的名称和属性
检索存储过程的代码
特定表空间中存储的所有数据文件
罗列依赖于其他对象的对象
….
82. dictionary
保存了数据词典中所有视图的完整列表和简短描述.
table_name :: 名称
comments :: 文本注释
83. DICt_COLUMNS
词典表中的列描述:
table_name :: 名称
column_name : 列名
comments :: 列的文本注释
84. DBA_objects
数据库中所有对象的列表和相关内容
85. dba_tables
数据库中所有表的列表和相关内容
86. dba_tab_columns
表的所有列, 视图, 簇的信息
87. dba_tab_privs
赋予数据库中对象上的特权信息
88. dba_col_privs
数据库特定列上所有特权
89. dba_tab_comments/dba_col_comments
表和视图上建立的注释
90. dba_constraints
表上的约束信息
91. dba_cons_columns
数据库表上约束的列的信息信息
92. dba_external_tables
外部表的信息
93. dba_external_locations
外部表的数据位置信息
94. dba_object_tables
对象表的信息
95. dba_coll_types
所有类集类型的相关信息
96. dba_jobs
所有排定作业的相关信息
97. dba_types
所有对象类型的信息
98. dba_type_attrs
对象类型属性的信息
99. dba_type_methods
对象类型中方法的信息
100. dba_method_params
对象类型方法的参数信息
101. dba_method_results
调用者可以使用的方法返回类型信息
102. dba_lobs
所有大对象或者lob
103. dba_views
所有视图的信息
104. dba_updatable_columns
联接视图的所有列,以及是否可以通过他们修改数据
105. dba_triggers
所有与触发器相关的信息
106. dba_trigger_cols
触发器的所有列的信息
107. dba_source
实际的PL/SQL源代码
108. dba_procedures
过程和函数的信息
109. all_arguments
过程和函数的参数和返回值的信息
110. dba_dependencies
对象之间的依赖信息
111. dba_errors
show errors将查询的视图, 返回最近一次的错误信息
112. dba_indexes
索引的相关信息
113. dba_ind_columns
索引的列的信息
114. dba_roles
角色信息
115. dba_role_privs
数据库的用户或者角色 被 赋予了何种角色的信息
116. dba_sys_privs
数据库的用户或者角色 被 赋予了何种系统特权
117. dba_directories
所建立的目录对象的信息
118. global_name
只有一行,一列, global_name : 显示数据库的全局名称
119. 数据词典视图的同义词
Cat User_catalog
Clu User_clusters
Cols User_tab_columns
ind User_indexes
obj User_objects
dict Dictionary
seq User_sequences(没有dba_sequences)
syn User_synonyms
tabs User_tables
六. 索引(省略了很多细节)
NULL不能包含在索引返回的记录中.
为恢复索引的效率, 管理员必须及时的对其进行重建.
所有的索引都应该考虑一下是否进行压缩.
120. 创建索引
Create index emp_name_idx
On emp(id) [ REVERSE ]; //reverse 反转索引
( grant dba to scott; //授权 )
创建表后，上面的的主键和唯一键将都被设为表上的索引。
121. 主键使用复合索引
create table pk_use_index (
id number(4) constraint pk_use_index_pk primary key
using index(create index pl_use_index_idx
on pk_use_index(id , warehouse)
pctfree 10),
warehouse varchar2(20),
price varchar2(50)) ；
因为主键、唯一键反正都会建立索引，所以考虑把非唯一性索引结合在主键、唯一键后面作复合索
引。这里， 应该使用约束列作为索引的主导键。
相同的分解语句：
create table ….
Create index …
Alter table … add( constraint … primary key(id) Using index …. )
122. 外键上的索引
当父表会对其主键进行修改/删除时, 应该考虑在外键上建立索引, 因为此时如果没有索引, 父表对
主键上的DML会锁定子表(9i已经可以缓解这种锁定,但如果父表本身在DML之前在子表中存在另外的
DML锁定,则刚才进一步的锁定会在其后排队), 但是如果有索引,则会只锁定相应的行.
123. 反转键索引
对于一个以单调增建的列为主键的表, ORACLE建立了其上的索引, 由于主键单调增加, 插入新的
记录的索引总是位于分结点末尾, 很有理论设置pctfree为0. 但是批量的插入会批量新增分结点, 由于分
结点都集中在末尾, 则会产生性能问题, 同样, 如果批量删除主键小于某一值的行,则对索引最左边的叶
子结点导致争用.
反转索引使得DML分散到各个子结点, 不会产生争用, 但是必然出现数据块分割的问题, 特别地,
如果对主键进行between 条件选择, 则反转索引失效.
Create index <index_name> on <table_name>( con1[co2,…] ) reverse ;
124. 重建索引
alter index <index_name> rebuild pctfree 10 ;
125. 分析表、索引
analyze table index_test compute statistics
ananlyze index <index_name> validate structure
七. SQL函数
NUMBER
126. abs(number_v) 绝对值
127. ceil(number_v) 向正无穷取整
128. floor(number_v) 向负无穷取整
129. mod(numerator,denominator) 第一个数对第二个数取模, 支持浮点数
130. power(base,power) 返回 basepower, 支持浮点数
131. round(base [, indicate ] )
没有第二个参数, 表示四舍五入取整, round()=-4, round()=-5 .
如果有第二个参数, 要求第二个参数为整数n, 表示四舍五入到power(10,-n)
132. trunc(number_v [, indicate] ) 截断
与round(number_v) 类似, 但只是截断,不四舍五入;
Select trunc(,3) From dual
得到
133. sign(number_v) 标准的符号函数 f(x)={ return x |1,x>0; 0, x=0; -1, x<0 }
DATE TIME
134. add_months( date_value, number_of_months)
从一个日期值中添加一些月份, 返回新的日期. 提供负值, 后退相同数目的月份
135. current_date 返回当前 会话时区(modifiable) 的当前日期.
Alter session set time_zone=’-08:00’ --- 设置为 -8 区
select current_date from dual ; --调用时不加()
136. current_timestamp([ timestamp_precision ])
也是返回当前 会话时区(modifiable) 的当前日期.
精度可以由给定的整型参数规定, power(10, - timestamp_precision) 秒
137. localtimestamp 同上, 只是精度为6位
138. systimestamp 同上
139. DBTIMEZONE() 返回当前的时区, 调用时不加()
select DBTIMEZONE from dual
140. LAST_DAY( date_v ) 日期参数的(月份)最后一天的日期
Select last_day(Date '2005-05-05') From dual
得到 Date 2005-5-31
141. months_between(date1,date2)
返回两个日期之间的月数,
select months_between(date'2000-07-01',sysdate+3) from dual
142. next_day(date_v, day_of_week)
select next_day(sysdate,'星期一') from dual
143. numtodsinterval( number, type_of_interval) 生成一个时间值
type_of_interval: second, minute, hour
select current_timestamp+numtodsinterval(2,'hour') from dual
144. numtoyminterval(number, type_of_interval)
type_of_interval: year, month
select current_timestamp+ numtoyminterval (2,'year') from dual
145. sysdate 根据数据库时区返回当前日期和时间
146. round(date [ , format_mask ] )
根据7月1日对年四舍五入,
第二季度第二个月的16日四舍五入.
147. trunc(date [ , format_mask] )
round, trunc 的数据化模型(format_mask):
年: YYYY, YYY, YY, Y, YEAR
季度: Q
月: Month, Mon, MM, RM
天: DDD, DD, J
星期的起始日: DAY, DY, D
小时: HH, HH12, 24HH
分钟: MI
Character
148. chr(number [ USING NCHAR_CS ] )
使用编码转化为字符,
select chr(65)||chr(114)||chr(101) from dual
CHR
---
Are
149. INITCAP( string )
第一个字符变成大写, 其他变成小写
150. INSTR(string, search_string [ ,start_position [,occurance]] )
搜索子字符串在给定字符串的位置. 区分大小写, 索引base 1
可以指定搜索第几个. 找不到, 返回0
151. length(String) 返回字符数
152. LOWER(STRING) 返回小写
153. LPAD(string1, width[,sting2])
为使得最后的宽度为width, 截取string1或者在string1的左边重复放置string2或者空格
154. LTRIM(String [ , string_to_trim] )
除去引导的空格或者指定的字符
155. replace(string, search_string [ , repace ] )
没有第三个参数, 就把search_string从中剥离
156. RPAD(string1, width [ , string2 ] )
类似LPAD, 只是在右边填充
157. RTRIM(String [ , string_to_trim] ) 类似LTRIM()
158. SOUNDEX(string)
返回一个表示单词或者字符串”发音”方式的字符串 .
159. SUBSTR(string, position [, length] )
可选一个选取子串的长度整型参数.
子串包含了起始位置所在的字符
160. translate(string, search, translating_string)
使用translating_string 中的字符对search中对应位置的字符对string进行替换, 如果search中相应位置
在translating_string中没有字符, 则删除string中相应的字符.
161. trim([{{ LEADING|TRAILING|BOTH}[char_to_trim]|char_to_trim}] from string)
从字符串开始/结尾/both 去除指定字符.
select trim('a' from 'asashjjhj') from dual
char_to_trim 只能是一个字符.
162. upper(string) 变为大写
Conversion 只选择经典的解释
163. chartorowid(string)
select empname from emp
where rowid=chartorowid(‘AAAAHqAABAAR74AAI’)
164. to_char
to_char(object) : 把CLOB, NCLOB, NCHAR 转化到数据库字符集
to_char(date [ , date_format ] ) :
to_char(number)
165. to_nchar(string)
与to_char的区别是, 返回的值在数据库国家字符集中.
166. to_date(string [ , format ] )
日期数值格式
AD, . , BC, . :: AD, BC 指示符
AM, PM, . , . :: 子午指示符
CC , SCC :: 世纪指示符, (1999 解析为20, 2001 为21 )
Y, YY, YYY, YYY, YYYY :: 年份数字
RR :: 两位数的年份
YEAR :: 年份的字符表示( “Two thousand one” )
MM :: 月份数字值( 1 –12 )
RM :: 月份罗马数值
MON :: 月份的缩写字符值( JAN, FEB )
MONTH :: 月份的字符值(JANUARY, FEBUARY )
WW :: 年中星期的数字值 (1-53)
W :: 月中星期数字值 (1-5)
D :: 星期中各天的数字值( 1 – 7 )
DD :: 月中各天的数字值( 1 – 31 )
DDD :: 年中各天的数字值( 1 – 366 )
DAY :: 星期的文本值
HH, HH12 :: 12小时制小时(1-12)
HH24 :: 24小时制小时(0-23)
MI :: 小时各分钟(0-59)
SS :: 分中的各秒(0-59)
SSSS:: 天中的各秒(0-86399)
数字格式::
9 ----- 根据位置返回一个数, 9重复可以返回多个数
9,999 -- 放置逗号
---- 放置小数点和精确到两位小数位数
$9999 – 加美刀符号
FM99---- 去除前后空格
RN, rn ----- 大小写罗马数字
X ------ 16进制的数
Miscellaneous(省略了很多细节)
167. coalesce(value { , value } …)
返回第一个非NULL值, 都为NULL也就返回NULL. 要求所有的值是相同的数据类型.
168. decode(base, val1, return1{ , val2, return2 } … { ,default_value } )
把base 同 { val1, val2… } 进行比较, 返回相应的reutrn值, 没有时返回default_value,如果同时又没
有default_value, 则返回空串.
169. DUMP(expression {, return_fromat { satrt {, length }}} )
返回各个字符的编码,返回格式可选, 起始位置, 长度 可选
return_format : 8 ,10,16 表示8/10/16进制, 17 表示单位字符
170. Greatest(value { ,value } … ) 返回字符集中最大值
171. Least(value { ,value } … ) 返回字符集中最小值
172. NULLF(exp1, exp2)
if(exp1==null) throw exception;
if(exp1==exp2) return null ;
else return exp1 ;
173. nvl(exp, alter_return)
if(exp==null) return alter_return;
else return exp ;
八. 事务处理和安全
事务处理
174. 事务处理的ACID特性
Atomicity 原子性 事务要么全表进行, 要么不进行
Consistency 一致性 事务将数据库从一种状态转变为另一种状态都满足施加给它的约束. 在两种状
态之间, 可能有不满足约束的临时的中间数据.
Isolation 隔离性 事务提交前, 其他事务看不到它的修改
Durability 持久性 提交了的事务将永久生效
ORACLE中的存储过程作为原子语句处理.
175. Commit做了什么?
1. 为事务生成SCN(系统改变编号), 在Oracle内部使用对事务进行排序.
2. 将剩余已缓冲的重做日志表项写入磁盘, 并且将SCN记录到在重做日志文件中. 这一步就是实际
的"提交", 让事务永久存在.
3. 释放会话的锁定.
无论事务处理的规模如何, 提交实际上花销的时间都很平稳.
因为重做日志本身会在以下情况定期执行:
每3秒; 当SGA中的日志缓冲超过了1/3的空间, 或者包含达到了1Mb的已缓冲数据; 事务提交
176. Rollback要做什么 ?
1. 撤销所有已经执行的改变. 这必须读取UNDO数据, 并反转我们的操作. (因此回滚事务处理的
时间受所改变的数据量的影响.)
2. 释放我们的会话的锁定.
177. 什么时候事务开始?
1. 连接到数据库, 执行第一个DML开始第一个事务;
2. 前一个事务结束了, 执行第一个DML开始一个新的事务.
即:: DML真正开始一个事务
178. 什么时候事务结束 ?
1. 执行COMMIT或者ROLLBACK
2. 执行一个创建或者修改数据库对象的DDL语句
3. 断开数据库:: SQL*Plus中Exit命令退出SQL*Plus或者关闭/退出Worksheet,ORACLE自动执
行COMMIT, 直接叉掉SQL*Plus, 则自动执行ROLLBACK
4. 数据库服务器发生崩溃, 很显然, 事务被迫结束.
179. roll back to savePoint[ 保存点 ]
SavePoint point_name
roll back to point_name
//回滚到指定的地方, 这样point_name之前的操作会被保持. 而且当前事务仍然维持. 以后仍然可以
使用rollback完全回滚该事务.
注意:: 保存点并不结束/开始一个事务 !! 但是可以人为地把一个大规模的事务分割成一系列
较小的执行单元. 如果依次调用一组函数, 可以在函数开始的地方使用: savepoint function_name , 在函
数执行期间使用Roll back to function_name, 这样函数要么完全执行成功, 要么完全没有执行, 且并不开
始结束当前事务.
安全
180. 权限类型
对象特权: 赋予用户访问或者操作数据库对象的特权. 主要面向DML
系统特权: 许可各种特性的访问或者许可数据库中的特定任务. 主要面向DDL. ORACLE具有超过
100个的独有的系统特权.
181. 系统特权
GRANT < privilge> TO <user_name> [ WITH ADMIN OPTION ] ;
//可选参数WITH ADMIN OPTION 选择是否让用户有权把相同的特权赋予给其他用户。
REVOKE < privilge> FROM <user_name>
//任何具有ADMIN OPTION系统特权的用户都可以取消其他用户的系统特权, 而不是一定要使用
最初授予它权限的用户。
基本的系统特权：：
1. 登陆数据库 create session
2. 建表 create table，还需要分配表空间
更简单的要让用户连接数据库, 而且可以建表::
Grant [ connect | resource ] to user_name
182. desc DBA_SYS_PRIVS ;
描述系统特权视图
183. 系统有哪些系统特权
select distinct PRIVILEGE from DBA_SYS_PRIVS ;
184. 对象特权
GRANT object_privilige ON objectName TO user [ WITH Grant Option]
REVOKE object_privilige ON objectName FROM user
基本对象特权：：
1. SELECT 表、视图、序列 的查询、取值
2. INSERT，UPDATE，DELETE 表、视图 的插入、更新、删除行
3. EXECUTE 执行(包括编译)过程、函数、程序包和其他可执行元素，比如Java类
4. INDEX 用于表的INDEX
5. REFERENCES 用于表上使用外键
6. ALTER 修改表或者序列的定义所需的对象特权
7. ALL 本身不是对象特权， 只是授予ALL表示把以上除EXECUTE外的七种对象特权授予
了用户，但仍然可以单独取消某一特权，同时也支持一次取消ALL。
用户试图在不具有任何特权的数据库对象上执行DML操作,遇到ORA-00942: table or view
does not exist 或者ORA-04043: object <object_name> does not exist. 一旦用户具有了数据库对象
上至少一个对象特权, 但执行的操作尚未有权限, 得到ORA-01031: insufficient privileges .
对一个用户的单独某一表授予权限给另一个用户, 必须是表的所有者才有权限.
只有授权的直接建立者, 才有权取消他们为另外的用户授予的特权.
185. desc USER_TAB_PRIVS ;
描述表特权： 还有ALL_TAB_PRIVS DBA_TAB_PRIVS
186. 授予特定列的对象特权（INSERT，UPDATE，REFERENCES）
grant update （id，name） on table_tmp to hr ;
187. 角色
特权的命名集合. 可以组合系统特权, 对象特权. Public角色会默认赋给数据库所有的用户.
188. CREATE ROLE
CREATE ROLE role_name ;
--创建角色role_name
为角色授权与为用户授权相似.
189. 角色CONNECt, RESOURCE
他们一共包含了13个不同的系统级别的特权.
CONNECT不仅包含了CREATE SESSION 系统特权, 还包含了CREATE TABLE, CREATE
SYNONYM以及其他6个系统特权.
UNLIMITED TABLESPACE 系统特权不是RESOURCE角色的组成部分, 但是RESOURCE 角色的
受让人会自动获得这个特权. 它使得用户具有在包括了SYSTEM表空间在内的任何表空间上使用无限
的空间. 理想的做法是取消授予了RESOURCE角色的用户的UNLIMITED TABLESPACE 特权.
不应该轻易使用任何预定义的角色, 而是应自己建立和管理特有的角色, 为访问和操作提供最少数
量的特权.
190. desc role_tab_privs ;
--查看角色视图
191. 授权给角色, 授角色给用户, 授角色给角色
为角色授权与为用户授权相似.
GRANT role_name TO user [ WITH ADMIN OPTION ]
GRANT role_name TO role_name2 [ WITH ADMIN OPTION ]
192. 会话角色, 启用/禁用
SELECT * FROM SESSION_ROLES
--查看当前会话启用了的角色
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
….
启用会话角色(角色事先已经赋予用户):
set role role1[role2, … ]
不在列表的角色会在当前会话中禁用, set role all 启用所以赋予用户的角色. set role none 在当前
会话禁用所有角色.
193. 密码保护角色
对角色启用密码保护:
alter role <role_name> identified by <role_passwd>
现在(如果角色不是默认角色), 除非用户为角色提供密码, 否则角色被挂起:
set role <role_name> identified by <role_passwd>, 如果提供的密码正确现在角色就生效. 角色所含的
特权也生效.
注意: 作为默认角色组成部分的所有角色, 无论是否受密码保护, 都会在数据库会话建立时启用.
Alter user <user_name> default role role1[ role2, role3 … ]
194. PL/SQL 和 数据库角色
PL/SQL函数, 过程,程序包要使用对象”定义者”的命名空间和特权, 使用的特权必须时直接赋予设
计用户的特权, 而不包括通过数据库角色获得的特权. 这使得相对于直接执行DML语句可能拥有较少的
特权.
简捷稳健的测试方法是使用set role none 禁用所有角色, 再执行存储过程的DML看是否有权限.
九. 数据库管理
配置管理(省略了很多细节)
195. 监听器
默认文件为: %ORACLE_HOME%\network\admin\ , 可以建立TNS_ADMIN环境变量把配
置文件放到非默认路径.
# Network Configuration File:
# D:\oracle\ora90\network\admin\
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aisee)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\oracle\ora90)
(SID_NAME = orcl)
)
)
存储结构(省略了很多细节)
196. 表空间(tablespace)
表空间是用户可以在Oracle中使用的最大的逻辑存储结构. 用户在数据库中建立的所有内容都会保
存到表空间中. 表空间简单可分为永久表空间和临时表空间. 写入临时表的数据, 为大规模排序操作写
入磁盘的数据块, 已经其他许多临时数据都会写入到临时表空间中.
每个Oracle数据库都提前配置有SYSTEM表空间, 存储了数据词典及系统管理信息.
197. 段(segment)
每一表都会在表空间有一个存储的逻辑段. 为用户建立的对象生成的段都要在磁盘上消耗空间. 三
种类型的段:
数据段: 表, 索引, 簇及表分区这些常规数据.
临时段: 临时表空间中的段, 存储临时表,引起内存交换的SQL操作这样的内容.
回滚段: 数据库的UNDO数据. 为事务处理提供数据库的读取一致性视图.
198. Oracle管理文件
Oracle9i引入了Oracle管理文件, 用以避免对以下类型的数据库对象进行手工文件管理:
表空间, 控制文件, 在线重做日志文件.
当然, 使用Oracle管理文件并不阻止旧有的文件管理.
启用Oracle管理文件很简单。在用户的参数文件中， 可以将名为DB_CREATE_FILE_DEST 的参
数设置就是Oracle为数据文件、临时文件、在线重做日志文件已经控制文件使用的默认目录。用户可以
使用DB_CREATE_online_log_DEST_n的格式, 设置多个使用序列号编号的参数.
Db_create_file_dest=’D:\oracle\oradata’
Db_create_online_log_dest_1=’D:\oracle\oradata’
Db_create_online_log_dest_2=’E:\oracle\oradata’
Db_create_online_log_dest_1=’F:\oracle\oradata’
系统根据参数文件中的规定自动建立每个文件, 如果没有规定任何额外的参数, 则Oracle使用
Db_create_file_dest参数. 很明显, 如果没有这个参数, 则Oracle就不能使用Oracle管理文件.
用户管理
199. 建立用户
create user <user_name>
identified by <password>
使用identified externally 表示这是一个将要由外部验证的数据库用户帐号(通常是客户的操作系统),
identified globally 用户的信用凭证以企业目录服务的形式维护, 通常是Oracle Internet目录这样
的LDAP (轻量级目录访问协议)服务器.
可以使用的选项包括:
Default tablespace: 默认是system
Temporary tablespace: 默认是TEMP, 9i以前是SYSTEM.
Quota : 用户在表空间的最大可用数量.
Password expire: 使用这个子句要求用户第一次登陆就需要改变密码.
可以在建立用户后授权:
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
200. 重设密码
alter user <user_name>
identified by <new_password>
201. 锁定/解锁账号
alter user <user_name> account [ luck | unlock ]
202. 删除用户
drop user <user_name> cascade
当用户有表时,将强制使用cascade关键字
表空间管理
203. 创建表空间
create tablespace AIC_SPACE DATAFILE 'D:\oracle\oradata\' size 20M
[temporary]
参数temporary表示是否是临时表空间, 参见”临时文件”小节.
create temporary tablespace tmp_ic
tempfile 'C:\'
size 10M
extent management local
uniform size 512K
create tablespace tmp_ic2
datafile 'C:\'
size 10M
default storage(
initial 1M
next 512k
pctincrease 0
)
extent management dictionary
temporary
204. 修改用户默认表空间
alter user <user_name>
default tablespace users //修改默认表空间为users
temporary tablespace temp ; //修改临时表空间为temp
EXAPLE::
alter user AIC
default tablespace AIC_SPACE
temporary tablespace temp ;
205. 移动表到新的表空间
alter table <table-name> move tablespace <new_tablespace_name>
user_tables 数据词典可以查看表所在的表空间, user_segments也可以
206. 修改表空间配额
alter user <user_name>
quota unlimited on users//给帐号赋予users表空间配额
quota 10M on temp
quota 0M on system //取消账号对system表空间的访问
EXAPLE::
alter user ic
quota unlimited on users quota 10M on temp
quota 0M on system ;
207. 表空间默认SYSTEM
table foo(
id int)
tablespace users ; //指定表空间
2. select table_name,tablespace_name
form user_tables
where table_name = ‘foo’ ; //查看表空间
3. select tablespace_name
from dba_atablespaces
order by tablespace_name ; .. 查询存在的表空间名称
alter user SCOTT quota unlimited on users ; //授权表空间
208. 保护SYSTEM表空间
识别:
select username, default_tablespace, temporary_tablespace
from dba_users
where default_tablespace = 'SYSTEM'
or temporary_tablespace = 'SYSTEM' ;
消除:
alter user scott default tablespace users ;
