Oracle SQL

--查看回滚段

select segment_name sn, decode(owner,'PUBLIC','Publ','Priv') ow,
       tablespace_name ts, name fn
from sys.dba_rollback_segs d, v$datafile f
where d.file_id = f.file#;

 

select d.segment_name nm,
       s.extents ex,
       (s.rssize/1024)||'K' rs,
       d.initial_extent init,
       d.next_extent next,
   d.pct_increase pct,
       d.min_extents mi,
       d.max_extents ma,
optsize op,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st
from v$rollname n, v$rollstat s, sys.dba_rollback_segs d
where n.usn = s.usn
and   d.segment_name = n.name(+);

 

--生成计算表中行平均大小的脚本(LONG and LOB columns will not report row size properly)

create table column_counts
        (
        table_name,
        column_count
        )
        as
        (
        select table_name, max(column_id)
        from user_tab_columns
        where data_type not like 'LONG%' AND table_name in
        (select table_name from user_tables)
        group by table_name
        )
        ;

select UTC.table_name tnm1,
        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) ||
                ' TB, ', '        ') ||
        'round(avg(nvl(vsize('||column_name||'),0)),0)' ||
        decode(column_id,column_count, ' row_size from ' || UTC.table_name
             || ';'|| chr(10)||chr(10),
                ' +') col_nm
from user_tab_columns UTC, column_counts CC
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name
order by UTC.table_name, UTC.column_id;

drop table column_counts;

 

--查看session空闲时间

1. 设置init.ora RESOURCE_LIMIT=TRUE;
2. 重启数据库
3. 建立 user_profile 并设置 idle_time=N;
4. alter user XXXX profile user_profile;

 

select sid, to_char((sysdate - (hsecs - value)/(100*60*60*24)),
'dd-mon-yy hh:mi:ss') last, to_char(sysdate, 'dd-mon-yy hh:mi:ss') curr,
(hsecs - value)/(100) secs, (hsecs - value)/(100*60) mins
from v$timer, v$sesstat
where statistic# = (select statistic# from v$statname
                    where name = 'process last non-idle time');

 

--生成重新编译所有失效对象的脚本

 

/*根据依赖关系排序 v8.1.5*/

select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
    dba_objects a,
    sys.order_object_by_dependency b
where
    A.OBJECT_ID = B.OBJECT_ID(+) and
    STATUS = 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
                      'TRIGGER', 'VIEW' )
order by
    DLEVEL DESC,
    OBJECT_TYPE,
    OBJECT_NAME;

/*不排序 v8i,9i,10g*/

select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
    dba_objects
where
    STATUS = 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
                      'TRIGGER', 'VIEW' )
order by
    OBJECT_TYPE,
    OBJECT_NAME;

--查看安装的模块

select comp_name, version, status from dba_registry;