Home
If would like 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.
If would like 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.
No comments:
Post a Comment