建站
咨詢
售后
建站咨詢
新聞中心
把AA數(shù)據(jù)庫遷移到BB數(shù)據(jù)庫
1、AA數(shù)據(jù)庫查看用戶的默認表空間
select username,default_tablespace from dba_users where username='AA_Username'
2、查看用戶的角色
select * from user_role_privs;
3、BB數(shù)據(jù)庫的數(shù)據(jù)文件所在位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\EXAMPLE01.DBF
成都創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設(shè)與策劃設(shè)計,三明網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:三明等地區(qū)。三明做網(wǎng)站價格咨詢:028-86922220
4、BB數(shù)據(jù)庫創(chuàng)建用戶使用的默認表空間，數(shù)據(jù)文件自動擴展
SQL> create tablespace BB_default_space
2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\BB_default_space.DBF'
3 size 5M autoextend on;
表空間已創(chuàng)建。
5、BB數(shù)據(jù)庫創(chuàng)建新用戶
SQL> create user BB_Username
2 identified by BB_Username000
3 default tablespace AA_default_space;
用戶已創(chuàng)建。
6、BB數(shù)據(jù)庫授權(quán)給新用戶
SQL> grant connect,resource,dba to BB_Username;
授權(quán)成功。
7、BB數(shù)據(jù)庫配置listener.ora 和 tnsnames.ora
配置ip，以便外面可以訪問
listener.ora文件：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
)
)
tnsnames.ora文件：
BB_Username =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BB_Username)
)
)
修改監(jiān)聽器配置文件，需要重新啟動監(jiān)聽器
關(guān)閉監(jiān)聽器：lsnrctl stop
重啟監(jiān)聽器：lsnrctl start
8、在另一個數(shù)據(jù)庫（配置方便使用plsql）配置tnsnames。ora
BB_Username =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BB_Username)
)
)
9、在AA數(shù)據(jù)庫查詢用戶下都用把數(shù)據(jù)放到哪些表空間中，建立相應(yīng)的表空間
select distinct tablespace_name from user_segments
where tablespace_name not in ('USERS','USERS02')
10、生成下面的腳本，用plsql導(dǎo)入到BB數(shù)據(jù)庫中
CREATE TABLESPACE DRILL_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\TEST_LOB_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE DH_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE TEST_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\TEST_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE LOG_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\LOG_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE MLOG_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\MLOG_NORM_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE MLOG_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\MLOG_LOB_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE LOG_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\LOG_NORM_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE HHH_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHH_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE GPE_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHDS_NORM_SPACE DATAFIL.DBF' size 5M autoextend on;
CREATE TABLESPACE HHDS_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHDS_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE PUB_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\PUB_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE PUB_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\BBII_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
11、導(dǎo)出AA數(shù)據(jù)庫的數(shù)據(jù)
exp AA_Username/AA_Password@AA_Database file=F:\AA_Databaseoraclebak\AA_Database14.dump log=F:\AA_Databaseoraclebak\AA_Database14.log
12、導(dǎo)入BB數(shù)據(jù)庫的數(shù)據(jù):不需要用戶授權(quán)權(quán)限
imp BB_Username/BB_Password@BB_Database FROMUSER=AA_Username TOUSER=BB_Username ignore=yes grants=no file=F:\AA_Databaseoraclebak\AA_Database14.dump log=F:\AA_Databaseoraclebak\IMP20100121BB_Username.log
文章題目：Oracle的exp、imp的數(shù)據(jù)遷移步驟
新聞來源：http://ef60e0e.cn/article/pocoge.html
