TABLE SPACE REORGANIZATION !! QUICK EXPERT INPUTS

[former_member69568]

HI.....Experts

We have a internal quick project of Reorganizing the our ABAP SCHEMA Table Space PSAPSR3 of Size 2 TB.

May i request you to give some quick check points / notes / links / advises to make it successfull.

We have to execute this to this weekend - request all to spare 2 seconds to reply this message.

Regards

[anindya_bose · Accepted solution]

Hi Friend

Check the following notes.

SAP Note 646681 - Reorganizing tables with BRSPACE

SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle

Get the information about fragmented tables from your EWA report.

Decide according to your database version. Post here if you have any question.

Thanks and Regards

Anindya

[former_member69568 · Accepted solution]

Anindya..........Thank you very much for your reply

Thanks ...the complete requirement of TABLESPCE started as,

we have cleaned up some BASIS TABLES as per

SAP Note 706478 - Preventing Basis tables from increasing considerbly

and

We have done TOP 40 INDEXES Compression as per note

1109743 - Use of Index Key Compression for Oracle Databases.

--

So just took a decission with out measuring further,

we want to reorg the TABLESPACE

Are we going in wrong direction.

Please confirm

Rgds

[Former Member · Accepted solution]

Jyothi,

So just took a decission with out measuring further, we want to reorg the TABLESPACE

See Point no.2 of SAP Note 541538 - FAQ: Reorganization:- Which objects can I reorganize?

The following objects can be reorganized:

a) Tables: Individual tables or groups of tables can be reorganized, and restructured in the same or a new tablespace. The corresponding indexes are also set up implicitly.

b) Indexes: The reconstruction of indexes is not a reorganization in the actual sense. Instead, commands such as DROP / CREATE, REBUILD or COALESCE can be used to set up an index again. For more information, refer to Note 332677. For this reason, this note does not deal with the rebuilding of indexes.

c) Tablespaces: If all segments are reorganized in a tablespace, the tablespace itself can also be modified at the same time (for example, it can be made smaller or its data file structure can be adjusted).

So it means you can either reorg Individual tables or groups of tables, Index or Tablespaces.

Please check mentioned note for further details.

You can perfrom reorg using BRSPACE tool (Note 647697).

Also see http://sapbasis.wordpress.com/tag/tablespace/

http://help.sap.com/saphelp_nw70/helpdata/EN/58/6bec38c9fa7e44b7f2163905863575/frameset.htm

Hope this answers your query.

Thanks

Sushil

[anindya_bose · Accepted solution]

Jyothi

By reorganization the ultimate gain you can have is more free spaces.You might not need to reorganiza the whole tablespace.

Instead, if you reorganize some tables you can get the 99% of the benefit in terms of free space.

Use the below SQL commad to know which are the top tables you should reorganize ( this information also available in EWA report)...

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 <=50;

If you need top 50 tables, you can run that, else you can change the number in the last line for top 10, 20 and so on tables.

Coming to the actual reorganization , you did not tell which database version you are using.

For oracle 9i, tables with LONG or LONG RAW filed need to be reorganized by export/import method.

For oracle 10G, you can change LONG to LOB ( Large object field) and then reorganize from brtools online!!

But , I would like to tell you one more point, that with that LONG-LOB migration you might face performance problem. So, better is to reorganize tables in a Demo system , and then decide for your production system..

Let me know if you have further quaries.

Thanks and Regards

Anindya

[former_member69568 · Accepted solution]

Anindya & Suhil Thanks

-

---

it is Oracle 10.2.0.2.0 on AIX.

when is executed the scriopt thry SE38 --> RSORADJV --> Execute

i get the below error.

(ORA-1,007) ORA-01007: variable not in select list

Message no. S1899

Diagnosis

This message is used in variable form, that is, it is not possible to specify additional information.

May be troubling you - but poor on oracle....thanks again for all your replies

Rgds

[anindya_bose · Accepted solution]

Jyothi

After > SE38 --> RSORADJV --> Execute

which SQL command you ran from there?

Try something like "Select username from dba_users:"........ and let us know if you can see the name of the schema owners.

I assumed that you know report RSORADJV is just a front end to the SQL.

Thanks and Regards

Anindya

[fidel_vales · Accepted solution]

you cannot do

SELECT * FROM (subquery)

in rsoradjv

yu need to specify all the field names that are retuned from the subquery

[former_member69568 · Accepted solution]

Anindya & all Experts Replied & reviewing this message..

Thank you very much,

The given SQL i executed at SQL promompt and got the results.

as you mentioned

For oracle 10G, you can change LONG to LOB ( Large object field) and then reorganize from brtools online!!

our SAP is ECC 6.0 and Oracle 10.2.0.2.

i executed the below commands and i see in PSAP<SID> schema there is only one table with field LONG & LONG RAW.

Select Owner from dba_tables where TABLE_NAME IN  (select TABLE_NAME from dba_tab_cols where DATA_TYPE = 'LONG' ) ;

.

is it correct.

in ECC 6.0 there is only one 1 table with LONG in ABAP SCHEMA TABLE SPACE ??

AM I CORRECT ?

a word of you will gives more confidense for tomorrows activity.

Rgds

[fidel_vales · Accepted solution]

hi,

first, you do not need to do a subquery, you already have the table_name and the owner in the dba_tab_cols

> Select Owner from dba_tables where TABLE_NAME IN (select TABLE_NAME from dba_tab_cols where DATA_TYPE = 'LONG' ) ;

you forgot to find the fields type "LONG RAW"

[anindya_bose · Accepted solution]

Exactly what Fidel said.

Check for below tables..

RSZWOBJ

BALDAT

RSRWBSTORE

UCL2040

UGMDTRANS

VARI

MONI

They should have LONG RAW field.

Thanks and Regards

Anindya

[former_member69568 · Accepted solution]

Thanks once agin Anindya & Fidel.

When i executed

Case -1

SELECT DISTINCT OWNER,TABLE_NAME FROM ALL_TAB_COLS WHERE DATA_TYPE = 'LONG' and OWNER = 'SAPP10';

i got only one table for that is as we discussing "PLAN_TABLE"

For LONG -- this os OK ....completed and concluded in current reorg list it is not there ..so we ft about this.

Case - 2

SELECT DISTINCT OWNER,TABLE_NAME FROM ALL_TAB_COLS WHERE DATA_TYPE = 'LRAW' and OWNER = 'SAPP10';

and

SELECT DISTINCT OWNER,TABLE_NAME FROM ALL_TAB_COLS WHERE DATA_TYPE = 'LONG RAW ' and OWNER = 'SAPP10';

both has resulted "no rows selected".

I went SE11 & i have taken the example of table "UGMDTRANS"i see a field "LRAW"

got bit conflused and at SQL i executed

SQL> desc UGMDTRANS

Name Null? Type

-

---

-

---

MANDT NOT NULL VARCHAR2(9)

RELID NOT NULL VARCHAR2(6)

TRKEY NOT NULL VARCHAR2(270)

SRTF2 NOT NULL NUMBER(10)

AEDAT NOT NULL VARCHAR2(24)

USNAM NOT NULL VARCHAR2(36)

SAPRL NOT NULL VARCHAR2(12)

CLUSTR NOT NULL NUMBER(5)

CLUSTD BLOB

-

---

-

---

in SE11 it show as LRAW in SQL it shows as BLOB.

Clarification

now can i think, in the table UGMDTRANS " LRAW" field is there and it is already converted to BLOB and no other requirements required to reorganize the table UGMDTRANS

Thanks once again for all your help specially foir Anindya & Fidel

WARM REGARDS

[former_member69568 · Accepted solution]

Experts Last Doubt

In the table UGMDTRANS " LRAW" field is there and it is already converted to BLOB ( when i see in SQL as DESC <Tablename>)

When & How it would have got converted.

From the day of installation - we have not done any conversion activity.

Rgds

[Former Member · Accepted solution]

Hi Jyothi,

hope you did your reorg activity successfully as i can gather from the posts. i have a similar situation with oracle 10g/ECC6.0 on windows 2003 platform. i am planing to do the extra client deletion followed by reorganization of whole database. Can you please guide/share your recent experience in doing tablespace reorganization.

i have no good experience on oracle, please share your experience/document. waiting for your response.

thank you.

[Former Member]

Jyothi,

If you search by term "table reorg" in sdn you will get many threads.

Anyway see below sap notes & links

SAP Note 646681 - Reorganizing tables with BRSPACE

SAP Note 541538 - FAQ: Reorganization.

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70c8f19f-8010-2c10-ac89-8d828039...

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70c8f19f-8010-2c10-ac89-8d828039...

Hope this helps you.

Thanks

Sushil