oracle数据库实践,Oracle数据库一些实践(best practise)

74 阅读 0 评论 49 点赞

我是靠谱客的博主魔幻乌冬面，最近开发中收集的这篇文章主要介绍oracle数据库实践,Oracle数据库一些实践(best practise)，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Oracle数据库提供了强大的数据字典视图和动态性能视图,但这些视图大多数晦涩难懂,就算专业的DBA也难以将所有的视图用好.这里列举了一些常用的数据库管理的命令 -- 查看当前的正在运行的SQL语句 SELECT t.Addr, t.Start_Time, Ss.Sid, Ss.Username, s.Sql_Text, Sp.Sql_Text FROM V$transaction t, V$session Ss, V$sql s, V$sql Sp WHERE Ss.Taddr = t.Addr AND Ss.Sql_Id = s.Sql_Id(+) AND Ss.Prev_Sql_Id = Sp.Sql_Id(+) -- 查看导致数据锁的SQL语句 select /*+ ordered */ a.sid blocker_sid, a.username blocker_username, a.serial#, a.logon_time, b.type, b.lmode mode_held, b.ctime time_held, c.sid waiter_sid, c.request request_mode, c.ctime time_waited from v$lock b, v$enqueue_lock c, v$session a where a.sid = b.sid and b.id1 = c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1 order by time_held, time_waited; -- 查看数据库表空间使用量 SELECT Dbf.Tablespace_Name, Dbf.Totalspace AS "Total (MB)", round(Dbf.Totalspace - Dfs.Freespace,2) AS "Used (MB)", substr('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>',1,trunc((Dbf.Totalspace - Dfs.Freespace) / Dbf.Totalspace * 100 / 2))|| substr('--------------------------------------------------',1,49-trunc((Dbf.Totalspace - Dfs.Freespace) / Dbf.Totalspace * 100 / 2)) AS "Percent Bar", round((Dbf.Totalspace - Dfs.Freespace) / Dbf.Totalspace * 100,1) AS "Used Percent", round(Dfs.Freespace,2) AS "Free (MB)" 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,t.file_name) Dbf, (SELECT Tt.Tablespace_Name, SUM(Tt.Bytes) / 1024 / 1024 Freespace, SUM(Tt.Blocks) Freeblocks FROM Dba_Free_Space Tt GROUP BY Tt.Tablespace_Name) Dfs WHERE TRIM(Dbf.Tablespace_Name) = TRIM(Dfs.Tablespace_Name) ORDER BY Tablespace_Name;