Oracle中常用的系统表

1、系统表

序号 表名 说明
1 
SELECT * FROM USER_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM DBA_USERS;
 用户表，可以查询对应的用户信息
2 
SELECT * FROM USER_TABLES;
SELECT * FROM USER_ALL_TABLES;
SELECT * FROM USER_TAB_COMMENTS;

SELECT * FROM DBA_TABLES;
SELECT * FROM DBA_ALL_TABLES;
SELECT * FROM DBA_TAB_COMMENTS;
 数据表，可以查询表名，表空间，表类型
3 
SELECT * FROM USER_TABLESPACES;
SELECT * FROM DBA_TABLESPACES;
 表空间
4 
SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLS;
SELECT * FROM USER_COL_COMMENTS;

SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLS;
SELECT * FROM USER_COL_COMMENTS;
 表结构，可以查询表列详细信息
5 
SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;
 约束
6  
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
 索引
 7 
SELECT * FROM USER_VIEWS;
SELECT * FROM USER_MVIEWS;
 视图
8 
SELECT * FROM USER_PROCEDURES;
SELECT * FROM USER_STORED_SETTINGS;

存储过程、存储函数

SELECT OBJECT_NAME AS "对象名[过程名|方法名]"
, PROCEDURE_NAME AS "过程名"
, OBJECT_TYPE AS "对象类型[PROCEDURE|FUNCTION]"
FROM SYS.USER_PROCEDURES t
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION');

SELECT OBJECT_NAME AS "对象名[过程名|方法名]"
, PARAM_VALUE AS "参数值"
, OBJECT_NAME AS "对象名[存储函数]" 
FROM SYS.USER_STORED_SETTINGS t;
9 

SELECT * FROM USER_TRIGGERS;
SELECT * FROM USER_TRIGGER_COLS;
SELECT * FROM USER_TRIGGER_ORDERING;

触发器

SELECT TRIGGER_NAME AS "触发器名"
, TRIGGER_TYPE AS "触发类型"
, TRIGGERING_EVENT AS "触发事件"
, TABLE_OWNER AS "表的主人"
, BASE_OBJECT_TYPE AS "基本对象类型"
, TABLE_NAME AS "表名"
, COLUMN_NAME AS "列名" 
FROM SYS.USER_TRIGGERS t;
10 
SELECT * FROM DBA_dependencies WHERE referenced_name = 'TAB';
SELECT * FROM ALL_dependencies WHERE referenced_name = 'TAB';
SELECT * FROM user_dependencies WHERE referenced_name = 'TAB';
SELECT * FROM user_source 
 WHERE TYPE = 'PROCEDURE' AND UPPER(text) LIKE '%TAB%';

查看对象（表）被哪些对象引用。

user_dependencies 视图包含了数据库对象之间的依赖关系。可以通过查询这个视图来找出哪些存储过程引用了特定的表。

user_source 视图包含了所有存储过程、函数、包等的源代码。可以通过搜索源代码中的表名来找出引用特定表的存储过程。
11 
SELECT * FROM all_synonyms WHERE synonym_name = '同义词名称';
SELECT * FROM user_synonyms WHERE synonym_name = '同义词名称';
SELECT * FROM dba_synonyms WHERE synonym_name = '同义词名称';

查看当前用户有权限访问的所有同义词。

查看当前用户所拥有的同义词。

具有DBA权限，可以查看整个数据库中的所有同义词。
posted @ 2023-04-25 15:17  Faith_zhang  阅读(409)  评论(0)    收藏  举报