oracle赋权dba语句,ORACLE惯用DBA经典语句

当前位置:我的异常网» 数据库 » ORACLE惯用DBA经典语句

ORACLE惯用DBA经典语句

www.myexceptions.net  网友分享于：2013-10-25  浏览：8次

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

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;

文章评论