Export/Import Tables using BRTOOLS

Case: I want to reorganize table and rebuild index offline for table SOFFCONT1. Table SOFFCONT1 contains SAP Business Workplace/SAPoffice objects which often grow rapidly in Netweaver based component.

Procedure: Here’s the procedure to export/import tables and rebuild index using BRTOOLS,

1. Export table SOFFCONT1
2. Drop index
3. Import table SOFFCONT1
4. Run Update Statistics Optimizer

Details steps :

1. Export table SOFFCONT1

>brspace -f tbexport -t soffcont1

BR0280I BRSPACE time stamp: 2009-06-06 21.10.00

BR1009I Name of database instance: R3X

BR1010I BRSPACE action ID: seasymoz

BR1011I BRSPACE function ID: tbe

BR1012I BRSPACE function: tbexport

BR0280I BRSPACE time stamp: 2009-06-06 21.10.07

BR0657I Input menu 359 - please enter/check input values

Main options for export of tables: SAPR3X.SOFFCONT1

1 * Export utility (utility) ............... [EXP]

2 * Tablespaces for export (tablespaces) ... []

3 * Owner for export (owner) ............... []

4 - Export table rows (rows) ............... [yes]

5 - Export table indexes (indexes) ......... [yes]

6 - Export table constraints (constraints) . [yes]

7 - Export table grants (grants) ........... [yes]

8 - Export table triggers (triggers) ....... [yes]

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 21.12.18

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 21.12.18

BR0657I Input menu 360 - please enter/check input values

Additional options for export of tables: SAPR3X.SOFFCONT1

1 - Use direct path (direct) ................ [yes]

2 - Export buffer size in KB (buffer) ....... [10240]

3 - Compress table extents/data (compress) .. [no]

4 - Consistent export (consistent) .......... [no]

5 # Parallel degree (parallel) .............. [1]

6 - Max. size of dump file in MB (filesize) . [20000]

7 - Force table export (force) .............. [no]

8 - EXP/EXPDP command (command) ............. [E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp]

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 21.13.15

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz created

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz.edd created

BR1164I Export of tables will be started with command line: E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

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

BR0280I BRSPACE time stamp: 2009-06-06 21.13.47

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0278I Command output of 'E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp':

Export: Release 10.2.0.2.0 - Production on Sat Jun 6 21:13:47 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8DEC character set and UTF8 NCHAR character set

About to export specified tables via Direct Path ...

Current user changed to SAPR3X

. . exporting table                      SOFFCONT1

continuing export into file I:\Oracle\R3X\sapreorg\seasymoz.edd\expdat1.dmp

962456 rows exported

Export terminated successfully without warnings.

BR0280I BRSPACE time stamp: 2009-06-06 22.44.59

BR1160I 1 table exported by EXP utility

BR0280I BRSPACE time stamp: 2009-06-06 22.45.02

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

BR0280I BRSPACE time stamp: 2009-06-06 22.55.09

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 22.55.10

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbexport' completed

BR1008I End of BRSPACE processing: seasymoz.tbe 2009-06-06 22.55.10

BR0280I BRSPACE time stamp: 2009-06-06 22.55.12

BR1005I BRSPACE completed successfully

2. Drop index

>sqlplus “/as sysdba”

SQL>drop index “SAPR3X”.”SOFFCONT1~0″;

Index dropped

3. Import table SOFFCONT1

>brspace -f tbimport -t soffcont1

BR0280I BRSPACE time stamp: 2009-06-06 23.00.00

BR1009I Name of database instance: R3X

BR1010I BRSPACE action ID: seasywiu

BR1011I BRSPACE function ID: tbi

BR1012I BRSPACE function: tbimport

BR0280I BRSPACE time stamp: 2009-06-06 23.00.00

BR0656I Choice menu 361 - please make a selection

Import tables main menu

1 = Import tables

2 - Show BRSPACE export runs

3 * Exit program

4 - Reset program status

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.00.14

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0699I Reading log file I:\Oracle\R3X\sapreorg\spaceR3X.log ...

BR0280I BRSPACE time stamp: 2009-06-06 23.00.15

BR0658I List menu 362 - please select one entry

List of BRSPACE exports for import

Pos.  Run           Date                   Tables  Dumps       Size[KB]  Util.

1 = seasymoz.tbe  2009-06-06 21.09.57        1      2       25519234   EXP

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

BR0662I Enter your selection:

BR0280I BRSPACE time stamp: 2009-06-06 23.01.22

BR0663I Your selection: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.01.22

BR0657I Input menu 363 - please enter/check input values

Main options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.edd\expd        at.dmp,... (2 dumps)

1 * Import utility (utility) ............... [IMP]

2 - Import type (type) ..................... [full]

3 # Owner for import (owner) ............... []

4 # Tables for import (tables) ............. [SOFFCONT1]

5 - Import table rows (rows) ............... [yes]

6 - Import table indexes (indexes) ......... [yes]

7 - Import table constraints (constraints) . [yes]

8 - Import table grants (grants) ........... [yes]

9 # Import table triggers (triggers) ....... [yes]

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.02.42

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.02.42

BR0657I Input menu 364 - please enter/check input values

Additional options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.ed        d\expdat.dmp,... (2 dumps)

1 - Import buffer size in KB (buffer) ....... [10240]

2 - Commit after each array insert (commit) . [yes]

3 - Ignore creation errors (ignore) ......... [yes]

4 # Table exists action (action) ............ [skip]

5 # Parallel degree (parallel) .............. [1]

6 # Max. size of dump file in MB (filesize) . [20000]

7 - Force table import (force) .............. [no]

8 - IMP/IMPDP command (command) ............. [E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp]

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.03.06

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.03.07

BR0370I Directory I:\Oracle\R3X\sapreorg\seasywiu created

BR1172I Import of tables will be started with command line: E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp

BR0280I BRSPACE time stamp: 2009-06-06 23.03.07

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

BR0280I BRSPACE time stamp: 2009-06-06 23.03.34

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0278I Command output of 'E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp':

Import: Release 10.2.0.2.0 - Production on Sat Jun 6 23:03:35 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path

import done in WE8DEC character set and UTF8 NCHAR character set

. importing SYS's objects into SYS

. importing SAPR3X's objects into SAPR3X

. . importing table                    "SOFFCONT1"     962456 rows imported

Import terminated successfully without warnings.

BR0280I BRSPACE time stamp: 2009-06-07 00.43.12

BR1166I 1 table imported by IMP utility

BR0280I BRSPACE time stamp: 2009-06-07 00.43.14

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

BR0280I BRSPACE time stamp: 2009-06-07 00.46.19

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-07 00.46.19

BR0656I Choice menu 361 - please make a selection

Import tables main menu

1 * Import tables

2 - Show BRSPACE export runs

3 = Exit program

4 - Reset program status

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

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-07 00.46.25

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-07 00.46.25

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

BR0280I BRSPACE time stamp: 2009-06-07 00.46.33

BR0257I Your reply: 'y'

BR0280I BRSPACE time stamp: 2009-06-07 00.46.33

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbimport' completed

BR1008I End of BRSPACE processing: seasywiu.tbi 2009-06-07 00.46.33

BR0280I BRSPACE time stamp: 2009-06-07 00.46.34

BR1005I BRSPACE completed successfully

4. Run Update Statistic Optimizer in SAP

Execute or Schedule this via DB13 then it will create a new index for table SOFFCONT1.

Note: Tables SOFFCONT1 need to be reorganized offline as they contain LONG RAW fields and must not be converted to LOBS with the online reorganization.