oracle创建用户和角色、管理授权以及表空间操作

show user 显示当前用户
connect username/password@datebasename as sysdba 切换用户和数据库 和用户身份
Oracle登录身份有三种:
normal普通身份
sysdba系统管理员身份
sysoper系统操作员身份
创建永久表空间
create tablespace tablespace_name datafile '存储路径.dbf' size 10m aotuextend on next 10m;
创建表空间:create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;
自动扩展大小:create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;
设置最大空间:create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;

更改用户默认表空间:alter database default tablespace ts1;
表空间改名:alter tablespace ts1 rename to tss1;
删除表空间:drop tablespace ts2 including contents and datafiles;
创建临时表空间
create temporary tablespace temptablespace_name tempfile '存储路径.dbf' size 10m aotuextend on next 10m;
查询表空间存储路径
desc dba_data_files
select file_name from dba_data_files where tablespace_name='大写的表空间名称';

创建用户
create user user_name identified by password
default tablespace tablespace_name;----创建用户并指定默认表空间
如果想要修改用户的永久表空间可以执行命令:
alter user user default tablespace tablespaceName,
其中第二个user为要操作的用户,tablespaceName为将要设置的默认表空间名称。

如果想修改新添加的用户的默认表空间可以执行如下命名
alter database default tablespace tablespaceName,
这样新建立的用户的默认表空间就为tablespaceName。
修改用户密码
alter user 用户名 identified by 口令[改变的口令]
删除用户
drop user 用户名;
若用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象,然后再删除用户。
语法: drop user 用户名 cascade;

alter user USERNAME quota 100M on TABLESPACENAME;
alter user USERNAME quota unlimited on TABLESPACENAME;
grant unlimited tablespace to USERNAME;
quota是为了限制用户对表空间的使用,比如你限制用户Guotu在tablespace CYYD中的quota为10m,
当用户Guotu在tablespace CYYD中的数据量达到10m后,无论你的tablespace CYYD中有多少空间,Guotu都无法再使用tablespace CYYD了。

授权角色
oracle为兼容以前版本,提供三种标准角色(role):connect/resource和dba.
1》. connect role(连接角色)
--临时用户,特指不需要建表的用户,通常只赋予他们connect role.
--connect是使用oracle简单权限,这种权限只对其他用户的表有访问权限,包括select/insert/update和delete等。
--拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)和其他 数据的链(link)

2》. resource role(资源角色)
--更可靠和正式的数据库用户可以授予resource role。
--resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

3》. dba role(数据库管理员角色)
--dba role拥有所有的系统权限
--包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有
授权命令
grant connect, resource to 用户名;
撤销权限
revoke connect, resource from 用户名;
授权类型:
grant create session to 用户名;
grant create table to 用户名;
grant create tablespace to 用户名;
grant create view to 用户名;
grant connect,resource to demo;
grant connect,resource to demo;
grant create any sequence to demo;
grant create any table to demo;
grant delete any table to demo;
grant insert any table to demo;
grant select any table to demo;
grant unlimited tablespace to demo;
grant execute any procedure to demo;
grant update any table to demo;
grant create any view to demo;
创建/授权/删除角色
除了前面讲到的三种系统角色----connect、resource和dba,用户还可以在oracle创建自己的role。
用户创建的role可以由表或系统权限或两者的组合构成。为了创建role,用户必须具有create role系统权限。
1》创建角色
语法: create role 角色名;
例子: create role testRole;
2》授权角色
语法: grant select on class to 角色名;
列子: grant select on class to testRole;

注:现在,拥有testRole角色的所有用户都具有对class表的select查询权限
3》删除角色
语法: drop role 角色名;
例子: drop role testRole;

注:与testRole角色相关的权限将从数据库全部删除

oracle添加主键的四种方法:
列级,表级建立主键
1.create table constraint_test
( name_id number not null constraint cons_name_id primary key,
 old number )
2.create table constraint_test
( name_id number  primary key,
 old number )
3.create table constraint_test

name_id number not null,
old number  ,
constraint cons_name_id primary key ( name_id ) 
);
4.create table constraint_test

name_id number not null,
old number   
);
alter table constraint_test add constraint cons_name_id primary key ( name_id );

复制表结构和表数据
create table new_table_name as select * from old_table_name;
只复制表结构
create table table_name_new as select * from table_name_old where 1=2;
或者:
create table table_name_new like table_name_old;
只复制表数据
如果两个表结构一样:
insert into table_name_new select * from table_name_old;

如果两个表结构不一样:
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;
nvl函数
格式为:nvl(string1, replace_with)   
功能:如果string1为null,则nvl函数返回replace_with的值,否则返回string1的值。  

注意事项:string1和replace_with必须为同一数据类型,除非显示的使用to_char函数。

如何使用like操作符 %:表示0到多个字符 _:表示任意单个字符 问题:如何显示首字符为s的员工姓名和工资?
select ename,sal from emp where ename like 's%';
问题:如何显示工资在2000到3000的员工?
select ename,sal from emp where sal>=2000 and sal<=3000;

select ename,sal from emp where sal between 2000 and 3000;

In和exists对比:
Exists只能用于子查询,可以替代in,若匹配到结果,则退出内部查询,并将条件标志为true,传回全部结果资料,
in不管匹配到匹配不到都全部匹配完毕,使用exists可以将子查询结果定为常量,不影响查询效果,而且效率高。

若子查询结果集比较小,优先使用in,若外层查询比子查询小,优先使用exists。
因为若用in,则oracle会优先查询子查询,然后匹配外层查询,
若使用exists,则oracle会优先查询外层表,然后再与内层表匹配。
最优化匹配原则,拿最小记录匹配大记录。

一般来说,这两个是用来做两张(或更多)表联合查询用的,in是把外表和内表作hash 连接,而exists 是对外表作loop 循环,假设有A、B两个表,使用时是这样的:

1、select * from A where id in (select id from B)--使用in
2、select * from A where exists(select B.id from B where B.id=A.id)--使用exists
也可以完全不使用in和exists:

3、select A.* from A,B where A.id=B.id--不使用in和exists

具体使用时到底选择哪一个,主要考虑查询效率问题:

第一条语句使用了A表的索引;

第二条语句使用了B表的索引;

第三条语句同时使用了A表、B表的索引;

如果A、B表的数据量不大,那么这三个语句执行效率几乎无差别;

如果A表大,B表小,显然第一条语句效率更高,反之,则第二条语句效率更高;

第三条语句尽管同时使用了A表、B表的索引,单扫描次数是笛卡尔乘积,效率最差。

上一篇:[转载]Oracle创建用户、角色、授权、建表


下一篇:Linux中Shell