Shulou(Shulou.com)06/01 Report--

Editor's note

There is a demand in the Yunhe Enmo lecture hall community (there is a way to join the group at the bottom of this article): a table has a large amount of data, how to export only some of the columns? Yang Tingkun, the director of Yunhe Enmo CTO, the director of Oracle ACE and the core expert of ACOUG, used the way of data pump to explain the process in detail and give the specific code implementation. Data and Cloud (ID:OraNews) are released exclusively for readers.

The following is the text

Both the old version of exp and the data pump expdp,Oracle provide the function of QUERY, which enables the function of querying some records in the table, but QUERY can only filter rows, not columns, and Oracle data pump will read all columns in the table.

In 12c, Oracle provides VIEW for the data pump so that the data in the table can be exported according to the definition of the view:

SQL > select banner from version; BANNER----OracleDatabase 18c Enterprise Edition Release 18.0.0.0.0-Production SQL > CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES

(swipe left and right to view the code section, same below)

The table has been created.

SQL > create view v_tables as select owner, table_name, tablespace_name from t_tableswhere owner not in ('SYS')

The view has been created.

The table data corresponding to the view can be directly exported by using the VIEWS_AS_TABLES parameter of the data pump:

C:\ Users\ yangt > expdpc##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables

Export:Release 18.0.0.0.0-Production on Tuesday Feb 26 13MU 37MU 312019

Version18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Password: connect to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-Production

Warning: an Oracle Data Pump operation is usually not required to connect to the root or seed of a container database.

Start "C##U1". "SYS_EXPORT_TABLE_01": directory=d_outputdumpfile=t_tab_view views_as_tables=v_tables handles the object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA handles the object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE. . Line "C##U1". "V_TABLES" 8.289 KB 71 has successfully loaded / unloaded the main table "C##U1". "SYS_EXPORT_TABLE_01" * * * the dump file set for C##U1.SYS_EXPORT_TABLE_01 is: d:\ TEMP\ T_TAB_VIEW.DMP job "C##U1". "SYS_EXPORT_TABLE_01" was successfully completed on Tuesday, February 26 13:37:40 2019 elapsed 0 00:00:06

Although exported through the view, the data pump treats it as a table, so the dump file can be imported directly into the database to become a table:

SQL > drop view v_tables

View has been deleted.

Complete the import operation:

C:\ Users\ yangt > impdpc##u1 directory=d_output dumpfile=t_tab_view full=y Import:Release 18.0.0.0.0-Production on February 26 14:33:022019Version18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Password: connect to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-Production

Warning: an Oracle Data Pump operation is usually not required to connect to the root or seed of a container database.

Successfully loaded / unloaded the main table "C##U1". "SYS_IMPORT_FULL_01" starts "C##U1". "SYS_IMPORT_FULL_01": directory=d_outputdumpfile=t_tab_view full=y processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA. . Imported "C##U1". "V_TABLES" 8.289 KB 71 line assignment "C##U1". "SYS_IMPORT_FULL_01" was successfully completed on Tuesday, February 26 14:33:07 2019 elapsed 0 00:00:02

Query table data:

SQL > select count (*) from vested tables; COUNT (*)-71 SQL > select table_name, tablespace_name from user_tables where table_name = 'Variety tables' TABLE_NAME TABLESPACE_NAME---V_TABLES USERS

This feature is a new feature of 12c. If it is a version prior to 12c, you can consider using external tables of type ORACLE_DATAPUMP to implement it:

SQL > create table t_external_tables (owner, table_name, tablespace_name) organization external 2 (type oracle_datapump 3 default directory d_output 4 location ('external_table.dp')) 5 asselect owner, table_name, tablespace_name from t_tables where owner not in (' SYS')

The table has been created.

By copying the external_table.dp file exported from the external table to the corresponding directory of the target environment, and rebuilding the external table according to the above definition, note that the syntax ends at the end of the location statement and does not include the as select part in the process of creating the target environment because the exported data has been copied.

SQL > create table t_target_ext_tab (owner varchar2 (30), table_name varchar2 (128), tablespace_name varchar2 (30)) 2 organization external 3 (type oracle_datapump 4 default directory d_output 5 location ('external_table.dp'))

The table has been created.

SQL > select count (*) from tasking targeting targets extension tabs; COUNT (*)-71 SQL > col table_name for a30SQL > select * from t_target_ext_tab where owner = 'SYSTEM' OWNER TABLE_NAME TABLESPACE_NAME SYSTEM OL$SYSTEM OL$HINTSSYSTEM OL$NODESSYSTEM HELP SYSTEM

So for 11g and 10g versions, the data can be exported and transferred without creating a new table and then copying the data. But if it is 9i and previous versions, then either through the creation of the table and then exp export, or write your own program to achieve data export.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.