Tablespace Restructuring in Oracle

[epost]

Anyone have experience restructuring the tablespaces in Oracle under JDE.
I'm going to move our development box to uniform extent tablespaces using
SAFE methodology. If your install is like ours you have tablespaces such as
proddtat, proddtai, prodctlt, prodctli...If you installed using default
storage parameters from JDE I assume you have some tables with too many
extents and also potential fragmentation...to find out just run "select *
from dba_segments where extents > 1024".

Anyway, is anyone aware of any potential problems with doing this? I'm
thinking of creating proddtat_big, proddtat_medium and proddtat_small (as
well as others) and placing objects accordingly should be fine but I'm
worried about any problems if we upgrade. Let me know if you have any
thoughts or ideas on this matter.

Thanks,
Ethan
www.freeocp.com

------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.

==============================================================================

 

[twaddell]

Ethan,

We too have noticed that the JDE default table storage parameters are not
optimal, and worry about future upgrades if we alter the tablespace structure.
To remedy the problem we have left the tablespaces the same, but reorganized
the tables on an individual basis. Here is the process we follow:

- Export a selected group of tables
- Drop the tables
- Coalesce the tablespace
- Edit the export file to change the table and index storage parameters.
You should also make a backup copy of the export file before you edit it.
- Import the tables back into Oracle with INDEXES=N option. This is to
speed up the import process.
- Import into the database with the IGNORE=Y, ROWS=N and INDEXES=Y option.
This should create the indexes for the tables.

This has worked for us in the past. Of course, the above steps should be
done while OneWorld is down and no one is accessing the database.
I hope this helps.

Michael T.
[email protected]
OneWorld 7.3.3.1
Oracle 8.1.5
Sun Solaris 2.7

epost wrote:
>
> Anyone have experience restructuring the tablespaces in Oracle under JDE.
> I'm going to move our development box to uniform extent tablespaces using
> SAFE methodology. If your install is like ours you have tablespaces such as
> proddtat, proddtai, prodctlt, prodctli...If you installed using default
> storage parameters from JDE I assume you have some tables with too many
> extents and also potential fragmentation...to find out just run "select *
> from dba_segments where extents > 1024".
>
> Anyway, is anyone aware of any potential problems with doing this? I'm
> thinking of creating proddtat_big, proddtat_medium and proddtat_small (as
> well as others) and placing objects accordingly should be fine but I'm
> worried about any problems if we upgrade. Let me know if you have any
> thoughts or ideas on this matter.
>
> Thanks,
> Ethan
> www.freeocp.com
>
> ------------------------------------------------------------------------------
> This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.
>
> ==============================================================================
>
> --------------------------
> To view this thread, visit the JDEList forum at:
> http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OW&Number=6296
> *************************************************************
> This is the JDEList One World / XE Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
> *************************************************************

 

[epost]

Thanks Michael...I suggest you take a look at
http://www.oreilly.com/catalog/oressentials/chapter/papers.html Download the
defrag.pdf paper. I think you will find that your process would not really
solve the problem of fragmentation and multiple extents. For example
everyones F0911 is growing at a huge rate most likely, but if you took the
default storage settings you might be creating 100's of extents per day on
this table. Even if you do an export with compress=Y then import which will
place the entire table in a single extent you have a few problems, (1)
downtime, (2) different extent sizes on a single tablespace which is really
what fragmentation is and (3) you are going to run into the problem again
soon enough.

The solution is to implement the SAFE method of object placement and uniform
extent sizes. You should see some dramatic performance improvments after
doing this, particulary with full table scans which JDE seems to find
endless ways of writing SQL that does this. I put together a presentation
on the topic of SAFE at www.freeocp.com which you can view to get an idea of
what it is about. Also I have talked to our CNC he feels very confident
that creating new tablespaces poses no hazard since everything is mapped to
an oracle user and tablespace is not specified anywhere. My only potential
concern is that during upgrade, a table might be dropped, then re-created
and re-loaded with data. This would occur on the default tablespace of the
JDE oracle user, so if one of the big tables was dropped during an upgrade
and then re-created it would effectivly move tablespaces if the default
tablespace is not one of the larger tablespaces. This should be pretty easy
to check for and I think the performance gain we will encounter is worth the
risk of some trouble during upgrade.

-Ethan


Ethan,

We too have noticed that the JDE default table storage parameters are not
optimal, and worry about future upgrades if we alter the tablespace
structure.
To remedy the problem we have left the tablespaces the same, but reorganized
the tables on an individual basis. Here is the process we follow:

- Export a selected group of tables
- Drop the tables
- Coalesce the tablespace
- Edit the export file to change the table and index storage parameters.
You should also make a backup copy of the export file before you edit
it.
- Import the tables back into Oracle with INDEXES=N option. This is to
speed up the import process.
- Import into the database with the IGNORE=Y, ROWS=N and INDEXES=Y option.
This should create the indexes for the tables.

This has worked for us in the past. Of course, the above steps should be
done while OneWorld is down and no one is accessing the database.
I hope this helps.

Michael T.
[email protected]
OneWorld 7.3.3.1
Oracle 8.1.5
Sun Solaris 2.7

------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.

==============================================================================

 

[jjeldres]

Hi list
My porttest not successful with security, but creating successful porttest
whit password oracle.
however , my Workstation connecting to OneWorld utilize passord OW.
?¿
I need up the logic server with security OW.

OW B7332/Oralce 8.1.6/solaris 7

Jerry Jeldres M.
[email protected]
56-2-6407783