Oracle 好用SQL

查詢Sequence

SELECT *
FROM all_sequences
WHERE sequence_owner = 'SchemaName'
  AND sequence_name = 'sequence_name';

查詢TABLE&欄位

SELECT
  C.OWNER
  ,C.TABLE_NAME     as 表格名稱
  , C.COLUMN_ID
  , C.COLUMN_NAME   as 欄位名稱
  , DATA_TYPE       as 資料型別
  , DATA_LENGTH     as 長度
  , DATA_PRECISION  as 整數位數
  , DATA_SCALE      as 小數位數
  , DATA_DEFAULT    as 預設值
  , NULLABLE        as 是否允許空值
  , COMMENTS        as 欄位描述
FROM
  ALL_TAB_COLUMNS C
JOIN ALL_TABLES T ON
  C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS R ON
  C.OWNER = R.Owner AND
  C.TABLE_NAME = R.TABLE_NAME AND
  C.COLUMN_NAME = R.COLUMN_NAME
WHERE 1=1
  and C.OWNER = 'SchemaName'
  and C.TABLE_NAME=upper('TableName')
ORDER BY C.TABLE_NAME, C.COLUMN_ID

Table Index

SELECT *
FROM DBA_IND_COLUMNS
where TABLE_OWNER = 'SchemaName' -- 替換成您的模式名稱
  and TABLE_NAME=upper('TableName')
ORDER BY INDEX_OWNER, INDEX_NAME, COLUMN_POSITION

Table PK欄位

SELECT cols.owner, cols.constraint_name, cols.column_name, cols.position
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cons.constraint_type = 'P'  -- 这里 'P' 表示主键
AND cons.STATUS='ENABLED'
AND cols.owner='SchemaName'
AND cols.table_name = upper('TableName')

Kill Data Lock

--table lock & kill sid ----------------------------
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
--alter system kill session 'SID,SERAIL#';
--alter system kill session '509,1657';
--table lock & kill sid ----------------------------
--table lock & kill sid ----------------------------
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
   -- l.type,
   -- l.lmode,
   -- l.request,
    o.object_name,
    o.object_type
FROM
    v$session s
    JOIN v$locked_object l ON s.sid = l.session_id
    JOIN dba_objects o ON l.object_id = o.object_id;
--ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION '516,16217';
--table lock & kill sid ----------------------------