Oracle操作用户和表空间的总结

1. Oracle数据库的操作流程

首先我们要弄明白Oracle数据库的整个操作流程，如下图所示。

接下来对表空间以及用户的各项操作介绍都是需要建立在以下三步的基础上：

• 第1步：使用cmd命令打开DOS窗口。
• 第2步：输入命令: sqlplus /nolog ,进入oracle控制台。
• 第3步：输入conn 用户名/密码 sysdba 以DBA角色进入,提示连接成功。(注：此处用户必须有dba权限，如：sys)
• 备注：在操作过程中可以使用clear SCR进行清屏

2. 操作表空间

2.1 创建表空间

create tablespace dweb
logging 
datafile 'C:\Program Files\Oracle\Inventory\dweb.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;

2.2 删除表空间

drop tablespace ackj including contents and datafiles;

2.3 查看表空间使用

SELECT  a.tablespace_name 表空间名
       ,total 表空间大小
       ,free 表空间剩余大小
       ,(total-free) 表空间使用大小
       ,(total/(1024*1024*1024)) as 表空间大小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;

3. 操作用户

3.1 创建用户

在实际操作中，一般一个用户负责对应一个表空间，因此在创建用户的同时，需要赋予其所属表空间。

create user dweb identified by dweb default tablespace dweb;

3.2 删除用户

drop user dweb cascade;

3.3 修改密码

alter user dweb identified by 123456;

3.4 查看用户列表

select username from dba_users;
select * from all_users;

4. 用户授权

4.1 权限说明

4.2 用户授权

grant connect,resource,dba to dweb;
grant create any sequence to dweb;
grant create any table to dweb;
grant delete any table to dweb;
grant insert any table to dweb;
grant select any table to dweb;
grant unlimited tablespace to dweb;
grant execute any procedure to dweb;
grant update any table to dweb;
grant create any view to dweb;

5. 相关操作

--查看用户所属的表空间(用户名必须大写)
select username,default_tablespace from dba_users where username='DWEB';

--查看用户具有的表空间(用户名必须大写)
select * from dba_sys_privs where grantee='DWEB';

--Oracle删除指定用户所有表的方法(用户名必须大写)
select 'Drop table '||table_name||';' from all_tables
where owner='DWEB';

--获取当前用户下所有的表
select table_name from user_tables;

--删除某用户下所有的表数据
select 'truncate table  ' || table_name from user_tables;

--启用外键约束的命令
alter table table_name enable constraint constraint_name; 

--禁用外键约束的命令
alter table table_name disable constraint constraint_name;

--用SQL查出数据库中所以外键的约束名
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

--ORACLE启用外键和触发器
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop; 
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;
/ 
commit;

--禁用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop; 
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
 end loop;
 end;
 /
 commit;