数据库的一些字典表查询和分页查询…

来源：互联网发布：国家的顶级域名编辑：程序博客网时间：2024/05/16 09:30

总结一下最近开发用到的一些数据库查询,以便日后查阅.

Oracle(8I/9I/10G/11G)

一,查询库中所有表(视图)名和对应的用户名

8I:(因为8i不支持left/right)

SELECT CONCAT(O.OWNER,O.OBJECT_NAME) AS OWNERANDOBJECTNAME ,O.OWNERAS OWNER,O.OBJECT_NAME AS OBJECTNAME,O.OBJECT_TYPE ASOBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTSO,ALL_TAB_COMMENTS C WHERE O.OBJECT_NAME = C.TABLE_NAME(+) ANDO.OWNER = C.OWNER(+) AND O.OBJECT_TYPE IN ('TABLE','VIEW') order byO.OWNER

9I/10G/11G:

SELECT CONCAT(O.OWNER,O.OBJECT_NAME) AS OWNERANDOBJECTNAME,O.OWNER AS OWNER,O.OBJECT_NAME AS OBJECTNAME,O.OBJECT_TYPE ASOBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTS O LEFT JOINALL_TAB_COMMENTS C ON O.OBJECT_NAME = C.TABLE_NAME AND O.OWNER =C.OWNER WHERE O.OBJECT_TYPE IN ('TABLE','VIEW') order byO.OWNER

二,查询表字段信息,包括是否是主键索引

8I/9I/10G/11G:

select atc.column_name,
      atc.data_type,
      NVL(atc.DATA_PRECISION, atc.data_length) AS data_length,
      acc.comments,
      (select 'Y'
         from all_cons_columns accs, all_constraints ac
        where accs.owner = atc.owner
          and ac.owner = accs.owner
          and accs.TABLE_NAME = atc.table_name
          and ac.CONSTRAINT_TYPE = 'P'
          and accs.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
          and accs.column_name = atc.column_name) AS dbKeyFlg,
     (select 'Y' from all_ind_columns t where t.column_name =atc.column_name and t.table_name = atc.table_name and t.index_owner= atc.owner and t.table_owner = atc.OWNER and rownum = 1 )colIndex
from all_tab_columns atc, all_col_commentsacc
where upper(atc.owner) = upper('#owner#')
   and atc.owner =acc.owner
   and upper(acc.table_name) =upper('#tableName#')
   and atc.table_name =acc.table_name
   and atc.column_name =acc.column_name
order by atc.column_id

三,给某表或字段添加注释

8I/9I/10G/11G:

comment on table #owner#.#tableName# is '#value#';

comment on column #owner#.#tableName#.#colName# is '#value#'

四,统计表的记录数和表大小

9I/10G/11G:

记录数:

select / *+ parallel(T,4) * / count(NVL((selectaccs.column_name AS COUNTKEY from all_cons_columns accs,all_constraints ac where (accs.owner || '.' || accs.TABLE_NAME) =upper('#REPLACE_SQL_KEY#') and ac.owner = accs.owner and(ac.CONSTRAINT_TYPE = 'P' or ac.INDEX_NAME isnot null) and accs.CONSTRAINT_NAME =ac.CONSTRAINT_NAME AND ROWNUM = 1 ),1)) AS COUNTS from#REPLACE_SQL_KEY# T

表大小:

SELECT B.OWNERTABLE_NAME, NVL(SUM(A.BYTES),0) AS SpaceCount,'1'spaceType
FROM DBA_SEGMENTS A,
      (SELECT SEGMENT_NAME, TABLE_NAME, OWNER,(OWNER || '.' ||TABLE_NAME) AS OWNERTABLE_NAME
         FROM DBA_LOBS
        where (OWNER || '.' || TABLE_NAME) IN(#REPLACE_SQL_KEY#)) B
WHERE (A.SEGMENT_NAME = B.SEGMENT_NAME orA.SEGMENT_NAME = B.TABLE_NAME)
   and A.owner = B.owner(+)
   and (A.segment_type like'TABLE%' or A.segment_type like 'LOB%')
   GROUP BYB.OWNERTABLE_NAME
union all
SELECT B.OWNERTABLE_NAME,
      NVL(SUM(B.BYTES),0) AS SpaceCount,'2' spaceType
FROM (SELECT (OWNER || '.' || SEGMENT_NAME) ASOWNERTABLE_NAME,
      BYTES FROM DBA_SEGMENTS) B
WHERE B.OWNERTABLE_NAMEIN(#REPLACE_SQL_KEY#)
GROUP BY B.OWNERTABLE_NAME

五,获取数据库的所有用户名

8I:

Select distinct OWNER AS USERNAME From all_objects

9I/10G/11G:

SELECT USERNAME FROM dba_users

六,获取所有表空间名称和利用率

9I/10G/11G:

select t1.spaceName, t1.totalSize, t1.freeSize, t1.usedSize,t1.usedPercent
from (select spaceName, totalSize, freeSize,usedSize, usedPercent
         from (SELECT a.tablespace_name spaceName,
                      NVL(a.BYTES / 1024 / 1024, 0) totalSize,
                      NVL(b.largest / 1024 / 1024, 0) freeSize,
                      NVL((a.BYTES - b.BYTES) / 1024 / 1024, 0) usedSize,
                      round(NVL((a.BYTES - b.BYTES) / a.BYTES * 100, 0), 2)usedPercent
                 from (SELECT tablespace_name, sum(BYTES) bytes
                         FROM dba_data_files
                        GROUP BY tablespace_name) a,
                      (SELECT tablespace_name,
                              sum(BYTES) bytes,
                              sum(BYTES) largest
                         FROM dba_free_space
                        GROUP BY tablespace_name) b
                WHERE a.tablespace_name = b.tablespace_name
                ORDER BY a.tablespace_name)
       UNION
       SELECT d.tablespace_name spaceName,
              NVL(a.BYTES / 1024 / 1024, 0) totalSize,
              NVL((a.BYTES - t.BYTES) / 1024 / 1024, 0) freeSize,
              NVL(t.BYTES, 0) / 1024 / 1024 usedSize,
              round(NVL(t.BYTES / a.BYTES * 100, 0), 2) usedPercent
         FROM dba_tablespaces d,
              (SELECT tablespace_name, SUM(BYTES) BYTES
                 FROM dba_temp_files
                GROUP BY tablespace_name) a,
              (SELECT tablespace_name, SUM(bytes_cached) BYTES
                 FROM v$temp_extent_pool
                GROUP BY tablespace_name) t
        WHERE d.tablespace_name = a.tablespace_name(+)
          AND d.tablespace_name = t.tablespace_name(+)
          AND d.extent_management = 'LOCAL'
          AND d.CONTENTS = 'TEMPORARY') t1,
      user_tablespaces t2
where t1.spaceName = t2.tablespace_name

七,查询所有触发器名称和状态(是否失效/是否停用)

9I/10G/11G:

SELECT trigger_name,DECODE(A.status,'DISABLED','0', '1') asSTATUS2,DECODE(B.status,'INVALID','0', '1') as STATUS FROMuser_triggersA left join user_objects B onA.trigger_name=B.OBJECT_NAME and B.OBJECT_TYPE='TRIGGER'

八,分页查询

8I/9I/10G/11G:

采用rownum

SELECT t.* FROM #table_name# t

WHERE ROWNUM <= (当页码 * 页大小) ANDROWNUM > ((当页码-1) * 页大小) ;

0 0