OraFAQ Forum: Server Administration » Switching between Local and Automatic Segment Management

Home » RDBMS Server » Server Administration » Switching between Local and Automatic Segment Management (Oracle 10.2.0.3, Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Switching between Local and Automatic Segment Management [message #280739]

Wed, 14 November 2007 11:11

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!

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280747 is a reply to message #280739]

Wed, 14 November 2007 11:39

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280749 is a reply to message #280747]

Wed, 14 November 2007 11:40

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!

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280757 is a reply to message #280747]

Wed, 14 November 2007 12:35

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!

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280759 is a reply to message #280757]

Wed, 14 November 2007 12:41

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280761 is a reply to message #280739]

Wed, 14 November 2007 12:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280763 is a reply to message #280761]

Wed, 14 November 2007 12:59

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

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #280804 is a reply to message #280763]

Wed, 14 November 2007 15:24

clintonf
Messages: 82
Registered: May 2006

Member

Now that's a heck of a thing... I'll have to give this a try.

Thanks!

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #281874 is a reply to message #280739]

Mon, 19 November 2007 15:47

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!

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #281923 is a reply to message #281874]

Tue, 20 November 2007 00:55

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

We don't have your script.

Regards
Michel

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #282069 is a reply to message #281923]

Tue, 20 November 2007 14:45

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.

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #282086 is a reply to message #282069]

Tue, 20 November 2007 16:53

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.

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #282371 is a reply to message #282086]

Wed, 21 November 2007 13:21

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.

Report message to a moderator

Re: Switching between Local and Automatic Segment Management [message #287725 is a reply to message #282069]

Thu, 13 December 2007 04:55

hexmanju
Messages: 78
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

Report message to a moderator

Previous Topic:	Dropping redolog group
Next Topic:	Cannt drop tablespace (please, urgent)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 13:56:33 CDT 2024