Oracle——几种最常用的系统视图(结合flyway进行数据库管理)_oracle系统视图

简介

使用FlyWay进行Oracle数据库表、索引、序列、触发器等管理时，往往为了避免多次执行导致额外创建等意外情况发生，一般都会进行索引、数据表、序列 等 是否存在的判断。

下面总结几种最常见的判断方式，以及说明常用的几个Oracle 系统视图。

常用视图

结合FlyWay的用法

1、user_all_tables 表信息

user_all_tables一般用来记录该用户默认表空间下所有的表段和索引段上的表或视图的名称、段的类型、表空间名称等。

select * from user_all_tables;
1

执行后，可以发现能够展示当前数据库中的所有表名、表大小、表所属空间、状态、设置项等。

如果需要使用到FlyWay进行动态的建表，可以根据此视图进行判断。

案例如下所示：

declare
  cnt integer;
begin
    select count(0)
      into cnt  
      from user_all_tables
     where table_name = upper('user_test');
   
    if cnt = 0 then
        execute immediate 'create table user_test 
        (
          id            INTEGER not null,
          user_name     varchar2(144),
          age     INTEGER
        )';
    end if;
    
    execute immediate 'comment on table user_test is ''用户测试表''';
    execute immediate 'comment on table user_test.id is ''主键编号''';
    execute immediate 'comment on table user_test.user_name is ''用户名称''';
    execute immediate 'comment on table user_test.age is ''用户年龄''';
    
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2、user_tab_columns 列信息

user_tab_columns一般用来记录该用户下的数据表的列信息。

这里是列信息！

select * from user_tab_columns ;
1

执行后，就能查看出对应的所有表的表名、列名、列的类型等。

如果涉及到针对某个表增加/删除/修改字段信息时，可以使用到这个系统视图。如下所示：

declare
  cnt integer;
begin
	select count(0)
      into cnt	
	  from user_tab_columns a
	 where a.Table_name = upper('user_test')
	   and a.COLUMN_NAME = upper('mail');
	   
    if cnt = 0 then
	   execute immediate 'ALTER TABLE user_test ADD mail VARCHAR2(32)';
	   execute immediate 'comment on column user_test.mail is ''邮箱''';
	end if;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、user_constraints 约束

user_constraints描述了所有的当前用户的所拥有的表的所有约束定义。

可以通过该视图，查询表的主键、外键等信息

select * from user_constraints;
1

执行后，可以查询到owner 所属空间、constraint_name 约束名、constraint_type 约束类型等信息。

关于Oracle的 Constraint_type 约束类型取值，可以参考下面文章：

Oracle Constraint_type,约束类型

使用FlyWay动态判断并增加/修改约束案例如下所示：

declare
 cnt integer;
begin
    select count(0)
    into cnt
    from user_constraints a
   where a.constraint_name = upper('user_test_id_PK');
    
    if cnt = 0 then
        execute immediate 'alter table user_test add constraint user_test_id_PK primary key (id)';
  end if;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13

4、user_sequences 序列

user_sequences用来管理当前数据库中所有的数据表的序列信息。通过下列命令可以查询相关信息：

select * from user_sequences;
1

执行后，可以获取到序列名、最小值、最大值、递增值、当前最新序列值等信息。

如果需要FlyWay对序列进行维护管理，则可以采取如下方式新增序列：

declare
 cnt integer;
begin
	select count(0)
		into cnt  
	from user_sequences 
	where sequence_name = upper('user_test_id_SEQ');
	
	if cnt = 0 then
	   execute immediate 'create sequence user_test_id_SEQ
						minvalue 1
						maxvalue 9999999999999999999999999999
						start with 1
						increment by 1
						cache 20';
	end if;						 
end;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5、user_indexes 索引

比如创建一个索引：

--创建索引
CREATE [UNIQUE] INDEX <索引名>
ON <基本表名> （<列名> [<次序>]，[，<列名> [<次序>]]…）； 
--说明：
--UNIQUE：规定索引的每一个索引值只对应于表中的唯一记录。
--<次序>：建立索引时指定列名的索引表是ASC（升序）或DESC（降序）。
1
2
3
4
5
6

常见的操作有如下几种：

--索引的创建语句（简洁）
create index 索引名 on 表名（列名）;
 
--标准语法
create index 索引名 on 表名（列名） tablespace 表空间名;
 
--tablespace 表空间名 可以省略，以下皆省略
 
--创建唯一索引
create unique index 索引名 on 表名（列名）；
 
--复合索引/组合索引
create index 索引名 on 表名(列名1, 列名2, 列名3, ...);
 
--反向键索引
create index 索引名 on 表名（列名） reverse；
 
--删除索引
drop index 索引名 on 表名;
 
--降序索引​
​CREATE INDEX 索引名 ON 表名(列名 desc) ;  --升序asc
 
--查看某个表中的所有索引
select * from all_indexes where table_name = '表名'
 
--查看某个表中建立了索引的所有列
​select * from all_ind_columns where table_name = '表名'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

扩充MySQL

上面说的是Oracle的相关操作，并不是说其他数据库就不可行，MySQL中也有一个information_schema全局数据库信息表，其中有多个视图。

这里不做太多说明，只展示几个常见的查询：

-- 查询数据库是否存在
select * from information_schema.SCHEMATA;
-- 表是否存在
select * from information_schema.TABLES where TABLE_SCHEMA ='test2' ;
-- 列
select * from information_schema.`COLUMNS` where TABLE_SCHEMA ='test2' and TABLE_NAME ='user';

-- 触发器
select * from information_schema.TRIGGERS t ;

-- 约束
select * from information_schema.KEY_COLUMN_USAGE kcu ;
-- 索引
select * from information_schema.STATISTICS s ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

MYSQL中information_schema简介