关于修改密码带特殊符号以及数据泵参数设置的一些小知识（2）
问题2：关于impdp导入数据表是否添加tables参数问题
由于生产上线紧急，当时针对上述密码问题还不是很明朗，所以直接明文指定密码进行导入，但是报错了，然后先暂时不使用用户导入而使用/ as sysdba的方式导入---已经脱敏处理
[oracle@sskkdds~]$ impdp aaa/"afj@1233" directory=dumpdir
dumpfile=sadfadfadfadfa_0209.DMP logfile=sadfadfadfadfa_0209.log
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 18:49:23 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDI-00001: user requested cancel of current operation
[oracle@sskkdds~]$ mv sadfadfadfadfa_0209.DMP dumpdir/
[oracle@sskkdds~]$ impdp directory=dumpdir dumpfile=dfadfadfadf_0209.DMP
tables=aaa.dfadfadfadf,aaa.dfadfadf logfile=sadfadfadfadfa_0209.log
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 18:52:07 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA directory=dumpdir
dumpfile=sadfadfadfadfa_0209.DMP tables=tables=aaa.dfadfadfadf,aaa.dfadfadf
logfile=sadfadfadfadfa_0209.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "aaa"."dfadfafafaf" 30.64 MB 38590 rows
. . imported "aaa"."fadfadfafadf" 1.277 MB 1692 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 18:52:14
但是就tables是否指定的问题，我和同事起了争执，我觉得不用添加也可以实现导入，但是同事坚持必须添加不然会创建不到对应的用户当中，因为我们的用户是指定表空间创建的，我依然坚持我的想法，但是安全起见还是添加了tables参数导入解决生产上线情况。
下来之后我做了测试论证了我的观点：
大概思路就是创建一个特定表空间的用户，然后创建一张测试表，导出然后导入，顺便论证一下加特殊符号密码适用数据泵的情况，具体步骤如下：
SQL> select username,default_tablespace from dba_users where username='TEST';
USERNAME
----------------------------------------------------------------------------
DEFAULT_TABLESPACE
----------------------------------------------------------------------------
TEST
USERS 这里原来test用户默认表空间为users表空间
SQL>
SQL>
SQL> create tablespace test datafile '/oradata/ptopdb/test.dbf' size 2M
autoextend on;
Tablespace created. 这里我们创建一个单独的表空间test
SQL>
SQL>
SQL>
SQL>
SQL> alter user test default tablespace test;
User altered.
SQL>
SQL> select username,default_tablespace from dba_users where username='TEST';
USERNAME
--------------------------------------------------------------------------
DEFAULT_TABLESPACE
--------------------------------------------------------------------------
TEST
TEST 这里我们修改test用户到默认表空间为test
SQL>
SQL>
SQL>
SQL> conn test/"test@1" 连接test用户查看t表
Connected.
SQL>
SQL>
SQL>
SQL> desc t;
Name Null? Type
--------------------------------------- -----
ID NUMBER(38)
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@perfordb01 ~]$ expdp directory=dumpdir dumpfile=t.dmp tables=t
logfile=t.log ----expdp导出t表数据
Export: Release 11.2.0.1.0 - Production on Thu Feb 9 19:04:58 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: test
Password: ---这里我们用了"test@1" 可以实现导出操作
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dumpdir
dumpfile=t.dmp tables=t logfile=t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T" 5.023 KB 2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
****************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 19:05:19
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$ impdp test/"test@1" directory=dumpdir dumpfile=t.dmp
logfile=t.log
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 19:05:59 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDI-12532: operation generated ORACLE error 12532
ORA-12532: TNS:invalid argument ---但是这里明文指定的情况下就报错了
[oracle@perfordb01 ~]$ impdp test directory=dumpdir dumpfile=t.dmp
logfile=t.log --这里没有加tables参数指定t表
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 19:06:21 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
UDI-01017: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied
Username: test
Password: ---这里我们用了"test@1" 可以实现导入操作
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=dumpdir
dumpfile=t.dmp logfile=t.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TEST"."T" exists. All dependent metadata and data
will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:06:44
[oracle@perfordb01 ~]$ impdp directory=dumpdir dumpfile=t.dmp logfile=t.log
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 19:07:01 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=dumpdir
dumpfile=t.dmp logfile=t.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TEST"."T" exists. All dependent metadata and data will be
skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:07:05
如上39151错误是因为我并没有删除对应test用户下的t表，索引由于t表依然存在导致
报错，这里也间接证明了是可以实现不加tables参数直接导入t表到test用户的。
如下即可证明
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$ sqlplus test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 9 19:07:22 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL>
SQL> drop table t purge; ---这里我们删除test用户下的t表
Table dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@perfordb01 ~]$ impdp directory=dumpdir dumpfile=t.dmp logfile=t.log
--未指定tables参数直接进行导入操作
Import: Release 11.2.0.1.0 - Production on Thu Feb 9 19:07:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=dumpdir
dumpfile=t.dmp logfile=t.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T" 5.023 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 19:07:38 -导入成功
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$
[oracle@perfordb01 ~]$ sqlplus test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 9 19:07:42 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL>
SQL> select * from t;
ID
----------
1
2 可以看到t表的数据
SQL>
SQL>
SQL> exit
更多相关文章
- 侵害10亿用户隐私，QQ、小米金融、搜狐新闻等41款App被通报
- 用户表空间配额（User tablespace Quota）
- 一种新的Android恶意软件HiddenMiner，影响印度和中国的用户
- 一步一步搭建11gR2 rac+dg之DG 机器配置(七)
- Servlet过滤器使用实例（防止用户恶意登录）
- 神策数据张涛：微信生态数字化运营解决方案
- 神策数据朱德康：用户中台建设实践解析
- 940万用户数据泄露，450万罚款，国泰航空为“低级失误”买单
- Samba安装与配置