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

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

 

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

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

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

SELECT CONCAT(O.OWNER,O.OBJECT_NAME) AS OWNERANDOBJECTNAME ,O.OWNER AS OWNER,O.OBJECT_NAME AS OBJECTNAME,O.OBJECT_TYPE AS OBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTS O,ALL_TAB_COMMENTS C WHERE O.OBJECT_NAME = C.TABLE_NAME(+) AND O.OWNER = C.OWNER(+) AND O.OBJECT_TYPE IN ('TABLE','VIEW') order by O.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 AS OBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTS O LEFT JOIN ALL_TAB_COMMENTS C ON O.OBJECT_NAME = C.TABLE_NAME AND O.OWNER = C.OWNER WHERE O.OBJECT_TYPE IN ('TABLE','VIEW') order by O.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_comments acc
 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((select accs.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  is not  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 or A.SEGMENT_NAME = B.TABLE_NAME)
   and A.owner = B.owner(+)
   and (A.segment_type like 'TABLE%' or A.segment_type like 'LOB%')
   GROUP BY B.OWNERTABLE_NAME
union all
SELECT B.OWNERTABLE_NAME,
       NVL(SUM(B.BYTES),0) AS SpaceCount,'2' spaceType
  FROM (SELECT (OWNER || '.' || SEGMENT_NAME) AS OWNERTABLE_NAME,
       BYTES FROM DBA_SEGMENTS) B
 WHERE B.OWNERTABLE_NAME IN(#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') as STATUS2,DECODE(B.status,'INVALID','0', '1') as STATUS FROM user_triggers A     left join user_objects  B on A.trigger_name=B.OBJECT_NAME and B.OBJECT_TYPE='TRIGGER'

 

八,分页查询

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

采用rownum

SELECT t.* FROM #table_name# t

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