DCA培训技术分享—表空间、用户、模式管理篇

表空间管理

表空间介绍
表空间是DM数据库的最大逻辑存储单元，用于存放数据页，采用段、簇、页的方式进行管理。
DM默认预定义SYSTEM、ROLL、MAIN、TEMP、HMAIN五个表空间。
SYSTEM：系统表空间，存储数据字典等信息（表、字段、视图、索引等对象的定义、权限信息等，
部分隐含参数写入SYSTEM表空间）
ROLL：回滚表空间，存放回滚页，数据库中的DML操作对应都会生成redo和undo，
undo信息放入ROLL表空间对应的数据文件，redo信息写入联机日志文件，用于保证数据的一致性。
ROLL回滚段数据包含Active、unexpired和expired三种状态。
遇到查询记录过旧的错误（等同于Oracle快照过旧的错误）。解决方法：
1、增大undo_retention（回滚段的保留时长），及时、分段提交。扩大ROLL表空间
2、查询时间过长导致，可以考虑优化查询。

SQL> alter system set ‘UNDO_RETENTION’=300 both; ----参数名需要用单引号引起来
SQL> select * from v$parameter where name like ‘%UNDO_RETENTION%’;

MAIN：用户默认表空间，
当创建用户时，如果未指定用户的默认表空间，则MAIN为用户的默认表空间，相当于Oracle的USERS表空间。
TEMP：临时表空间。
存放临时表数据、临时结果集等，当大量的数据排序与创建索引时占用临时表空间。
DM中临时表空间由参数TEMP_SIZE指定。

DM中TEMP表空间在数据库每次启动时会按照如下参数重建TEMP表空间。
SQL> select name,value,type from v$parameter where name like ‘TEMP%’;
TEMP_SIZE：临时表空间初始大小
TEMP_PATH：临时表空间路径
TEMP_SPACE_LIMIT：临时表空间的空间限制，0表示不限制。

修改临时表空间的初始大小
SQL> alter system set ‘TEMP_SIZE’=100 spfile;

收缩TEMP表空间的方法
1、重启数据库，TEMP表空间数据文件会重建。
2、在线收缩临时表空间，使用sp_trunc_ts_file函数。
SQL> select name,id from v$ifunwherenamelik{e}^{\prime }S{P}_{T}RUNCSQL>select\ast fromv$ifun_arg where id=1298;

HMAIN：HUGE表的默认表空间（HTS表空间）

SQL管理表空间
创建表空间
create tablespace tbs datafile ‘tbs01.dbf’ size 32;
DM数据文件大小，最大值不能低于页大小的4096倍（如果页大小是8K，最小值将不低于32M），
最大值为页的2的31次方-1（如果页大小是8K，最大值为16T-1）。

数据库安装用户dmdba要有对指定的数据文件目录的操作权限
alter tablespace tbs rename datafile ‘tbs01.dbf’ to ‘/dm8/data/DM/TBS/TBS01.DBF’;

相关数据字典：
select * from dba_tablespaces;
select * from dba_data_files;
select * from v$tablespace;select\ast fromv$datafile;
select * from dba_free_space;

表空间脱机
SYSTEM、ROLL、TEMP表空间不能脱机
表空间脱机后，该表空间中的数据将不能读写。
示例：
create table t_test(id int,name varchar()20) tablespace tbs;
insert into t_test(id,name) values(1,‘joy’);

alter tablespace tbs offline;
select * from t_test; ------此时查询失败

表空间重命名
DM预定义表空间不支持重命名，自定义表空间支持重命名。
alter tablespace tbs rename to dmtbs;

删除表空间
系统预定义表空间不能删除，DM只能删除空的表空间
drop tablespace dmtbs; ----此时删除失败，需删除表空间中的所有对象

创建表空间示例
create tablespace tbstest datafile ‘/dm8/data/DM/TBS/TBSTEST01.DBF’ size 64 autoextend on next 2 maxsize 10240,
‘/dm8/data/DM/TBS/TBSTEST01.DBF’ size 64 autoextend on next 2 maxsize 10240 CACHE=NORMAL;

管理数据文件
修改表空间添加数据文件
alter tablespace tbs add datafile ‘tbs02.dbf’ size 128 autoextend on next 2 maxsize 20480;
修改表空间数据文件的扩展属性
alter tablespace tbs datafile ‘tbs01.dbf’ autoextend on next 2 maxsize 20480;
alter tablespace tbs datafile ‘tbs01.dbf’ autoextend off;
修改表空间数据文件大小
alter tablespace tbs resize datafile ‘tbs01.dbf’ to 128;

迁移表空间数据文件
alter tablespace tbs offline;
alter tablespace tbs rename datafile ‘tbs01.dbf’ to /dm8/data/DM/TBS/TBS01.DBF’;
alter tablespace tbs rename datafile ‘tbs02.dbf’ to /dm8/data/DM/TBS/TBS02.DBF’;
alter tablespace tbs online;

管理重做日志文件
联机重做日志：存放redo信息，循环使用，不断覆盖
DM数据库联机日志自动切换，不能手工切换。
DM联机日志没有日志组。
数据字典：
select * from v$rlogfile;select\ast fromv$rlog;
修改联机日志文件大小
alter database resize logfile ‘/dm8/data/DM/DM01.log’ to 300;
添加联机日志文件
alter database add logfile ‘/dm8/data/DM/DM02.log’ to 300;

修改联机日志文件路径（迁移联机日志文件）
alter database mount;
alter database rename logfile ‘DM01.log’ to ‘/dm8/data/DM/REDO/DM01.log’;
alter database open;

归档管理
默认情况下，DM不开启归档。
归档是对redo的归档。
归档的目的是使数据库故障时可以恢复到故障的前一刻（完全恢复），或者恢复到指定的时间点或指定LSN（不完全恢复）

开启归档方法：
alter database mount;
alter database archivelog;
select arch_mode from v$database;alterdatabaseaddarchivelo{g}^{\prime }type=local,dest=/dm8/arch,fil{e}_{s}ize=64,spac{e}_{l}imit=10240^{\prime };alterdatabaseopen;select\ast fromv$dm_arch_ini;

关闭归档
alter database mount;
alter database noarchivelog;
alter database delete archivelog ‘type=local,dest=/dm8/arch’;
alter database open;

DM管理工具图形化界面开启归档
右击“LOCALHOST(SYSDBA)”→“管理服务器”→“系统管理”，选择“配置”，点击“转换”→
选择“归档配置”，确定归档路径、文件大小、文件限制等后，点击“确定”→再次选择“系统管理”，
选择“打开”，点击“转换”，至此，归档开启完成。

修改dm.ini和dmarch.ini配置文件开启归档
$vim dmarch.ini
ARCHI_WAIT_APPLY=0
[ARCHIVE_LOCAL1]
ARCH_TYPE=LOCAL
ARCH_DEST=/dm8/arch
ARCH_FILE_SIZE=64
ARCH_SPACE_LIMIT=10240
ARCH_FLUSH_BUF_SIZE=0

$vim dm.ini
ARCH_INI=1

归档日志文件的切换和删除
数据库自动完成归档的切换，DM支持手工切换归档
alter system archive log current;
alter system switch logfile;
alter database archivelog current;

归档文件的删除
select * from v$ifun where name like ‘SF_ARCHIVELOG_%’;
SF_ARCHIVELOG_DELETE_BEFORE_LSN
SF_ARCHIVELOG_DELETE_BEFORE_TIME
select SF_ARCHIVELOG_DELETE_BEFORE_TIME(sysdate-10); ----删除10天前的归档日志

针对业务繁忙期间，出现磁盘IO不定时繁忙的情况
因为配置了归档的空间上限，生成新的归档日之前删除就的归档日志文件。
配置JOB作业，在业务空闲期定时清理N天前的归档文件。

用户管理

DM默认的预定义用户：
SYS:系统内置用户，不允许登录
SYSDBA:系统管理员，拥有几乎所有权限（除审计和强制访问控制）
SYSAUDITOR:系统审计管理员，具有审计相关权限
SYSSSO:系统安全管理员，具有强制访问控制等权限
SYSDBO:安全版本才有的用户，安全操作员

系统口令策略
PWD_POLICY（隐含参数，不在dm.ini中）参数指定系统的口令策略，默认为2
系统支持的口令策略有：
0 无策略
1 禁止与用户名形同
2 口令长度需大于等于ini参数PWD_MIN_LEN设置的值
4 至少包含一个大写字母（A-Z）
8 至少包含一个数字（0-9）
16 至少包含一个标点符号（英文输入法状态下，除’'和空格外的所有符号）
口令策略可单独应用，也可组合应用。组合应用时，如需要应用策略2和4，则设置口令策略为2+4=6即可。

示例1
查看当前口令策略
select * from v$parameter where name=‘PWD_POLICY’;

创建新用户
create user hr identified by dameng123；

修改系统口令策略
alter system set PWD_POLICY=15 both;

此时再创建新用户设置密码为dameng123会报错，提示“密码不符合复杂度规则”

用户输入密码错误多次（默认3次），将会被锁定。
查看DBA_USERS表了解用户状态
select a.id,b.username,b.ACCOUNT_STATUS
from sysusers a,dba_users b
where a.id=b.user_id;

指定表空间创建用户
create user hrtest identified by dameng123 default tablespace tbstest；
用户锁定和解锁
alter user hr account unlock;
alter user hr account lock;
修改用户的默认表空间
alter user hr default tablespace tbs_dm;

查看用户所使用的的表空
select a.username,a.default_tablespace,a.created from dba_users a;

修改用户密码
alter user httest identified by DM@123;
删除用户：
drop user if exists hr;
drop user if exists hr cascade; —生产环境慎用

用户资源限制
可以直接使用limit子句，也可以使用profile来管理用户的资源限制
alter user hr limit failed_login_attemps 5,password_lock_time 3;

DM支持使用profile管理用户资源限制（2021年10月以后的新版本支持）
select * from DBA_PROFILES;
create profile profile_test1 limit failed_login_attemps 5,password_lock_time 3，password_life_time 180;
alter user hrtest profile profile_test1;

密码带有特殊字符的登录处理，需使用同时使用单引号和双引号引起来，例：‘“password”’。
SQL> create user hr_1 identified by “DMdameng@123”; —这样的密码设置为DMdameng@123

示例1，在linux下使用disql登录：
$ disql hr_1/‘“DMdameng@123”’
示例2，在SQL下使用conn登录：
SQL> conn hr_1/“DMdameng@123”

授予权限
授予user02用户查询user01下的table_01表
SQL> grant select on user01.table01 to user02;

查看当前用户拥有的权限
select * from session_privs;

权限回收
回收user02用户查询user01下的table_01表的权限
SQL> revoke select on user01.table01 from user02;
SQL> revoke create table from user02;

授予查询对象权限，精确到列
SQL> grant select(clumns1,clumns2,…) on user01.table01 from user02;

相关数据字典：
select * from dba_sys_privs t where t.grantee=‘USER01’;
select * from dba_role_privs t where t.grantee=‘USER01’;
select * from dba_tab_privs t where t.grantee=‘USER01’;

DM默认不能授予用户在其他模式下数据定义（DDL）的权限，由参数ENABLE_DDL_ANY_PRIV指定，默认为0；
grant select any table to user02; —未设置之前可以授予查询任何表的权限，但无法授予DDL权限。
select * from v$parameter t where name like ‘%ENABLE_DDL_ANY_PRIV%’;

alter system set ENABLE_DDL_ANY_PRIV=1 both; ----开启后可正常赋予权限
grant create any table to user02;
grant create any index to user02;

示例：
授予test用户更新/删除emp表的权限（赋予表更新或删除的权限时，同时要赋予查询权限，否则报错）
grant update on emp to test;
grant delete on emp to test;
grant select on emp to test;

角色管理
角色是权限的集合，角色使权限管理更加方便。
DBA：具有几乎所有权限（除审计和强制访问控制外），默认赋予SYSDBA用户。
PUBLIC：具有对当前模式下对象的DML数据操作权限。
RESOURCE：具有在当前模式下对象定义权限（创建表、索引、视图等）。
SOI：具有查询sys开头的系统表的权限。
VTI：具有查询v$开头的动态视图权限（没有此权限，DM管理工具上会报没有查询v$视图权限）

select * from dba_roles;

create role r1;
grant create table to r1;
grant select on user01.table01 to r1;
grant r1 to user02;

sp_set_role(‘R1’,0) ------角色禁用和启用，禁用后角色权限将不在生效。

授予对象权限增加with grant option说明权限可以转授，回收时要增加cascade关键字级联回收权限。
create role r2;
grant select on user01.table01 to r2 with grant option;
revoke select on user01.table01 from r2 cascade;

模式对象管理

管理模式
模式与用户之间的关系
模式：是一个特定的对象集合，在概念上可将其看作是包含表、视图、索引等若干对象的对象集。
模式对象：表、视图、约束、索引、序列、触发器、存储过程/函数、包、同义词、类、域

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

select * from SYSOBJECTS t where t.type${=}^{\prime }SC{H}^{\prime };-------查看模式select\ast fromSYSOBJECTStwheret.subtype$=‘USER’; ------查看用户

查询模式和用户的对应关系
select a.id schema_id,a.name schema_name,b.id user_id,b.name user_name
from sys.sysobjects a,sys.sysobjects b
where a.type$=‘SCH’ and a.pid=b.id;

模式管理
创建模式
create schema hrtest01 authorization hrtest;
create table hrtest01_test(id int,name varchar(20));

查看当前模式和当前用户
select sys_context(‘USERENV’,‘CURRENT_SCHEMA’);
select sys_context(‘USERENV’,‘CURRENT_USER’);

切换模式（仅对当前会话生效）
set schema dmhr;

删除模式
drop schema if exists hrtest01;
drop schema if exists hrtest01 cascade; --级联删除模式下的对象，生产环境慎用

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

对聚簇索引键的范围查看，索引组织表更高效。

DM默认创建的索引组织表，由参数LIST_TABLE指定。
select * from v$parameter t where name=‘LIST_TABLE’;

DM创建表时如果指定了主键，则主键为聚簇索引键（针对部分版本，由参数PK_WITH_CLUSTER指定）；
如果创建表时未指定主键，则rowid为聚簇索引键。
select * from v$parameter where name=‘PK_WITH_CLUSTER’;

SQL管理表
创建表
create table hrtest.table01(
pid int,
pname varchar(20),
sex bit,
logtime datetime)
tablespace dmtbs;

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 where name like ‘%CTAB_SEL_WITH_CONS%’;

添加字段
alter table hrtest.table01 add column email varchar(20);
修改字段
alter table hrtest.table01 modify email varchar(50);
删除字段
alter table hrtest.table01 drop logtime;
对字段添加默认值（大表不建议添加字段时给默认值）
alter table hrtest.table01 add column logtime datetime default sysdate;

数据字典
select * from dba_tables where owner=‘HRTEST’;
select * from dba_tab_columns where owner=‘HRTEST’;

对大表添加字段时，不建议给默认值（耗时很长）
修改表的表空间（DM会自动重建该表上的索引）
alter table hrtest.table01 move tablespace tbs_user;
select * from dba_tables where table_name = ‘table01’;

数据的导入
DM中disql下用start或’'都可以，Oracle中使用@ SQL> start /dm8/backup/dts/t_department.sql SQL> /dm8/backup/dts/t_department.sql
或
disql可以直接调用脚本
$disql hr/Dameng123 ‘`city.sql’
可以关闭回显以提高导入效率
SQL> help set
SQL> set timing off;
SQL> set feedback off;
SQL> set echo off;

DM管理工具只能使用’`'，且后面的文件路径不能有空格，客户端导入需要手工commit；

管理约束
约束类型
NOT NULL：非空约束
UNIQUE：唯一约束
PRIMARY KEY：主键约束（唯一约束和非空约束）
FOREIGN KEY：外键约束
CHECK：检验约束

SQL管理约束
非空约束
alter table hrtest.table01 modify pname not null;
唯一约束
alter table hrtest.table01 add constraint uk_table01_email unique(email);
主键约束
alter table hrtest.table01 add constraint pk_table01_pid primart key(pid);
外键约束
alter table hrtest.table02 add constraint fk_table02_id foreign key(id) references hrtest.table01(pid);
检验约束
alter table hrtest.table01 add salary number(10,2);
alter table hrtest.table01 add constraint ck_table01_salary check (salary>=2100);

约束的禁用、启用和删除
alter table hrtest_table disable constraint fk_table_id;
alter table hrtest_table enable constraint fk_table_id;
alter table hrtest_table drop constraint fk_table_id;

测试插入数据
SQL> insert into hrtest.table01(pid,pname,sex,email,salary) values(1,‘test’,0,‘test@126.com’,2800);

相关数据字典
select * from dba_constraints where owner=‘HRTEST’;
select *from dba_cons_columns where owner=‘HRTEST’;

批量禁用外键约束
select ‘alter table ‘||owner||’.’||table_name|| ’ disable constraint ‘||constraint_name||’;’
from dba_constraints
where owner=‘HRTEST’ and constraint_type=‘R’;

管理索引
索引：
二级索引（B树索引），索引的存在是为了提高查询效率。
索引存放的是索引列值和rowid。
一种数据对象，通过指针加速查询速度，通过快速定位数据的方法，减少磁盘IO；
索引与表互相独立，索引占用存储空间（如果一个表越大，其索引也会越来越大）；
索引相当于一个小表，索引是有序的（按照索引字段排序）；
在查询时服务器自动使用索引，DML操作时自动维护索引；
索引不是越多越好，索引会降低DML的效率（DML操作需要维护索引）；
索引包含；聚簇索引、二级索引、函数索引、位图索引和组合索引等；
组合索引创建时要注意索引列的顺序，一般经常查询的列和等值查询列放在前面。

SQL管理索引
查看执行计划
explain select *from hrtest.emp01 where employee_name=‘JOY’;
创建索引
create index ix_emp01_employeename on hrtest.emp01(employee_name);

统计信息的收集
SQL> dbms_stats.gather_table_stats(‘HRTEST’,‘EMP01’);
统计信息的查看
SQL> dbms_stats.table_stats_show(‘HRTEST’,‘EMP01’);
SQL> dbms_stats.index_stats_show(‘HRTEST’,‘IX_EMP01_EMPLOYEENAME’);

索引的监控
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 rebulid online;

索引的数据字典
select * from dba_indexes where owner=‘HRTEST’;
select * from dba_ind_columns where index_owner=‘HRTEST’;

删除索引
drop index hrtest.ix_emp01_employeename;

管理视图
普通视图（物化视图除外）中不包含数据，数据来源于基表。视图仅提供一个查询窗口。
从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。
从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。

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

复制视图：
多表连接，包含聚合函数、group by等，一般不支持增删改操作。

创建视图
create view hrtest.v_table01
as
select employee_id,employee_name,email,phone_num
from dmhr.employee
where department_id=1001;

create or replace view hrtest.v_table02
as
select employee_id,employee_name,email,phone_num
from dmhr.employee
where department_id=1001;

select * from hrtest.v_table02;

创建复制视图
create or replace view hrtest.v_emp_salary
as
select a.deparment_id,sum(a.salary) sum_salary
from dmhr.employee a
group by a.deparment_id
having sum(a.salary) > 100000;

select * from hrtest.v_emp_salary;

create or replace view v_empnum
as
select t.department_id,count() empnum
from t_emp01 t
group by t.department_id
having count() >25;

select * from v_empnum;

排行榜视图
create view hrtest.v_salarytop
as
select e.employee_name,d.deparment_name,e.salary
rank()over(order by salary desc) rank,
dense_rank()over(order by salary desc) dense_rank,
row_number ()over(order by salary desc) rownumber
from dmhr.employee e,dmhr.deparment t
where e.deparment_id=d.department_id(+);

select * from hrtest.v_salarytop;

数据字典和动态性能视图
数据字典
存储在SYSTEM表空间中，包含对象定义、权限、用户角色等信息；
USER_* 用户所拥有的的对象信息
ALL_* 用户能访问的对象信息
DBA_* 整个数据库中的对象信息

系统中所有对象的信息
select * from sysobjects;
系统中所有索引定义信息
select * from sysindexes;
系统中所有列定义的信息
select * from syscolumns;

select * from sysstats;
select * from sysobjects where id=1058
select * from syscons;

动态性能视图
动态性能视图是从内存中或控制文件中读取的数据
select * from v$bufferpool;--数据缓冲区select\ast fromv$mem_pool;

显示数据文件、表空间信息
select * from v$datafile;select\ast fromv$tablespace;

显示当前进程、线程信息
select * from v$process;select\ast fromv$threads;

事务等待案例
会话一：
在t_testpid（pid为主键）表中插入一行记录，不提交；
SQL> insert into t_testpid(pid,pname) values(4,‘test01’);
会话二：
在t_testpid表中插入相同的记录；
SQL> insert into t_testpid(pid,pname) values(4,‘test01’);

查询事务等待：
SQL> select * from sys.v$trxwait;SQL>select\ast fromv$lock where blocked=1;
SQL> select a.sess_id,a.sql_text,a.state,b.*
from v$sessionsa,v$trxwait b
where a.trx_id=b.id;

SQL> select * from sys.sysobjects where id=1069; —查询被锁的表
SQL> sp_close_session(sess_id);

查询未提交的事务
select b.object_name,c.sess_id,c.thrd_id,c.state,c.sql_text,a.*
from v$locka,db{a}_{o}bjectsb,v$sessions c
where a.table_id=b.object_id
and a.ltype=‘OBJECT’
and a.trx_id=c.trx_id
and a.ign_flag=0;

达梦社区：https://eco.dameng.com