IMP导入数据文件

IMP导⼊数据⽂件
listener.ora 的配置：
# listener.ora Network Configuration File: /u01/app/oracle/product/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = trms)
(ORACLE_HOME =/u01/app/oracle/product/db_1)
(SID_NAME = trms)
)
(SID_DESC =
(GLOBAL_DBNAME = bss1)
(ORACLE_HOME =/u01/app/oracle/product/db_1)
(SID_NAME = bss1)
)
(SID_DESC =
(GLOBAL_DBNAME = bss2)
(ORACLE_HOME =/u01/app/oracle/product/db_1)
(SID_NAME = bss2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT =1521))
)
)
tnsnames.ora的配置：
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BSS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bss2)
)
)
BSS1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bss1)
)
)
TRMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trms)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
(1)指定导⼊的表空间
a)如果⽬标数据库已有表空间，但是表空间的名字不同不同，所以修改表空间为BSS11 ALTER TABLESPACE BSS RENAME TO BSS11
查看表空间名字是否修改成功
SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='BSS11'
b)如果没有⽬标表空间，则创建表空间
-- create tablespace
CREATE TABLESPACE BSS11
DATAFILE 'BSS_01.dbf'SIZE1000M REUSE
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER"DB01BSS1" IDENTIFIED BY"DB01BSS1"
DEFAULT TABLESPACE BSS11
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BSS11;
-- ROLES
GRANT"RESOURCE"TO"DB01BSS1";
GRANT"CONNECT"TO"DB01BSS1";
GRANT DBA TO"DB01BSS1";
ALTER USER"DB01BSS1"DEFAULT ROLE DBA;
(2)设定导⼊session的字符集
set NLS_LANG=JAPANESE_JAPAN.JA16SJISTILDE
(3)导⼊数据
imp db01bss1/db01bss1@BSS1 file=db01bss1_tables_20121116.dmp Full=Y log=bss1.log。