oracle磁盘空间sql

（一）、表空间

计算表空间的剩余大小
select A.TABLESPACE_NAME,A.BYTES/(1024*1024*1024) "SPACE(G)",
C.BYTES/(1024*1024) "FREE SPACE(M)",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=C.TABLESPACE_NAME;
或者


select tablespace_name, sum(bytes)/(1024*1024*1024) "SPACE(G)"
from dba_free_space 
group by tablespace_name;
（二）、用户


计算每个用户占用的磁盘空间


select owner,sum(bytes)/1024/1024/1024 "Space(G)"
from dba_segments
group by owner
order by 2;
计算某个用户占用的磁盘空间


select owner,sum(bytes)/1024/1024/1024 "Space(G)" 
from dba_segments 
where owner='LIAOJL'
group by owner;
（三）、表


Oracle都是以段为存储的，segment_name包含了表、索引、回滚段等，所以在dba_extents，dba_segments都可以找到占用空间大小的信息。


select sum(bytes)/1024/1024 "Space(M)" 
from dba_extents
where owner='LIAOJL' and segment_name='STUDENTS';
dba_segments也可以计算表的大小：


select segment_name,bytes/1024/1024 "Space(MB)"
from dba_segments
where SEGMENT_TYPE='TABLE' and segment_name=upper('你要查找的表的名字');
当时上面的写法不完全正确，当表是分区表，dba_segments有多条信息，可改成：


select segment_name,sum(bytes)/1024/1024 "Space(MB)"
from dba_segments
where segment_name=upper('你要查找的表的名字');
上述方法对于一个很大的数据库，SQL语句执行起来会很慢，而且消耗数据库资源。Oracle支持对表进行分析，执行分析表操作后可以在dba_tables等系统表中查询表大小、行数等信息，不过这些信息不是实时更新的，可以在数据库空闲时，通过计划任务来更新。


分析SQL方法：


analyze   table   tab_name   compute   statistics;
表太大的话可以执行：


analyze   table   tab_name   estimate   statistics;






1.查看所有用户：
 
select * from dba_users;   
 
select * from all_users;   
 
select * from user_users;
 
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
 
select * from dba_sys_privs;   
 
select * from user_sys_privs; (查看当前用户所拥有的权限)
 
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
 
sql>select * from role_sys_privs;
 
4.查看用户对象权限：
 
select * from dba_tab_privs;   
 
select * from all_tab_privs;   
 
select * from user_tab_privs;
 
5.查看所有角色： select * from dba_roles;
 
6.查看用户或角色所拥有的角色：
 
select * from dba_role_privs;   
 
select * from user_role_privs;
 
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
 
select * from V$PWFILE_USERS
 
8.SqlPlus中查看一个用户所拥有权限
 
SQL>select * from dba_sys_privs where grantee='username'; 其中的username即用户名要大写才行。
 
比如： SQL>select * from dba_sys_privs where grantee='TOM';
 
9、Oracle删除指定用户所有表的方法
 
select 'Drop table '||table_name||';' from all_tables where owner='要删除的用户名(注意要大写)';
 
10、删除用户
 
drop user user_name cascade; 如：drop user SMCHANNEL CASCADE
 
11、获取当前用户下所有的表：
 
select table_name from user_tables;
 
12、删除某用户下所有的表数据:
 
select 'truncate table  ' || table_name from user_tables;
 
13、禁止外键 ORACLE数据库中的外键约束名都在表user_constraints中可以查到。
 
其中constraint_type='R'表示是外键约束。
 
启用外键约束的命令为：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'
 
14、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;




 select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
 from dba_segments t
 where t.owner = '你要查询的用户' 
 and t.segment_type='TABLE'
 group by OWNER, t.segment_name, t.segment_type
 order by mmm desc;