How to change the location of Datable used by a Table space in Oracle?Process to change the location of TABLE SPACE file in oracle :
Note : The below steps are very sensitive; not run by normal users. It is recommended to run with the help of Oracle DBA prior taking backups. If any problem happens the old data might be lost.
1. Confirm and note down the existing Data file location for both CDS_TEMP_TS and CDS_TS by running the SQL commands.
Ex : current Data file locations are
- SQL > shutdown immediate;
Ex : For new location of Data files.
slplus sys as sysdba; Provide password and connect to idle instance.
5.start the Oracle and mount it with the below command
- SQL> startup mount;ORACLE instance started.Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
6. Now run the below commands at SQL> to associate the new data file location with database.
- alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TEMP_TS.DBF' to 'C:\DCD_DB\CDS_TEMP_TS.DBF';
- alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TS.DBF' to 'C:\DCD_DB\CDS_TS.DBF';
- alter database open;
- sqlplus "sys/oracle@orcl as sysdba"
- SQL> select tablespace_name,file_name from dba_temp_files where tablespace_name LIKE 'CDS%';
- SQL> select tablespace_name,file_name from dba_data_files where tablespace_name LIKE 'CDS%';
Where are Oracle logs Reflecting this ?
SQL> show parameter dump;
NAME TYPE VALUE
background_core_dump string partial
background_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
core_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
check the " alert_orcl.log " which shows the commands and details used to create CDS_TEMP_TS and CDS_TS.
Reference : http://www.orafaq.com/wiki/Move_datafile_to_different_location
0 comments to "How to change the location of Data file used by a Table space in Oracle?"
Powered by WidgetsForFree
- ► 2013 (19)
- ▼ 2011 (18)
- ► 2010 (35)
- ► 2009 (73)
My Blog List
- Airtel and vodafone GPRS settings for pocket PC phones
- Andhra 2 America
- Ayyappa Deeksha required things
- Blogs I watch !
- Captions for your bike
- DB2 FAQs
- Deepavali Vs The Goddes of sleep
- ETV - Dhee D2 D3
- Evolution of smoking in India Women
- How to make credit card payments?
- Java-J2EE interview preparation
- My SQL FAQs
- My Travelogues
- Old is blod - New is italic
- Online pay methids for credit cards
- Oracle FAQs
- Smoking in Indian Women
- Technology Vs Humans
- Twitter feeds for all Telugu stars on single page.
- Unix FAQs
- Unix best practices
- init 0, init 1, init 2 ..
- mCheck Application jar or jad download