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
- C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TEMP_TS.DBF
- C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TS.DBF
- SQL > shutdown immediate;
Ex : For new location of Data files.
- C:\DCD_DB\CDS_TEMP_TS.DBF
- C:\DCD_DB\CDS_TS.DBF
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
Database mounted.
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
\ORCL\BDUMP
core_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\CDUMP
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP
SQL>
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\bdump
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?"
Popular Posts
-
The best solution to know about these init levels is to understand the " man init " command output on Unix. There are basically 8...
-
How to Unlock BSNL 3G data card to use it with Airtel and Vodafone Model no : LW272 ? How to unlock BSNL 3G data card( Model no : LW272 )us...
-
How to transfer bike registration from one State to other (Karnataka to Andhra)?? Most of us having two wheelers purchased and registered in...
-
ఓం శ్రీ స్వామియే శరణం ఆయ్యప్ప!! Related posts : Trip to Sabarimala - Part 1 Trip to Sabarimala - Part 2 Ayappa Deeksha required things...
-
Following are some of interesting blogs I found till now ...please comment to add your blog here. Blogs in English : http://nitawriter.word...
Popular posts
- 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?
- init 0, init 1, init 2 ..
- Java-J2EE interview preparation
- mCheck Application jar or jad download
- My SQL FAQs
- My Travelogues
- Old is blod - New is italic
- Online pay methids for credit cards
- Oracle FAQs
- Pilgrimages
- Smoking in Indian Women
- Technology Vs Humans
- Twitter feeds for all Telugu stars on single page.
- Unix best practices
- Unix FAQs
Post a Comment
Whoever writes Inappropriate/Vulgar comments to context, generally want to be anonymous …So I hope U r not the one like that?
For lazy logs, u can at least use Name/URL option which doesn’t even require any sign-in, The good thing is that it can accept your lovely nick name also and the URL is not mandatory too.
Thanks for your patience
~Krishna(I love "Transparency")