Space released during Orcale DB reorg..??

[Former Member]

Hi All,

We have to do database reorg. Do we have a method to know:

1.How fragmented  tables and table spaces are?

2. How much approx space would be released after the reorganization?

Please guide how to go about it. Any method or any approach someone has done and can share will be greatly appreciated.

Regards,
Vicky

[Former Member · Accepted solution]

Hi,

You can take out the list of top tables from db02 and have to look for the tables in this list which are being archived or regular pruning jobs are running. Entries are deleted from this table and hence having fragmented space. There is a sql query to find out the net DE-fragmented space in a table but right now I dont have it. You can search for it.

Sometimes table lob segments will have lot of fragmented data. In the top tables list you may find some tables which are small in size but lob segment belonging to it is huge in size.

Follow the below method to find DE-fragmented data

table size :

SELECT BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='table_name';

lob size:

select bytes/1024/1024/1024 from dba_segments where SEGMENT_NAME='lob_name';

to see the lob belongs to which table and column

SQL> select TABLE_NAME, COLUMN_NAME from dba_lobs where SEGMENT_NAME='lob_name';

LOB segment fragmentation:

You can use DBMS_LOB.GETLENGTH to determine the space used within an LOB segment. You can use the following script to determine the net allocation of the existing LOB segments:

SELECT ROUND(SUM(DBMS_LOB.GETLENGTH("CLUSTD"))/ 1024 / 1024) "NET(MB)" FROM SAPSR3.table_name;

Run the below queries before and after you run reorganization to verify cleared space :

select sum(bytes/1024/1024/1024) from dba_data_files;

SQL> select bytes/1024/1024/1024, segment_name from dba_segments where segment_name = 'table_name';

Also cross check the #tableentries b4 and after run >select count(*) from <schema>.<table_name>

[REDACTED - MODERATOR]

Regards,

Amit Jana.

[Former Member · Accepted solution]

Hi Amit,

We donot have any data which we archieve. Thanks a lot for your response.

I am working on what you and Reagan has shared. Will come back in case

of any clarifications..thanks again..

Regards,

[Former Member]

Hi Anil, Regan,

While I have yet to go through SAP notes(927813 & 821687) in details.

As an initial check I did the following check..

1.Basis Details: It is BW system with 4:TB DB size.

2. No data archival/purging has happened in the system.

3. I went to to DB02 and took out the details of the top 50/100 tables.

4. Went to DB02OLD and in details analysis checked the top 10 tables.

5. Except table BALDAT ,non has free space in used blocks.

6. SCreen shot of BALDAT spcae details analysis is below.

7 Based on the below screen shot I assume if we do a reorg of table BALDAT we will

be able to reclaim approximately 13 percentage of free space in used blocks.

8 For other tables, free space for used blocks is coming as zero(0). Hence I conclude no

space will be reclaimed after the reorg for those tables.

9 So far no archival/purging has happened in the systems and I believe reorg will not release too much of space.

Regards,

Vicky

[Former Member]

Hi All, I went through SAP note 821687 I ran the below query and got the wasted space of the top 20 tables. I have never worked or reorg but believe if I do a reorg of these tables I can reclaim the wasted space.

"SELECT * FROM (SELECT     SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,     NUM_ROWS,     AVG_ROW_LEN ROWLEN,     BLOCKS,     ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,     ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *       (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,     ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -       (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"   FROM DBA_TABLES   WHERE     NUM_ROWS IS NOT NULL AND     OWNER LIKE 'SAP%' AND     PARTITIONED = 'NO' AND     (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)   ORDER BY 7 DESC) WHERE ROWNUM <=20;"

Can this wasted space be reclaimed after the reorg..??

Thanks in advance.

Regards,

[Former Member]

Ya, thats the query i was mentioning about.

The space cleared may not be the exact figure given by this query output, but it will be some value nearby.

You can try these tables. Check the table structures and be sure which all tables can be done online and offline reorg. Table having fields of data type long and long raw should be done offline.

Better crosscheck in sap notes before you proceed.

Amit.

[Reagan]

You can make use of Oracle Segment Advisor.

927813 - Oracle 10g: Using Oracle Segment Advisor to optimize space

Regards

RB