常用Oracle DBA 查询

落日映苍穹つ 2024-02-18 14:47 67阅读 0赞

常用Oracle DBA 查询

—— 肯定有你需要的！

\-- -------------------------------------------------------------------  
\-- Part1 Oracle常用查询  
\-- -------------------------  
\--1. 查询系统所有对象  
select owner, object\_name, object\_type, created, last\_ddl\_time, timestamp, status from  
dba\_objects  
\--2. 查看系统所有表  
select owner, table\_name, tablespace\_name from dba\_tables  
\--3. 查看所有用户的表  
select owner, table\_name, tablespace\_name from all\_tables  
\--4. 查看当前用户表  
select table\_name, tablespace\_name from user\_tables  
\--5. 查看用户表索引  
select t.\*,i.index\_type from user\_ind\_columns t,user\_indexes i where  
t.index\_name = i.index\_name and t.table\_name = i.table\_name  
and t.table\_name = 要查询的表  
\--6. 查看主键  
select cu.\* from user\_cons\_columns cu, user\_constraints au  
where cu.constraint\_name = au.constraint\_name  
and au.constraint\_type = 'P' and au.table\_name = 要查询的表  
\--7. 查看唯一性约束  
select column\_name from user\_cons\_columns cu, user\_constraints au  
where cu.constraint\_name = au.constraint\_name and au.constraint\_type = 'U'  
and au.table\_name = 要查询的表  
\--8. 查看外键  
select \* from user\_constraints c where c.constraint\_type = 'R' and c.table\_name = 要查询的表  
select \* from user\_cons\_columns cl where cl.constraint\_name = 外键名称  
select \* from user\_cons\_columns cl where cl.constraint\_name = 外键引用表的键名  
\--9. 查看表的列属性  
select t.\*,c.COMMENTS from user\_tab\_columns t,user\_col\_comments c  
where t.table\_name = c.table\_name and t.column\_name = c.column\_name  
and t.table\_name = 要查询的表  
\--10. 查看所有表空间  
select tablespace\_name from dba\_data\_files group by tablespace\_name  
\-- -------------------------------------------------------------------  
  
  
\-- Part2 Oracle常用管理  
\-- -------------------------  
\--1. 查看Oracle最大连接数  
SQL>show parameter processes \#最大连接数  
\--2. 修改最大连接数  
SQL>alter system set processes=value scope=spfile  
\--重启数据库  
SQL>shutdown force  
SQL>start force  
\--3. 查看当前连接数  
SQL>select \* from V$SESSION where username is not null  
\--4. 查看不同用户的连接数  
SQL>select username,count(username) from V$SESSION where username is not null group by username \#查看指定用户的连接数  
\--5. 查看活动的连接数  
SQL>select count(\*) from V$SESSION where status='ACTIVE' \#查看并发连接数  
\--6. 查看指定程序的连接数  
SQL>select count(\*) from V$SESSION where program='JDBC Thin Client' \#查看JDBC连接Oracle的数目  
\--7. 查看数据库安装实例(DBA权限)  
SQL>select \* from v$instance  
\--8. 查看运行实例名  
SQL>show parameter instance\_name  
\--9. 查看数据库名  
SQL>show parameter db\_name  
\--10. 查看数据库域名  
SQL>show parameter db\_domain  
\--11. 查看数据库服务名  
SQL>show parameter service\_names  
\--12. 查看全局数据库名  
SQL>show parameter global  
\--13. 查看表空间使用率  
\-- (1)  
select dbf.tablespace\_name, dbf.totalspace "总量(M)", dbf.totalblocks as "总块数",  
dfs.freespace "剩余总量(M)", dfs.freeblocks "剩余块数",  
(dfs.freespace / dbf.totalspace) \* 100 as "空闲比例"  
from (  
select t.tablespace\_name,  
sum(t.bytes) / 1024 / 1024 totalspace,  
sum(t.blocks) totalblocks  
from dba\_data\_files t  
group by t.tablespace\_name) dbf, (  
select tt.tablespace\_name,  
sum(tt.bytes) / 1024 / 1024 freespace,  
sum(tt.blocks) freeblocksfrom dba\_free\_space tt  
group by tt.tablespace\_name) dfs  
where trim(dbf.tablespace\_name) = trim(dfs.tablespace\_name)  
\-- (2)  
select T.name "Tablespace Name", Free\_space, (total\_space-Free\_space) Used\_space, total\_space  
FROM  
(select tablespace\_name, sum(bytes/1024/1024) Free\_Space  
from sys.dba\_free\_space  
group by tablespace\_name  
) Free,  
(select b.name, sum(bytes/1024/1024) total\_space  
from sys.v\_$datafile a, sys.v\_$tablespace B  
where a.ts\# = b.ts\#  
group by b.name  
) T  
WHERE Free.Tablespace\_name = T.name  
\-- -------------------------------------------------------------------