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;
复制
