常用的数据库schema迁移语句

最新推荐文章于 2024-04-17 16:39:38 发布

原创最新推荐文章于 2024-04-17 16:39:38 发布 · 919 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#schema #数据库 #user #compression #parallel #session

ORACLE 专栏收录该内容

113 篇文章

订阅专栏

本文介绍了数据库迁移的步骤，包括使用expdp进行源数据库的导出，以及目标数据库中对用户<CATA>、<CATB>、<CORE>、<PUB>的账户锁定。同时，展示了如何通过SQL查询来结束特定用户的会话，并创建目录DB_BACKUP用于存储备份文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--1. Source DB

expdp system/<password>@<orcl> DIRECTORY=db_backup DUMPFILE=FALABELLA_SO_20080808.DMP SCHEMAS=<CATA>,<CATB>,<CORE>,<PUB> logfile=expdp_falabella_SO_20080808.log parallel=1 CONTENT=ALL COMPRESSION=ALL FLASHBACK_TIME=sysdate

--2. Target DB

alter user <CATA> account lock;
alter user <CATB> account lock;
alter user <CORE> account lock;
alter user <PUB> account lock;

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' AS SQL FROM V$SESSION WHERE USERNAME IN ('<CATA>','<CATB>','<CORE>','<PUB>');

CREATE directory DB_BACKUP as '/opt/oracle/db_backup';

GRANT read,write ON directory DB_BACKUP TO public;

drop user <CATA> cascade;
drop user <CATB> cascade;
drop user <CORE> cascade;
drop user <PUB> cascade;

CREATE USER <CATA> IDENTIFIED BY <CATA> QUOTA UNLIMITED ON TBS_FALABELLA
DEFAULT TABLESPACE TBS_FALABELLA
TEMPORARY TABLESPACE TEMP
/
GRANT CONNECT, RESOURCE,DBA TO <CATA>;

CREATE USER <CATB> IDENTIFIED BY <CATB> QUOTA UNLIMITED ON TBS_FALABELLA
DEFAULT TABLESPACE TBS_FALABELLA
TEMPORARY TABLESPACE TEMP
/
GRANT CONNECT, RESOURCE,DBA TO <CATB>;

CREATE USER <CORE> IDENTIFIED BY <CORE> QUOTA UNLIMITED ON TBS_FALABELLA
DEFAULT TABLESPACE TBS_FALABELLA
TEMPORARY TABLESPACE TEMP
/
GRANT CONNECT, RESOURCE TO <CORE>;

CREATE USER <PUB> IDENTIFIED BY <PUB> QUOTA UNLIMITED ON TBS_FALABELLA
DEFAULT TABLESPACE TBS_FALABELLA
TEMPORARY TABLESPACE TEMP
/
GRANT CONNECT, RESOURCE,DBA TO <PUB>;

alter user <CATA> account lock;
alter user <CATB> account lock;
alter user <CORE> account lock;
alter user <PUB> account lock;

impdp system/<password>@<orcl> DIRECTORY=db_backup DUMPFILE=FALABELLA_SO_20080808.DMP LOGFILE=impdp_FALABELLA_SO_20080808.log SCHEMAS=<CATA>,<CATB>,<CORE>,<PUB> CONTENT=ALL PARALLEL=1 TABLE_EXISTS_ACTION=REPLACE

alter user <CATA> account unlock;
alter user <CATB> account unlock;
alter user <CORE> account unlock;
alter user <PUB> account unlock;