cancel
Showing results for 
Search instead for 
Did you mean: 

steps for table level reorganisation

Former Member
0 Kudos

Dear Friends.

We are going for Archiving on big tables like EDI40 table.

So I have decided to regain space after archiving by using table level reorg is it ok??

can you please suggest necessary steps and prerequisites.

Thanks in advance.

Regards

JIggi

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi,

You can regain the space after archiving and runing the reorganization.

For this table EDI40, you have to use archiving object IDOC. This archiving object it will archive EDIDC, EDIDS, EDI30C along with EDI40 table entries.

Suggestion to run the deletion IDOC link entries for the archived data from the table SRRELROLES be get more space benifits.

After archiving execute reorganization program as suggested by many of our Forum mates.

-Thanks,

Ajay

Former Member
0 Kudos

Dear All,

I have done following steps:--

Microsoft Windows [Version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\wiqadm.WSECCQA>brtools

BR0651I BRTOOLS 7.00 (24)

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.21

BR0656I Choice menu 1 - please make a selection

-


BR*Tools main menu

1 = Instance management

2 - Space management

3 - Segment management

4 - Backup and database copy

5 - Restore and recovery

6 - Check and verification

7 - Database statistics

8 - Additional functions

9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

3

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.25

BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.25

BR0656I Choice menu 7 - please make a selection

-


Database segment management

1 = Reorganize tables

2 - Rebuild indexes

3 - Export tables

4 - Import tables

5 - Alter tables

6 - Alter indexes

7 - Additional segment functions

8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

1

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.28

BR0663I Your choice: '1'

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.28

BR0657I Input menu 91 - please check/enter input values

-


BRSPACE options for reorganization of tables

1 - BRSPACE profile (profile) ...... [initWIQ.sap]

2 - Database user/password (user) .. [/]

3 ~ Reorganization action (action) . []

4 ~ Tablespace names (tablespace) .. []

5 ~ Table owner (owner) ............ []

6 ~ Table names (table) ............ []

7 - Confirmation mode (confirm) .... [yes]

8 - Extended output (output) ....... [no]

9 - Scrolling line count (scroll) .. [20]

10 - Message language (language) .... [E]

11 - BRSPACE command line (command) . [-p initWIQ.sap -s 20 -l E -f tbreorg]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

6

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.38

BR0663I Your choice: '6'

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.38

BR0681I Enter string value for "table" []:

BDCP

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.41

BR0683I New value for "table": 'BDCP'

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.41

BR0657I Input menu 91 - please check/enter input values

-


BRSPACE options for reorganization of tables

1 - BRSPACE profile (profile) ...... [initWIQ.sap]

2 - Database user/password (user) .. [/]

3 ~ Reorganization action (action) . []

4 ~ Tablespace names (tablespace) .. []

5 ~ Table owner (owner) ............ []

6 ~ Table names (table) ............ [BDCP]

7 - Confirmation mode (confirm) .... [yes]

8 - Extended output (output) ....... [no]

9 - Scrolling line count (scroll) .. [20]

10 - Message language (language) .... [E]

11 - BRSPACE command line (command) . [-p initWIQ.sap -s 20 -l E -f tbreorg -t "

BDCP"]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

c

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.45

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0291I BRSPACE will be started with options '-p initWIQ.sap -s 20 -l E -f tbreo

rg -t "BDCP"'

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.45

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

c

BR0280I BRTOOLS time stamp: 2009-04-01 15.14.46

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

###############################################################################

BR1001I BRSPACE 7.00 (24)

BR1002I Start of BRSPACE processing: seagjxpe.tbr 2009-04-01 15.14.46

BR0484I BRSPACE log file: F:\oracle\WIQ\sapreorg\seagjxpe.tbr

BR0280I BRSPACE time stamp: 2009-04-01 15.14.48

BR1009I Name of database instance: WIQ

BR1010I BRSPACE action ID: seagjxpe

BR1011I BRSPACE function ID: tbr

BR1012I BRSPACE function: tbreorg

BR0280I BRSPACE time stamp: 2009-04-01 15.14.52

BR0657I Input menu 353 - please check/enter input values

-


Options for reorganization of tables: SAPSR3.BDCP (degree 1)

1 ~ New destination tablespace (newts) ..... []

2 ~ Separate index tablespace (indts) ...... []

3 - Parallel threads (parallel) ............ [1]

4 ~ Table/index parallel degree (degree) ... []

5 - Create DDL statements (ddl) ............ [yes]

6 ~ Initial extent size category (initial) . []

7 ~ Sort by fields of index (sortind) ...... []

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2009-04-01 15.14.55

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-04-01 15.14.55

BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2009-04-01 15.14.56

BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0280I BRSPACE time stamp: 2009-04-01 15.14.56

BR0370I Directory F:\oracle\WIQ\sapreorg\seagjxpe created

BR0280I BRSPACE time stamp: 2009-04-01 15.14.56

BR1101I Starting online table reorganization...

BR0280I BRSPACE time stamp: 2009-04-01 15.14.56

BR1124I Starting reorganization of table SAPSR3.BDCP ...

BR0280I BRSPACE time stamp: 2009-04-01 15.17.24

BR1105I Table SAPSR3.BDCP reorganized successfully

BR0280I BRSPACE time stamp: 2009-04-01 15.17.24

BR1141I 1 of 1 table reorganized - 2152767 of 2152767 rows processed

BR0204I Percentage done: 100.00%, estimated end time: 15:17

BR0001I **************************************************

BR0280I BRSPACE time stamp: 2009-04-01 15.17.24

BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2009-04-01 15.17.24

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

c

BR0280I BRSPACE time stamp: 2009-04-01 15.18.05

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-04-01 15.18.05

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: seagjxpe.tbr 2009-04-01 15.18.05

BR0280I BRSPACE time stamp: 2009-04-01 15.18.05

BR1005I BRSPACE completed successfully

###############################################################################

BR0292I Execution of BRSPACE finished with return code 0

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.05

BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRTOOLS:

c

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.10

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.10

BR0656I Choice menu 7 - please make a selection

-


Database segment management

1 + Reorganize tables

2 - Rebuild indexes

3 - Export tables

4 - Import tables

5 - Alter tables

6 - Alter indexes

7 - Additional segment functions

8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

b

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.13

BR0663I Your choice: 'b'

BR0673I Going back to the previous menu...

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.13

BR0656I Choice menu 1 - please make a selection

-


BR*Tools main menu

1 = Instance management

2 - Space management

3 + Segment management

4 - Backup and database copy

5 - Restore and recovery

6 - Check and verification

7 - Database statistics

8 - Additional functions

9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-


BR0662I Enter your choice:

9

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.15

BR0663I Your choice: '9'

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.15

BR0680I Do you really want to exit BRTOOLS? Enter y[es]/n[o]:

y

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.17

BR0257I Your reply: 'y'

BR0280I BRTOOLS time stamp: 2009-04-01 15.18.17

BR0652I BRTOOLS completed successfully

And I compared space before reorg and after reorg.

I got 5 Gb space back .

I hv done the whole procedure on BDCP table on WIQ.

Shall i do the same procedure on production server.

Thanks in advance.

JIggi

Former Member
0 Kudos

yes you should do the same on prd server

Rohit

Former Member
0 Kudos

Hi,

I hope you are planning this activity on prd when there is less load.

Manoj

Former Member
0 Kudos

Hi,

Yes i will do it on prd when less load is there.

I have done all above procedure in the same tablespace "SAPSR3".

that means I have not created any other tablespace for reorg.

Is that ok??

Regards

Nirgun

Former Member
0 Kudos

yes thats ok

you can do it both ways by creating a table space or directly

Rohit

Former Member
0 Kudos

Hi Jiggi,

I would suggest you to do a index re-org first and see if it suffices your requirement.

Consider note 53062 before re-org.

Thanks,

Naveen

Former Member
0 Kudos

hi,

you can do table reorganization using BRTOOLS. For step by step you can access here (http://sapbasis.wordpress.com/2008/07/16/table-reorganization-using-brspace-via-temporary-tablespace/) or here (http://sapbasis.wordpress.com/2008/06/11/simple-table-reorganization-for-sap-r3-using-brspace/)

ardhian

http://ardhian.kioslinux.com

http://sapbasis.wordpress.com

lbreddemann
Active Contributor
0 Kudos

Hi there,

as EDI40 is a cluster table, online reorganisation is very likely not possible.

You may want to check if the table on your system uses LONG or LOB. In the later case you still can do the online reorganisation.

If the table uses LONG then you've to use the offline export/import method.

Check note

#646681 - Reorganizing tables with BRSPACE

for a complete description of the process.

ATTENTION: the note does not specifically refer to single table reorgs, but rather it is assumed that you reorganize a complete tablespace.

Therefore, instead of dropping the tablespace before you import the data, just drop/rename the table to make sure that the table gets re-created.

regards,

Lars

Former Member
0 Kudos

Hi

You can do the reorg using BRSPACE

Please follow the steps in the blog in the below link

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

You will be able to do the reorg