Oracle 11g R2 表空间、用户的新建与删除

Oracle 11g R2 表空间、用户的新建与删除

1、查询表空间位置

    SELECT tablespace_name,
    file_id,
    file_name,
    round(bytes / (1024 * 1024), 0) total_space
    FROM dba_data_files
    ORDER BY tablespace_name;

2、创建临时表空间

    create temporary tablespace TEST_TEMP
    tempfile '/u01/app/oracle/oradata/XE/TEST_TEMP.dbf'
    size 50m
    autoextend on
    next 50m maxsize 20480m
    extent management local;

3、创建数据表空间

    create tablespace TEST_DATA
    logging
    datafile '/u01/app/oracle/oradata/XE/TEST_DATA.dbf'
    size 50m
    autoextend on
    next 50m maxsize 20480m
    extent management local;

4、删除表空间(如有必要)

    -- drop tablespace TEST_TEMP including contents and datafiles;  
    -- drop tablespace TEST_DATA including contents and datafiles;  

5、创建用户并指定表空间

    create user TEST identified by TEST
    default tablespace TEST_DATA
    temporary tablespace TEST_TEMP;

6、给用户授予权限

    grant connect,resource,dba to TEST;

系统权限分类：

• DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
• RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
• CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

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

系统权限授权命令：

• 系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)
• 授权命令：

 grant connect, resource, dba to 用户名1 [,用户名2]...;

注:普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。

回收dba权限

• 查询具有DBA权限用户

    select * from dba_role_privs where granted_role='DBA';

• 取消用户DBA权限

revoke dba from user_Name

7、删除用户(TEST)

级联删除

drop user TEST cascade;

如果提示错误 ORA-01940:无法删除当前连接的用户

• 查看用户的连接状况

select username,sid,serial# from v$session where username = 'TEST'

	USERNAME	SID	SERIAL#
1	TEST	166	18950

• 根据 SID 和 SERIAL# 删除当前用户的连接

alter system kill session'166,18950'

• 再次删除

drop user TEST cascade;

如果依然提示 ORA-01940:无法删除当前已链接的用户，查询Session状态

select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

	SADDR	SID	SERIAL#	PADDR	USERNAME	STATUS
1	0000000311B8E6A8	6	27434	000000031191FCA8	TEST	INACTIVE
2	0000000314B49E18	9	46312	0000000311917928	TEST1	INACTIVE
3	0000000311B889C8	10	37874	0000000311923E68	TEST2	INACTIVE
4	0000000314B46FA8	11	9992	0000000311928028	TEST3	INACTIVE
5	0000000314B44138	13	58528	00000003119303A8	TEST4	INACTIVE

解锁锁定的用户

alter user 用户ID account unlock

注:执行以上语句的用户必须具有DBA权限