---檢查SFISM4下物件數量
select   OWNER, OBJECT_TYPE, count(OWNER) OBJECT_COUNT
from     SYS.DBA_OBJECTS
where    wner ='SFISM4'
group by OWNER, OBJECT_TYPE
order by OBJECT_TYPE

---導出腳本
windows下，

set NLS_LANG=American_america.AL32UTF8
exp system/"""system@dbsec""" wner=gwdms_lh file=e:\backup\gwdms_lh.dmp log=e:\backup\gwdms_lh.txt

在Linux下，

NLS_LANG=American_america.AL32UTF8;export NLS_LANG

exp 'system/"system$test"'@csdqc70 wner=tms file='/u01/backup/tms.dmp' log=/u01/backup/tms.log'

---產生賦權腳本
select 'grant select on schemaname.'||table_name||' to username; ' from DBA_TABLES where wner='GWDMS'

---檢查命中率
select a.value + b.value "logical_reads", c.value "physical_reads",
round(100 * ((a.value+b.value)-c.value)/(a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a,v$sysstat b,v$sysstat c
where a.statistic# =47 and b.statistic# =50 and c.statistic#=54;

---導出表欄位註釋信息

SELECT      'COMMENT ON COLUMN '
         || OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME
         || ' IS '|| ''''||COMMENTS||''';'
  FROM   (SELECT   *
            FROM   DBA_COL_COMMENTS
           WHERE   WNER = 'SFIS1' AND COMMENTS IS NOT NULL);

---檢查所有失效的物件
 select * from dba_objects where status='INVALID' and object_type <> 'SYNONYM'

---查詢Schema為'EHEALTH','EHC','HHCARE','HAMSTC'中包含LOB類型的表的數據量

select 'select count(*) from '||owner||'.'||table_name||';'from  dba_tab_columns where data_type like '%LOB%' and owner  in ('EHEALTH','EHC','HHCARE','HAMSTC') order by owner