You Asked

Hi

Thanks a lot for the fantastic site and all the help you are providing to the Oracle developers.

My question is like this:

Many times I receive a data dump exported using the data pump utility.

But there is no log file along with the .dmp file.

I would like to know the rowcount of the tables in the dumpfile without importing the file.

The sqlfile parameter for the impdp utility gives only the DDL not the rowcount of tables.

Using the ESTIMATE=BLOCKS parameter for the impdp utility gives the following error:

ORA-39005: inconsistent arguments
ORA-39208: Parameter ESTIMATE is invalid for IMPORT jobs.

Thanks in advance.
Vadi

and Connor said...

Yes, from the docs:

"The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified."

so its really only valid when you are importing directly from a remote source database (hence its really the same as ESTIMATE on an export).

The closest I think you could probably do is an METADATA_ONLY import and look at the dictionary stats (eg user_tables.num_rows etc).

But I think its a good idea, for example, the log file being appended *within* the dump file at the end of the dump operation. I'd recommend you log an enhancement request for it.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the response

A reader, November 03, 2016 - 2:09 am UTC

Hi Connor

Thanks a lot for the quick response.

I will check out the METADATA_ONLY parameter.

Regards
Vadi

MASTER_ONLY=Y

Mikhail Velikikh, November 03, 2016 - 2:29 am UTC

Hi,

Run your impdp job passing the parameter MASTER_ONLY=Y.
In this way you will create only the datapump master table in the database that holds the metadata for your DataPump job.
Then just select COMPLETED_ROWS field from the master table restricting the rows using the relevant OBJECT_TYPE, BASE_OBJECT_NAME, BASE_OBJECT_SCHEMA columns, such as in the example below:

oracle@host ~$ impdp userid=TC master_only=y

Import: Release 12.1.0.2.0 - Production on Thu Nov 3 09:22:24 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "TC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Job "TC"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 3 09:22:28 2016 elapsed 0 00:00:01

TC@SQL> select completed_rows
  2    from sys_import_full_01
  3   where object_type='TABLE_DATA'
  4     and base_object_name='T'
  5     and base_object_schema='TC'
  6  /

COMPLETED_ROWS
--------------
         97471

1 row selected.

November 03, 2016 - 10:55 pm UTC

There you go - thats why I love AskTom. I learn something every day.

Thanks to Connor and Mikhail

Vadi, November 03, 2016 - 4:15 am UTC

Hi Connor

Thanks mate, Your method works.

I used the below command and the query and it shows the required information.

impdp username/password@ORCL SCHEMAS=myschema DIRECTORY=DATA_PUMP_DIR DUMPFILE=filename.DMP LOGFILE=IMP_filename.LOG REMAP_SCHEMA=from_schema:to_schema REMAP_TABLESPACE=from_table_space:to_tablespace CONTENT=METADATA_ONLY

SELECT table_name, num_rows
FROM user_tables
ORDER BY table_name;

Hi Mikhail Velikikh

Spasibo. Your method also works.

I used the below command and the query and it shows the required information.

impdp username/password@ORCL directory=DATA_PUMP_DIR dumpfile=filename.dmp master_only=y

select object_name, completed_rows
from sys_import_full_01
where object_type='TABLE_DATA'
and object_name='MYTABLE'
and base_object_schema='MYSCHEMA';

Thanks to you both again
Vadi
Bengaluru, India

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for the response

MASTER_ONLY=Y

Thanks to Connor and Mikhail

More to Explore

Data Pump

Data Pump - How to get rowcount of tables from data dump file without importing data?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for the response

MASTER_ONLY=Y

Thanks to Connor and Mikhail

More to Explore

Data Pump