达梦7迁移过程中常用sql整理

--dm7禁用/启用所有用户模式触发器

select 'alter trigger '||owner||'.'||trigger_name||' disable; ' from 
sys.dba_triggers where owner not like 'SYS%' and trigger_name not like 'SREF_CON_%';

--dm7禁用/启用所有用户模式外键

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||'; ' 
from sys.dba_constraints where owner not like 'SYS%' and constraint_type='R';

--扩充日志文件（也可在manager中右键管理服务器日志文件进行增加）

alter database resize logfile 'DALIAN01.log' to 2048;
alter database resize logfile 'DALIAN02.log' to 2048;
alter database add logfile '/dbdata/dmdata/DALIAN/DALIAN03.log' size 2048;
alter database add logfile '/dbdata/dmdata/DALIAN/DALIAN04.log' size 2048;

--表空间创建

select 'create tablespace "'||name||'" datafile ''/home/dmdba/dmdbms/data/DAMENG/'||name||'.dbf''size 128 CACHE = NORMAL;' FROM "SYSDBA"."SYSSCHEMAS"
 WHERE NAME NOT IN ('SYSDBA','SYSSSO','SYSAUDITOR','INFORMATION_SCHEMA');

--用户创建

SELECT 'create user "'||NAME||'" identified by "'||NAME||'.2019" default tablespace "'||NAME||'" ;'
 FROM V$TABLESPACE WHERE NAME NOT IN ('ROLL','MAIN','TEMP','SYSTEM')

--权限授予

select 'grant "DBA","PUBLIC","RESOURCE" to "'||username||'";'FROM "SYS"."ALL_USERS" WHERE USERNAME NOT IN ('SYSDBA','SYS','SYSAUDITOR','SYSSSO') ;

--查询目的端是否有DML操作sql

select sql_test appname from v$sessions where appname<>'dmhs_server' and sql_test not like '%select%';

O_O......