{"id":2057,"date":"2020-12-01T07:00:00","date_gmt":"2020-12-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2057"},"modified":"2024-07-01T17:53:56","modified_gmt":"2024-07-01T14:53:56","slug":"pos-eisagoyme-mazika-dedomena-sti-vas","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-eisagoyme-mazika-dedomena-sti-vas\/","title":{"rendered":"How to bulk data into database using Oracle Data Pump (impdp)"},"content":{"rendered":"

Many times we will need to keep the table data and their metadata (packages, procedures, functions, etc.) so that we can then import them into the same or a different system.<\/p>\n\n\n\n

In previous article<\/a> we had seen its use Oracle Data Pump Export (expdp)<\/strong> so that we can export a dump file with this data. In this article we will analyze the tool that imports this data into the database Oracle Data Pump Import (impdp)<\/strong>.<\/p>\n\n\n\n

How Oracle Data Pump Import (impdp) works<\/h2>\n\n\n\n

Calling it Data Pump Import (impdp) <\/strong>a job is created and starts running whose job is to read dump files and insert them into the database.<\/p>\n\n\n\n

We can clean up what will be inserted and how by using parameters. These parameters can be declared either on the command line or in a separate parameter file.<\/p>\n\n\n\n

The preliminary work<\/h2>\n\n\n\n

For starters we'll need a user that has the role DATAPUMP_IMP_FULL_DATABASE<\/em><\/strong>. Alternatively we can use a user set to SYSDBA although it is not recommended.<\/p>\n\n\n\n

grant DATAPUMP_IMP_FULL_DATABASE to stratos;<\/pre>\n\n\n\n
We'll need one too logical directory<\/strong><\/em> which will point to a physical directory on the server where the dump files are located.<\/p>\n\n\n\n
create directory fakelos as '\/home\/oracle\/Documents';<\/pre>\n\n\n\n
To see the existing directories:<\/p>\n\n\n\n
select owner,directory_name,directory_path from all_directories;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To see the existing tablespaces and the tables they contain:<\/p>\n\n\n\n
select tablespace_name,status,max_size from dba_tablespaces;\n\nselect owner,segment_name,tablespace_name,bytes from dba_segments where tablespace_name = 'USERS' and segment_type = 'TABLE';\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Optionally<\/h2>\n\n\n\n
As we may import a table that you associate with another table we may need to disable the constraints.<\/p>\n\n\n\n
SELECT * FROM user_cons_columns where OWNER = 'SCOTT' TABLE_NAME ='DEPT'\n\nALTER TABLE SCOTT.DEPT DISABLE CONSTRAINT fk_dname;<\/pre>\n\n\n\n
We may want to pass an entire schema, so the process will be easier if we drop it first.<\/p>\n\n\n\n
drop user SCOTT cascade; <\/pre>\n\n\n\n
Also we may want to delete the tablespace to insert it e.g. with a different name during the import process.<\/p>\n\n\n\n
DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;<\/pre>\n\n\n\n
The example<\/h2>\n\n\n\n
The simplest example we can try is to import a small table. The parameters needed are the logical directory we made earlier, the name of the dumpfile and the name of the table (we have set metrics and logfile to also be exported to the same directory). But since the table already exists we can enter it with another name with the parameter REMAP_TABLE<\/strong>.<\/p>\n\n\n\n
[opc@dp-gr ~]$ impdp stratos\/stratos directory=FAKELOS dumpfile=pinakas_dept.DMP logfile=import.log metrics=y TABLES=SCOTT.DEPT REMAP_TABLE=SCOTT.DEPT:DEPT_TEST <\/pre>\n\n\n\n
Alternatively if we would like to use a parameter file. We would create a file named e.g. params.par which would contain all the parameters one below the other and we would set it with the parameter in impdp parfile<\/strong>.<\/p>\n\n\n\n
directory=FAKELOS\ndumpfile=pinakas_dept.DMP\nlogfile=import.log\nmetrics=y \nTABLES = SCOTT.DEPT\nREMAP_TABLE=SCOTT.DEPT:DEPT_TEST <\/code><\/pre>\n\n\n\n
[opc@dp-gr ~]$ impdp stratos\/stratos parfile=\/home\/oracle\/Documents\/params.par<\/pre>\n\n\n\n
[opc@dp-gr ~]$ impdp stratos\/stratos directory=FAKELOS dumpfile=pinakas_dept.DMP logfile=import.log REMAP_TABLE=SCOTT.DEPT:DEPT_TEST metrics=y\n...\nConnected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real \nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\n     Completed 1 TABLE objects in 1 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\n. . imported \"SCOTT\".\"DEPT_TEST\"                         6.023 KB       4 rows in 1 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/INDEX\n     Completed 1 INDEX objects in 0 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\nORA-31684: Object type CONSTRAINT:\"SCOTT\".\"PK_DEPT\" already exists\n     Completed 1 CONSTRAINT objects in 1 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\n     Completed 1 INDEX_STATISTICS objects in 0 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\n     Completed 1 TABLE_STATISTICS objects in 0 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\n     Completed 1 MARKER objects in 43 seconds\n     Completed 1 TABLE_EXPORT\/TABLE\/TABLE_DATA objects in 1 seconds\nJob \"STRATOS\".\"SYS_IMPORT_FULL_01\" completed with 1 error(s) <\/code><\/pre>\n\n\n\n
To see the Data Pump jobs in progress.<\/p>\n\n\n\n
select * from dba_datapump_jobs <\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We also have the possibility to do it by having the name of the job (using the previous query). attach <\/strong>in another window and see lots of useful information like completion rate and bytes written.<\/p>\n\n\n\n
[opc@dp-gr ~]$ impdp stratos\/stratos attach=SYS_IMPORT_TABLE_01<\/pre>\n\n\n\n
Alternatively if we use the sysdba account.<\/p>\n\n\n\n
[oracle@dp-gr opc]$ impdp \"'\/as sysdba'\" attach=SYS_IMPORT_TABLE_01<\/pre>\n\n\n\n
Import> status\n\nJob: SYS_IMPORT_TABLE_01\n  Owner: STRATOS\n  Operation: IMPORT\n  Mode: TABLE\n  Instance: ORCL\n  Max Parallelism: 1\n  NLS character set: AL32UTF8\n  NLS NCHAR character set: AL16UTF16\n  Dump File: \/home\/oracle\/Documents\/pinakas_dept.DMP\n\nWorker 1 Status:\n  Instance ID: 1\n  Instance name: ORCL\n  Host name: dp-gr\n  Process Name: DW00\n  State: UNDEFINED\n  Object Schema: SYS\n  Object Type: TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\n  Total Objects: 1\n  Completed Bytes: 18,160\n  Worker Parallelism: 1\n<\/code><\/pre>\n\n\n\n
This information is updated by writing the command status<\/strong>.<\/p>\n\n\n\n
If for some reason the job stops, e.g. there is not enough space, a message will also appear in the following dynamic view. If we fix it in a reasonable time it will continue automatically.<\/p>\n\n\n\n
select name,status,timeout,error_number,error_msg from DBA_RESUMABLE;<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
After the task is completed we will see that the table SCOTT.DEPT_TEST has been created.<\/p>\n\n\n\n
select count(*) from SCOTT.DEPT_TEST;<\/pre>\n\n\n\n
4<\/code><\/pre>\n\n\n\n
Useful parameters<\/h2>\n\n\n\n
Let's also look at some useful parameters that we can set in Data Pump Import <\/strong>so that we have better control over the data we will enter.<\/p>\n\n\n\n
REMAP_TABLE<\/h5>\n\n\n\n
With this parameter we can import the table with a different name as it may already exist.<\/p>\n\n\n\n
REMAP_TABLE =SCOTT.DEPT:DEPT_NEW;<\/pre>\n\n\n\n
REMAP_SCHEMA<\/h5>\n\n\n\n
With this parameter we can import the schema into a different one.<\/p>\n\n\n\n
REMAP_SCHEMA=SCOTT:STRATOS<\/pre>\n\n\n\n
REMAPS_TABLESPACE<\/h5>\n\n\n\n
With this parameter we can import the data into a different tablespace.<\/p>\n\n\n\n
REMAPS_TABLESPACE=USERS:TABLESPACE_ARCHIVE<\/pre>\n\n\n\n
TABLE_EXISTS_ACTION<\/h5>\n\n\n\n
We use this parameter when we want to import tables that already exist and accepts 3 options:<\/p>\n\n\n\n
With the append <\/strong>writes the entries in continuation of the previous ones.<\/p>\n\n\n\n
TABLE_EXISTS_ACTION=APPEND<\/pre>\n\n\n\n
With the replace<\/strong> overwrites the table along with its metadata.<\/p>\n\n\n\n
TABLE_EXISTS_ACTION=REPLACE<\/pre>\n\n\n\n
With the truncate<\/strong> deletes as many records as the table already has without disturbing its metadata and keeping the sequential numbering in a key if it existed as it is.<\/p>\n\n\n\n
TABLE_EXISTS_ACTION=TRUNCATE<\/pre>\n\n\n\n
CONTENT<\/h5>\n\n\n\n
We use this parameter when we want to import only the data or only metadata.<\/p>\n\n\n\n
content=data_only \n\ncontent=metadata_only <\/pre>\n\n\n\n
TRANSFORM<\/h5>\n\n\n\n
If we are not interested in the information being recorded in the redo log and force_logging is not activated, we can not record the import information in order to speed up the process.<\/p>\n\n\n\n
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y<\/pre>\n\n\n\n
TABLESPACES<\/h5>\n\n\n\n
With this parameter we can import selected tablespaces.<\/p>\n\n\n\n
TABLESPACES=USERS<\/pre>\n\n\n\n
TABLES<\/h5>\n\n\n\n
With this parameter we can insert selected tables.<\/p>\n\n\n\n
TABLES=SCOTT.DEPT,SCOTT.SALGRADE<\/pre>\n\n\n\n
SCHEMES<\/h5>\n\n\n\n
With this parameter we can enter entire specific user.<\/p>\n\n\n\n
schemas=SCOTT <\/pre>\n\n\n\n
DUMPFILE<\/h5>\n\n\n\n
With this parameter we can give a name to the dumpfile that Data Pump Export will produce. However, with the characters "%u" several sequentially numbered files (up to 99) can be created. <\/p>\n\n\n\n
dumpfile=export_pinaka%u.DMP <\/pre>\n\n\n\n
EXCLUDE<\/h5>\n\n\n\n
With this parameter we can exclude specific objects such as schema, tables, etc.<\/p>\n\n\n\n
EXCLUDE=SCHEMA:\"=SYS\"<\/pre>\n\n\n\n
INCLUDE<\/h5>\n\n\n\n
Accordingly, with this parameter we can add specific objects such as schema, tables, etc.<\/p>\n\n\n\n
INCLUDE=SCHEMA:\"=SCOTT\"\n\nINCLUDE=TABLE:\"IN (select table_name from dba_tables where table_name like 'DEP%')\"<\/pre>\n\n\n\n
QUERY<\/h5>\n\n\n\n
With this parameter we can write queries to insert specific data only from one table.<\/p>\n\n\n\n
query=SCOTT.SALGRADE:\"where HISAL > '2000'\"<\/pre>\n\n\n\n
PARALLEL<\/h5>\n\n\n\n
With this parameter we define the simultaneous parallel channels that will perform the task. We usually define the number of CPU cores by two.<\/p>\n\n\n\n
Parallel=8<\/pre>\n\n\n\n
LOGFILES<\/h5>\n\n\n\n
In this parameter we define the name of the logfile that will contain information about the process.<\/p>\n\n\n\n
METRICS<\/h5>\n\n\n\n
With this parameter, additional information is displayed in the log file.<\/p>\n\n\n\n
metrics=y<\/pre>\n\n\n\n
SQLFILE<\/h5>\n\n\n\n
If we want to see what the dump files (dmp) contain without doing the import, we can use the SQLFILE parameter to export the metadata only with the DDL (Data Definition Language) to a file.<\/p>\n\n\n\n
SQLFILE=expfull.sql<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n