4.3 Creating Directories in DB

To create directories in DB:

1. Login to the database server and create the below directories:
2. Create a directory to store logs generated by the external table.
   mkdir -p /file_store/fs_list/logs
3. Create a directory to store a preprocessor script for listing files. This directory requires read and execute permissions.
   mkdir -p /file_store/fs_list/script
4. Create a directory to store control files that define accessible directories. This directory requires read permissions.
   mkdir -p /file_store/fs_list/control
5. Create a directory to store CSV files.
   mkdir -p /scratch/oraofss/fccm-data
6. Log in to the database as the SYS user and run the following commands. Replace <STSA/MMG App Schema> with the actual schema name created for the application.

   CREATE OR REPLACE DIRECTORY fs_list_logs_dir AS '/file_store/fs_list/logs/'; GRANT READ, WRITE ON DIRECTORY fs_list_logs_dir TO <STSA/MMG App Schema>; CREATE OR REPLACE DIRECTORY fs_list_script_dir AS '/file_store/fs_list/script/'; GRANT READ, EXECUTE ON DIRECTORY fs_list_script_dir TO <STSA/MMG App Schema>; CREATE OR REPLACE DIRECTORY fs_list_control_dir AS '/file_store/fs_list/control/'; GRANT READ ON DIRECTORY fs_list_control_dir TO <STSA/MMG App Schema>; CREATE OR REPLACE DIRECTORY external_tables_dir AS '/scratch/oraofss/fccm-data/'; GRANT READ ON DIRECTORY external_tables_dir TO <STSA/MMG App Schema>; GRANT CREATE TABLE TO <STSA/MMG App Schema>; 

7. Create preprocessor script.
   This script lists files in the directory referenced by the external table's LOCATION clause, formatting timestamps in a readable format.

   cat > /file_store/fs_list/script/list_directory.sh <<EOF #!/bin/bash /usr/bin/ls -l --time-style=+"%Y%m-%d:%H:%M:%S" "\$(/usr/bin/cat \$1)" EOF 

8. To view the script created and the contents of the file created from the previous step run the following command:

   cat > /file_store/fs_list/control/trace.txt <<EOF /scratch/oraofss/fccm-data EOF