Oracle一些常用的SQL

查询表结构

select substr(table_name,1,20) tabname,
substr(column_name,1,20)column_name,
rtrim(data_type)||'('||data_length||')' from system.dba_tab_columns
where owner='username'

表空间使用状态

select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4)  "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name

查询某个模式下面数据不为空的表

declare
Cursor c is select TNAME from tab;
vCount Number;
table_nm Varchar2(100);
sq varchar2(300);
begin
for r in c loop
table_nm:=r.TNAME;
sq:='select  count(*)  from '|| table_nm;
execute immediate sq into vCount;
if vCount>0 then
dbms_output.put_line(r.tname);
end if;
end loop;
end;

客户端主机信息

SELECT
SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
SYS_CONTEXT('USERENV','HOST') HOST,
SYS_CONTEXT('USERENV','OS_USER') OS_USER,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
FROM DUAL

查看回滚段名称及大小

COLUMN roll_name   FORMAT a13          HEADING 'Rollback Name'
COLUMN tablespace  FORMAT a11          HEADING 'Tablspace'
COLUMN in_extents  FORMAT a20          HEADING 'Init/Next Extents'
COLUMN m_extents   FORMAT a10          HEADING 'Min/Max Extents'
COLUMN status      FORMAT a8           HEADING 'Status'
COLUMN wraps       FORMAT 999          HEADING 'Wraps'
COLUMN shrinks     FORMAT 999          HEADING 'Shrinks'
COLUMN opt         FORMAT 999,999,999  HEADING 'Opt. Size'
COLUMN bytes       FORMAT 999,999,999  HEADING 'Bytes'
COLUMN extents     FORMAT 999          HEADING 'Extents'

SELECT
    a.owner || '.' || a.segment_name          roll_name
  , a.tablespace_name                         tablespace
  , TO_CHAR(a.initial_extent) || ' / ' ||
    TO_CHAR(a.next_extent)                    in_extents
  , TO_CHAR(a.min_extents)    || ' / ' ||
    TO_CHAR(a.max_extents)                    m_extents
  , a.status                                  status
  , b.bytes                                   bytes
  , b.extents                                 extents
  , d.shrinks                                 shrinks
  , d.wraps                                   wraps
  , d.optsize                                 opt
FROM
    dba_rollback_segs a
  , dba_segments b
  , v$rollname c
  , v$rollstat d
WHERE
       a.segment_name = b.segment_name
  AND  a.segment_name = c.name (+)
  AND  c.usn          = d.usn (+)
ORDER BY a.segment_name;

【责编:landss】