imp-导入小写字母表问题

最新推荐文章于 2024-08-04 22:36:44 发布

转载最新推荐文章于 2024-08-04 22:36:44 发布 · 169 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/697813

文章标签：

#数据库

本文详细介绍了在Oracle数据库中创建和使用小写字母表的方法，包括如何通过使用双引号建立小写表，以及在查询时需要使用双引号进行引用的特点。此外，还提供了在遇到同名大写表时，如何单独导入小写字母表的具体步骤，包括使用exp全用户导出和imp命令进行选择性导入。

1建立测试表“emp_bak"

使用双引号建立的表，默认为小写字母的表.

SQL>  create table "emp_bak" as select * from emp;

Table created.

SQL> slect * from tab;
SP2-0734: unknown command beginning "slect * fr..." - rest of line ignored.
SQL> select * from tab;

TNAME                   TABTYPE    CLUSTERID
------------------------------ ------- ----------
BONUS                   TABLE
DEPT                   TABLE
EMP                   TABLE
E_TMP                   TABLE
OD                   TABLE
OUTPUT                   TABLE
OUTPUT2                TABLE
SALGRADE               TABLE
TS1                   TABLE
TS2                   TABLE
emp_bak                TABLE

13 rows selected.

小写字母表特点

查询需要使用单引号：


SQL> select count(*) from emp_bak;
select count(*) from emp_bak
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from "emp_bak";

  COUNT(*)
----------
    14

是否可以建立同名大写的表？

SQL> create table emp_bak as select * from emp;

Table created.

SQL> select * from tab;

TNAME                   TABTYPE    CLUSTERID
------------------------------ ------- ----------
BONUS                   TABLE
DEPT                   TABLE
EMP                   TABLE
EMP_BAK                TABLE
E_TMP                   TABLE
emp_bak                TABLE

如何单独导入小写字母的表呢？

exp 全用户导出

[oracle@prod04 ~]$ exp scott/snow file=snow1.dmp

Export: Release 11.2.0.4.0 - Production on Mon Jan 7 18:54:29 2019

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT 
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        EMP_BAK         14 rows exported
. . exporting table                          E_TMP         14 rows exported
. . exporting table                             OD          4 rows exported
. . exporting table                         OUTPUT          5 rows exported
. . exporting table                        OUTPUT2          3 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                            TS1       7585 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            TS2     288657 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        emp_bak         14 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

解决方法：

[oracle@prod04 ~]$ imp scott/snow file=snow1.dmp tables='\"emp_bak\"'

Import: Release 11.2.0.4.0 - Production on Mon Jan 7 18:45:02 2019

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 
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                      "emp_bak"         14 rows imported
Import terminated successfully without warnings.