Do you know the fastest way to analyze DB content and tables distribution in your SAP system?
…If not, I’d suggest You to check this self-explaining <less than 2 minutes video>
Already done?
Yes, it’s so easy!
Let me know drive you in explaining the just two steps needed by the application:
- how to do execute the top 1.000 tables download from your SAP system
- how to upload the list into Inquaero.
So let’s start with the top 1.000 objects download
The way for downloading the top 1.000 tables depends on which database your SAP is running. We have basically 3 options:
- Oracle Database
- HANA Database
- Other Databases (ex. MaxDB, DB2, etc…)
Let’s go into details:
-
Oracle Database
In order to get the top 1.000 table list in Oracle, you have to execute the following SQL script / statement:
with segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
select rank() over (order by sum(bytes) desc) rank, sum(bytes) bytes, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner=r.segment_owner and s.segment_name=r.segment_name
and r.owner=upper('SAPSR3')
group by r.owner, r.table_name
)
select table_name, round(bytes/1024) kb
from ranked_tables
where rank<=1000;
You should copy and execute the script above in DBA Cockpit, accessible via Sap transaction code DBACOCKPIT, or transactions ST04, DB02, etc… Once you are in DBA Cockpit you can use path from the cockpit navigation panel: Performance > Additional functions > SQL Command editor.
After execution you can access the results shown in the “Result” tab of the SQL command editor.
Copy the results to clipboard and go read the “Upload” section below.
Step 1 is done for SAP running on Oracle DBs!
-
HANA Database
To get the top 1000 tables from an HANA system it is quite straightforward, just access to transaction DB02 and click on System Information à Large Tables
In the main screen insert following parameters:
- Schema name = SAP*
- no. of Hits = 1000
- Sort by = Memory Size in Total
As size value, use the values in column Memory Size in Total, since the size is in bytes, it should be converted to kb before loading the list.
-
Other Databases (ex. MaxDB, DB2, etc…)
All other Databases use the same approach of HANA DB, for MaxDB for instance it is possible to get the top tables list from DB02 > Table size Statistics > Largest Tables
For DB2 you can get it from transaction DB02 > Space >Top Space Consumers
Here a screen from DB2 system:
First of all register and login to inquaero:
Go to address https://app.inquaero.com/login and click the “REGISTER” button:
Once you’re logged in… you have to add a new system
Then fill-in system details as follows:
And the very last step: “Upload data snapshot”
Simply paste here the list of top 1.000 tables and size:
And click “Upload” …. now it’s time to see the result:
Click on your system:
And here you are Inquaero SEMPLICE… your SAP tables distribution, by size and module, and many many other valuable info, statistics and recommendations from SAP Data Management Guide…
Enjoy the discovery:
