Oracle sql 日常积累

--查询相同的数据--

SELECT *
  FROM GL_VOUCHER
 WHERE PK_VOUCHER IN (SELECT PK_VOUCHER
                        FROM GL_VOUCHER
                       GROUP BY PK_VOUCHER
                      HAVING COUNT(PK_VOUCHER) > 1)

--删除相同的数据--
DELETE FROM GL_DETAIL
 WHERE PK_DETAIL IN (SELECT PK_DETAIL
                       FROM GL_DETAIL
                      GROUP BY PK_DETAIL
                     HAVING COUNT(PK_DETAIL) > 1)
   AND ROWID NOT IN (SELECT MIN(ROWID)
                       FROM GL_DETAIL
                      GROUP BY PK_DETAIL
                     HAVING COUNT(PK_DETAIL) > 1)


--生成创建索引语句--

SELECT 'create index ' || B.INDEX_NAME || ' on ' || B.TABLE_NAME || '(' || COL ||
       ') tablespace ' || B.TABLESPACE_NAME || ';'
  FROM (SELECT INDEX_NAME, WMSYS.WM_CONCAT(COLUMN_NAME) COL
          FROM (SELECT INDEX_NAME, COLUMN_NAME
                  FROM USER_IND_COLUMNS
                 ORDER BY INDEX_NAME, COLUMN_POSITION)
         GROUP BY INDEX_NAME) A,
       USER_INDEXES B
 WHERE A.INDEX_NAME = B.INDEX_NAME



--查询Oracle隐含参数--

SELECT X.KSPPINM NAME, Y.KSPPSTVL VALUE, X.KSPPDESC DESCRIB
  FROM X$KSPPI X, X$KSPPCV Y
 WHERE X.INST_ID = USERENV('Instance')
   AND Y.INST_ID = USERENV('Instance')
   AND X.INDX = Y.INDX
   AND X.KSPPINM LIKE '%_db_block_hash%'