用户定义视图的导出和导入_expdp 导出视图-CSDN博客

本文链接：https://blog.csdn.net/Sebastien23/article/details/128957799

本文介绍了在Oracle数据库中如何为用户授予创建表和视图的权限，以及如何使用EXPDP和IMPDP命令来导出和导入表和视图。在示例中，用户MIGUEL创建了一个表rdr_npc和一个视图v_npc_view1，然后导出并导入这些对象，强调了在导入时需要注意的数据完整性问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用户定义视图的导出和导入

表和视图的创建
表和视图的导出
表和视图的导入

表和视图的创建

给用户授予创建表和视图的权限：

SQL> grant create table,create view to miguel;

查看当前用户拥有的表和视图：

SQL> select default_tablespace from user_users; 
SQL> select table_name,tablespace_name from user_tables;
SQL> select view_name, text from user_views;

创建表和视图：

SQL> create table rdr_npc(npc_id number(6), 
first_name varchar2(20), last_name varchar2(25), 
age number(4), message varchar2(100));

SQL> insert into rdr_npc 
values(1, 'Arthur', 'Morgan', 35, 'He''s a good man.');
SQL> insert into rdr_npc 
values(2, 'John', 'Maston', 25, 'Revenge is a stupid game.'); 
SQL> insert into rdr_npc 
values(3, 'Dutch', 'Vanderlin', 45, 'I have a god damn plan!');

SQL> create view v_npc_view1 as 
select npc_id, age, message from rdr_npc where npc_id<10;

SQL> select * from v_npc_view1;

    NPC_ID        AGE MESSAGE
---------- ---------- --------------------------------------------------
         1         35 He's a good man.
         2         25 Revenge is a stupid game.
         3         45 I have a god damn plan!
--'

SQL> insert into rdr_npc values(4, 'Micah', 'Bell', 33, 'Black lung!');

SQL> select * from v_npc_view1;

    NPC_ID        AGE MESSAGE
---------- ---------- --------------------------------------------------
         1         35 He's a good man.
         2         25 Revenge is a stupid game.
         3         45 I have a god damn plan!
         4         33 Black lung!

可以看到，视图的内容会随着表的更新而变化。

表和视图的导出

检查数据泵：

SQL> select owner,directory_name,directory_path 
from dba_directories where directory_name='DUMPDIR1';

OWNER      DIRECTORY_NAME       DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS        DUMPDIR1             /oradata/dumpdir

使用EXPDP导出MIGUEL用户的所有视图（include=view）：

[oracle@oracledb1 ~]$ expdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dump_${ORACLE_SID}_`date +%F`_%U.dmp \
logfile=dump_${ORACLE_SID}_`date +%F`_%U.log \
include=view parallel=2 compression=all schemas=MIGUEL

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=dumpdir1 dumpfile=dump_bangkok_2023-02-09_%U.dmp logfile=dump_bangkok_2023-02-09_%U.log include=view parallel=2 compression=all schemas=MIGUEL 
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /oradata/dumpdir/dump_bangkok_2023-02-09_01.dmp
  /oradata/dumpdir/dump_bangkok_2023-02-09_02.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 9 17:12:16 2023 elapsed 0 00:00:06

[oracle@oracledb1 ~]$ ll -h /oradata/dumpdir/
total 104K
-rw-r----- 1 oracle oinstall 8.0K Feb  9 17:12 dump_bangkok_2023-02-09_01.dmp
-rw-r----- 1 oracle oinstall  40K Feb  9 17:12 dump_bangkok_2023-02-09_02.dmp
-rw-r--r-- 1 oracle oinstall  922 Feb  9 17:12 dump_bangkok_2023-02-09_%U.log

表和视图的导入

删除表和视图：

SQL> drop view v_npc_view1;

View dropped.

SQL> drop table rdr_npc;

Table dropped.

SQL> select table_name,tablespace_name from user_tables;

no rows selected

SQL> select view_name, text from user_views;

no rows selected

使用IMPDP导入之前导出的dump文件：

[oracle@oracledb1 ~]$ impdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dump_bangkok_2023-02-09_01.dmp,dump_bangkok_2023-02-09_02.dmp \
logfile=dump_bangkok_2023-02-09_%U.log \
schemas=MIGUEL

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=dumpdir1 dumpfile=dump_bangkok_2023-02-09_01.dmp,dump_bangkok_2023-02-09_02.dmp logfile=dump_bangkok_2023-02-09_%U.log schemas=MIGUEL 
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"MIGUEL"."V_NPC_VIEW1" created with compilation warnings

Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Feb 9 17:23:43 2023 elapsed 0 00:00:0

检查导入的视图：

SQL> select view_name, text from user_views;

VIEW_NAME            TEXT
-------------------- --------------------------------------------------------------------------------
V_NPC_VIEW1          select npc_id, age, message from rdr_npc where npc_id<10

SQL> select * from v_npc_view1;
select * from v_npc_view1
              *
ERROR at line 1:
ORA-04063: view "MIGUEL.V_NPC_VIEW1" has errors

查看视图错误：

SQL> show error view v_npc_view1;
Errors for VIEW V_NPC_VIEW1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-00942: table or view does not exist

SQL> select table_name,tablespace_name from user_tables;

no rows selected

由于我们使用EXPDP时只导出了视图，所以导入时报错，只有视图定义而没有数据。

这次我们使用EXPDP同时导出MIGUEL用户的所有表和视图（include=view,table）：

[oracle@oracledb1 ~]$ expdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dump_${ORACLE_SID}_`date +%F`_%U.dmp \
logfile=dump_${ORACLE_SID}_`date +%F`_%U.log \
include=view,table parallel=2 compression=all schemas=MIGUEL

删除表和视图后，重新导入：

[oracle@oracledb1 ~]$ impdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dump_bangkok_2023-02-09_01.dmp,dump_bangkok_2023-02-09_02.dmp \
logfile=dump_bangkok_2023-02-09_%U.log \
schemas=MIGUEL

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=dumpdir1 dumpfile=dump_bangkok_2023-02-09_01.dmp,dump_bangkok_2023-02-09_02.dmp logfile=dump_bangkok_2023-02-09_%U.log schemas=MIGUEL 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MIGUEL"."RDR_NPC"                          5.226 KB       4 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Feb 9 17:50:14 2023 elapsed 0 00:00:01
#--这次没报ORA-39082

检查导入的表和视图：

SQL> select * from v_npc_view1;

    NPC_ID        AGE MESSAGE
---------- ---------- --------------------------------------------------
         1         35 He's a good man.
         2         25 Revenge is a stupid game.
         3         45 I have a god damn plan!
         4         33 Black lung!
--'

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME           TABLESPACE_NAME
-------------------- ------------------------------
RDR_NPC              OMF_TBS1

SQL> select view_name, text from user_views;

VIEW_NAME            TEXT
-------------------- ------------------------------------------------------------
V_NPC_VIEW1          select npc_id, age, message from rdr_npc where npc_id<10