-- ★★★
-- 当删除数据出现 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
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;
分享到:
相关推荐
ORACLE DBA常用SQL语句.............
Oracle DBA常用SQL语句.日常一些查询与分析
创建表空间,创建用户,导入导出表等SQL语句
oracle dba日常管理的工作内容以及常用sql语句
DBA常用SQL语句,其中很多ORACLE DBA需要用到的常用语句
oracle dba 常用的 sql 语句 包括 空间,用户,进程等等。
oracle_DBA_常用SQL语句[借鉴].pdf
Oracle维护常用SQL语句汇总: 如何远程判断Oracle数据库的安装平台 select * from v$version; 查看表空间的使用情况 select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by ...
查看表空间的名称及大小 查看表空间物理文件的名称及大小等
查看某个进程正在执行什么SQL语句.sql 查看用户表所占空间的大小.sql 查看系统SGA区状态.sql 查看系统中使用了哪些设备文件.sql 查看系统中每个表空间的使用情况.sql 查看系统中每个表空间的大小.sql 查看系统联接数...
Oracle维护常用SQL语句 ************************************************ 如何查看各个表空间占用磁盘情况? col tablespace format a20 select b.file_id 文件ID号, b.tablespace_name 表空间名,b.bytes ...
oracle的SQL语句的一些经验总结,里边有很多大家和自己的东西。
表空间、会滚段、控制文件、数据库对象、查看锁等DBA最常用的SQL查询语句汇总
v$sqlarea:共享池中使用当前光标的统计信息,光标是一块内存区域,有Oracle处理SQL语句时打开。 v$statname:在v$sesstat中报告各个统计的含义 v$sysstat:基于当前操作会话进行的系统统计 v$waitstat:出现一个...
ORACLE常用命令 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup ...
本文档包含以下内容: 1. Oracle 数据库日常维护 2. Oracle DBA 常用管理脚本 3. Oracle DB 常用 SQL 语句
oracle 一些常用的sql语句,特别适合oracle DBA 使用
dba 常用sql 语句,oracle 数据库中,常用的数据字典sql 描述 dba 常用sql 语句,oracle 数据库中,常用的数据字典sql 描述 dba 常用sql 语句,oracle 数据库中,常用的数据字典sql 描述
5.2 SQL语句的执行过程 5.3 执行计划 5.4 如何阅读执行计划 5.5 小结 第6章 访问路径和连接 6.1 QUBE模型 6.2 单表访问——全表扫描还是索引 6.3 连接 6.4 半连接 6.5 小结 第7章 并行...