背景描述:
创建DB用户时,一般是先创建此DB用户自己专有的表空间,即DB用户默认表空间。如果不指定DB用户的表空间,则数据会默认存到MAIN表空间。时间久了,MAIN会越来越大,且很难定位哪个业务导致的。
达梦支持修改DB用户的默认表空间,但是,修改的这一时刻将变成分界岭,一部分历史数据还在MAIN表空间,新写入的数据会存到新表空间。
早些年管理要求低,导致有的DB用户在MAIN表空间,有的DB用户在专有表空间。随着时代进步和更新换代,客户就要求在新数据库部署时,保留老数据的同时,重新规范管理数据表空间。
场景需求:
老库DB1有100个DB用户,其中60个用的默认表空间MAIN,剩余40个用的专有表空间。要求将DB1的数据迁移到新库DB2,同时100个用户的数据都在各自专有表空间。
实操之前,需要先了解逻辑导出(dexp)两个参数:
cd /home/dmdba/dmdbms/bin
./dexp SYSDBA/SYSDBA@192.168.122.1:5236 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236.log FULL=Y
./dexp SYSDBA/SYSDBA@192.168.122.1:5236 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236.log FULL=Y TABLESPACE=Y
full=y表示导出整个数据库,包括数据库的所有对象(如表、视图、存储过程等)和数据。但是,不包含表空间。
全库导出的同时,导出表空间的话,追加参数TABLESPACE=Y
PS:如果源库本身表空间管理就不规范,那么即使导出了,还原到新库也是不规范的。
实操:方式一(知道所有DB用户的密码)
DB1数据库创建测试数据,以10个为例,参考附件SQL1-1
PS:此方式,前提是知道这100个DB用户的密码,不然会影响业务。这种方式成本比较高,好多老项目DB用户已经丢失了密码。
1.DB1数据库全库逻辑导出
cd /home/dmdba/dmdbms/bin
./dexp SYSDBA/SYSDBA@192.168.122.1:5236 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236-out.log FULL=Y
2.DB2数据库,创建专有表空间
x1~6手动创建,x7~10的可以复制DB1的ddl,创建用户指定各自的表空间。参考附件SQL2-1
此时DB2还没有数据。
3.DB2逻辑导入
cd /home/dmdba/dmdbms/bin
./dimp SYSDBA/SYSDBA@192.168.122.1:5238 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236-in.log
--x5 x6 不指定专有表空间,一会对比更直观
预想结果:X1-X4、X7-10数据都在自己的表空间,X5-X6还在MAIN表空间。
验证截图:
实操:方式二(不知道DB用户密码)
1.模拟DB2库不创建用户和表空间,直接还原
先删除DB2的测试数据,参考附件SQL2-2
2.DB2再次逻辑导入
cd /home/dmdba/dmdbms/bin
./dimp SYSDBA/SYSDBA@192.168.122.1:5238 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236-in.log
预想结果:X1-10数据都在MAIN表空间。DB用户都会导入进来。
验证截图:
3.创建每个用户的专有表空间
命令同上
4.再次逻辑导入,加参数TABLE_EXISTS_ACTION=REPLACE
如果表存在,则替换,这样就能把main里面的数据转移到专有表空间
cd /home/dmdba/dmdbms/bin
./dimp SYSDBA/SYSDBA@192.168.122.1:5238 file=/home/dmdba/full5236.dmp log=/home/dmdba/full5236-in.log TABLE_EXISTS_ACTION=REPLACE
SQL附件
SQL1-1:DB1创建demo数据sql脚本
create user "X1" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x1.t1 (id varchar(10),name varchar(10));
insert into x1.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X2" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x2.t1 (id varchar(10),name varchar(10));
insert into x2.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X3" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x3.t1 (id varchar(10),name varchar(10));
insert into x3.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X4" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x4.t1 (id varchar(10),name varchar(10));
insert into x4.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X5" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x5.t1 (id varchar(10),name varchar(10));
insert into x5.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X6" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
create table x6.t1 (id varchar(10),name varchar(10));
insert into x6.t1 values ('1','zhangsan'),('2','lisi');
commit;
create tablespace "x7" datafile '/home/dmdba/dmdbms/data/DM/x7.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x8" datafile '/home/dmdba/dmdbms/data/DM/x8.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x9" datafile '/home/dmdba/dmdbms/data/DM/x9.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x10" datafile '/home/dmdba/dmdbms/data/DM/x10.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create user "X7" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x7";
create table x7.t1 (id varchar(10),name varchar(10));
insert into x7.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X8" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x8";
create table x8.t1 (id varchar(10),name varchar(10));
insert into x8.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X9" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x9";
create table x9.t1 (id varchar(10),name varchar(10));
insert into x9.t1 values ('1','zhangsan'),('2','lisi');
commit;
create user "X10" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x10";
create table x10.t1 (id varchar(10),name varchar(10));
insert into x10.t1 values ('1','zhangsan'),('2','lisi');
commit;
SQL2-1:DB2创建demo数据sql脚本
create tablespace "x1" datafile '/home/dmdba/dmdbms/data/DM2/x1.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x2" datafile '/home/dmdba/dmdbms/data/DM2/x2.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x3" datafile '/home/dmdba/dmdbms/data/DM2/x3.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x4" datafile '/home/dmdba/dmdbms/data/DM2/x4.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x5" datafile '/home/dmdba/dmdbms/data/DM2/x5.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x6" datafile '/home/dmdba/dmdbms/data/DM2/x6.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x7" datafile '/home/dmdba/dmdbms/data/DM2/x7.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x8" datafile '/home/dmdba/dmdbms/data/DM2/x8.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x9" datafile '/home/dmdba/dmdbms/data/DM2/x9.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create tablespace "x10" datafile '/home/dmdba/dmdbms/data/DM2/x10.dbf' size 32 autoextend on next 10 maxsize 1024 CACHE = NORMAL;
create user "X1" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x1";
commit;
create user "X2" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x2";
commit;
create user "X3" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x3";
commit;
create user "X4" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x4";
commit;
create user "X7" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x7";
commit;
create user "X8" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x8";
commit;
create user "X9" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x9";
commit;
create user "X10" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10
default tablespace "x10";
commit;
--x5 x6 不指定专有表空间,一会对比更直观
create user "X5" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
commit;
create user "X6" identified by "dmdba1234"
limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10;
commit;
SQL2-2:DB2删除测试表、用户、表空间
删除有依赖约束,先删表、再删用户、最后删表空间
drop table x1.t1;
drop table x2.t1;
drop table x3.t1;
drop table x4.t1;
drop table x5.t1;
drop table x6.t1;
drop table x7.t1;
drop table x8.t1;
drop table x9.t1;
drop table x10.t1;
drop user "x1";
drop user "x2";
drop user "x3";
drop user "x4";
drop user "x5";
drop user "x6";
drop user "x7";
drop user "x8";
drop user "x9";
drop user "x10";
drop tablespace "x1";
drop tablespace "x2";
drop tablespace "x3";
drop tablespace "x4";
drop tablespace "x5";
drop tablespace "x6";
drop tablespace "x7";
drop tablespace "x8";
drop tablespace "x9";
drop tablespace "x10";
仅供参考,如有不足,欢迎斧正。
更多资讯请上达梦技术社区了解: https://eco.dameng.com/