Home » RDBMS Server » Enterprise Manager » To change Segment_Space_Management of a tablespace to AUTO.
To change Segment_Space_Management of a tablespace to AUTO. [message #292492] Wed, 09 January 2008 01:19 Go to next message
sudhir_sahu
Messages: 10
Registered: September 2007
Junior Member
I am using Oracle 10g. I want to change Segment_Space_Management of a tablespace to AUTO.
Can somebody tell me that how can I change it to AUTO FROM MANUAL.
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292500 is a reply to message #292492] Wed, 09 January 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, you have to recreate the tablespace.

Regards
Michel
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292712 is a reply to message #292500] Wed, 09 January 2008 13:18 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You can use OEM for this.
http://i264.photobucket.com/albums/ii182/DreamzZ_2007/em1.jpg
http://i264.photobucket.com/albums/ii182/DreamzZ_2007/em2.jpg
http://i264.photobucket.com/albums/ii182/DreamzZ_2007/em3.jpg
http://i264.photobucket.com/albums/ii182/DreamzZ_2007/em4.jpg
Connect to dbcolsole and navigate to ADMINISTRATION tab and then Select
TABLESPACES.

[Updated on: Wed, 09 January 2008 14:56]

Report message to a moderator

Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292723 is a reply to message #292712] Wed, 09 January 2008 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the script generated?

Regards
Michel
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292732 is a reply to message #292723] Wed, 09 January 2008 14:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
--   
--   Script generated at:	09-JAN-2008   20:49
CREATE  SMALLFILE  TABLESPACE "ALRM_ALARM_01_TS_REORG0"
  DATAFILE '/u106/oradata/alrm/ALRM_ALARM_01_TS_reorg0.DBF' SIZE 800M REUSE
  LOGGING  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO 
ALTER SESSION SET sort_area_size = 49999872 sort_area_retained_size = 49999872 
ALTER TABLE "ORACLE"."ALARM" MOVE SUBPARTITION "SYS_SUBP109" TABLESPACE "ALRM_ALARM_01_TS_REORG0" 
ALTER TABLE "ORACLE"."ALARM" MOVE SUBPARTITION "SYS_SUBP119" TABLESPACE "ALRM_ALARM_01_TS_REORG0"  
[Edit MC: remove bunch of alter table and alter index]
ALTER INDEX "ORACLE"."ALARM_TICKET_ID_IDX" REBUILD SUBPARTITION "SYS_SUBP97" TABLESPACE "ALRM_ALARM_IDX_03_TS" 
ALTER INDEX "ORACLE"."ALARM_TICKET_ID_IDX" REBUILD SUBPARTITION "SYS_SUBP99" TABLESPACE "ALRM_ALARM_IDX_04_TS" 
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"ORACLE"', '"ALARM"', estimate_percent=>NULL); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"ORACLE"', '"ALARM"', estimate_percent=>NULL); END;
{Edit MC: remove a bunch of dbms_stats....]
BEGIN DBMS_STATS.GATHER_INDEX_STATS('"ORACLE"', '"ALARM_TICKET_ID_IDX"', estimate_percent=>NULL); END;
BEGIN DBMS_STATS.GATHER_INDEX_STATS('"ORACLE"', '"ALARM_TICKET_ID_IDX"', estimate_percent=>NULL); END;
mgmt$reorg_dropTbsp('"ALRM_ALARM_01_TS"')
ALTER TABLESPACE "ALRM_ALARM_01_TS_REORG0" RENAME TO "ALRM_ALARM_01_TS"

[Updated on: Wed, 09 January 2008 15:42] by Moderator

Report message to a moderator

Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292750 is a reply to message #292732] Wed, 09 January 2008 15:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good! This confirm what I said: you have to recreate the tablespace.

Regards
Michel
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292770 is a reply to message #292750] Wed, 09 January 2008 16:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Yes Michel you are right.But if you use Em you get rid of all other additional steps.
Like moving tables and indexes ..blah blah.
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292807 is a reply to message #292770] Wed, 09 January 2008 22:33 Go to previous messageGo to next message
sudhir_sahu
Messages: 10
Registered: September 2007
Junior Member
Thnaks every one.
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #292822 is a reply to message #292770] Wed, 09 January 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes DreamzZ I didn't want to dispute it is easier in EM, it is indeed.
I just wanted to know if there is another way.
In addition, knowing the script and all the steps it executes prevent thinking it is a simple task, just a click.

Regards
Michel
Re: To change Segment_Space_Management of a tablespace to AUTO. [message #293024 is a reply to message #292822] Thu, 10 January 2008 11:28 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Michel I'm agree.If we have such a option we should use in order to safe time ,but if we don't have any...then...YOYO (manually) Wink
Previous Topic: Problem in Data control tool of 10g
Next Topic: "Agent Unreachable" after first system reboot
Goto Forum:
  


Current Time: Fri Mar 29 06:03:09 CDT 2024