创建Oracle synonym 详解

 

--创建使用同义词

--同义词就是给表、视图等对象取得别名，用于简化对其的访问

 

--分为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语句添加、修改、删除列

 

  bonus number constraint bonus_ck 

--给表添加一个新的列

--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; 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';