1.模式对象管理

1.1 管理模式

1.1.1 模式与用户之间的关系

模式定义：
模式是一个特定的对象集合，在概念上可将其看作是包含表、视图、索引等若干对象的对象集

模式对象:
表、视图、约束、索引、序列、触发器、存储过程/函数、包、同义词、类、域

模式与用户之间的关系：
当系统建立一个用户时，会自动生成一个同名的模式用户还可以建立其他模式，DM 中用户和模式是一对多的关系，一个用户可以拥有多个模式，一个模式仅能归属于一个用户。Oracle 中用户和模式是一对一的关系。

相关查询：

--查看模式
select * from SYSOBJECTS t where t."TYPE$" ='SCH'; 
--查看用户
select * from SYSOBJECTS t where t."SUBTYPE$" ='USER'; 
--查询模式和用户的对应关系
select a.id scheid, a.name schename, b.id userid, b.name username
from SYS.SYSOBJECTS a, SYS.SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.pid = b.id;

1.1.2 模式管理

建议采用单用户，单模式来使用

创建模式：

--创建模式
create schema hrtest01 AUTHORIZATION HRTEST;
--指定模式创建表
create table hrtest01.t_test(id int, name varchar(20));

查看当前模式和当前用户：

--查询当前模式
select sys_context('USERENV','CURRENT_SCHEMA');
--查询当前用户
select sys_context('USERENV','CURRENT_USER'); 或 select user;

切换模式

--仅对当前会话生效
[dmdba@dmserver ~]$ disql sysdba/Newcapec.2022

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.327(ms)
disql V8
SQL> select sys_context('USERENV','CURRENT_SCHEMA');

行号     SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1          SYSDBA
SQL> set schema dmhr;
操作已执行
已用时间: 0.410(毫秒). 执行号:0.
SQL> select sys_context('USERENV','CURRENT_SCHEMA');

行号     SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1          DMHR

删除模式

drop SCHEMA IF EXISTS HRTEST01;
--级联删除模式下对象，生产环境慎用
drop SCHEMA IF EXISTS HRTEST01 CASCADE; 

1.2 管理表

1.2.1 管理表

表包含：索引组织表、堆表、分区表、HUGE 列存储表、外部表、临时表。DM 默认创建的是索引组织表，Oracle 默认创建的是堆表。

索引组织表和堆表的区别：

• 索引组织表：有且仅有一个聚簇索引键，表数据按照聚簇索引键排序（数据是有序的，插入
  有序），如果在创建表时未指定聚簇索引键，默认使用 rowid 为聚簇索引键。rowid 是逻辑
  rowid，占用存储空间，所以索引组织表比堆表占用较多空间。
• 堆表：数据是无序的，插入无序，堆表插入效率较高，rowid 是物理 rowid，不占用存储空
  间。所以堆表比索引组织表节约空间。
  对聚簇索引键的范围查询，索引组织表更高效。

创建表：

--创建表
create table t_userinfo(userid int,username varchar(20)，sex bit,logdate date default sysdate,logtime defaut sysdate,logdatetime default sysdate) tablespace tbs;
--插入数据
insert into t_userinfo(userid,username,sex) values(1,'cheng',0)

CTAS创建表：

--备份表和数据
create table t_emp01 as select * from dmhr.employee;
create table t_emp02 like dmhr.employee;
--只要表结构，不要数据
create table t_emp03 as select * from dmhr.employee where 1=0

使用CREATE table as创建表时，默认不会复制表的约束信息，由参数CTAB_SEL_WITH_CONS指定：

select * from SYS."V$PARAMETER" t where t.name like 'CTAB_SEL_WITH_CONS%';

添加字段：

alter table t_userinfo add column email varchar(50);

修改字段：

alter table t_userinfo modify email varchar(200);

删除字段：

alter table t_userinfo drop column email;

重命名表或字段：

alter table t_userinfo rename to t_user;
alter table t_userinfo rename column sex to gender;

相关数据字典：

select * from dba_tables t where t.owner='HRTEST';
select * from dba_tab_columns t where t.owner='HRTEST';
select t.TABLE_NAME, t.TABLESPACE_NAME from user_tables t;
select t.TABLE_NAME, t.COLUMN_NAME, t.NULLABLE from USER_TAB_COLS t;

1.2.2 数据导入

DM 在 DISQL 下用 start 或`都可以， Oracle 用@：

SQL> start /dm8/backup/dts/t_department.sql
SQL> `/dm8/backup/dts/t_department.sql

DM 管理工具只能使用`, `后面的文件路径不能有空格，导入后需要手工 commit：

`/dm8/backup/dts/t_department.sql

1.3 管理约束

1.3.1 约束类型

NOT NULL：非空约束

UNIQUE：唯一约束

PRIMARY KEY：主键约束 (唯一约束+非空约束)

FOREIGN KEY：外键约束

CHECK：检验约束

1.3.2 管理约束

非空约束：

alter table hrtest.t_testpid modify pname not null;

唯一约束：

alter table hrtest.t_testpid add CONSTRAINT uk_testpid_email unique (email);

主键约束：

alter table hrtest.t_testpid ADD CONSTRAINT pk_testpid_pid PRIMARY KEY(pid);

检查约束：

alter table hrtest.t_testpid ADD CONSTRAINT ck_testpid_salary CHECK (salary>=2100);
alter table dmtest.t_userinfo add constraint ck_userinfo_logdatetime check (logdatetime>'2022-1-1');

外键约束：

--引用另外一张表的主键或者唯一键
alter table hrtest.t_test add CONSTRAINT fk_test_id FOREIGN KEY(id) REFERENCES
hrtest.t_testpid(pid);
alter table ccense.t_userinfo add CONSTRAINT fk_dmhr_dept FOREIGN KEY(deptno) REFERENCES dmhr.DEPARTMENT(department_id);

约束的禁用和启用、删除：

alter table hrtest.t_test disable CONSTRAINT fk_test_id;
alter table hrtest.t_test enable CONSTRAINT fk_test_id;
alter table hrtest.t_test drop CONSTRAINT fk_test_id;

批量禁用外键约束：

select 'alter table '||owner||'.'||table_name||' disable constraint '|| t.CONSTRAINT_NAME ||';'
from DBA_CONSTRAINTS t
where t.OWNER ='DMTEST' and t.CONSTRAINT_TYPE = 'R';

相关数据字典：

select * from dba_constraints t where t.owner='HRTEST';
select * from DBA_CONS_COLUMNS t where t.owner='HRTEST';

1.4 管理索引

索引类型包含：聚簇索引、二级索引、函数索引、位图索引（主要用于 OLAP 系统）、位图连接
索引、全文索引、组合索引等。
索引：二级索引（B 树索引），索引的存在是为了提高查询速度。索引存放的是索引列值、聚簇
索引键和 rowid。
一种数据库对象，通过指针加速查询速度，通过快速定位数据的方法，减少磁盘 I/O。

索引特点：
索引与表相互独立，索引占用存储空间（如果一个表越大，其索引也会越来越大），
索引相当于一个小表，索引是有序的（按照索引字段排序），在查询时服务器自动使用索引，DML
操作时自动维护索引。
索引的优点：
提高查询性能、减少排序。
索引的缺点：
索引不是越多越好，索引会降低 DML 的效率（DML 操作需要维护索引）。
组合索引创建时要注意索引列的顺序（一般经常查询的列放在前面，等值查询列放在前面）

创建索引：

create index ix_emp01_employeename ON HRTEST.T_EMP01(EMPLOYEE_NAME);
--查看执行计划
explain select * from hrtest.t_emp01 t where T.EMPLOYEE_NAME = '马学铭';

索引的监控：

--开启索引监控
alter index HRTEST.IX_EMP01_EMPLOYEENAME MONITORING USAGE; 
--关闭索引监控
alter index HRTEST.IX_EMP01_EMPLOYEENAME NOMONITORING USAGE; 
--查看索引的监控信息
select * from v$object_usage; 

索引的重建:

--生产环境建议使用 online 方式重建，不影响表的DML操作
alter index HRTEST.IX_EMP01_EMPLOYEENAME rebuild ONLINE;

删除索引：

drop index HRTEST.IX_EMP01_EMPLOYEENAME;

索引相关的数据字典：

select * from dba_indexes t where t.OWNER ='HRTEST';
select * from dba_ind_columns t where t.index_OWNER ='HRTEST';

1.5 管理视图

普通视图（物化视图除外）中不包含数据，数据来源于基表。视图提供一个查询窗口。

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。
从数据库系统内部来看，一个视图是由 SELECT 语句组成的查询定义的虚拟表。

简单视图：单表查询，不包含聚合函数、group by 等。一般可以支持 DML 操作，对视图的
DML 操作都会转化为对基表的 DML 操作，DML 操作要满足基表的约束条件。
复杂视图：多表连接，包含聚合函数、group by 等。一般不支持增删改操作。

创建视图：

create view hrtest.v_emp
as
select a.EMPLOYEE_ID, a.EMPLOYEE_NAME, a.EMAIL, a.PHONE_NUM
from dmhr.employee a
where a.DEPARTMENT_ID = 1001;

重建视图：

create or REPLACE view hrtest.v_emp
as
select a.EMPLOYEE_ID, a.EMPLOYEE_NAME, a.EMAIL, a.PHONE_NUM, a.SALARY
from dmhr.employee a
where a.DEPARTMENT_ID = 1001;

创建复杂视图：

create or REPLACE view hrtest.v_emp_salary
as
select a.DEPARTMENT_ID, sum(A.SALARY) SUM_SALARY
from dmhr.employee a
group by a.DEPARTMENT_ID
having sum(A.SALARY) > 100000;