oracle内置表常用查询SQL

-----------------数据库版本--------------------
查询当前数据库的版本

SELECT * FROM v$version;

-----------------表空间----------------------

1.查询表空间使用情况

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 

------------------用户----------------------

1.查看当前用户的表空间

select username as 用户名,default_tablespace as 表空间 from user_users;

2.查询指定表空间对应的用户列表

SELECT DISTINCT s.owner as 用户名 FROM dba_segments s WHERE  s.tablespace_name ='tablespace_name'

3.查看当前用户的角色列表+角色权限

select * from user_role_privs;--用户角色列表
select * from dba_sys_privs where grantee='角色名称（eg:DBA）';--指定角色的权限列表

4.查看当前用户的系统权限和表级权限

select * from user_sys_privs;--系统权限
select * from user_tab_privs;--表级权限

---------------------表-------------------------

1.查询当前用户的所有对象（表、序列、索引、存储过程）

select 
ub.OBJECT_ID AS 对象ID,
ub.OBJECT_NAME AS 对象名称,
ub.OBJECT_TYPE AS 对象类型,
(CASE ub.status WHEN 'VALID' THEN '有效的' WHEN 'INVALID' THEN '无效的' END) AS 对象状态
from user_objects ub;

2.计算查看某张表的大小（M）

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('表名称');

---------------------索引-----------------------

1.创建索引

CREATE INDEX 索引名 ON 表名(列名);

2.查询索引和表的关系

--查询某张表的索引信息
select 
t.INDEX_NAME AS 索引名称,
t.COLUMN_NAME AS 关联列名,
t.TABLE_NAME AS 所属表名,
i.index_type AS 索引类型
from user_ind_columns t,user_indexes i 
where t.index_name = i.index_name and t.table_name = i.table_name and i.table_name='表名称';

3.自动生成索引语句

SELECT A.TABLE_NAME 外键表, A.CONSTRAINT_NAME 外键名, B.COLUMN_NAME 外键字段,
                         C.TABLE_NAME 主键表, A.R_CONSTRAINT_NAME 主键名,C.COLUMN_NAME 主键字段,
'create index '||A.TABLE_NAME||'_'||B.COLUMN_NAME||' on ' ||A.TABLE_NAME||'('||B.COLUMN_NAME||');' 索引名称,
length(A.TABLE_NAME||'_'||B.COLUMN_NAME) 索引字段长度
        FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B, USER_CONS_COLUMNS C
WHERE A.CONSTRAINT_TYPE = 'R'
         AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
         AND A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
         AND C.TABLE_NAME = 'TMS_ORDER'

-------------------------序列------------------

1.查询序列信息

select seq.sequence_name AS 序列名,seq.last_number AS 当前最大序列 from user_sequences seq;

-------------------------约束--------------------

1.查询某个约束信息

SELECT  
c.table_name AS 表名,
c.constraint_name AS 约束名称,
(CASE c.constraint_type WHEN 'R' THEN '外键约束'
WHEN 'U' THEN '唯一约束' 
WHEN 'P' THEN '主键约束'
WHEN 'C' THEN '检查/非空约束' END) AS 约束类型,
cc.column_name AS 约束列
FROM user_constraints c,user_cons_columns cc
WHERE c.owner = cc.owner and c.constraint_name = cc.constraint_name
AND c.constraint_name='约束名称' 
ORDER by cc.position;

2.添加/失效/生效/禁用外键约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... , column_n);
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;