expdp - nfs mount point

Destination Machine: -
1. Choose the folder to share, which is having full permissions: -
#chmod 777 /(fs)/(folder_name)

2. To sharing the folder, add an entry in /etc/exports file: -
ex: - /(fs)/(folder_name) * (rw) ---read write
/(fs)/(folder_name) * (ro) ---read only (to read the data)---this one won't match for our senario

3. Execute the below command to read the new entry in /etc/exports file: -
#exportfs -r

Source Machine: -

1. Add the below line in /etc/fstab: -
hostname:/shared/folder /mnt_point nfs proto=tcp,vers=3,rw,hard,intr,bg,rsize=65536,wsize=65536 0 2

2. Mounting the /mnt_point: -
#mount /mnt_point

3. Now connect to the Oracle user and follow the below steps: -

1. Change the DATA_PUMP_DIR path to the /mnt_point in Database: -
SQL)create or replace directory DATA_PUMP_DIR as '/mnt_point/';
2. Execute the below command to export the backup using the 'expdp': -
$expdp username/passwd directory=DATA_PUMP_DIR dumpfile=file_name.dmp logfile=log_file.log full=y

Now, you can see the dumpfile & logfile in the Destination machine (in the shared folder).

Oracle table to html file

$sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" username@Ora10g @script.sql>filename.html
ex: - where script.sql contains:
select col1, col2 from table_name;
exit

Oracle tables to Excel

set echo off pagesize 0 head off feed off veri off
set trimspool on feedback off
spool FILE_NAME.csv
select COL1 || ',' || COL2 || ',' || COL3 || ',' ||........ from TABLE_NAME;
spool off

If table data contain any 'comma' in middle , then follow this below steps: -

set echo off pagesize 0 head off feed off veri off
set trimspool on feedback off
spool FILE_NAME.txt
select COL1 || '~' || COL2 || '~' || COL3 || '~' ||........ from TABLE_NAME;
spool off

open the .txt file in MS-Excel. Selecting the separator option with '~'