Home » RDBMS Server » Server Administration » Switching between Local and Automatic Segment Management (Oracle 10.2.0.3, Windows Server 2003)
Switching between Local and Automatic Segment Management [message #280739] Wed, 14 November 2007 11:11 Go to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hi folks,

What is the best way to convert from local to automatic segment management?

I know searching that you cannot simply switch between the two management types.

My thoughts are to create a new tablespace that is automatically managed and do an export/import (or something else?) from the locally managed tablespace and then offline the locally managed tablespace and online the automatic one.

I'm not sure of the advantages/disadvantages of this plan or if there is a better way to do it.

Any advice is appreciated.

Thanks!
Re: Switching between Local and Automatic Segment Management [message #280747 is a reply to message #280739] Wed, 14 November 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

What is the best way to convert from local to automatic segment management?

I think you meant manual to automatic. You can't, you have to recreate the tablespace.

Or do you talk about dictionary and locally managed tablespace?
In this case, the best is to create a new tablespace.

Regards
Michel
Re: Switching between Local and Automatic Segment Management [message #280749 is a reply to message #280747] Wed, 14 November 2007 11:40 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
You are correct,

I mean manual segment management in all these cases.

Sorry about that.

Looks like it's going to be tablespace recreation! Uh Oh

Thanks!
Re: Switching between Local and Automatic Segment Management [message #280757 is a reply to message #280747] Wed, 14 November 2007 12:35 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Is there a single command to move the entire tablespace to another tablespace?

The options that I am considering now are:

* ALTER TABLE MOVE of every table in the tablespace
-This command will require rebuilding of indexes
-I'm not certain how triggers will be affected
* export/import of every table in the tablespace
-I'm not sure of the implications here.

Of course, indexes, triggers, packages/procedures/functions/etc. will all have to be moved. This is a pretty onerous task to do per object.

Any ideas?

Thanks!
Re: Switching between Local and Automatic Segment Management [message #280759 is a reply to message #280757] Wed, 14 November 2007 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to move tables, indexes, lobs, partitions.
Procedures, triggers... are in SYSTEM tablespace.
There is no command to move the whole tablepace content.

Regards
Michel
Re: Switching between Local and Automatic Segment Management [message #280761 is a reply to message #280739] Wed, 14 November 2007 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Of course, indexes, triggers, packages/procedures/functions/etc. will all have to be moved.
NO! triggers/packages/procedures/functions remain in SYSTEM tablespace

>This is a pretty onerous task to do per object.
>Any ideas?
Write SQL to write SQL that can then be executed.
Re: Switching between Local and Automatic Segment Management [message #280763 is a reply to message #280761] Wed, 14 November 2007 12:59 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Using OEM.
http://www.orafaq.com/forum/m/255149/105911/?srch=segment+space#msg_255149
Re: Switching between Local and Automatic Segment Management [message #280804 is a reply to message #280763] Wed, 14 November 2007 15:24 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Now that's a heck of a thing... I'll have to give this a try.

Thanks!
Re: Switching between Local and Automatic Segment Management [message #281874 is a reply to message #280739] Mon, 19 November 2007 15:47 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hey all,

I could use a little assistance with this switch.

10g creates a script to convert my tablespace to auto segment management for me, but this script has errors in it Question

The specific problem is that the script is attempting to revoke update on columns (ORA-01750). From what I've read this is not permitted.

However, since it's Oracle itself that is trying this I suspect that it is allowed and I'm just missing some configuration option.

Is there a minimum COMPATIBLE setting for this (eg. 10.2.0.0) to operate? I haven't been able to find anything stating this.

Thanks!
Re: Switching between Local and Automatic Segment Management [message #281923 is a reply to message #281874] Tue, 20 November 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't have your script.

Regards
Michel
Re: Switching between Local and Automatic Segment Management [message #282069 is a reply to message #281923] Tue, 20 November 2007 14:45 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
The script is quite large so I will just provide the relevant parts:

DBMS_SQL.PARSE (grant_cur, '' REVOKE UPDATE ON "OPS$AEM"."RS3_STRUCTURE_QUERY" FROM "AEM_USER"'', DBMS_SQL.NATIVE);
...
DBMS_SQL.PARSE (grant_cur, '' REVOKE UPDATE ON "OPS$AEM"."RS3_STRUCTURE_QUERY" FROM "AEM_USER"'', DBMS_SQL.NATIVE);


For anyone else who runs into this problem I just edited the script to revoke updates on the entire table - the answer to my earlier question about whether it's a compatibility issue seems to be "no".

Editing the script should be OK because with a tablespace reorganization the script puts all the existing data into a new tablespace and then back into the old one. Thus, all the revoked permissions will be re-added.

At least, so is my understanding.
Re: Switching between Local and Automatic Segment Management [message #282086 is a reply to message #282069] Tue, 20 November 2007 16:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
I had the similar problem but when OEM generate the script copy that script and run it from SQLPLUS.
Re: Switching between Local and Automatic Segment Management [message #282371 is a reply to message #282086] Wed, 21 November 2007 13:21 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Running the script in SQL*Plus will also create a log file of what the script is doing.

This is explicitly added by the script creation process in EM but isn't actually applied (or I can't find the file) when you execute in EM.
Re: Switching between Local and Automatic Segment Management [message #287725 is a reply to message #282069] Thu, 13 December 2007 04:55 Go to previous message
hexmanju
Messages: 71
Registered: September 2005
Location: MUMBAI
Member
Hi

Here in this Statement that you have written to parse

DBMS_SQL.PARSE('REVOKE ...................'),say i use cursor and make it

DBMS_SQL.PARSE(CUR,''REVOKE ...................'),Is it necessary to again execute the Cursor as i belive it will execute for DDL and DCL at parse time itself.


Thanks
MM
Previous Topic: Dropping redolog group
Next Topic: Cannt drop tablespace (please, urgent)
Goto Forum:
  


Current Time: Thu Dec 08 22:31:54 CST 2016

Total time taken to generate the page: 0.06264 seconds