Query to find the EBS Instance Refresh/Clone Date

 select name, creation_date

from apps.FND_APPS_SYSTEM;

SELECT resetlogs_time

FROM   v$database; 

Query to find the Oracle EBS Application Version

 SELECT RELEASE_NAME FROM apps.FND_PRODUCT_GROUPS

Find a file under specific directories in UNIX

Use below command to find Filename under sub directories of $AP_TOP

find /$AP_TOP -name "Filename*" -print

 

Unix commands to get the count of files

 ls -l *.rdf | wc -l

SQL Query to find spaces in a data column

select *from (select 'testname@email.com ,noname@gmail.com  ' email from dual) where regexp_like(email,'(^ | $)')

How to search for a text from files in a directory on Linux

 find -type f -name "*.xml" -exec grep -l 'searchtext' {} +

Use the above command to find all files (in this case xnl) containing specific text 'searchtext' on Linux

How to test read/write permissions on UTL directory (in UNIX) from database or backend

Below is the sample script which can be run from TOAD/SQL*PLUS to test the read write permissions on UTL directory from database or backend

DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'DBA_UTL_DIR_NAME', 'test_file_name.txt', 'W' );
  utl_file.put_line( l_file, 'Here is sample text' );
  utl_file.fclose( l_file );
END;

Direcory name (DBA_UTL_DIR_NAME) should exists in DBA_DIRECTORIES table  

Make sure that the UTL_FILE directory path exists in UNIX


select * from DBA_DIRECTORIES where directory_name = 'DBA_UTL_DIR_NAME'