Tablespace reorganization

[Former Member]

Hello to all,

i have a question. We have a Oracle 9.2.0.7 DB with 1,1TB. Now we plann to reorganize the DB with the BRTOOLS. The Tablespaces are LMTS.

The steps are:

1. Create a new TS

brspace -f tscreate -t <new_tsp> -s <size> -a yes -m <max_size>

-i <incr_size> -l {all|<old_tsp>}

2. Reorganization of TS without LONG-fields

brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"

-n <new_tsp> [-p <par_degree>]

3. Stop SAP

4. Create DDL for Tables with LONG-fields

brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"

-n <new_tsp> -d only_tab

brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"

-n <new_tsp> -d only_ind

brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"

-n <new_tsp> -d only_dep

5. Export Data for table with LONG-fields

brspace -f tbexport -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"

-r yes -i no -c no -g no -e no

6. Delete (old) Tablespace

brspace -f tsdrop -t <tsp_name> -f

7. Create tables with LONG-fields in new TS

SQL> connect / as sysdba

SQL> @<SAPDATA_HOME>/sapreorg/<work_dir1>/ddl.sql

8. Import data from tables with LONG-fields

brspace -f tbimport -y full -r yes -i no -c no -g no -n yes

9. Create indices and dependent objects

SQL> connect / as sysdba

SQL> @<SAPDATA_HOME>/sapreorg/<work_dir2>/ddl.sql

SQL> @<SAPDATA_HOME>/sapreorg/<work_dir3>/ddl.sql

10. Start SAP

11. Create new statistics for reorganized tables

brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4

Here my question:

I have created a new TS ,

brspace -c force -f tscreate -a yes -c data -d both -f /oracle/T12/sapdata1/ddicd_1_/ddicd_1_.data1 -i 30 -m 3072 -o SAPR3 -p auto -s 1800 -t psapddicd_

and after the steps i have explained above i have a TS psapddicd_ which contains the data and indices. Can evereyone tell me what are the advantages of an TS which contain both (data and indices) ? Or what is recommended ? I think it will be better when you are separate the indexes, but this is only my opinion.

Thanks for HELP

Best regards

Carsten

[Former Member]

I also agree with Markus - the fragmentation issues that you think might be slowing down your SQL may in fact be corrected by looking into other areas - like indexes perhaps. They might be in need of a rebuild or you might need additional indexes to speed up the access to the data. Otherwise if you just want to recapture some wasted disk space within your tablespaces then I would look for objects with lots of free blocks below the High Water Mark and then try DBMS_REDEFINITION to clean up those objects that truly need to be rebuilt. Try it out in a test environment first - once you have the hang of it - it's pretty straight forward. Oh - and by the way it's an online operation. you just need to do it during non peak hours.

[former_member204746]

Markus is right. I would like to add:

1. when finished, you may need to adapt tables TAORA, IAORA and TSORA.

2. you cannot rename a tablespace with Oarcle 9i, so, choose a better tablespsce name and refer to Markus< note about the new tablespace layout and do not use PSAPDDICD_ as tablespace name.

3. prepare for LONG downtimes for tables with LONG filed types.

4. do not use too high parrallel degrees when reorging online, it will really slowdown your system

5. reorging online will create a lot of offline redologs, just prepare yourself for this.

good luck!

[Former Member]

Hello Eric,

why shell I use another Tablespace name as PSAPDDICD_ ?

Greetings

[former_member204746]

it does not respect SAP tablespace name defaults.

old tablespace layout (mus end with D or I):

PSAPxxxxxD or PSAPxxxxxI

new tablespace layout:

read Markus' SAP note he supplied.

[markus_doehr2]

Can evereyone tell me what are the advantages of an

TS which contain both (data and indices) ? Or what

is recommended ? I think it will be better when you

are separate the indexes, but this is only my

opinion.

Check Note 355771 - Oracle: Explanation of the new tablespace layout:

However, the distribution of I/O is generally no longer an issue, thanks to advances in disk technology and storage systems. If you, for example, use a large EMC storage as back-end storage (with the latest HA technology), you can no longer allocate data to separate tablespaces and therefore to separate physical disks to improve performance by distributing the I/O, because this storage system does not allow management and activation of individual disks. If you are using this technology, there is therefore no need to allocate tables (data) and indexes (index data) to separate tablespaces.

IMHO - if you have ONE SAN, where everything resides (both table and index) it won´t make a difference because the data will be read from the same SAN.

--

Markus