Oracle how to move and rename datafiles

First of all, you don't have to restart the database. Renaming data files is very common due to the following reasons. 

Maybe you don't like the name of data file and want to keep a naming method, or the most important, you want to move the datafile to a new, faster filesystem with more empty space. Moving datafiles is something that you need when you will start searching how I/O is spread on you filesystems! 

How to 
For our example. Let's say that the name of the tablespace is TBS1 and the datafile is '/filesystem01/tbs01.dbf' and you want to move it to '/filesystem02/tbs01.dbf' 

1. First take the tablespace offline

ALTER TABLESPACE TBS1 OFFLINE;

If someone is using the tablespace, then you can't take it offline! You must kill all sessions using it. The time needed to take it offline, depends on the size of tablespace, a 10Mbytes tablespace may take 1 sec, a 1TByte tablespace may take some minutes… so be patient. 

2. Move the datafile to the new filesystem with the os command.

$>mv /filesystem01/tbs01.dbf /filesystem02/tbs01.dbf

3. Do the renaming to update the dictionary and controlfile

ALTER TABLESPACE TBS1 RENAME DATAFILE '/filesystem01/tbs01.dbf' 

TO '/filesystem02/tbs01.dbf';

4. Take back tablespace online.

ALTER TABLESPACE TBS1 ONLINE;

Oracle How to calculate export dump size

Sometimes you have to export all the database or many schemas but you don't have all the necessary space needed, or you don't know exactly how much space the dmp file it will take. 

Oracle with exports (or expdp) stores at the dmp file the table data. Indexes are just "Create index statements" which will be created after loading the table data. So the key factor of how big will be your dmp file, depends on how much table data you have. 

The following query calculates how much table data each schema takes on your database

SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,

ROUND(bytes/(1024*1024),2) SIZE_MB,

tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',

 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC;

OWNER                              MBYTES
------------------------------ ----------
TARGET_DW                         3774208
TARGET_POC                         673192
STAGE_DW                           469263
PRESTAGE_DW                        389326
SHADOW_DW                          257233
PRESENT_PERIF                      148063
SNAP                               141565
PKIOUSIS                           117535
DM_SPSS                             44760
MONITOR_DW                          35336
CUSTOMER_VIEW                       29807

for example if you export the SHADOW_DW schema, it will create a dmp file approximately 257233MB->250GBytes. 

Tip: 
Bear in mind that the actual dump file will have smaller size than 250GB!!!, because in most cases the TABLE DATA are fragmented!. Export-Datapump, removes fragmentation!. 

Indexes, packages, procedures, views, etc, are not taking too much space in the dmp file, are just DDL statements.

SELECT 'expdp SYSTEM/oracle DUMPFILE=' || owner || '_%U.dmp

 DIRECTORY=exp_dir PARALLEL=10 LOGFILE='

 || owner ||'.log schemas= ' || owner SQL
FROM (
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,

 ROUND(bytes/(1024*1024),2) SIZE_MB,

 tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',

 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
AND OWNER <> 'SYSTEM' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC);

SAP Moving Oracle data files.

How to move data files in Oracle database?

When your system works using the ORACLE database as the repository, you might want to transfer one or more file dates from one file system to another. This could be done in the following way:

1. Create a backup copy of the database. For example, SAP tools (Brbackup, DB13).
2. Stop the SAP application server, the ORACLE database.
3. Migrate the date-files at the OS level from the source file system to the target. If you have a Unix-like system, be careful with your rights/permissions on the data files.
4. Run Sqlplus, connect to the DBMS, and open the ORACLE database in the Mount mode:
   # Sqlplus /nolog
   SQL > connect /as SYSDBA
   SQL > startup Mount
5. Runs the following command in SQLPlus for each migrated file date:
   SQL > ALTER DATABASE Rename file ' full source path to date-file ' to ' full target path to date-file ';
6. Close the database and open in normal mode:
   SQL > shutdown
   SQL > startup Open
7. Run the SAP application server.

Then, if it's normal, and in DB02 you've checked that the database refers to the date files that lie on the new file system, you can delete the data files from the original OS-level file system. Try to make a complete backup of the database in the near future, especially if you have deleted the entire old file system.

In the same way, you can rename a datafile, for example, if you create it in a file name or directory name.