20150828 --Oracle的学习笔记

Oracle数据库的物理结构

物理文件:数据文件(.dbf 信息的物理存储位置),控制文件(.CTL 数据库的控制信息),日志文件(.log记录对数据库所有修改信息,用于故障恢复),参数文件(.ora 数据库启动时读取的属性).

数据字典

1.在数据库创建的时候,Oracle服务器会在数据文件里创建一些额外的对象结构,数据字典包括:数据字典表和动态性能表.
2.数据字典是一套记录,验证和提供数据库房屋信息的只读表和视图.描述了数据库及其对象,包含两类对象:基表-储存数据库的说明由,数据字典视图-基表信息的摘要.
3.数据字典提供如下信息:

• 数据库的逻辑和物理结构
• 对象的定义和空间分配
• 完整性约束
• 用户
• 角色
• 权限
• 审计

• 其他信息
  4.数据字典视图具有三个主要用途:

• Oracle服务器借助他查找关于如下对象的信息: 用户,方案对象,存储结构

• 执行DDL语句后,Oracle服务器修改它

• 用户和DBA可以把它作为数据库的只读的信息参考
  5.数据字典视图的分类

• DBA:所有方案的对象(数据库中的所有对象)

• ALL:用户所能访问的所有对象(当前用户可以访问的对象)
• USER:在用户方案中的对象(当前用户拥有的对象)

标题

Oracle是通过用户去管理数据. 我们要存储数据,必须先建表空间,再建用户,再建对象(表,索引,视图……)
1.创建表空间.表空间:一个或多个数据文件的逻辑集合.
2.表空间分为:
- system表空间 :在数据库创建时创建,包含数据字典,system回滚段
- non -system表空间:独立的段,简化空间管理,控制给用户分配的空间
- 临时表空间:用于临时操作,不能包含任何永久对象

Oracle删除用户和表空间

Oracle 使用时间长了， 新增了许多user 和tablespace. 需要清理一下
对于单个user和tablespace 来说， 可以使用如下命令来完成。
步骤一： 删除user
drop user ×× cascade
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。
步骤二： 删除tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

但是，因为是供开发环境来使用的db, 需要清理的user 和 table space 很多。
思路：
Export出DB中所有的user和tablespace, 筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。
然后写例程循环，把不在有用表的tablespace删掉
1. select username,default_tablespace from dba_users;
2.
create table MTUSEFULSPACE
(
ID Number(4) NOT NULL PRIMARY KEY,
USERNAME varchar2(30),
TABLESPACENAME varchar2(60),
OWNERNAME varchar2(30)
);
3.
declare icount number(2);
tempspace varchar2(60);
begin
for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)
loop
tempspace :=curTable.alltblspace;
dbms_output.put_line(tempspace);
select count(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
if icount=0 then
DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;
end if;
commit;
end loop;
end;

执行后会报如下错误
ORA-06550: 第 10 行, 第 5 列:
PLS-00103: 出现符号 “DROP”在需要下列之一时：
begin case declare exit
for goto if loop mod null pragma raise return select update
while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
06550. 00000 - “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:

好像是被锁了。。

没办法，例程不能写，就只能组出语句执行了。
把需要删除的user, tablespace 导出到Excel. 使用CONCATENATE 组出SQL.
贴到SQLdevelop 批量执行。

整个删除会比较耗时间， 100多个user. 用了12个小时左右。

如要找datafile的具体位置，可以使用
select t1.name,t2.name from vtablespacet1,vdatafile t2 where t1.ts# = t2.ts#;

SQL code
–删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
–删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
–删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
–删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
–如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

Oracle用户权限管理方法

sys;//系统管理员，拥有最高权限
system;//本地管理员，次高权限
scott;//普通用户，密码默认为tiger,默认未解锁
二、登陆

sqlplus / as sysdba;//登陆sys帐户
sqlplus sys as sysdba;//同上
sqlplus scott/tiger;//登陆普通用户scott

三、管理用户

create user zhangsan;//在管理员帐户下，创建用户zhangsan
alert user scott identified by tiger;//修改密码

四，授予权限
1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限

/管理员授权/
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
grant unlimited session to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权

/oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的/
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

五、撤销权限

基本语法同grant,关键字为revoke

六、查看权限

select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限

七、操作表的用户的表

/需要在表名前加上用户名，如下/
select * from zhangsan.tablename

八、权限传递
即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

grant alert table on tablename to zhangsan with admin option;//关键字 with admin option
grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似

九、角色
角色即权限的集合，可以把一个角色授予给用户

create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
/但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字/