Oracle-常用用户SQL1-优快云博客

本文链接：https://blog.youkuaiyun.com/ygj666888/article/details/83368529

本文提供了一系列Oracle数据库管理的实用SQL查询语句，包括表结构查询、表空间使用情况检查、客户端主机信息获取以及回滚段的详细信息。这些工具性的查询能够帮助数据库管理员更好地了解并维护数据库的状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询表结构

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