Oracle-系统-视图-常用

--数据库版本

select * from V$INSTANCE;

--所有对象

select T.* from SYS.ALL_OBJECTS T where T.OBJECT_NAME like 'EXAM';

--表名称
select T.*
from USER_TAB_COMMENTS T
where T.table_name like '%APPLY%'
order by T.COMMENTS;

--表注释
select T.*
from USER_TAB_COMMENTS T
where T.COMMENTS like '%公布%'
order by T.COMMENTS;

--列注释
select utc.comments
,t.*
from user_col_comments t
,user_tab_comments utc
where t.table_name = utc.table_name
and t.comments like '%报考%'
and t.table_name not like 'BIN$%';

--列名称
select utc.comments
,t.*
from user_col_comments t
,user_tab_comments utc
where t.table_name = utc.table_name
and t.column_name = 'EXAM_TIME'
and t.table_name not like 'BIN$%';

--列所属表
select utc.comments
,t.*
from user_col_comments t
,user_tab_comments utc
where t.table_name = utc.table_name
and t.table_name = 'SCORE_COM'
and t.table_name not like 'BIN$%';
--列详细
select *
from sys.dba_tab_columns t
where t.OWNER = 'EMS2'
AND T.DATA_TYPE = 'CLOB';

--锁信息
select /*+ rule */
S.USERNAME
,DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', L.TYPE) LOCK_LEVEL
,O.OWNER
,O.OBJECT_NAME
,O.OBJECT_TYPE
,S.SID
,S.SERIAL#
,S.TERMINAL
,S.MACHINE
,S.PROGRAM
,S.OSUSER
from V$SESSION S
,V$LOCK L
,DBA_OBJECTS O
where L.SID = S.SID
and L.ID1 = O.OBJECT_ID(+)
and S.USERNAME = 'EMS2'
-- and s.OSUSER = 'vernon.chen'
-- and O.OBJECT_NAME = 'P_WR_SCORE_TOTAL'
-- S.USERNAME
order by S.USERNAME
,S.OSUSER;

--杀死锁
--alter system kill session '55,31431';

select * from dict_item t where t.type_id = 36;

-- 查看约束信息

select UC_1.TABLE_NAME as 从表名称
,UC_1.CONSTRAINT_NAME as 从表约束名称
,UC_1.CONSTRAINT_TYPE as 从表约束类型
,UCC_1.column_name as 从表约束列名称
,UC_2.TABLE_NAME as 主表名称
,UC_2.CONSTRAINT_NAME as 主表约束名称
,UC_2.CONSTRAINT_TYPE as 主表约束类型
,UCC_2.column_name as 主表约束列名称
from user_constraints UC_1
,user_constraints UC_2
,USER_CONS_COLUMNS UCC_1
,USER_CONS_COLUMNS UCC_2
where UC_1.R_CONSTRAINT_NAME = UC_2.CONSTRAINT_NAME
and UC_1.constraint_name = UCC_1.constraint_name
and UC_2.constraint_name = UCC_2.constraint_name
and UC_1.constraint_type = 'R' --从表约束类型
and UC_2.constraint_type = 'P' --主表约束类型
and UC_1.CONSTRAINT_NAME = 'FK_OFFENSE__REFERENCE_EXAM_OFF';

--查看索引
select t.* from user_indexes t where t.INDEX_NAME = 'INDEX_CRED_APPLY_NO';

select t.* from user_indexes t where t.INDEX_NAME = 'PK_CERT_IMP_LOG';