Table Reorg - check size of tables

[Former Member]

Hello masters,

I am pretty new in Oracle, and I would like to make online reorg of tables.

My questions would be:

How can I check in Oracle a table size before and after reorg ?

I would be interested in the table size in mb, index size.

I would like to start the reorg, I have the tables, but I just need to check before and after to see how much space I gained.

Thanks in advance,

Peace.

[former_member524429 · Accepted solution]

Hi,

How can I check in Oracle a table size before and after reorg ?

You can get it under DB02old/DB02 -> Detailed Analysis. It will give you the Size of Table + Index in KB.

I would like to start the reorg, I have the tables, but I just need to check before and after to see how much space I gained.

I would suggest you to refer this useful [SAP Note 541538 - FAQ: Reorganization|https://service.sap.com/sap/support/notes/541538] && [Note 646681 - Reorganizing tables with BRSPACE|https://service.sap.com/sap/support/notes/646681] to get all the required details. Refer these SAP notes line by line and understand each options. During Table Reorganization you will have to consider sufficient space of oraarch location, sufficient space in responsible Tablespace(s).

Also, You can refer this [SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle|https://service.sap.com/sap/support/notes/821687] to get the information on wasted Space Table/Index wise which is occurred due the Fragmentation in Table/Index. I would record such information before and after Table reorg.

I hope the above information will be helpful.

Regards,

Bhavik G. Shroff

[Former Member · Accepted solution]

Hi Bhavik,

Thanks for the very helpful reply:

1. DB02 - I can check tables there. Perfect.

2. I checked the space on oraarch and I see there is enough.

3. The notes are really useful too,

I checked how to reorganize and improve overall response time.

nohup brspace -c force -o process,time -u / -f tbreorg -a reorg -tablespace PSAPSR3 -t TST03 &

but, now I need to find how to perform online reorg to save the space, any clues for the moment ?

Most probably I just need to change the options.

Thanks again.

[Former Member · Accepted solution]

Hi Jordy,

>> but, now I need to find how to perform online reorg to save the space, any clues for the moment ?

Check the note 646681 - Reorganizing tables with BRSPACE.

Find the title "tablespace reorganization with brspace as of Oracle 10g"

Read the note and apply the steps very carrefully.

Best regards,

Orkun Gedik

[former_member213250 · Accepted solution]

Hello Jordy

Regards to re-organisations with online/offline options and steps to perform these reorg jobs

are explained in SAP Note : 541538

If you can have a look at this note you can get useful info regards to reorgs.

Hope it helps

Thanking you

BR

Venkat

[Former Member · Accepted solution]

Hi Ordun,

I need to reorganize tables to free up some space. I think the paragraph is for tablespace reorg.

Thanks,

Jordy

[Former Member · Accepted solution]

Hi and thanks for reply,

Really useful note, but I can't see anywhere written how to do reorg to save space .

Thanks again,

Jordy

[Former Member · Accepted solution]

Hi Jordy,

We need to clarify an issue here. Table reorganization does not reclaim free space to the OS, but in the tablespace. In short, if you reorg a table, you can save freespace in the tablespace.

Take into account that you should be careful when you are performing online reorganization. This is because, there shouldn't be an activity, during the reorganization, over the table.

As you noted in your previous message that you can perform an online reorganization in its tablespace, by the command, below;

brspace -u / -f tbreorg -t <table name>

You can find it, in the note 646681 - Reorganizing tables with BRSPACE, at the first page.

As an additional information, do not forget to create new statistics, after the respectful reorganization. You can find this information in the same note, under "11. creating new statistics for the reorganized tables"

Moreover, you should take into account LONG into LOB convertion. This is because, if there's a long field in the, table you will not be able to online reorganization.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Thanks Orkun.