oracle11g导库方法,oracle11g导库备份

最新推荐文章于 2025-08-07 19:52:07 发布

谭伯龙

最新推荐文章于 2025-08-07 19:52:07 发布

阅读量231

点赞数

文章标签： oracle11g导库方法

本文档详细介绍了如何在Oracle数据库中进行用户迁移和数据覆盖操作。首先，通过PL/SQL创建并授权ods_6002、metabase_6002和data_6002用户，然后备份原有用户数据，接着删除旧用户，并创建新用户。在创建新用户后，执行数据导入覆盖操作，确保新用户的权限和数据与源用户一致。整个过程涉及用户管理、权限分配、数据备份与恢复等核心步骤。

1、使用pl/sql工具，查看ods_6002/metabase_6002/data_6002的用户创建语句

-- Create the user

create user METABASE_6002

identified by ""

default tablespace USERS

temporary tablespace TEMP

profile DEFAULT

password expire;

-- Grant/Revoke role privileges

grant connect to METABASE_6002 with admin option;

grant dba to METABASE_6002;

grant exp_full_database to METABASE_6002;

grant imp_full_database to METABASE_6002;

grant resource to METABASE_6002 with admin option;

-- Grant/Revoke system privileges

grant create view to METABASE_6002;

grant debug any procedure to METABASE_6002;

grant debug connect session to METABASE_6002;

grant select any table to METABASE_6002;

grant unlimited tablespace to METABASE_6002 with admin

option;

-- Create the user

create user DATA_6002

identified by ""

default tablespace DATA_DATA

temporary tablespace TEMP

profile DEFAULT

password expire;

-- Grant/Revoke role privileges

grant connect to DATA_6002 with admin option;

grant dba to DATA_6002 with admin option;

grant exp_full_database to DATA_6002;

grant imp_full_database to DATA_6002;

grant resource to DATA_6002;

-- Grant/Revoke system privileges

grant create view to DATA_6002;

grant select any table to DATA_6002;

grant unlimited tablespace to DATA_6002 with admin option;

-- Create the user

create user ODS_6002

identified by ""

default tablespace ODS_DATA

temporary tablespace TEMP

profile DEFAULT

password expire;

-- Grant/Revoke role privileges

grant connect to ODS_6002 with admin option;

grant dba to ODS_6002 with admin option;

grant exp_full_database to ODS_6002;

grant imp_full_database to ODS_6002;

grant resource to ODS_6002;

-- Grant/Revoke system privileges

grant create view to ODS_6002;

grant select any table to ODS_6002;

grant unlimited tablespace to ODS_6002 with admin option;

注意create user XXX identified by “填写密码”

2、将要覆盖的用户ods_6002/metabase_6002/data_6002做备份

SQL> select instance_name from v$instance;--查询所在数据库实例

ccbi

SQL> select userenv('language') from dual;--查询数据库字符集为UTF8

SIMPLIFIED CHINESE_CHINA.AL32UTF8

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ exp metabase_6002/123456

file=metabase6002_0731.dmp owner=metabase_6002

log=metabase6002_0731.log

[oracle@NEWCORE ~]$ exp ods_6002/ods_6002 file=ods6002_0731.dmp

owner=ods_6002 log=ods6002_0731.log

[oracle@NEWCORE ~]$ exp data_6002/data_6002

file=data6002_0731.dmp owner=data_6002

log=data6002_0731.log

3、备份想要导入的数据库用户ods_6003/metabase_6003/data_6003

为了防止Oracle11g，空表不导出，需要登录导出的用户分别执行如下的SQL

select 'alter table '||table_name||' allocate extent;' from

user_tables where num_rows=0;

SQL> conn metabase_6003/metabase_6003

Connected.

SQL> select 'alter table '||table_name||' allocate extent;'

from user_tables where num_rows=0;

把罗列出来的SQL执行复制出来，执行一遍。

注意：只执行alter table XXX allocate

extent;的SQL语句

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$exp metabase_6003/metabase_6003

file=metabase6003_0731.dmp owner=metabase_6003

log=metabase6003_0731.log

[oracle@NEWCORE ~]$ exp ods_6003/ods_6003 file=ods6003_0731.dmp

owner=ods_6003 log=ods6003_0731.log

[oracle@NEWCORE ~]$ exp data_6003/data_6003

file=data6003_0731.dmp owner=data_6003 log=data6003_0731.log

4、删除ods_6002/metabase_6002/data_6002用户

确认数据库实例名

SQL> drop user metabase_6002 cascade;

SQL> drop user data_6002 cascade;

SQL> drop user ods_6002 cascade;

5、创建用户ods_6002/metabase_6002/data_6002用户

逐一登录创建的用户来验证，会提示密码过期重新设置，这时重新输入一遍原始密码即可。

或者创建用户的时候password expire改为 account

unlock就不需要用户重新登录修改密码。

6、将数据覆盖到ods_6002/metabase_6002/data_6002用户之中

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ imp metabase_6002/123456

file=metabase6003_0731.dmp fromuser=metabase_6003

touser=metabase_6002 log=imp_metabase6002.log

[oracle@NEWCORE ~]$ imp data_6002/data_6002

file=data6003_0731.dmp fromuser=data_6003 touser=data_6002

log=imp_data6002.log

[oracle@NEWCORE ~]$ imp ods_6002/ods_6002 file=ods6003_0731.dmp

fromuser=ods_6003 touser=ods_6002 log=imp_ods002.log