1.1 資料泵新特性測試

1.1.1 資料匯出工具expdp差異

The available keywords and their descriptions follow. Default values are listed within square brackets.

ABORT_STEP

Stop the job after it is initialized or at the indicated object.

Valid values are -1 or N where N is zero or greater.

N corresponds to the object's process order number in the master table.

ACCESS_METHOD

Instructs Export to use a particular method to unload data.

Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.

COMPRESSION_ALGORITHM ----壓縮演算法

Specify the compression algorithm that should be used.

Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

ENCRYPTION_PWD_PROMPT

Specifies whether to prompt for the encryption password [NO].

Terminal echo will be suppressed while standard input is read.

KEEP_MASTER

Retain the master table after an export job that completes successfully [NO].

LOGTIME

Specifies that messages displayed during export operations be timestamped.

Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

METRICS

Report additional job information to the export log file [NO].

VIEWS_AS_TABLES

Identifies one or more views to be exported as tables.

For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

START_JOB

Start or resume current job.

Valid keyword values are: SKIP_CURRENT.

1.1.2 檢視轉換成表

SQL> show con_name

CON_NAME

------------------------------

PDBA

SQL> show user

USER is "SCOTT"

SQL> select table_name from user_tables;

TABLE_NAME

--------------------

SALGRADE

BONUS

EMP

DEPT

SQL> create view v_emp as select * from emp;

View created.

SQL> select object_name,object_type from user_objects where object_type not like 'INDEX';

OBJECT_NAME OBJECT_TYPE

------------------------------ -----------------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

V_EMP VIEW

測試中我們將上面的V_EMP轉換成V_emp_TAB:

l 匯出是將試圖轉換成表：

[oracle@DBA12C03 dump]$ expdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=view_to_table_02.log views_as_tables=v_emp directory=expdp

Export: Release 12.1.0.2.0 - Production on Wed Jan 14 16:36:43 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=view_to_table_02.log views_as_tables=v_emp directory=expdp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

Total estimation using BLOCKS method: 16 KB

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

. . exported "SCOTT"."V_EMP" 8.781 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/dump/view_to_table_02.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 14 16:36:52 2015 elapsed 0 00:00:08

l 匯入轉換出來的表

如果還是本地匯入，則在匯入的時候一定要注意需要將本地的檢視刪除，否則會報錯如下：

[oracle@DBA12C03 dump]$ impdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp

Import: Release 12.1.0.2.0 - Production on Wed Jan 14 16:39:42 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

ORA-39325: TABLE_EXISTS_ACTION cannot be applied to "SCOTT"."V_EMP".

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Jan 14 16:39:44 2015 elapsed 0 00:00:01

即使在匯入時使用了table_exists_action同樣出錯，同上一樣。

刪除檢視開始匯入：

SQL> drop view v_emp;

View dropped.

[oracle@DBA12C03 dump]$ impdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp

Import: Release 12.1.0.2.0 - Production on Wed Jan 14 16:41:46 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

. . imported "SCOTT"."V_EMP" 8.781 KB 14 rows

Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Wed Jan 14 16:41:48 2015 elapsed 0 00:00:01

OBJECT_NAME OBJECT_TYPE

------------------------------ -----------------------

V_EMP TABLE

SALGRADE TABLE

BONUS TABLE

EMP TABLE

DEPT TABLE

關於匯出檢視成為表還有其他方式：

expdp scott/scott@pdba dumpfile=view_to_table_03.dmp logfile=view_to_table_03.log views_as_tables=emp_v:v_guijian_tab directory=expdp

Export: Release 12.1.0.2.0 - Production on Wed Jan 14 16:45:23 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdba dumpfile=view_to_table_03.dmp logfile=view_to_table_03.log views_as_tables=emp_v:v_guijian_tab directory=expdp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

Total estimation using BLOCKS method: 16 KB

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

. . exported "SCOTT"."EMP_V" 8.789 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/dump/view_to_table_03.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 14 16:45:31 2015 elapsed 0 00:00:07

Oracle 12C 資料泵新特性測試

1.1 資料泵新特性測試

1.1.1 資料匯出工具expdp差異

1.1.2 檢視轉換成表

相關文章