Oracle 常用DBA语句 日常DBA维护的SQL命令收集

-- 当删除数据出现 ORA-02292: 违反完整约束条件 (CCMS.REFWORK_AREA27) - 已找到子记录日志 
-- 查询完整约束条件涉及的表和字段. 
Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME 
FROM SYS.ALL_CONS_COLUMNS A 
WHERE OWNER = user 
and CONSTRAINT_NAME like 'REFWORK_AREA27' 
ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION, COLUMN_NAME 



-- 查询所有 sequence 
Select SEQUENCE_NAME, to_char(MIN_VALUE) min_value, to_char(MAX_VALUE) max_value, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, to_char(LAST_NUMBER) last_number 
from SYS.USER_SEQUENCES 
where 1=1 


-- 查询所有表名 及其物理参数 
Select t.*, user owner from sys.user_all_tables t where 1=1 
and ((iot_type is null) or (iot_type <> 'IOT_MAPPING')) 

-- 查询单个 表 字段的 
Select table_name, column_name, data_type, data_type_mod, data_type_owner, 
decode(data_type, 'CHAR', char_length, 
'VARCHAR', char_length, 
'VARCHAR2', char_length, 
'NCHAR', char_length, 
'NVARCHAR', char_length, 
'NVARCHAR2', char_length, 
data_length) data_length, 
data_precision, data_scale, nullable, char_used 
, user owner 
FROM SYS.USER_TAB_COLUMNS 
WHERE 1=1 
and table_name ='ACTIONS' 
order by column_id 


-- 查询所有存储过程 
Select * from SYS.ALL_OBJECTS where owner = user AND OBJECT_TYPE = 'PACKAGE' ; 
Select * from SYS.ALL_OBJECTS where owner = user AND OBJECT_TYPE = 'PACKAGE BODY' ; 
Select * from SYS.ALL_OBJECTS where owner = user AND OBJECT_TYPE = 'PROCEDURE' 
Select * from SYS.ALL_OBJECTS where owner = user AND OBJECT_TYPE = 'FUNCTION' 

-- 查询视图 
Select v.view_name, v.text_length, v.text, o.status 
,v.type_text, v.oid_text, v.view_type_owner, v.view_type 
, superview_name 
from SYS.ALL_VIEWS v, SYS.ALL_OBJECTS o 
where v.owner = o.owner 
and o.object_type = 'VIEW' 
and v.view_name = o.object_name 
and o.owner = user 

-- 查询视图中的字段定义 
Select COLUMN_NAME, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, TABLE_NAME, DATA_TYPE 
,DATA_TYPE_MOD, DATA_TYPE_OWNER 
FROM SYS.USER_TAB_COLUMNS 
WHERE 1=1 
and table_name = 'V_STAFF' 
order by column_id 

-- 查询所有存储过程,函数或包的代码 
-- 对象类型 = 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION' 
-- 对象名称 = 存储过程,函数或包的名称 
Select 
decode(SUBSTRB(text, LENGTHB(text), 1),

CHR(10),--ascii中LF--下一行'/n'

SUBSTRB(text, 1, LENGTHB(text) - 1),

CHR(13), --回车'/r'

SUBSTRB(text, 1, LENGTHB(text) - 1), text) as text 
from SYS.USER_SOURCE 
where 1=1 
and type = 对象类型 
and name = 对象名称 
order by line; 




-- 查询所有的索引名称,及其字段 
Select INDEX_NAME, COLUMN_NAME, COLUMN_LENGTH, TABLE_OWNER, TABLE_NAME, COLUMN_POSITION 
, DESCEND 
FROM SYS.ALL_IND_COLUMNS 
WHERE INDEX_OWNER = user 
ORDER BY INDEX_NAME, COLUMN_POSITION; 

-- 查询所有表定义的 注释 
Select c.TABLE_NAME, NULL COLUMN_NAME, c.COMMENTS 
FROM SYS.ALL_TAB_COMMENTS c, SYS.ALL_TABLES t 
WHERE c.OWNER = user 
AND c.COMMENTS IS NOT NULL 
and c.OWNER = t.OWNER 
and c.TABLE_NAME = t.TABLE_NAME 



-- 查询所有表 的所有字段的 注释 
Select c.TABLE_NAME, c.COLUMN_NAME, c.COMMENTS 
FROM SYS.ALL_COL_COMMENTS c, SYS.ALL_TAB_COLUMNS t 
WHERE c.OWNER = user 
AND c.COMMENTS IS NOT NULL 
and c.OWNER = t.OWNER 
and c.TABLE_NAME = t.TABLE_NAME 
and c.COLUMN_NAME = t.COLUMN_NAME 



-- 查询所有视图的 注释 
Select c.TABLE_NAME, NULL COLUMN_NAME, c.COMMENTS 
FROM SYS.ALL_TAB_COMMENTS c, SYS.ALL_VIEWS v 
WHERE c.OWNER = user 
AND c.COMMENTS IS NOT NULL 
and c.OWNER = v.OWNER 
and c.TABLE_NAME = V.VIEW_NAME; 

-- 查询所有视图的所有字段的 注释 
Select c.TABLE_NAME, c.COLUMN_NAME, c.COMMENTS 
FROM SYS.ALL_COL_COMMENTS c, SYS.ALL_VIEWS v 
WHERE c.OWNER = user 
AND c.COMMENTS IS NOT NULL 
and c.OWNER = V.OWNER 
and c.TABLE_NAME = V.VIEW_NAME; 



-- 查询 所有 的约束的 表名 及其字段 
DECLARE 
TYPE rslt_tbl IS TABLE OF VARCHAR2 (98) 
INDEX BY BINARY_INTEGER; 

rslt1 rslt_tbl; 
rslt_size NUMBER; 
i NUMBER; 

CURSOR cs 
IS 
SELECT c.constraint_name, c.table_name, cc.column_name, c.search_condition 
FROM SYS.all_constraints c, SYS.all_cons_columns cc 
WHERE c.owner = cc.owner 
AND c.table_name = cc.table_name 
AND c.constraint_name = cc.constraint_name 
AND c.constraint_type = 'C' 
AND c.owner = USER; 

cs_var cs%ROWTYPE; 

PROCEDURE addit (in_str VARCHAR2) 
IS 
BEGIN 
rslt_size := rslt_size + 1; 
rslt1 (rslt_size) := in_str; 
DBMS_OUTPUT.put_line ('---> ' || in_str); 
END; 
BEGIN 
rslt_size := 0; 

FOR cs_var IN cs LOOP 
IF (cs_var.search_condition = cs_var.column_name || ' IS NOT NULL') 
OR (cs_var.search_condition = '"' || cs_var.column_name || '" IS NOT NULL') THEN 
addit ( cs_var.constraint_name 
|| '.TN=' 
|| cs_var.table_name 
|| '.CN=' 
|| cs_var.column_name 
); 
END IF; 
END LOOP; 
END; 



-- 查询所有失效或关闭(Disable) 的约束 
/* Formatted on 2005/08/13 11:44 (Formatter Plus v4.8.0) */ 
DECLARE 
TYPE rslt_tbl IS TABLE OF VARCHAR2 (255) 
INDEX BY BINARY_INTEGER; 

rslt1 rslt_tbl; 
rslt_size NUMBER; 

CURSOR c 
IS 
SELECT c.constraint_name, cc.column_name, c.search_condition, c.GENERATED, c.owner, 
c.table_name 
FROM SYS.all_cons_columns cc, SYS.all_constraints c 
WHERE c.owner = cc.owner 
AND c.constraint_name = cc.constraint_name 
AND c.table_name = cc.table_name 
AND c.owner = USER 
AND c.constraint_type = 'C' 
ORDER BY 1, 2; 

c_var c%ROWTYPE; 
s VARCHAR2 (255); 
LAST VARCHAR2 (32); 
nbl VARCHAR2 (1); 

PROCEDURE addit (in_str VARCHAR2) 
IS 
BEGIN 
rslt_size := rslt_size + 1; 
rslt1 (rslt_size) := in_str; 
DBMS_OUTPUT.put_line ('---> ' || in_str); 
END; 
BEGIN 
s := NULL; 
LAST := '!'; 
rslt_size := 0; 

FOR c_var IN c LOOP 
nbl := 'Y'; 

IF (c_var.GENERATED <> 'USER NAME') 
AND ( (c_var.search_condition = '"' || c_var.column_name || '" IS NOT NULL') 
OR (c_var.search_condition = c_var.column_name || ' IS NOT NULL') 
) THEN 
SELECT nullable 
INTO nbl 
FROM all_tab_columns 
WHERE owner = c_var.owner 
AND table_name = c_var.table_name 
AND column_name = c_var.column_name; 
END IF; 

IF (c_var.GENERATED = 'USER_NAME') OR (nbl = 'Y') THEN 
IF (LAST <> c_var.constraint_name) 
AND (s IS NULL OR INSTR (s, '''' || c_var.constraint_name || '''') = 0) THEN 
s := s || '''' || c_var.constraint_name || '''' || ','; 
LAST := c_var.constraint_name; 

IF (LENGTH (s) >= 223) OR (LENGTHB (s) >= 223) THEN 
addit (SUBSTRB (s, 1, LENGTHB (s) - 1)); 
s := NULL; 
END IF; 
END IF; 
END IF; 
END LOOP; 

IF s IS NOT NULL THEN 
addit (SUBSTRB (s, 1, LENGTHB (s) - 1)); 
END IF; 
END; 


-- 查询 指定的 约束 的信息 
SELECT constraint_name, owner, table_name, constraint_type, SUBSTRB (status, 1, 1) status, 
search_condition, SUBSTRB (DEFERRABLE, 1, 1) DEFERRABLE, 
SUBSTRB (DEFERRED, 1, 1) DEFERRED, SUBSTRB (GENERATED, 1, 1) GENERATED, RELY 
FROM SYS.all_constraints 
WHERE owner = USER 
AND constraint_type = 'C' 
AND constraint_name IN ('SYS_C003296') 


-- 查询 约束类型为xxx 的信息 
-- 约束类型 P:未知 U:未知 R:relation, C:column 
Select CONSTRAINT_NAME, OWNER, TABLE_NAME, CONSTRAINT_TYPE, SUBSTRB(STATUS, 1, 1) STATUS 
, SUBSTRB(DEFERRABLE, 1, 1) deferrable, SUBSTRB(DEFERRED, 1, 1) deferred, SUBSTRB(GENERATED, 1, 1) generated 
, RELY 
FROM SYS.ALL_CONSTRAINTS 
WHERE OWNER = user 
AND CONSTRAINT_TYPE = 'P' 

-- 查询约束的表名,字段名 
Select TABLE_NAME, COLUMN_NAME 
FROM USER_CONS_COLUMNS A 
WHERE CONSTRAINT_NAME = 'SYS_C003297' 
ORDER BY TABLE_NAME, POSITION 

Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME 
FROM SYS.ALL_CONS_COLUMNS A 
WHERE OWNER = user 
ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION, COLUMN_NAME 


-- 查询所有外键约束的引用关系 
Select cfk.OWNER, cfk.TABLE_NAME name, 'FOREIGN KEY' TYPE, 
'TABLE' referenced_type, ct.OWNER referenced_owner, 
ct.TABLE_NAME referenced_name, null referenced_link_name, cfk.constraint_name 
FROM SYS.ALL_CONSTRAINTS cfk, SYS.ALL_CONSTRAINTS ct 
where cfk.OWNER = user 
and ct.OWNER = cfk.R_OWNER 
and cfk.CONSTRAINT_TYPE = 'R' 
and cfk.r_CONSTRAINT_NAME = ct.CONSTRAINT_NAME 


-- 查询所有的 JOB 
Select * 
from ALL_JOBS 
where log_user = user 


-- 查询 存储过程,视图的引用关系 
SELECT /*+ ALL_ROWS */ 
NAME, TYPE, referenced_owner, referenced_name, referenced_type, referenced_link_name 
FROM SYS.all_dependencies 
WHERE owner = USER 
AND owner || NAME || TYPE <> referenced_owner || referenced_name || TYPE 
AND TYPE IN 
('FUNCTION', 
'INDEX', 
'MATERIALIZED VIEW', 
'SNAPSHOT', 
'PACKAGE', 
'PACKAGE BODY', 
'PROCEDURE', 
'TRIGGER', 
'TABLE', 
'TYPE', 
'TYPE BODY', 
'VIEW' 
) 
ORDER BY NAME;