oracle元数据查询

环境：

• window server 2019
• oracle19c
• pl/sql developer 14.0.1 x64位

参考：

• oracle19.3安装和配置

说明：

"ORACLE数据字典视图的种类分别为：USER,ALL 和 DBA.
USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息
ALL_*:有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息
DBA_*:有关整个数据库中对象的信息
(这里的*可以为TABLES,INDEXES,OBJECTS,USERS等。)

一、查看用户

查看当前登录用户名、创建时间、默认表空间、临时表空间：
select * from user_users;

查询全部的用户信息（信息有限，不能查看默认表空间等）：
select * from all_users;

查看全部的用户(信息全，需要dba角色)：
select * from dba_users;

查看用户是否以dba身份登录：
select SYS_CONTEXT('USERENV','ISDBA') from dual;

二、查看角色

查看所有的角色（需要已授权dba角色）：
select * from dba_roles;

查看当前用户拥有的角色：
select * from user_role_privs;

查看哪些用户有sysdba或sysoper系统权限(需要dba角色)：
select * from V$PWFILE_USERS

查看角色的授予情况：
select * from dba_role_privs;

三、查看权限

用户的权限分两类：系统权限和对象权限。
系统权限是针对用户设定的。而对象权限是用来控制用户访问其他用户模式对象的。

查看所有的系统权限：
select * from system_privilege_map;

查看直接授予给当前用户的系统权限：
select * from user_sys_privs;

查看当前用户可以使用的权限信息（将解析拥有的角色对应的权限）：
select * from session_privs

查看已经授予所有用户的系统权限信息（需要dba角色）：
select * from dba_sys_privs

查看当前用户的对象权限：
select * from user_tab_privs;

查看所有授予的对象权限（需要dba角色）：
select * from dba_tab_privs;

四、查询当前登录数据库信息

查看当前登录的数据库名：
select SYS_CONTEXT('USERENV','DB_NAME') from dual;

查看当前登录的实例名称：
select SYS_CONTEXT('USERENV','INSTANCE_NAME') from dual;

五、查看当前用户创建的表

查看当前用户的所有表：
select * from user_tables;

查看当前用户的表的表结构：

SELECT *
  FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C
 WHERE T.TABLE_NAME = C.TABLE_NAME
   AND T.COLUMN_NAME = C.COLUMN_NAME
   AND T.TABLE_NAME = UPPER('test1')

六、查看当前用户创建的视图

查看当前用户的所有视图：
select * from user_views;

注意：仅给test1授予connect和resource角色并不能给test2创建视图的权限，我这里使用dba账户执行grant create view to test2;将创建视图的权限给了test2，可以查看最终的权限效果：

七、查看当前用户创建的存储过程

查看当前用户创建的所有存储过程：
准备存储过程：

/*****
** 创建加、减、乘、除计算的存储过程
**输入参数： 数字1，数字2，计算类型
**输出参数： 数字3
*****/
create or replace procedure Proc_Test
(
--定义输入、输出参数--
num_A in integer,
num_B in integer,
numType in integer,
num_C out integer
)
as
--定义变量--
 -- numCount integer;
 -- numStr varchar(20);  
begin   
     --判断计算类型--
     if numType=1 then
        num_C := num_A + num_B;
     elsif numType=2 then
        num_C := num_A - num_B;
     elsif numType=3 then
        num_C := num_A * num_B; 
     elsif numType=4 then
        num_C := num_A / num_B; 
     else
     --其它处理
       dbms_output.put_line('其它处理');
     end if;
end;
-- 调用存储过程
declare num_C integer;
begin
   --调用存储过程---
   Proc_Test(3,4,3,num_C);
   dbms_output.put_line('输出结果：'|| num_C );
end;

上面存储过程的调用结果：

select * from user_procedures;

查看存储过程定义语句：
select * FROM user_source WHERE NAME ='PROC_TEST' ORDER BY line;

八、查看当前用户创建的序列

准备序列：

create sequence t_user_id_seq start with 1 increment by 1;
select * from user_sequences;

九、查看当前用户创建的触发器

参照：《oracle创建触发器》
这里，我们使用上面创建的序列结合触发器实现表列自增的效果，准备表：

create table t_user (
  id number(10),
  name varchar2(20) not null,
  phone_Number varchar2(20),
  email_Address varchar2(200) not null,
  home_Address varchar2(200) not null,
  constraint pk_user primary key (id)
);

创建触发器：

create or replace trigger t_user_trigger
before insert on t_user
for each row
when(new.id is null)
begin
select t_user_id_seq.nextval into:NEW.ID from dual;
end;

测试自增效果：

注意：由于当前用户仅授予了connect、resource、create view权限，所以还不能插入数据到表t_user中，此时我们使用dba账户执行grant unlimited tablespace to test2后就可以插入了。

查看创建的触发器：
select * from user_triggers;

查看触发器语句及其他信息：

十、查看当前用户创建的函数

准备函数：

CREATE OR REPLACE FUNCTION func_add (
    x int,
    y int
) RETURN int IS
    res int;
BEGIN
    return x+y;
END;

调用：

DECLARE results NUMBER;
BEGIN
results :=func_add(2,3);
DBMS_OUTPUT.PUT_LINE('输出: 2+3='||results);
END;

输出：

查看函数：
select * from USER_OBJECTS where OBJECT_TYPE='FUNCTION';

查看函数语句：
select * from USER_SOURCE where TYPE='FUNCTION' and NAME='FUNC_ADD'

十一、查看当前用户创建的索引

select * from user_indexes;