导航：首页 > 数据库 >

常用SQL语句整理

发表于：2025-11-08 作者：千家信息网编辑

千家信息网最后更新 2025年11月08日，-----表空间使用率----- SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE -

千家信息网最后更新 2025年11月08日常用SQL语句整理-----表空间使用率----- SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --如果有临时表空间 SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1;
-----查询所有依赖对象（存储过程，触发器等基于那张表）的信息----- select * from dba_dependencies where referenced_owner='USERNAME';
-----查询表上的外键关系----- select A.* from user_constraints A, user_constraints B WHERE b.table_name = 'TABLENAME' and a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name;
-----查询所有主外键关系----- select a.table_name 主表, b.table_name 子表, column_name 键, substr(position,1,1) P from user_constraints a, user_constraints b, user_cons_columns c where a.constraint_name = b.r_constraint_name and a.constraint_name = c.constraint_name order by 1, 2, 4;
-----查看没有主键的表----- SELECT * FROM dba_tables A WHERE owner='CCPS' AND NOT EXISTS ( SELECT * FROM dba_constraints b WHERE A .table_name = b.table_name AND b.constraint_type = 'P' );
-----session_cached_cursor和open_cursor使用率----- SELECT 'session_cached_cursors' PARAMETER, LPAD(VALUE, 5) VALUE, DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE) || '%') USAGE FROM ( SELECT MAX(S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = 'session cursor cache count' AND S.STATISTIC# = N.STATISTIC# ), ( SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors' ) UNION ALL SELECT 'open_cursors', LPAD(VALUE, 5), TO_CHAR(100 * USED / VALUE) || '%' FROM ( SELECT MAX(SUM(S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ( 'opened cursors current' ,'session cursor cache count' ) AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID ), ( SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors' );
-----查询所有表和索引的大小----- select segment_name,sum(bytes)/1024/1024/1024 size_GB from dba_extents where owner='USERNAME' group by segment_name order by 2 desc;
-----查看oracle自动任务结果----- select f.task_name,o.type,o.attr1,attr3,message,more_info,execution_type,e.execution_start,e.execution_end from dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e where o.task_id=f.task_id and o.object_id=f.object_id and f.task_id=e.task_id and e.execution_start >sysdate -1;
select dbms_sqltune.report_auto_tuning_task from dual;
-----查看oracle是否启用块跟踪----- select status from v$block_change_tracking; 如果没有启用用，1级备份的时候会比较所有的数据块文件，所以备份时间并不会比0级备份少，甚至可能会更长。可通过如下语句启用： alter system set DB_CREATE_FILE_DEST='/home/oracle/backup' scope=both; --配置追踪文件存放位置 alter database enable block change tracking; ---开启块跟踪
-----查看正在执行的sql语句----- select a.program 请求程序, a.username 登录oracle用户名, a.sid oracleSID, a.SERIAL#, a.machine 计算机名, b.spid 操作系统ID, c.sql_text 正在执行的SQL, c.SQL_ID SQLID from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;
-----查看锁定的表及SQL----- SELECT l.session_id sid, s.serial#, l.locked_mode 锁模式, l.oracle_username 登录用户, l.os_user_name 登录机器用户名, p.spid 操作系统ID, s.machine 机器名, s.terminal 终端用户名, o.object_name 被锁对象名, s.logon_time 登录数据库时间, q.sql_id SQLID, q.sql_text SQL语句 FROM v$locked_object l, all_objects o, v$session s,v$process p,v$sql q WHERE l.object_id = o.object_id and s.paddr = p.addr AND l.session_id = s.sid and s.sql_hash_value = q.hash_value ORDER BY sid, s.serial#;
-----查看oracle隐含参数----- SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_optim_peek_user_binds';
修改：SQL> alter system set "_optimizer_max_permutations"=200 scope=both sid='*';
-----查看被锁的表----- select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-----查看那个用户那个进程造成死锁----- select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
-----查出锁表的sid, serial#,os_user_name, machine_name, os processes，terminal，锁的type,mode----- SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,s.process,s.terminal, s.logon_time, l.type FROM v$session s, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;
-----查看锁定的表----- SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;
-----根据操作系统进程号查询正在执行的SQL语句----- SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = 10769)) ORDER BY piece ASC;
-----观察等待事件总数及大致分类----- select a.wait_class#,wait_class_id,wait_class,count(*) from v$event_name a group by wait_class#,wait_class_id,wait_class order by wait_class#;
-----查询latch地址----- select addr,LATCH#,CHILD#,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains' and rownum < 21;
-----根据latch地址确定数据块----- select dbarfil,dbablk from x$bh where hladdr in (select addr from (select addr from v$latch_children order by sleeps desc) where rownum < 1000);
-----根据数据块确认具体对象----- select distinct a.owner,a.segment_name from dba_extents a, (select dbarfil,dbablk from x$bh where hladdr in (select addr from (select addr from v$latch_children order by sleeps desc) where rownum < 20) ) b where a.RELATIVE_FNO = b.dbarfil and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk and a.owner='USERNAME';
-----查询数据库中的热点块----- select /*+ rule */a.owner,a.object_name,b.tch from dba_objects a,x$bh b where a.data_object_id=b.obj order by tch desc;
-----查看sql历史执行时间----- select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = '14nrwtwftffq5' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3
-----BLOCKING TREE----- with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter from gv$session where blocking_instance is not null and blocking_session is not null) select lpad(' ',2*(level-1))||waiter lock_tree from (select * from lk union all select distinct 'root', blocker from lk where blocker not in (select waiter from lk)) connect by prior waiter=blocker start with blocker='root';

很赞哦！