Oracle How to move data file on different location (on ASM Diskgroup)!!!

 
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.