dba必备技能：
给你一个dmp文件，怎么正确快速的导入到指定的新库新用户下？
比如目标库给你了：要求将提供的dmp，导入到新库的test用户和testtbs表空间下。
你需要了解的信息：
1.源端字符集和目标字符集是否匹配？–否则导入也可能是乱码。
2.dmp是expdp还是exp的？–才知道导入该用impdp还是imp
3.源dmp是按库，按用户，按表导出的？–源端一般导出的时候有dmp和log一起的，可以要一下，要不到再说。假设知道按某个用户u1吧，为了和上面的题目匹配要求导入新用户test下。
4.源数据占用表空间多大？–如果不知道，就参考dmp大小评估，比如dmp为50G，那表空间一般在2-5倍左右，可以考虑加8个10G的文件，最大可达256G，所以预先加几个数据文件，并保证自动扩展是更安全的。
如果一上来就导入的，就踩坑处理，比如https://blog.csdn.net/Aaron_ch/article/details/113007263，测试库随意，但在生产库可能就容易事故，慎行。
测试过程：
1.准备dmp目录：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS ORADMP /oracle/dmp
2.准备dmp文件
我找了一个已有的测试随便导出一个dmp，我这里按表导。
如果自己建的用户，建表，就改成
[oracle@lnkf ~]$ expdp ‘/ as sysdba’ directory=oradmp schemas=GISTAR dumpfile=a1.dmp logfile=a1.log
[oracle@lnkf ~]$ expdp \'/ as sysdba\' directory=oradmp tables=gistar.an_odn dumpfile=a.dmp logfile=a.log
Export: Release 11.2.0.4.0 - Production on Wed Oct 25 14:53:39 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=oradmp tables=gistar.an_odn dumpfile=a.dmp logfile=a.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 715 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "GISTAR"."AN_ODN" 633.1 MB 2547167 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/dmp/a.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Oct 25 14:54:13 2023 elapsed 0 00:00:33
3.为了查看用户和表空间等信息,生成DDL脚本
然后生成DDL语句，观察建表，表空间等信息，注意增加的参数sqlfile=xxx.sql,这个操作只会生成DDL脚本，不会实际导入操作的 ：
一般参考导出时候的写法，改下impdp和sqlfile即可
[oracle@lnkf ~]$ impdp \'/ as sysdba\' directory=oradmp tables=gistar.an_odn dumpfile=a.dmp logfile=a1.log sqlfile=a.sql
Import: Release 11.2.0.4.0 - Production on Wed Oct 25 14:55:39 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_TABLE_01": "/******** AS SYSDBA" directory=oradmp tables=gistar.an_odn dumpfile=a.dmp logfile=a1.log sqlfile=a.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at Wed Oct 25 14:55:45 2023 elapsed 0 00:00:05
[oracle@lnkf ~]$ cd /oracle/dmp
[oracle@lnkf dmp]$ more a.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "GISTAR"."AN_ODN"
( "ID" VARCHAR2(16 BYTE) NOT NULL ENABLE,
......
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
---其实可以用full=y来代替指定表或用户参数。如下：
[oracle@lnkf dmp]$ impdp \'/ as sysdba\' directory=oradmp full=y dumpfile=a.dmp logfile=a1.log sqlfile=a1.sql
Import: Release 11.2.0.4.0 - Production on Wed Oct 25 14:58:40 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=oradmp full=y dumpfile=a.dmp logfile=a1.log sqlfile=a1.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Oct 25 14:58:44 2023 elapsed 0 00:00:03
[oracle@lnkf dmp]$ ll *.sql
-rw-r--r-- 1 oracle oinstall 267052 Oct 25 14:58 a1.sql
-rw-r--r-- 1 oracle oinstall 267052 Oct 25 14:55 a.sql
快速找出涉及的表空间信息：
[oracle@lnkf dmp]$ grep TABLESPACE a1.sql|uniq|more
TABLESPACE "USERS" ;
TABLESPACE "USERS" PARALLEL 1 ;
TABLESPACE "INDX" PARALLEL 1 ;
TABLESPACE "USERS" PARALLEL 1 ;
TABLESPACE "INDX" PARALLEL 1 ;
TABLESPACE "USERS" PARALLEL 1 ;
4.查看字符集，可以看到ZHS16GBK，也可以看到源库名称lndb
[oracle@lnkf dmp]$ ll *.dmp
-rw-r----- 1 oracle oinstall 664182784 Oct 25 14:54 a.dmp
-rw-r--r-- 1 oracle oinstall 19617376256 Aug 25 16:18 an_odn_picture.dmp
[oracle@lnkf dmp]$ strings a.dmp|head
"SYS"."SYS_EXPORT_TABLE_01"
x86_64/Linux 2.4.xx
lndb
ZHS16GBK
11.02.00.04.00
001:001:000001:000001
HDR>T
