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;