Skip to main content

Moving Datafile From One Location To Another Location in Oracle 10G db.



The below are the steps. I have followed.

The database is in no archive log mode.

E:\Documents and Settings\KRISHNA>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 29 22:55:36 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
RISHI

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF

Here I will be changing USERS01.DBF file location.

SQL> select open_mode,log_mode from v$database;

OPEN_MODE  LOG_MODE
---------- ------------
READ WRITE NOARCHIVELOG

SQL> select name from v$database;

NAME
---------
RISHI

SQL> shut immediate;

 Database closed.
Database dismounted.
ORACLE instance shut down.

NOTE: Once database shuts down, copy the data file USERS01.DBF from old location to new location & open the database in MOUNT stage & Issue the ALTER RENAME like as below.

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
Database mounted.


SQL> alter database rename file  'E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\USERS01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\USE
RS01.DBF';

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE


SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\USERS01.DBF  - Here we can location change.
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF

SQL> exit


Here we are done :)

Thank You !

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

Process m000 died, see its trace file

Alert Log: [oracle@ bdump]$ view alert_HDRTG12.log Thu May 28 22:05:42 PDT 2015 Process P021 died, see its trace file Thu May 28 22:05:47 PDT 2015 Process m000 died, see its trace file Thu May 28 22:05:47 PDT 2015 ksvcreate: Process(m000) creation failed Thu May 28 22:06:00 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:10 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:43 PDT 2015 [oracle@bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 28 23:38:59 2015 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------------------------- HDRTG12 SQL> select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions'); RESOURCE...
Post a Comment
Read more