OraFAQ Forum: Server Administration » Moving Data Between Datafiles / Drooping Datafile

Home » RDBMS Server » Server Administration » Moving Data Between Datafiles / Drooping Datafile

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

Moving Data Between Datafiles / Drooping Datafile [message #146334]

Wed, 09 November 2005 23:01

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

Hi

1. I want to move all data of a datafile to another datafile. (How to locate objects and how to move)?

2. How to drop a datafile cleanly?

Wishes
Jawad

Report message to a moderator

Re: Moving Data Between Datafiles / Drooping Datafile [message #146357 is a reply to message #146334]

Thu, 10 November 2005 00:42

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

--> How to locate objects and how to move?

you can identify the objects in that datafile by the following query

Quote:

SQL> select segment_name, file_name from
dba_extents ext, dba_data_files dbf
where ext.file_id =dbf.file_id
and dbf.file_id= your_file_id;

And now create another tablespace and move these objects to the new tablespace

Quote:

SQL> Alter object object_name move tablespace tablespace_name;

And the other way is to

export old tablespace and import the data into new tablespace.

regards,
tarun

Report message to a moderator

Re: Moving Data Between Datafiles / Drooping Datafile [message #146392 is a reply to message #146334]

Thu, 10 November 2005 02:53

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

I think doing that invalidates the indexes, views, PK's, and any functions/procedures/packages that are dependant to the moved object, if I remember correctly. No big thing if you are ready for it. There is a script in $ORACLE_HOME/rdbms/admin to recompile all invalid objects. I think it is named utlprp.sql and run as sysdba.

Report message to a moderator