Home » RDBMS Server » Server Administration » Tablespace
Tablespace [message #264521] Mon, 03 September 2007 11:24 Go to next message
dbasudar
Messages: 8
Registered: August 2007
Location: chennai
Junior Member

hi,
Actually my database has 5 tablespaces
1)System
2)Undo
3)temp
4)userdata
5)userdata1
in which system is dictionary and all others are local.How can i migrate a dictionary tablespace to local.

AS i donot have a nonsystem dictionary tablespace,i created a tablespace using "reate tablespace...."command with extent manamgent dictionary..How can i do the migration

Mad Thanks
Re: Tablespace [message #264525 is a reply to message #264521] Mon, 03 September 2007 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE is your friend, but only when you actually use it!
Results 1 - 10 of about 117,000 for oracle migrate a dictionary tablespace to local..
Re: Tablespace [message #264609 is a reply to message #264521] Mon, 03 September 2007 23:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
How can i migrate a dictionary tablespace to local.


EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');


But there are several restrictions before running this one.

like, you will perform this action while system in restricted mode.
There will be no rollback segment in dictionary tablespace.
you must have a default temp tablespace.



N.B: Before and after performing this operation take database backup.
Re: Tablespace [message #264611 is a reply to message #264521] Mon, 03 September 2007 23:09 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
.How can i do the migration of nonsystem tablespace


Simply running,

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME'):
Re: Tablespace [message #265591 is a reply to message #264611] Thu, 06 September 2007 12:20 Go to previous messageGo to next message
dbasudar
Messages: 8
Registered: August 2007
Location: chennai
Junior Member

hi arju,
i tried this and iam getting the error

SQL> exec dbms_space_admin.tablespace_migrate_to_local ('SYSTEM')
BEGIN dbms_space_admin.tablespace_migrate_to_local ('SYSTEM'); END;

*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMP not found in read only
mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

can u help me
Re: Tablespace [message #265598 is a reply to message #265591] Thu, 06 September 2007 12:45 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.oracledba.co.uk/tips/dbms_space_admin.htm
Re: Tablespace [message #265599 is a reply to message #265591] Thu, 06 September 2007 12:47 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello dbasudar,

1) You should read the forum guidelines. Every member here wants to read a clear and formated post; Also before posting threads you should search for a while... just one search may give you an instant solution. Believe... it is true! It is NOT a joke.

Even in already posted message by you... you have rights to go back to that initial message and format the message because further... others may need to read your post.

2) Regarding your question:
Let say you have N-tablespaces... ts1,ts2,ts3,tsN.
You need to:
a) exec dbms_space_admin.tablespace_migrate_to_local('ts1');
repeat from ts1... until tsN... EXCEPT to temp/undo/system/sysaux;

b) shutdown immediate; and... startup restrict;
c) alter tablespace ts1 read only;
repeat from ts1,ts2,... until tsN... except to temp/undo/sysaux
d) alter tablespace sysaux offline;
e) exec dbms_space_admin.tablespace_migrate_to_local('system');

That is all... but:
Bring back all the user's tablespace to read write, and sysaux to online. Sysaux is for 10g and on.

3) Final considerations:
Next time or even this time... try to consider that the person who is reading your post... does not know your environment. And be sure that actions/behavior in one version is different to another version. It is true.

Regards,

mson77
Re: Tablespace [message #265601 is a reply to message #265599] Thu, 06 September 2007 12:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Well Explained!!!!Mson!!!
Re: Tablespace [message #265606 is a reply to message #265601] Thu, 06 September 2007 12:58 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello DreamzZ,

YOU are responsible for it.
Deeply thankful to YOU.

Sincerely,

mson77
Re: Tablespace [message #265809 is a reply to message #265606] Fri, 07 September 2007 08:08 Go to previous messageGo to next message
dbasudar
Messages: 8
Registered: August 2007
Location: chennai
Junior Member

thank u all for ur replies and advices......As iam new to orafaq.com your advices will be more helpful for me........Thank u once again
Re: Tablespace [message #265820 is a reply to message #265809] Fri, 07 September 2007 08:40 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello dbasudar,

My question for you... if you do not mind:

1) What does "u" mean?
2) What does "ur" mean?

At this forum GUIDE... you would read at Posting guidelines/IM speak:
  • It is not appreciated:
  • It is hard to read.
  • It is unprofessional.
  • It doesn't show much respect towards your fellow forum members.


Next time you stop here... would you mind to consider in applying the rules of this forum written here?

Regards,

mson77
Previous Topic: rman error
Next Topic: Unable to login to SQLPLUS to start an Instance
Goto Forum:
  


Current Time: Tue Dec 06 02:22:50 CST 2016

Total time taken to generate the page: 0.11544 seconds