ORACLE常用DBA经典语句
当删除数据出现 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), SUBSTRB(text, 1, LENGTHB(text) - 1), CHR(13), 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