Oracle-常用用户SQL1

查询表结构

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'

54com.cn

 

表空间使用状态 中国网管联盟www.bitscn.com

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 网管联盟www.bitsCN.com

 

feedom.net

 

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

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;

feedom.net

 

  中国网管联盟www、bitsCN、com

客户端主机信息 中国网管联盟www_bitscn_com

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
中国网管联盟www.bitscn.com

查看回滚段名称及大小

54ne.com

 

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'

中国网管联盟www_bitscn_com

COLUMN extents     FORMAT 999          HEADING 'Extents'

feedom.net

 

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

网管网bitsCN.com

  , 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

中国网管论坛bbs.bitsCN.com

       a.segment_name = b.segment_name
  AND  a.segment_name = c.name (+)
  AND  c.usn          = d.usn (+)
ORDER BY a.segment_name;

网管网bitsCN.com