-
-创建使用同义词
- -同义词就是给表、视图等对象取得别名,用于简化对
- -其的访问
- -分为 2种:
- -私有同义词:用户自己创建自己使用的
- -公共同义词:dba创建,给其它用户使用的
- -为dept_sum_vu视图创建一个同义词
- -不使用 public关键字创建的同义词就叫做私有同义词
create synonym d_sum
for dept_sum_vu;
- -通过同义词访问视图
select *
from d_sum;
- - SQL 错误: ORA - 01031: 权限不足
create public synonym hremp1
for employees;
- -以下操作由sys操作
- -为hr的copy_emp表创建公共同义词
create public synonym hrcemp1
for hr.copy_emp;
- -将访问同义词的权限给所有用户
grant select on hrcemp1 to public;
- -权限控制:
- - schema是表、视图、序列等对象的集合。
- - schema被一个数据库用户拥有并且和用户有相同的名字。
- -以下操作由sys完成
- -创建新用户
create user demo
identified by demo;
- -授予demo登录到数据库的权限
grant create session
to demo;
- -授予demo创建各种对象的权限
grant create table, create view, create sequence,
create procedure
to demo;
- -为了简化权限管理,使用角色
- - 1、创建角色
create role manager;
- - 2、给角色授权
grant create table, create view
to manager;
- - 3、将角色授给用户,那么用户就拥有了角色拥有的权限
grant manager
to demo;
- -修改自己的口令
- -由hr操作
alter user hr
identified by hr;
- -授予对象权限给其它用户
- -将hr的dept80表的查询权限授给demo
grant select
on dept80
to demo;
- -将hr的dept80表的last_name和annsal列的修改权限授给demo
grant update(last_name,annsal)
on dept80
to demo;
- -授权时使用 with grant option选项
- -它的含义是:允许被授权的用户继续将同样的权限授给其它用户
- -以下由sys操作
create user demo1
identified by demo1;
grant create session
to demo1;
- -以下由hr操作
grant select
on dept80
to demo
with grant option;
- -以下由demo操作
grant select
on hr.dept80
to demo1
with grant option;
- - - -以下由hr操作
- -查询数据字典得到有关权限的信息
DESC role_sys_privs;
- -查看角色具备的系统权限
select *
from role_sys_privs;
- -查看自己把哪些对象的访问权限授给了哪些用户
select *
from user_tab_privs_made;
- -查看其它用户把哪些对象的访问权限授给了自己
select *
from user_tab_privs_recd;
- -废除对象权限
revoke select
on dept80
from demo;
- -注意:demo通过传递授权授给demo1的访问hr的dept80
- -表的权限同时被废除。
- -系统权限也可以进行传递授权,使用 with admin option选项
- -废除系统权限时,通过传递授权授予的系统权限不会被同时废除
- -管理 schema对象
- -使用 alter table语句添加、修改、删除列
- - 1、 alter table... add:
- -给表添加一个新的列
- - 1)不能指定列要出现在哪里。新列成为最后的列。
- - 2)当添加列时,如果表中已经包含了行,对所有的行来讲,
- -新的列被初始化为 null,或者赋予默认值。
- -给dept80表添加一个job_id列
alter table dept80
add (job_id varchar2( 9));
alter table dept80
add (job_id1 varchar2( 9) default 'abc');
- - 2、 alter table... modify:
- -修改表中已有列的定义
- -列修改包括改变列的数据类型、大小和默认值。
- -可增加数字列的宽度或精度
- -可增加字符列的宽度
- -可减少列的宽度,如果:
- - 列只包含 null值
- - 表没有行
- - 减少列宽度不少于列中已有的值
- -如果列中只包含 null值可改变数据类型。
- -列的默认值的改变只影响后续插入的行
- -将dept80表的last_name列的宽度增加到 30
alter table dept80
modify (last_name varchar2( 30));
- -将dept80表的last_name列的宽度减少到 20
alter table dept80
modify (last_name varchar2( 20));
- -将dept80表的last_name列的宽度减少到 8
- - SQL 错误: ORA - 01441: 无法减小列长度, 因为一些值过大
alter table dept80
modify (last_name varchar2( 8));
- -将dept80表的annsal列的宽度减少到 10
- - SQL 错误: ORA - 01440: 要减小精度或标度, 则要修改的列必须为空
alter table dept80
modify (annsal number( 10));
- - 3、 alter table... drop或者 alter table... drop column
- -使用带 DROP COLUMN子句的 ALTER TABLE语句可以删除一个列。
- -列可以包含或不包含数据。
- -一次只删除一列
- -表必须至少还留一列
- -列被删除后不能被恢复。
- -列不能被删除,如果它是约束或索引键的一部分,除非增加了级联选项
- -删除列要花一点时间,如果列有大量的值。在这种情况下,
- -最好把列设置为未使用的,然后在系统用户较少时再删除
alter table dept80
drop column job_id1;
- -将列标记为未使用的
- -未使用的列作为被删除的列对待,即使列的数据还保留在表行中。
- -在列被标记为未使用的之后,你不能访问那个列。
- - SELECT *查询不从未使用的列中检索数据。
- -在 DESCRIBE命令执行时,未使用的列的名字和类型不被显示,
- -并且你可以给表添加一个新列,其名字和未使用的列一样。
- - SET UNUSED 信息存储在USER_UNUSED_COL_TABS视图中。
- -特别注意:该操作没有可逆操作
alter table dept80
set unused (job_id);
- -查看未使用的列的信息
select *
from user_unused_col_tabs;
- -删除未使用的列
alter table dept80
drop unused columns;
- -使用 alter table语句管理约束:
- -可以添加、删除、启用或禁用一个约束,但是不能修改约束
- -通过使用 MODIFY子句,可以给一个已有的列添加 NOT NULL约束
- -创建emp2表,先添加主键约束,再添加外键约束
create table emp2
as select * from employees;
alter table emp2
add constraint emp2_empid_pk
primary key(employee_id);
alter table emp2
add constraint emp2_managerid_fk
foreign key(manager_id)
references emp2(employee_id);
- -使用延迟约束特性
- -默认情况下,约束都是立即检查的:即,当dml语句一执行完毕,
- -oracle会立即检查操作结果是否违反约束,如果违反则抛出异常。
- -可以把约束检查的时间推迟到事务提交的时候再检查,这叫做
- -延迟约束检查。
- -和延迟约束有关的关键字:
- - DEFERRABLE(可延迟的)、
- - INITIALLY DEFERRED(初始化延迟的) 、
- - INITIALLY IMMEDIATE(初始化立即的),这是默认值
- -后面的两个值是配合 DEFERRABLE用的
create table emp_new_sal(
salary number constraint sal_ck
check(salary > 100)
deferrable initially immediate,
bonus number constraint bonus_ck
check(bonus > 0)
deferrable initially deferred
);
- -测试
- - SQL 错误: ORA - 02290: 违反检查约束条件 (HR.SAL_CK)
INSERT INTO emp_new_sal VALUES( 90, 5);
- - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 110, - 1);
- - SQL 错误: ORA - 02091: 事务处理已回退
- -ORA - 02290: 违反检查约束条件 (HR.BONUS_CK)
commit;
- -设置 DEFERRED状态给所有约束。
SET CONSTRAINTS ALL DEFERRED;
- - - - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 90, 5);
- - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 110, - 1);
- - SQL 错误: ORA - 02091: 事务处理已回退
- -ORA - 02290: 违反检查约束条件 (HR.SAL_CK)
commit;
- -禁用约束
- -通过使用带DISABLE子句的 ALTER TABLE语句禁用一个约束而不删除它
- -语法:
- - ALTER TABLE table
- - DISABLE CONSTRAINT constraint [ CASCADE];
- -禁用emp2表的外键约束
alter table emp2
disable constraint emp2_managerid_fk;
- -通过使用带ENABLE子句的 ALTER TABLE语句启用一个约束而不删除它
- -语法:
- - ALTER TABLE table
- -ENABLE CONSTRAINT constraint;
- -启用emp2表的外键约束
alter table emp2
enable constraint emp2_managerid_fk;
- - - -禁用emp2表的主键键约束
- - SQL 错误: ORA - 02297: 无法禁用约束条件 (HR.EMP2_EMPID_PK) - 存在相关性
alter table emp2
disable constraint emp2_empid_pk;
alter table emp2
disable constraint emp2_empid_pk cascade;
alter table emp2
enable constraint emp2_empid_pk;
- -禁用主键约束。观察主键索引被自动删除
- -注意:当禁用主键或者唯一键约束时,为这些约束自动创建的索引
- -会被自动删除。当启用主键或者唯一键约束时,Oracle会自动
- -为其创建索引
alter table emp2
disable constraint emp2_empid_pk;
alter table emp2
enable constraint emp2_empid_pk;
- -如果启用一个约束,约束应用到表中的所有数据。
- -删除约束
- -根据名字来删
alter table emp2
drop constraint SYS_C0013878;
- - CASCADE CONSTRAINTS选项
- -该选项专门配合 drop column子句使用。
- -当删除以下列时,需要带上该选项:
- - 1)被外键引用的主键列
- - 2)被删除的列被一个多列约束引用
- -使用 alter table语句将列改名字
alter table emp2
rename column phone_number
to phone;
- -使用 alter table语句将约束改名字
alter table emp2
rename constraint SYS_C0013876
to emp2_email_nn;
- -在 create table语句中使用 using index选项可以指定
- -主键约束使用指定的索引而不是oracle自动创建的索引
create table new_emp(
employee_id number( 6)
primary key using index(
create index emp_id_idx on new_emp(employee_id)
),
fname varchar2( 20),
lname varchar2( 20)
);
- -禁用主键约束。观察索引未被删除
alter table new_emp
disable constraint SYS_C0013883;
- -启用主键约束。观察索引未变化
alter table new_emp
enable constraint SYS_C0013883;
drop table new_emp;
- -指定主键使用唯一索引
create table new_emp(
employee_id number( 6)
primary key using index(
create unique index emp_id_idx on new_emp(employee_id)
),
fname varchar2( 20),
lname varchar2( 20)
);
- -禁用主键约束。观察索引被删除
alter table new_emp
disable constraint SYS_C0013884;
- -启用主键约束。观察索引被自动创建,名字和约束同名
alter table new_emp
enable constraint SYS_C0013884;
- -使用基于函数的索引
- -如果在查询的 where条件中,经常要对一个函数值进行比较,
- -那么可以在该函数上建一个索引
create table dept2
as select * from departments;
create index dept2_dname_idx
on dept2(department_name);
- -以下查询不走上面的索引
select *
from dept2
where upper(department_name) = 'SALES';
- -创建基于函数的索引
create index dept2_upperdname_idx
on dept2( upper(department_name));
- -以下查询走上面的索引
select *
from dept2
where upper(department_name) = 'SALES';
- -使用闪回操作
- -Oracle为删除表提供了一个特色。当你删除一个表时,
- -数据库不立即释放和表相关的空间。相反,数据库将表改名
- -并将它放入回收站中,以便以后如果你发现误删了,
- -可以用FLASHBACK TABLE 语句恢复。
- -如果你想当发出 DROP TABLE语句时,立即释放和表相关的空间,
- -就包含PURGE 子句。
- -删除表emp2
drop table emp2;
- -查询回收站
select *
from recyclebin;
- -从回收站站中恢复误删的表
flashback table emp2 to before drop;
- -彻底删除表emp2
drop table emp2 purge;
- - SQL 错误: ORA - 38305: 对象不在回收站中
flashback table emp2 to before drop;
- -清空回收站
purge recyclebin;
- -查询回收站
select *
from recyclebin;
- -第二种闪回操作
- -将某个表的内容恢复到事务提交之前的状态
commit;
- - - -以sys查询数据库最新的scn: 5716081
select current_scn
from v$ database;
delete from dept2;
commit;
- -将dept2中的内容闪回到事务提交之前
- - SQL 错误: ORA - 08189: 因为未启用行移动功能, 不能闪回表
flashback table dept2
to scn 5716081;
- -启用行移动功能
alter table dept2 enable row movement;
flashback table dept2
to scn 5716081;
select scn_to_timestamp( 5716081)
from dual;
flashback table dept2
to timestamp
to_timestamp( '2014-06-20 14:23:52', 'yyyy-mm-dd hh24:mi:ss');
- -创建使用临时表
- -临时表是一个表,它的数据只在事务或会话期间存在。
- -在临时表中的数据对于会话是私有的,这意味着每个会话只
- -能看见和修改它自己的数据。
- -可以为临时表创建索引。索引也是临时的。
- -也可以在临时表上创建视图或触发器。
- -临时表和它们的索引在创建时不自动分配段。
- -当数据第一次被插入时才分配临时段。
- -临时表有两种:事务级的和会话级的
- - 1、 SESSION级临时表
create global temporary table today_sales(sales number)
on commit preserve rows;
insert into today_sales
values( 10000);
commit; - -提交事务
- -可以看到行还在临时表中
select * from today_sales;
- -断开连接,重新登录,再次查询,数据消失
select * from today_sales;
- - 2、事务级临时表
create global temporary table cart(sales number)
on commit delete rows;
insert into cart
values( 10000);
- -可以看到行还在临时表中
select * from cart;
commit; - -提交事务
- -可以看到行已被删除
select * from cart;
- -创建使用外部表
- -外部表实际上是Oracle的一个数据加载( load)和卸载(unload)工具
- -外部表是一个只读的表,它的元数据存储在数据库中,
- -但是它的数据存储在数据库外面(操作系统中)。
- -外部表和普通表的主要区别是外部表是只读的。因此,
- -不能在外部表上执行DML操作,并且不能在外部表上创建索引。
- -Oracle服务器为外部表提供了两种访问驱动。
- -一个是加载器访问驱动(名字叫ORACLE_LOADER),只能
- -用来做数据导入;
- -另一个ORACLE_DATAPUMP访问驱动,既可用来导入数据,
- -又可用来导出数据,使用一种跨平台的格式。
- -要使用外部表,首先在操作系统中准备好目录和文件(用来加载数据)
- -然后在oracle中创建目录对象
- -注意:普通用户没有创建目录的权限,可以由sys
- -先授权: grant create any directory to hr;
- -注意:路径是绝对的
create or replace directory emp_dir
as 'D:\emp_dir';
- -创建外部表
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
nobadfile
nologfile
fields terminated by ','(
fname position ( 1: 20) char,
lname position ( 22: 41) char
)
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -修改上列:
drop table oldemp;
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
nobadfile
nologfile
fields terminated by ','
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -修改上列:
drop table oldemp;
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
fields terminated by ','
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -使用oracle_datapump驱动做数据加载和卸载
- - 1、将employees表的所有数据导出到操作系统文件中
create table emp_ext(
employee_id,first_name,last_name
) organization external(
type oracle_datapump
default directory emp_dir
location ( 'emp1.exp', 'emp2.exp')
)
parallel 2
as select employee_id,first_name,last_name
from employees;
- -数据加载
drop table import_emp;
create table import_emp
( employee_id number( 6),
first_name varchar2( 20),
last_name varchar2( 25)
)
organization external
( type oracle_datapump
default directory emp_dir
location ( 'emp1.exp', 'emp2.exp')
);
select * from import_emp;
- -使用数据字典
- -数据字典是由Oracle服务器创建和维护的表和视图的集合,
- -它们包含有关数据库的信息。对所有用户来说它们是重要的工具
- -因为数据字典是只读的,你只能查询
- -可以把 dictionary视图作为使用数据字典视图的入口点。
- -它包含了所有可用视图的名字以及简单的解释
select *
from dictionary;
- -查询user_objects视图得到自己的所有对象的信息
desc user_objects;
select object_name,object_type,created,status
from user_objects
order by object_type;
- -专门查询自己的表的信息
desc user_tables;
select table_name
from user_tables;
- -查询表列的信息
desc user_tab_columns;
select column_name,data_type,data_length,
DATA_PRECISION,DATA_SCALE, NULLABLE
from user_tab_columns
where table_name = 'EMPLOYEES';
- -查看约束的信息: 2个视图
- -user_constraints: 包含表的所有约束定义
- -user_cons_columns:约束定义的列的信息
desc user_constraints;
select constraint_name,constraint_type,
search_condition,r_constraint_name,
delete_rule,status
from user_constraints
where table_name = 'EMPLOYEES';
- -查询employees表中每个约束都定义在哪些列上
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMPLOYEES';
- -同义词就是给表、视图等对象取得别名,用于简化对
- -其的访问
- -分为 2种:
- -私有同义词:用户自己创建自己使用的
- -公共同义词:dba创建,给其它用户使用的
- -为dept_sum_vu视图创建一个同义词
- -不使用 public关键字创建的同义词就叫做私有同义词
create synonym d_sum
for dept_sum_vu;
- -通过同义词访问视图
select *
from d_sum;
- - SQL 错误: ORA - 01031: 权限不足
create public synonym hremp1
for employees;
- -以下操作由sys操作
- -为hr的copy_emp表创建公共同义词
create public synonym hrcemp1
for hr.copy_emp;
- -将访问同义词的权限给所有用户
grant select on hrcemp1 to public;
- -权限控制:
- - schema是表、视图、序列等对象的集合。
- - schema被一个数据库用户拥有并且和用户有相同的名字。
- -以下操作由sys完成
- -创建新用户
create user demo
identified by demo;
- -授予demo登录到数据库的权限
grant create session
to demo;
- -授予demo创建各种对象的权限
grant create table, create view, create sequence,
create procedure
to demo;
- -为了简化权限管理,使用角色
- - 1、创建角色
create role manager;
- - 2、给角色授权
grant create table, create view
to manager;
- - 3、将角色授给用户,那么用户就拥有了角色拥有的权限
grant manager
to demo;
- -修改自己的口令
- -由hr操作
alter user hr
identified by hr;
- -授予对象权限给其它用户
- -将hr的dept80表的查询权限授给demo
grant select
on dept80
to demo;
- -将hr的dept80表的last_name和annsal列的修改权限授给demo
grant update(last_name,annsal)
on dept80
to demo;
- -授权时使用 with grant option选项
- -它的含义是:允许被授权的用户继续将同样的权限授给其它用户
- -以下由sys操作
create user demo1
identified by demo1;
grant create session
to demo1;
- -以下由hr操作
grant select
on dept80
to demo
with grant option;
- -以下由demo操作
grant select
on hr.dept80
to demo1
with grant option;
- - - -以下由hr操作
- -查询数据字典得到有关权限的信息
DESC role_sys_privs;
- -查看角色具备的系统权限
select *
from role_sys_privs;
- -查看自己把哪些对象的访问权限授给了哪些用户
select *
from user_tab_privs_made;
- -查看其它用户把哪些对象的访问权限授给了自己
select *
from user_tab_privs_recd;
- -废除对象权限
revoke select
on dept80
from demo;
- -注意:demo通过传递授权授给demo1的访问hr的dept80
- -表的权限同时被废除。
- -系统权限也可以进行传递授权,使用 with admin option选项
- -废除系统权限时,通过传递授权授予的系统权限不会被同时废除
- -管理 schema对象
- -使用 alter table语句添加、修改、删除列
- - 1、 alter table... add:
- -给表添加一个新的列
- - 1)不能指定列要出现在哪里。新列成为最后的列。
- - 2)当添加列时,如果表中已经包含了行,对所有的行来讲,
- -新的列被初始化为 null,或者赋予默认值。
- -给dept80表添加一个job_id列
alter table dept80
add (job_id varchar2( 9));
alter table dept80
add (job_id1 varchar2( 9) default 'abc');
- - 2、 alter table... modify:
- -修改表中已有列的定义
- -列修改包括改变列的数据类型、大小和默认值。
- -可增加数字列的宽度或精度
- -可增加字符列的宽度
- -可减少列的宽度,如果:
- - 列只包含 null值
- - 表没有行
- - 减少列宽度不少于列中已有的值
- -如果列中只包含 null值可改变数据类型。
- -列的默认值的改变只影响后续插入的行
- -将dept80表的last_name列的宽度增加到 30
alter table dept80
modify (last_name varchar2( 30));
- -将dept80表的last_name列的宽度减少到 20
alter table dept80
modify (last_name varchar2( 20));
- -将dept80表的last_name列的宽度减少到 8
- - SQL 错误: ORA - 01441: 无法减小列长度, 因为一些值过大
alter table dept80
modify (last_name varchar2( 8));
- -将dept80表的annsal列的宽度减少到 10
- - SQL 错误: ORA - 01440: 要减小精度或标度, 则要修改的列必须为空
alter table dept80
modify (annsal number( 10));
- - 3、 alter table... drop或者 alter table... drop column
- -使用带 DROP COLUMN子句的 ALTER TABLE语句可以删除一个列。
- -列可以包含或不包含数据。
- -一次只删除一列
- -表必须至少还留一列
- -列被删除后不能被恢复。
- -列不能被删除,如果它是约束或索引键的一部分,除非增加了级联选项
- -删除列要花一点时间,如果列有大量的值。在这种情况下,
- -最好把列设置为未使用的,然后在系统用户较少时再删除
alter table dept80
drop column job_id1;
- -将列标记为未使用的
- -未使用的列作为被删除的列对待,即使列的数据还保留在表行中。
- -在列被标记为未使用的之后,你不能访问那个列。
- - SELECT *查询不从未使用的列中检索数据。
- -在 DESCRIBE命令执行时,未使用的列的名字和类型不被显示,
- -并且你可以给表添加一个新列,其名字和未使用的列一样。
- - SET UNUSED 信息存储在USER_UNUSED_COL_TABS视图中。
- -特别注意:该操作没有可逆操作
alter table dept80
set unused (job_id);
- -查看未使用的列的信息
select *
from user_unused_col_tabs;
- -删除未使用的列
alter table dept80
drop unused columns;
- -使用 alter table语句管理约束:
- -可以添加、删除、启用或禁用一个约束,但是不能修改约束
- -通过使用 MODIFY子句,可以给一个已有的列添加 NOT NULL约束
- -创建emp2表,先添加主键约束,再添加外键约束
create table emp2
as select * from employees;
alter table emp2
add constraint emp2_empid_pk
primary key(employee_id);
alter table emp2
add constraint emp2_managerid_fk
foreign key(manager_id)
references emp2(employee_id);
- -使用延迟约束特性
- -默认情况下,约束都是立即检查的:即,当dml语句一执行完毕,
- -oracle会立即检查操作结果是否违反约束,如果违反则抛出异常。
- -可以把约束检查的时间推迟到事务提交的时候再检查,这叫做
- -延迟约束检查。
- -和延迟约束有关的关键字:
- - DEFERRABLE(可延迟的)、
- - INITIALLY DEFERRED(初始化延迟的) 、
- - INITIALLY IMMEDIATE(初始化立即的),这是默认值
- -后面的两个值是配合 DEFERRABLE用的
create table emp_new_sal(
salary number constraint sal_ck
check(salary > 100)
deferrable initially immediate,
bonus number constraint bonus_ck
check(bonus > 0)
deferrable initially deferred
);
- -测试
- - SQL 错误: ORA - 02290: 违反检查约束条件 (HR.SAL_CK)
INSERT INTO emp_new_sal VALUES( 90, 5);
- - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 110, - 1);
- - SQL 错误: ORA - 02091: 事务处理已回退
- -ORA - 02290: 违反检查约束条件 (HR.BONUS_CK)
commit;
- -设置 DEFERRED状态给所有约束。
SET CONSTRAINTS ALL DEFERRED;
- - - - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 90, 5);
- - 1 行已插入。
INSERT INTO emp_new_sal VALUES( 110, - 1);
- - SQL 错误: ORA - 02091: 事务处理已回退
- -ORA - 02290: 违反检查约束条件 (HR.SAL_CK)
commit;
- -禁用约束
- -通过使用带DISABLE子句的 ALTER TABLE语句禁用一个约束而不删除它
- -语法:
- - ALTER TABLE table
- - DISABLE CONSTRAINT constraint [ CASCADE];
- -禁用emp2表的外键约束
alter table emp2
disable constraint emp2_managerid_fk;
- -通过使用带ENABLE子句的 ALTER TABLE语句启用一个约束而不删除它
- -语法:
- - ALTER TABLE table
- -ENABLE CONSTRAINT constraint;
- -启用emp2表的外键约束
alter table emp2
enable constraint emp2_managerid_fk;
- - - -禁用emp2表的主键键约束
- - SQL 错误: ORA - 02297: 无法禁用约束条件 (HR.EMP2_EMPID_PK) - 存在相关性
alter table emp2
disable constraint emp2_empid_pk;
alter table emp2
disable constraint emp2_empid_pk cascade;
alter table emp2
enable constraint emp2_empid_pk;
- -禁用主键约束。观察主键索引被自动删除
- -注意:当禁用主键或者唯一键约束时,为这些约束自动创建的索引
- -会被自动删除。当启用主键或者唯一键约束时,Oracle会自动
- -为其创建索引
alter table emp2
disable constraint emp2_empid_pk;
alter table emp2
enable constraint emp2_empid_pk;
- -如果启用一个约束,约束应用到表中的所有数据。
- -删除约束
- -根据名字来删
alter table emp2
drop constraint SYS_C0013878;
- - CASCADE CONSTRAINTS选项
- -该选项专门配合 drop column子句使用。
- -当删除以下列时,需要带上该选项:
- - 1)被外键引用的主键列
- - 2)被删除的列被一个多列约束引用
- -使用 alter table语句将列改名字
alter table emp2
rename column phone_number
to phone;
- -使用 alter table语句将约束改名字
alter table emp2
rename constraint SYS_C0013876
to emp2_email_nn;
- -在 create table语句中使用 using index选项可以指定
- -主键约束使用指定的索引而不是oracle自动创建的索引
create table new_emp(
employee_id number( 6)
primary key using index(
create index emp_id_idx on new_emp(employee_id)
),
fname varchar2( 20),
lname varchar2( 20)
);
- -禁用主键约束。观察索引未被删除
alter table new_emp
disable constraint SYS_C0013883;
- -启用主键约束。观察索引未变化
alter table new_emp
enable constraint SYS_C0013883;
drop table new_emp;
- -指定主键使用唯一索引
create table new_emp(
employee_id number( 6)
primary key using index(
create unique index emp_id_idx on new_emp(employee_id)
),
fname varchar2( 20),
lname varchar2( 20)
);
- -禁用主键约束。观察索引被删除
alter table new_emp
disable constraint SYS_C0013884;
- -启用主键约束。观察索引被自动创建,名字和约束同名
alter table new_emp
enable constraint SYS_C0013884;
- -使用基于函数的索引
- -如果在查询的 where条件中,经常要对一个函数值进行比较,
- -那么可以在该函数上建一个索引
create table dept2
as select * from departments;
create index dept2_dname_idx
on dept2(department_name);
- -以下查询不走上面的索引
select *
from dept2
where upper(department_name) = 'SALES';
- -创建基于函数的索引
create index dept2_upperdname_idx
on dept2( upper(department_name));
- -以下查询走上面的索引
select *
from dept2
where upper(department_name) = 'SALES';
- -使用闪回操作
- -Oracle为删除表提供了一个特色。当你删除一个表时,
- -数据库不立即释放和表相关的空间。相反,数据库将表改名
- -并将它放入回收站中,以便以后如果你发现误删了,
- -可以用FLASHBACK TABLE 语句恢复。
- -如果你想当发出 DROP TABLE语句时,立即释放和表相关的空间,
- -就包含PURGE 子句。
- -删除表emp2
drop table emp2;
- -查询回收站
select *
from recyclebin;
- -从回收站站中恢复误删的表
flashback table emp2 to before drop;
- -彻底删除表emp2
drop table emp2 purge;
- - SQL 错误: ORA - 38305: 对象不在回收站中
flashback table emp2 to before drop;
- -清空回收站
purge recyclebin;
- -查询回收站
select *
from recyclebin;
- -第二种闪回操作
- -将某个表的内容恢复到事务提交之前的状态
commit;
- - - -以sys查询数据库最新的scn: 5716081
select current_scn
from v$ database;
delete from dept2;
commit;
- -将dept2中的内容闪回到事务提交之前
- - SQL 错误: ORA - 08189: 因为未启用行移动功能, 不能闪回表
flashback table dept2
to scn 5716081;
- -启用行移动功能
alter table dept2 enable row movement;
flashback table dept2
to scn 5716081;
select scn_to_timestamp( 5716081)
from dual;
flashback table dept2
to timestamp
to_timestamp( '2014-06-20 14:23:52', 'yyyy-mm-dd hh24:mi:ss');
- -创建使用临时表
- -临时表是一个表,它的数据只在事务或会话期间存在。
- -在临时表中的数据对于会话是私有的,这意味着每个会话只
- -能看见和修改它自己的数据。
- -可以为临时表创建索引。索引也是临时的。
- -也可以在临时表上创建视图或触发器。
- -临时表和它们的索引在创建时不自动分配段。
- -当数据第一次被插入时才分配临时段。
- -临时表有两种:事务级的和会话级的
- - 1、 SESSION级临时表
create global temporary table today_sales(sales number)
on commit preserve rows;
insert into today_sales
values( 10000);
commit; - -提交事务
- -可以看到行还在临时表中
select * from today_sales;
- -断开连接,重新登录,再次查询,数据消失
select * from today_sales;
- - 2、事务级临时表
create global temporary table cart(sales number)
on commit delete rows;
insert into cart
values( 10000);
- -可以看到行还在临时表中
select * from cart;
commit; - -提交事务
- -可以看到行已被删除
select * from cart;
- -创建使用外部表
- -外部表实际上是Oracle的一个数据加载( load)和卸载(unload)工具
- -外部表是一个只读的表,它的元数据存储在数据库中,
- -但是它的数据存储在数据库外面(操作系统中)。
- -外部表和普通表的主要区别是外部表是只读的。因此,
- -不能在外部表上执行DML操作,并且不能在外部表上创建索引。
- -Oracle服务器为外部表提供了两种访问驱动。
- -一个是加载器访问驱动(名字叫ORACLE_LOADER),只能
- -用来做数据导入;
- -另一个ORACLE_DATAPUMP访问驱动,既可用来导入数据,
- -又可用来导出数据,使用一种跨平台的格式。
- -要使用外部表,首先在操作系统中准备好目录和文件(用来加载数据)
- -然后在oracle中创建目录对象
- -注意:普通用户没有创建目录的权限,可以由sys
- -先授权: grant create any directory to hr;
- -注意:路径是绝对的
create or replace directory emp_dir
as 'D:\emp_dir';
- -创建外部表
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
nobadfile
nologfile
fields terminated by ','(
fname position ( 1: 20) char,
lname position ( 22: 41) char
)
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -修改上列:
drop table oldemp;
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
nobadfile
nologfile
fields terminated by ','
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -修改上列:
drop table oldemp;
create table oldemp(
fname varchar2( 25),lname varchar2( 25)
) organization external(
type oracle_loader
default directory emp_dir
access parameters(
records delimited by newline
fields terminated by ','
)
location ( 'emp.dat')
)
parallel 5
reject limit 200;
select * from oldemp;
- -使用oracle_datapump驱动做数据加载和卸载
- - 1、将employees表的所有数据导出到操作系统文件中
create table emp_ext(
employee_id,first_name,last_name
) organization external(
type oracle_datapump
default directory emp_dir
location ( 'emp1.exp', 'emp2.exp')
)
parallel 2
as select employee_id,first_name,last_name
from employees;
- -数据加载
drop table import_emp;
create table import_emp
( employee_id number( 6),
first_name varchar2( 20),
last_name varchar2( 25)
)
organization external
( type oracle_datapump
default directory emp_dir
location ( 'emp1.exp', 'emp2.exp')
);
select * from import_emp;
- -使用数据字典
- -数据字典是由Oracle服务器创建和维护的表和视图的集合,
- -它们包含有关数据库的信息。对所有用户来说它们是重要的工具
- -因为数据字典是只读的,你只能查询
- -可以把 dictionary视图作为使用数据字典视图的入口点。
- -它包含了所有可用视图的名字以及简单的解释
select *
from dictionary;
- -查询user_objects视图得到自己的所有对象的信息
desc user_objects;
select object_name,object_type,created,status
from user_objects
order by object_type;
- -专门查询自己的表的信息
desc user_tables;
select table_name
from user_tables;
- -查询表列的信息
desc user_tab_columns;
select column_name,data_type,data_length,
DATA_PRECISION,DATA_SCALE, NULLABLE
from user_tab_columns
where table_name = 'EMPLOYEES';
- -查看约束的信息: 2个视图
- -user_constraints: 包含表的所有约束定义
- -user_cons_columns:约束定义的列的信息
desc user_constraints;
select constraint_name,constraint_type,
search_condition,r_constraint_name,
delete_rule,status
from user_constraints
where table_name = 'EMPLOYEES';
- -查询employees表中每个约束都定义在哪些列上
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMPLOYEES';