topic Re: DB02 transaction in Application Development and Automation Discussions

DB02 transaction

Former Member — Tue, 30 Jul 2013 05:25:21 GMT

Hello Experts,

Developing program for DB02 transaction. I need to display data for "Tablespace_name", "Total size" and "Free Space".

Looked into DB02 transaction report and they are using tables "dba_data_files" and "dba_free_space".

But i dont find these tables. Please do let me know how to fetch data for the above mentioned fields from the table.

regards,

Gururaj.

Re: DB02 transaction

GauthamV — Tue, 30 Jul 2013 06:16:11 GMT

You will get all the list of tablespace names from TSDB6 table.

Regards,

Gautham.

Re: DB02 transaction

FredericGirod — Tue, 30 Jul 2013 06:20:40 GMT

Hi,

the code to get the size space of a table :

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME .
PARAMETERS : p_bytes TYPE k_bytes.

SELECT-OPTIONS : s_sn FOR zbc_stat_table-sn,
                 s_s_type FOR zbc_stat_table-s_type,
                 s_owner   FOR zbc_stat_table-owner,
                 s_ts      FOR zbc_stat_table-ts.

SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME .
PARAMETERS : p_maj TYPE c.
SELECTION-SCREEN END OF BLOCK b2.

* Types

TYPES : BEGIN OF ts_segment.
INCLUDE STRUCTURE segments_f.
TYPES : END OF ts_segment,
tt_segment TYPE TABLE OF ts_segment.

* Data

DATA : it_segment TYPE tt_segment,
is_segment TYPE ts_segment.

REFRESH it_segment.

EXEC SQL PERFORMING APP_SEGMENTS.
SELECT
    OWNER,
    NVL(PARTITION_NAME, SEGMENT_NAME) NAME,
    SEGMENT_TYPE,
    TABLESPACE_NAME,
    HEADER_FILE,
    HEADER_BLOCK,
    BYTES/1024,
    BLOCKS,
    EXTENTS,
    INITIAL_EXTENT/1024,
    NEXT_EXTENT/1024,
    MIN_EXTENTS,
    MAX_EXTENTS,
    PCT_INCREASE,
    SEGMENT_NAME,
    ''
FROM DBA_SEGMENTS
INTO :is_sEGMENT
WHERE BYTES/1024 > :P_BYTES

ENDEXEC.

regards

Fred

Re: DB02 transaction

RaymondGiuseppi — Tue, 30 Jul 2013 06:20:59 GMT

Those tables don't exist in SAP ddic, those are standard Oracle tables and you must use native sql statements to read their records.

Look at Oracle online documentation (http://docs.oracle.com) for documents like DBA_DATA_FILES or DBA_FREE_SPACE.
There is also a FAQ OSS note : Note 821687 - FAQ: Space utilization and fragmentation in Oracle, this note contains some native SQL statements for reference and should be useful for you.

Regards,

Raymond

Re: DB02 transaction

Former Member — Tue, 30 Jul 2013 06:37:52 GMT

Hi Gururaj,

dba_data_files and dba_free_space are not tables in the ABAP dictionary, they are dictionary views in the underlying Oracle database.

DB02 gives information on allocation of data in the Oracle database.

Functions of DB02 in the Database are

Space analysis of the SAP database and transaction logs showing space allocated, free space and used space.
List of basic options and the recovery model that have been set for the database. By default, automatic creation and update of statistics is set for an SAP system.
List of all files of the SAP database and the free space available on disk.

Specifies the space allocated for individual files, the amount of space used and other data.

The following are the structures of dba_data_files and dba_free_space.

DBA_DATA_FILES

DBA_FREE_SPACE

Dba_data_files:

The dba_data_files means total size of the data file. The data file size is total number of the dba_free_space + dba_segments.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name order by 1;

This example display the total size of the data file:
SQL> select sum(bytes)/(1024*1024*1024) from dba_data_files;
SUM(BYTES)/(1024*1024*1024)

Dba_segments:

The dba_segments means used size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name order by 1;
This example display the total size of the segments:
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments;
SUM(BYTES)/(1024*1024*1024)

Dba_free_space:

The dba_free_space means free size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name order by 1;

This example display the total size of the free space:
SQL> select sum(bytes)/(1024*1024*1024) from dba_free_space;
SUM(BYTES)/(1024*1024*1024)

These tables are accessed using the native SQL statements .

Example IN DB02

dba_data_files

EXEC SQL PERFORMING APP_1.
     SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024
      FROM DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME INTO :TAB_1
ENDEXEC.

dba_free_space

EXEC SQL PERFORMING APP_2.
SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024 FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME INTO :TAB_2
ENDEXEC.

dba_tablespaces

EXEC SQL PERFORMING DBA_SEG_ANALYZER2.

SELECT * FROM DBA_TABLESPACES
INTO :TAB_3
ENDEXEC.

dba_segments

EXEC SQL PERFORMING DBA_SEG_ANALYZER2.

    SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME,
           EXTENTS, SEGMENT_TYPE, BYTES / 1024
          FROM DBA_SEGMENTS
          INTO :DBA_SEG_AN
ENDEXEC.

Re: DB02 transaction

Former Member — Tue, 30 Jul 2013 12:00:57 GMT

Hi,

Developing report to display like the following output.. Columns are Tablename_space, Total space, Free space and %used.

PSAPSR3	250,000.00	173,731.38	31
PSAPSR3701	70,350.00	8,727.56	88
PSAPSR3USR	290.00	97.75	66
PSAPTEMP	2,220.00	2,220.00	0
PSAPUNDO	10,000.00	6,366.50	36
SYSAUX	1,284.00	798.94	38
SYSTEM	1,894.00	967.81	49
Sum

i have developed little bit of code, but able to display only one tablename_space(Report in progress).

Attached my code here : ( INTO :TAB_1 is not working as its not an internal table, but ow to declare it as an internal table?)

   TYPES : BEGIN OF ty_tab1,
         tablespace_name(50),
*        size_mb,
*         free_mb,
        END OF ty_tab1.

DATA : tab_1 TYPE ty_tab1.
*       wa_tab_1 TYPE ty_tab1.

EXEC SQL.
SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024
   FROM DBA_DATA_FILES
   GROUP BY TABLESPACE_NAME INTO :TAB_1
ENDEXEC.

*EXEC SQL.
*    SELECT TABLESPACE_NAME, round(SUM(NVL(BYTES,0))/1024/1024) size_mb
*      FROM DBA_DATA_FILES
*      GROUP BY TABLESPACE_NAME INTO :TAB_2
* ENDEXEC.

*LOOP AT tab_1 INTO wa_tab1.
WRITE : / tab_1.
*ENDLOOP.

Thanks in advance.

Gururaj.

Re: DB02 transaction

Former Member — Wed, 31 Jul 2013 06:02:18 GMT

Hi,

Developing report to display like the following output.. Columns are Tablename_space, Total space, Free space and %used.

PSAPSR3	250,000.00	173,731.38	31
PSAPSR3701	70,350.00	8,727.56	88
PSAPSR3USR	290.00	97.75	66
PSAPTEMP	2,220.00	2,220.00	0
PSAPUNDO	10,000.00	6,366.50	36
SYSAUX	1,284.00	798.94	38
SYSTEM	1,894.00	967.81	49
Sum

i have developed little bit of code, but able to display only one tablename_space(Report in progress).

Attached my code here : ( INTO :TAB_1 is not working as its not an internal table, but ow to declare it as an internal table?)

Thanks in advance.

Gururaj.

Re: DB02 transaction

RaymondGiuseppi — Wed, 31 Jul 2013 11:50:26 GMT

To fill an internal table in native SQL, use OPEN cursor, FETCH NEXT cursor, CLOSE cursor statements, look a the sample at http://help.sap.com/abapdocu_731/en/abapexec_cursor.htm.

(Native SQL doesn't know anything of Abap internal table )

For better select statement, check Oracle forums for threads like query to check tablespace size and freespace in https://forums.oracle.com/, or wiki like http://www.orafaq.com/wiki/Tablespace.

Regards,

Raymond