Oracle数据库相应的系统权限和对象权限介绍

Oracle数据库相应的系统权限介绍

系统权限：系统权限允许用户执行一个或一类特殊的数据库操作。

对象权限：用户维护数据库对象的权限，如表、视图、函数。

与索引相关的系统权限

create any index; –创建任何模式下对象的索引

alter any index;

drop any index;

与表相关的系统权限

create table;

create/delete/drop/select/alter/update any table;

与会话相关的系统权限

create session;

alter session;

与表空间相关的系统权限

create tablespace;

alter tablespace;

drop tablespace;

unlimited tablespace; –允许使用所有表空间

授予用户系统权限

grant 系统权限 to user|role|public –所有用户

with admin option; –加上这句代表可以被授权的用户可以给其他用户授权。

回收用户系统权限

revoke 系统权限 from user|role|public;

授予对象权限

grant alter on table|sequence|procedure to user with grant option; –被授权的用户可以给其他用户授权

grant all on table|sequence|procedure to user; –将当前用户的某个数据库对象的所有权限赋予新用户。

garnt update(column1,column2) on table to user; –更新某列的权限

grant select|update|delete on table|view to user;

grant execute on procedure to user;

回收对象权限

revoke 对象权限 on 对象 from user;

视图

视图是一种虚表，它不存数据。

授予用户创建视图的权限

grant create view to user;

创建视图

create or replace force|noforce view view_name –replace 如果视图存在，则重建。force 不论基表是否存在，强制建立视图。

as

select * from aa

with check option|with read only;

create or replace view view_name –replace 如果视图存在，则重建

(“column1″,”column2”)

as

select column1,column3 from aa;

视图的执行过程

1、首选读取数据字典，获得视图的定义，找到视图引用的表。

2、从数据字典中查看当前用户对于表的引用权限。

3、执行视图SQL语句。

删除视图

drop view view_name;

物化视图

与普通视图相反，物化视图会物理存储数据，占用存储空间，可以进行分区和创建索引。

查询重写:重写SQL语句，该参数和SQL语句优化有很大关系。

show parameter query_rewrite;

物化视图同步

物化视图刷新方式：on commit(基表变化提交时，自动刷新)，on demand(需要手工同步，手动执行dbms_mview.refresh);

物化视图刷新类型

complete:无论基表中修改了多少数据，重新执行下创建视图的SQL语句。

fast：只同步变化的数据。

force：先使用fast，如果失败再使用complete

never:从不更新。

dbms_mview.refresh(‘物化视图名’, ‘F’) –快速刷新，也就是增量刷新

dbms_mview.refresh(‘物化视图名’, ‘C’)–完全刷新

创建物化视图

授权

grant create materialized view to scott;

grant query rewrite to scott;

创建基于基表的物化视图日志

create materialized view log on table_name;

创建物化视图

create materialized view view_name

build immediate|deffered –立即创建|延迟创建

refresh fast on commit –有可能会报没有主键的错，在这里加上 with rowid

enable query rewrite

as

select * from aa;

删除物化视图

drop materialized view view_name;

序列号

序列号是oracle使用序列生成器自动产生用户可以在事务中使用的唯一序号

1、序列号是独立于表的对象，由oracle自动维护。

2、序列号可以由多个用户共享使用。

3、在SQL语句中使用序列号就可以使用它产生的序列号。

创建序列号

create sequence sequence_name

start with n — 从n开始

increment by m –序列号的增长幅度，默认为1

maxvalue p|nomaxvalue –定义序列号最大值

minvalue p|nominvalue

cache q|nocache –预先分配q个序列号在内存中

cycle r|nocycle –循环

sequence_name.currval –当前序列值

sequence_name.nextval –下一序列值

序号列不可逆，如果先增加后删除，则序号列会不连续。

修改序列号

alter sequence sequence_name

increment by m

maxvalue p|nomaxvalue

minvalue p|nominvalue

cache q|nocache

cycle r|nocycle;

删除序列号

dop sequence sequence_name;

同义词

公有同义词所有用户都可以使用，私有同义词只有指定的用户才可以使用。

创建公有同义词

create public synonym 同义词 for 表名;

创建私有同义词

create synonym 同义词 for 表名;

删除同义词

drop public synonym 同义词;

drop synonym 同义词;

切换用户模式

alter session set current_schema=scott;

数据库闪回

闪回数据库必须位于归档模式

archive log list; –查看归档模式

如果不是归档模式，先关闭数据库，然后启动到mount 状态,更改为归档模式。

shutdown immediate;

startup mount;

alter database archivelog;

查看数据库闪回功能是否开启

select * from v$database;

启动数据库闪回功能

alter database flashback on; –mount 状态下

alter database flashback off; –mount 状态下 闪回日志会自动删除

alter tablespace tablespace_name flashback on; –mount 状态下

alter tablespace tablespace_name flashback off; –mount 状态下

查看默认归档位置

show parameter db_recovery_file_dest;

数据库可闪回的最远时间

show parameter db_flashback_retention_target;

修改数据库可闪回的最远时间 –计量单位为 分钟

alter system set db_flashback_retention_target=2880 scope=both;

查看数据库可闪回的最远时间

select * from v$flashback_database_log;

闪回数据库的5种方法 –mount 状态下

rman：flashback database to time=to_date(”,”);

rman：flashback database to scn=10000;

rman：flashback database to sqeuence=100;

sql:flashback database to scn=10000;

sql:flashback database to timestamp(sysdate-1/24);

闪回后可以先使数据库只读，验证数据后再打开数据。

alter database open read only;

alter database open resetlogs;

闪回删除

show parameter recyclebin;

alter system set recyclebin=on scope=both;

查看删除的表

show recyclebin;

select * from recyclebin;

闪回删除的表

flashback table a to before drop;

flashback table a to before drop rename to a1;

永久删除表

drop table a purge;

闪回表

将表中的数据闪回到某个时间点。

flashback table a to timestamp(sysdate-1/24);

改功能是使用需开启以下两个功能：undo以及行移动功能

show parameter undo;

alter system set undo_retention=86400 scope=spfile; –未必会保存86400

alter tablespace undo_space retention guarantee; –强制保存86400，没有新的空间会报错。

alter table a enable row movement;

闪回查询

select * from TEST as of timestamp sysdate-1/24;

select * from TEST as of SCN 1257245;

数据库当前scn查询

select current_scn from v$database;

select dbms_flashback.get_system_change_number from dual;