Oracle.. How to move data file on different location (different ASM Diskgroup)!!!
primary:sys@vihcdbd8-vihcdbd8 sqlplus> def DEFINE _DATE = "06-01-22 09:46" (CHAR) DEFINE _CONNECT_IDENTIFIER = "vihcdbd8" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) <<<< on 12c !!! on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production !!! not possible in SE (Oracle Database Standard Edition) CREATE TABLESPACE dummy DATAFILE '+DATA' SIZE 1M AUTOEXTEND ON NEXT 1M; select FILE_NAME from dba_data_files; FILE_NAME -------------------------------------------------------------------------------------------------- +DATA/VIHCDBD8/DATAFILE/system.1368.1085481593 +DATA/VIHCDBD8/DATAFILE/sysaux.1355.1085481587 +DATA/VIHCDBD8/DATAFILE/undotbs1.1360.1085481603 +DATA/VIHCDBD8/DATAFILE/users.1416.1085481605 +DATA/VIHCDBD8/DATAFILE/undotbs2.1359.1085481603 +DATA/VIHCDBD8/DATAFILE/dummy.1288.1093254157 <<<<<< 6 rows selected. ALTER DATABASE MOVE DATAFILE '+DATA/VIHCDBD8/DATAFILE/dummy.1288.1093254157' TO '+LOGS'; Database altered. select FILE_NAME from dba_data_files; FILE_NAME -------------------------------------------------------------------------------------------- +DATA/VIHCDBD8/DATAFILE/system.1368.1085481593 +DATA/VIHCDBD8/DATAFILE/sysaux.1355.1085481587 +DATA/VIHCDBD8/DATAFILE/undotbs1.1360.1085481603 +DATA/VIHCDBD8/DATAFILE/users.1416.1085481605 +DATA/VIHCDBD8/DATAFILE/undotbs2.1359.1085481603 +LOGS/VIHCDBD8/DATAFILE/dummy.1437.1093254285 <<<<<<<<< Now in +LOGS 6 rows selected. alter session set nls_date_format='dd-mon-rr hh24:mi'; undefine tablespace_name set pages 1500 lines 400 term off col fn new_value fname select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES; col file_name for a60 col AUTOEXTENSIBLE for a18 col BIGFILE for a15 col ENCRYPTED for a10 col TABLESPACE_NAME for a20 --- col COMPRESS_FOR for a10 col file_name for &fname heading "File Name" select file_id, b.block_size, a.tablespace_name,a.file_name, trunc(a.bytes/(1024*1024*1024),2) size_gb, trunc(a.maxbytes/(1024*1024*1024),2) max_gb , a.autoextensible ,bigfile --,encrypted,compress_for ,contents,a.status,creation_time from dba_data_files a ,dba_tablespaces b ,v$datafile v where 1=1 and a.tablespace_name=b.tablespace_name and a.tablespace_name in ('DUMMY') -- and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name')) and a.file_id=v.file# --and file_id=31 --order by 10 desc order by 1 ; FILE_ID BLOCK_SIZE TABLESPACE_NAME File Name SIZE_GB MAX_GB AUTOEXTENSIBLE BIGFILE CONTENTS STATUS CREATION_TIME ---------- ---------- ------------------------------ ------------------------------------------------- ---------- ---------- ------------------ --------------- --------------------- --------- --------------- 70 8192 DUMMY +LOGS/VIHCDBD8/DATAFILE/dummy.1437.1093254285 0 31.99 YES NO PERMANENT AVAILABLE 06-jan-22 09:42 DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.
Search This Blog
Total Pageviews
Thursday, 6 January 2022
Oracle How to move data file on different location (on ASM Diskgroup)!!!
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
No comments:
Post a Comment