Oracle 常用sql汇总

常用sql汇总

1. 查询表结构，导出到Excel表

原文传送门：http://blog.csdn.net/u011544778/article/details/50907157

SELECT B.TABLE_NAME     AS "表名",
   C.COMMENTS       AS "表说明",
   B.COLUMN_ID      AS "字段序号",
   B.COLUMN_NAME    AS "字段名",
   CONCAT(CONCAT(CONCAT(B.DATA_TYPE, '('),B.DATA_LENGTH),')')      AS "字段数据类型",
   B.DATA_LENGTH    AS "数据长度",
   B.DATA_PRECISION AS "整数位",
   B.DATA_SCALE     AS "小数位",
   A.COMMENTS       AS "字段说明"
FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C

WHERE A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)
   AND A.OWNER = B.OWNER
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND C.TABLE_NAME = A.TABLE_NAME
   AND C.OWNER = A.OWNER
   AND A.OWNER = 'O2OTESTUSER' 
   AND B.TABLE_NAME = 'INSURANCE_CONTRACT'
ORDER BY A.TABLE_NAME, B.COLUMN_ID

上述sql中，A.OWNER 获取当前表所属用户，B.TABLE_NAME 查询要导出的表名

另外一些辅助查询：

SELECT * FROM ALL_USERS WHERE USERNAME='PYE'; --查询用户
SELECT * from user_tables　where table_name like '%TJ%'; --模糊查询表明

select owner from dba_tables where table_name='TJ_T_TJTCDJB'; --查询表拥有者

2. 对比两个数据库字段是否一样

-- 查看当前用户拥有的表的所有字段,通过在两个不同的数据库执行查询，可以查看字段数量是否一样
SELECT t.table_name,
       t.colUMN_NAME,
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
       t1.COMMENTS
  FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
    AND t.column_name = t1.column_name
 -- 某个表的所有字段
 -- and t.table_name = 'SYS_AUTHORIZATION'


-- 查看当前数据库拥有的表数量,通过在两个不同的数据库执行查询，可以查看表数量是否一样
SELECT
    DISTINCT t.table_name 
FROM
    User_Tab_Cols t,
    User_Col_Comments t1 
WHERE
    t.table_name = t1.table_name AND
    t.column_name = t1.column_name

3. 查连接数（dba权限）

select count(*) from v$process --当前的连接数

4. 查看某些sql执行状况

select w2.SCHEMANAME, w2.osuser, w1.ADDR, w1.USERNAME, w1.SPID, w1.PNAME, w1.PROGRAM, w2.*, w2.PREV_SQL_ID, w3.SQL_TEXT 
from v$process w1
left join v$session w2 on w1.ADDR = w2.PADDR 
left join v$sql w3 on w2.SQL_ID = w3.SQL_ID
order by w2.SCHEMANAME