-----------------数据库版本--------------------
查询当前数据库的版本
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;