How to get the no. of tables and no. of entries of all SAP tables

[Former Member]

Hi Techies,

We are doing DB export and Import on our Production system, Before that we want to know the thing how can we get the no. of tables and no. of entries of those tables to complete the techical checks.

OR any usefull suggestions for doing a techical check pre and post Export and Import?

Regards,

Nick Loy

[Former Member · Accepted solution]

You can use below command to count the number of tables.

select count(*) from dba_tables;

Its difficult to count entries of each table and compare as tables are in thousands.

The best ways it if you don't get any error / warning while exporting and importing the database mean you have successfully exported/imported the database.

For your satisfaction you can note down around 10-15 business critical table name and number of entries before export and compare after import. Use below syntax:

select count(*) from <schema>.<table>;

Also request functional team to validate the FI balance etc.

[lbreddemann · Accepted solution]

>

> For your satisfaction you can note down around 10-15 business critical table name and number of entries before export and compare after import. Use below syntax:
> 
> select count(*) from <schema>.<table>;

Are you serious?

Usually you wouldn't want to run this on your productive system, especially not for your critical business (usually huge) tables.

Instead, you may just use the information you already have, e.g. the optimizer information in DBA_TABLES.NUM_ROWS.

There you can easily get the (estimated) row count for all tables without impacting performance too much.

But taking a step back here - why the export/import?

regards,

Lars

[Former Member · Accepted solution]

>Usually you wouldn't want to run this on your productive system, especially not for your critical business (usually huge) tables.

Agree Lars, Is the information from DB20 can be considered to get the number of rows?

[lbreddemann · Accepted solution]

>

> >Usually you wouldn't want to run this on your productive system, especially not for your critical business (usually huge) tables.
> Agree Lars, Is the information from DB20 can be considered to get the number of rows?

Yes, DB20 in fact displays the information from DBA_TABLES.

regards,

Lars

[Former Member]

But taking a step back here - why the export/import?

We are expecting some space at file system level to reuse and expecting a bit performance improvement.

Pre and post Data validation is in our plan but to convince them we need a techical checks what can show us the no. of tables/objects exported and no. of tables/objects imported(entries or size if possible).

Regards,

Nick Loy

[lbreddemann]

Ok - this is of course a valid approach.

However, you might reconsider to perform a step-by-step Online-Reorganisation and take the opportunity to separate tables into different tablesspaces to make the whole thing more manageable.

For example you could decide to put very large tables or those that are very volatile concerning the data volume in their own tablespaces, so that in the future you can reorganize them without affecting the other tables.

I know this takes more time and effort in the first place, but provides a chance to get rid of those unproductive and risky downtimes needed for offline reorgs.

regards,

Lars

[stefan_koehler]

Hello Nick,

what can show us the no. of tables/objects exported and no. of tables/objects imported(entries or size if possible).

For size analysis you can use the PL/SQL procedure DBMS_SPACE.SPACE_USAGE as long as you use ASSM tablespaces.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACBDJIH

I use this procedure from time to time and its very useful.

Regards

Stefan