Skip to Main Content
  • Questions

  • about converting tablespace into ASSM in oracle9i

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Amey.

Asked: February 26, 2006 - 11:22 am UTC

Last updated: August 17, 2012 - 12:26 pm UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

hi Tom,
We just upgrade our database from 8.1.7.4 to 9.2.0.6
In 8i our database was LMT but not ASSM. Now we like to use ASSM in 9i.
Problem is that database is 1.8 TB. Due to which we can't export database and import database by creating assm tablespaces.
Is there any way we can convert one tablespace from LMT to LMT assm ?
Can you please provide me directions how to write that script ? Or if you have any script can you provide to me.

Thanks a lot
Amey

and Tom said...

You would have to create a new tablespace and then MOVE things into it.

As you move things from old tablespaces into the new (I would never suggest export/import) you can drop the old tablespaces - releasing the space on disk they occupy.

There is no way to "convert" - the data must be migrated if you want to do this.


But - I would ask myself - do I need to? Am I a highly concurrent database that hasn't set up multiple freelists and or freelist groups already? (ASSM wastes space in order to increase concurrency - it removes the need for you to determine pctused, freelist and freelist groups - but if you already have those configured properly....)


Also - why someone would upgrade from the unsupported to the "going to be unsupported way before 10gr2" versions.... ugh.


So, I'm not sure I would move to ASSM for the sake of moving to ASSM. You can consider it for tablespaces in the future, but for now - I'd likely stay exactly where you are.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 26, 2006 - 6:19 pm UTC

Tom,

Most of the times not opting for latest release is just because vendor application hasn't been tested ugggh...

Rahul.

about assm

amey, February 27, 2006 - 9:01 am UTC

totally agree

Performance Improvement

keyur, June 20, 2006 - 5:29 pm UTC

Hello Tom:

What id I would have buffer busy wait in my database and instead of spending time on tunning that part. If I will move from ASSM, that will eliminate the problem. Do you thing that would help me to improve the performance?

Thank you
~Keyur

Tom Kyte
June 21, 2006 - 4:28 pm UTC

if the buffer busy waits for due to new space allocation (inserts trying to find a block to insert into), maybe.

if the buffer busy waits are due to one of the other 10 or so causes - maybe not (eg: buffer busy wait could be that I am waiting for you to complete a physical IO, using ASSM would not "fix" that)

10g ASSM New Tablesoaces

Steve, May 12, 2007 - 12:57 pm UTC

Tom - quick question?

If you are migrating to 10g; is the recommendation to use ASSM for tables that have high level of concurrent inserts? I'm a little confused looking at ASSM; from what I'm reading I still need to set INITRANS in situations where I have multiple processes inserting? Is that correct or can I simply forget about INITRANS?
Tom Kyte
May 14, 2007 - 1:05 pm UTC

ASSM is about not having to set up freelist and freelist groups, it would not affect your decisions on setting initrans - you would use the same thought processes you did in the past for that.

assm for indexes tablespace

A reader, June 20, 2012 - 8:22 am UTC

hi Tom,

Is a good idea that assm for indexes with oltp application?

i have significant buffer busy waits in tablespace xxx_data but not in tablespace xx_indexes.

assm for xxx_indexes has impact on buffer busy waits ?


Thank You for help
Tom Kyte
June 21, 2012 - 7:38 am UTC

yes, assm is what you should be using.

assm for tablespace

aaa, August 07, 2012 - 8:25 am UTC

Hi Tom,

can i use assm for tablespace where are only tables?

i have tablespace for index with assm.
Tom Kyte
August 17, 2012 - 12:26 pm UTC

yes, you should use assm for tables.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.