Tablespace reorganization

[Former Member]

Hi all,

We have done done SLO activity in our test server.

So during SLO activity we have added more data files to psapundo.

Now it is 267GB and used size is 5 GB only. SLO activity completed now.

So i want to roll back psapundo to 10GB. So i want to do tablespace reorganization, here i am gathering

informations and reading snotes and few documents to do thios activity.

Sap note 646681 is helping here but i need you all help also.

BR,

Kutty

[Former Member · Accepted solution]

Hi Kutty,

I suggest you to drop and re-create PSAPUNDO tablespace. You can find the further information in the document, below;

http://download.oracle.com/docs/cd/B12037_01/server.101/b10739/undo.htm

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

thanks for your quick resp..

Dropping and recreating undo may create issue know?

because in undo nearly 5GB used space is there.

Are you sure i can drop and recreate it?

[Former Member · Accepted solution]

Hi,

Can you execute the statement in order to identify what objects in the PSAPUNDO?

select segment_name, tablespace_name, status from dba_rollback_segs;

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

Find the below log:

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

SYSTEM SYSTEM ONLINE

_SYSSMU1$ PSAPUNDO ONLINE

_SYSSMU2$ PSAPUNDO ONLINE

_SYSSMU3$ PSAPUNDO ONLINE

_SYSSMU4$ PSAPUNDO ONLINE

_SYSSMU5$ PSAPUNDO ONLINE

_SYSSMU6$ PSAPUNDO ONLINE

_SYSSMU7$ PSAPUNDO ONLINE

_SYSSMU8$ PSAPUNDO ONLINE

_SYSSMU9$ PSAPUNDO ONLINE

_SYSSMU10$ PSAPUNDO ONLINE

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

_SYSSMU11$ PSAPUNDO OFFLINE

_SYSSMU12$ PSAPUNDO OFFLINE

_SYSSMU13$ PSAPUNDO OFFLINE

_SYSSMU14$ PSAPUNDO OFFLINE

_SYSSMU15$ PSAPUNDO OFFLINE

_SYSSMU16$ PSAPUNDO OFFLINE

_SYSSMU17$ PSAPUNDO OFFLINE

_SYSSMU18$ PSAPUNDO OFFLINE

_SYSSMU19$ PSAPUNDO OFFLINE

_SYSSMU20$ PSAPUNDO OFFLINE

_SYSSMU21$ PSAPUNDO OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

_SYSSMU22$ PSAPUNDO OFFLINE

_SYSSMU23$ PSAPUNDO OFFLINE

_SYSSMU24$ PSAPUNDO OFFLINE

_SYSSMU25$ PSAPUNDO OFFLINE

_SYSSMU26$ PSAPUNDO OFFLINE

_SYSSMU27$ PSAPUNDO OFFLINE

_SYSSMU28$ PSAPUNDO OFFLINE

_SYSSMU29$ PSAPUNDO OFFLINE

_SYSSMU30$ PSAPUNDO OFFLINE

_SYSSMU31$ PSAPUNDO OFFLINE

_SYSSMU32$ PSAPUNDO OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

_SYSSMU33$ PSAPUNDO OFFLINE

_SYSSMU34$ PSAPUNDO OFFLINE

_SYSSMU35$ PSAPUNDO OFFLINE

_SYSSMU36$ PSAPUNDO OFFLINE

_SYSSMU37$ PSAPUNDO OFFLINE

_SYSSMU38$ PSAPUNDO OFFLINE

_SYSSMU39$ PSAPUNDO OFFLINE

_SYSSMU40$ PSAPUNDO OFFLINE

_SYSSMU41$ PSAPUNDO OFFLINE

_SYSSMU42$ PSAPUNDO OFFLINE

_SYSSMU43$ PSAPUNDO OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

_SYSSMU44$ PSAPUNDO OFFLINE

_SYSSMU45$ PSAPUNDO OFFLINE

_SYSSMU46$ PSAPUNDO OFFLINE

_SYSSMU47$ PSAPUNDO OFFLINE

_SYSSMU48$ PSAPUNDO OFFLINE

_SYSSMU49$ PSAPUNDO OFFLINE

_SYSSMU50$ PSAPUNDO OFFLINE

_SYSSMU51$ PSAPUNDO OFFLINE

_SYSSMU52$ PSAPUNDO OFFLINE

_SYSSMU53$ PSAPUNDO OFFLINE

_SYSSMU54$ PSAPUNDO OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS

-

---

-

---

-

---

_SYSSMU55$ PSAPUNDO OFFLINE

56 rows selected.

BR,

Kutty

[Former Member · Accepted solution]

Hi,

Ok. Undo tablespace uses with the states, below;

1) Rollback operations,

2) Read consistent queries

3) Flashback utility

4) Database recovery process

So, if you do not use flashback you can drop and create tablespace from scratch.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

Thanks for your support!

I have dropped and re-created as per your advice.

BR,

Kutty

[Former Member · Accepted solution]

Hi Orkun and all,

Yesterday everything went nicely now i am planning to do reorg of psapbtabd after demerger completion.

I am going with snote-646681.

I have few doubts, should i create a new tablespace and then only i have to execute brspace

command.

BR,

Kutty

[Former Member · Accepted solution]

Hi,

>> I have few doubts, should i create a new tablespace and then only i have to execute brspace

Why you are planning to create new tablespace? You can reorg "psapbtabd" directly.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

In snote it is mentioned clearly that we need to create new tablespace thats why i asked.

But first let me clear my requirement.

1)We have done demerger, so few company code datas has been deleted .

2)Now project is over.

3)before deletion database used size was 1.3TB

4)after deletion database used size is 640GB.

5)Everything went good and now i would like to do reorg, am i correct here.

Best Regards,

Kutty

[Former Member · Accepted solution]

Hi,

It is more clear, now. So, if the current number of datafiles or size of datafiles are suitable with your needs, you don't need to recreate the tablespace from the scratch. Otherwise you should recreate it.

But under both conditions, ts reorganization is required.

Best Regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

So in this situation ts reorg is required to improve the performance. But nothing more than that. is it..?

If there is any other reason mention it for this perticular situation.

BR,

Kutty

[Former Member · Accepted solution]

Hi,

>> So in this situation ts reorg is required to improve the performance. But nothing more than that. is it..?

You should reorganize the tablespace and create the statistics.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

1)Whats your recommendation in reorg.?

2)Reorg of one full tablespace in one run or reorganising tables one by one in a tablespace?

3) If i want to reorg tables in brtools, Segment management->Reorganize tables->Reorganization action(reorg)-> then mentioning tablespace and table name,thats it rite?

4)If i want to reorg one full tablespace in brtools, Space management-> Alter tablespace->Alter tablespace action(coalesce)-->then mentioning the required tablespace name, is it rite?

Correct me if i went wrong.

BR,

Kutty

[Former Member · Accepted solution]

Hi,

You can check the note 646681 - Reorganizing tables with BRSPACE. For example, you can execute the command, below;

brspace -f tbreorg -s psapbtabd -t "*" -p 4

Please note that , if you have LONG fields in the table, you should convert to LOB before the reorganization. You can find the further information in the note.

Best regards,

Orkun Gedik

[Former Member]

k

[former_member213250]

Hi

Just some additional infor

As per Orkun info you need to convert LONG fields to LOBs and if LOBs exists online re-org with for LOB segments is not recommended.

Regards

Venkat