创建表空间

1 create tablespace caoke datafile 'D:\caoke.ora' size 100M ;

2 create tablespace test datafile 'c:oracleoradataorcl9test.dbf' size 50M 
default storage (initial 500K Next 500K minextents 1 maxextents unlimited pctincrease 0);

3 create tablespacetbs_namedatafile '/dba/oradata/ORADEV/datafile/tbs_name01.dbf' 
    size 100m autoextend on next 100m;

创建用户

create user caoke123 
       identified by caoke123 
       default tablespace caoke quota 50M on users ;

quota 配额 ，限制用户对表空间的使用
比如你限制用户a在tablespace aa中的quota为10m，当用户a在tablespace aa中的数据量达到10m后，
无论你的tablespace aa中有多少空间，a都无法再使用tablespace aa了。

授权

1、首先，grant XXX to user;,grant是授权的作用，这里的XXX可以是一个角色role，也可以是权限，例如grant role to user;，或grant insert on table to user;。

MOS中给出的标准sql语句：

创建角色：
create role <role name> [IDENTIFIED BY <password>/USING <package>/EXTERNALLY/GLOBALLY ];

赋予角色权限：
grant <object/system privilege> to <role name>;

从角色收回权限：
revoke <privilege> from <role name>;

将角色赋予另一个角色或用户：
grant <role> to <username or role> ;

2、其次，connect和resource是两个系统内置的角色，和dba是并列的关系。

参考一些帖子的说法，权限可以分为两类：

系统权限：系统规定用户使用数据库的权限。(系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。(是针对表或视图而言的)。

接下来看系统权限：

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect,resource权限。

对于DBA管理用户：授予connect，resource,dba权限。

且系统权限只能由DBA用户授出：sys,system(最开始只能是这两个用户)。 普通用户通过授权可以具有与system相同的用户权限， 但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。

另外，对于WITH ADMIN OPTION级联授权的问题，

1）如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限。

2）系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。

3、证明下为什么resource和connect的角色不能创建视图。

sql> select role,count(*) from role_sys_privs group by role;
ROLE                  COUNT(*)
------------------------------ ----------
EXP_FULL_DATABASE        8
DBA                     160
SCHEDULER_ADMIN          6
RESOURCE          8
IMP_FULL_DATABASE      68
CONNECT          1
可以看到RESOURCE和CONNECT具有的权限个数。
下面看看各自都有什么权限：
sql> select grantee,privilege from dba_sys_privswhere grantee='RESOURCE' order by privilege;
GRANTEE               PRIVILEGE
------------------------------ ----------------------------------------
RESOURCE              CREATE CLUSTER
RESOURCE              CREATE INDEXTYPE
RESOURCE              CREATE OPERATOR
RESOURCE              CREATE 'PROCEDURE'(代码格式会乱，加个引号，这个词的问题)
RESOURCE              CREATE SEQUENCE            
RESOURCE              CREATE TABLE                   
RESOURCE              CREATE TRIGGER
RESOURCE              CREATE TYPE
8 rows selected    

sql> select grantee,privilege from dba_sys_privs where grantee='CONNECT' order by privilege;

GRANTEE               PRIVILEGE
------------------------------ ----------------------------------------
CONNECT               CREATE SESSION

结果不言自明了，

CREATE VIEW权限并不在这两个角色中，因此需要额外grant CREATE VIEW to user;，才能让这用户可以创建视图。往往实验是最好的老师，这次又证明了这点。

grant all privileges to caoke123 ;
grant dba to caoke123;--授予DBA权限
grant unlimited tablespace to caoke123;--授予不限制的表空间 
grant select any table to caoke123;--授予查询任何表 
grant select any dictionary to caoke123;--授予 查询 任何字典
grant create session,create table to zzg; --给用户赋权(多个采用逗号间隔) 
oracle 正在连接的用户不能删除，确实要删除的话 
　　1、select sid,serial#,username from v$session where user='USERNAME';
　　2、alter system kill session 'sid,serial#';
　　3、drop user username cascade;

查询

查询用户相关信息

查询当前用户下有多少表

select * from tab ;
select * from tabs ;
select * from user_tables ;

查看用户所拥有的视图 

select view_name from user_views ;

查看用户所拥有的触发器 

select trigger_name from user_triggers ;

查看用户拥有的序列 

select sequence_name from user_sequence;

查看用户拥有的索引

 select index_name from user_indexs;

显示当前用户    

  show user;

修改用户密码

 alter user zzg identified by zzg123;


给用户加锁,解锁

 alter user scott account lock;

 alter user scott account unlock;

删除用户及相关对象

 drop user zzg cascade;

当前用户所具有的权限

  select * from session_privs;

表空间相关

查看当前数据库的全称

    select * from global_name ;

查看当前数据库的用户信息

    select * from dba_users ;

分配表空间给用户

    alter user zzg default tablespace ts_zzg;

查询用户的配额

    select tablespace_name,username,max_bytes from  DBA_TS_QUOTAS ;

用户在其他表空间的操作权限

    select * from user_ts_quotas;

查看用户所具有的系统权限

    select * from dba_sys_privs where grantee = '用户名称';

查看用户所具有的角色

    select * from dba_roles ;
    select * from dba_role_privs where grantee = '用户名称';

查询当前用户可以访问的所有数据字典视图

    select * from dict where comments like '%grant%' ;

查询数据库中存在的表空间

    select * from dba_tablespaces ;

查询表空间名下所有的表名

    select * from all_tables where tablespace_name = '' ;

查询表属于哪个表空间

    select * from user_tables where table_name = '' ;

查看表空间剩余

SELECT SUM(bytes) / (1024 * 1024) AS free_space,tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name;   


SELECT a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes * 100) / a.bytes "% USED ",(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

查询表空间的用量

SELECT a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小",total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)",(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",round((total - free) / total,4) * 100 "使用率 %" 
FROM (SELECT tablespace_name,SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a,(SELECT tablespace_name,SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name ;

修改表空间的状态

显示表空间的信息

select * from dba_tablespaces ;

查询表空间所包含的数据文件，数据保存地址

select * from dba_data_files ;

使表空间脱机

Alter tablespace 表空间名 offline ;

使表空间联机

Alter tablespace 表空间名 online ;

只读表空间 如果不希望对表空间进行insert,update,delete操作，可以将表空间修改为只读

Alter tablespace 表空间名 read only ;

使表空间可读可写

Alter tablespace 表空间名 read write ;

删除表空间

由特权用户或者dba来操作，如果是其他用户，要求有 drop tablespace 权限

drop tablespace  表空间名  including contents and datafiles
Including contents 表示删除表空间的时候删除该空间的所有数据对象，而datafiles表示将数据库文件也删除。

增加表空间

1 增加数据文件
Alter tablespace 表空间名add datafile ‘d:/aaa.ora’ size 20M

2 修改数据文件的大小
Alter tablespace 表空间名 ‘d:/aaa.ora’  resize 20m

3 设置文件的自动增长
Alter tablespace 表空间名 ‘d:/aaa.ora’ autoextend on next 10m maxsize 500m ;