General
=======================
Oracle user can write files to destinations specified in ALL_DIRECTORIES.
All directories known to Oracle are listed in DBA_DIRECTORIES.
There is no table "USER_DIRECTORIES".
To add an entry to ALL_DIRECTORIES, need to create it, and grant permissions
CREATE OR REPLACE DIRECTORY MY_DIR AS '/my/path/to/my_dir';
GRANT READ, WRITE ON DIRECTORY MY_DIR TO MY_USER ;
Linux Permissions
In addition, in Linux, a special permission is needed if Oracle is to write to a folder owned by another user. This can be done using setfacl.
getfacl <source_file> | setfacl --set-file=- <target_file>
=============================
Examples
=============================
A. Create a new Directory on Linux
add_xfer_directory_linux.sh
#!/bin/bash
USERNAME=`whoami`
WORK_DIR=`pwd`
CDR_TAP_PATH=~iu/workarea
CDR_TAP_DIR=CDR_TAP
if [[ $USERNAME != 'iu' ]]
then
echo "Script terminated with Error!! Login as iu user and run the script again";
exit 1
fi
cd ${CDR_TAP_PATH}
if [[ ! -d ${CDR_TAP_DIR} ]]
then
mkdir ${CDR_TAP_DIR}
fi
getfacl ora_exp | setfacl --set-file=- ${CDR_TAP_DIR}
cd $WORK_DIR
sqlplus /nolog @cre_directory_entry.sql
echo "Script terminated Successfully"
exit 0
B. Create a new Directory in Oracle
@./set_user.sql
--my_user is defined in set_user.sql
PROMPT &&sysuser/&&syspass@&&connectstr
CONNECT &&sysuser/&&syspass@&&connectstr
CREATE OR REPLACE DIRECTORY CDR_TAP_DIR AS '&&CDR_TAP_DIR';
GRANT READ, WRITE ON DIRECTORY CDR_TAP_DIR TO &&my_user;
EXIT;
C. Basic example of writing to file from PL/SQL
DECLARE
v_my_file_handler UTL_FILE.FILE_TYPE;
BEGIN
v_my_file_handler := UTL_FILE.FOPEN('MY_DIR', 'my_file.txt', 'W');
UTL_FILE.PUT_LINE(v_my_file_handler, 'LINE ONE');
UTL_FILE.PUT_LINE(v_my_file_handler, 'LINE TWO');
UTL_FILE.FCLOSE(v_my_file_handler);
END;
/
Elaborate example of creating extract from database to several files.
PROCEDURE CUST_REPORT_COMMUNITIES IS
CURSOR get_community_imsi_cur IS
SELECT attr1 AS COMMUNITY_NAME, key1 AS IMSI
FROM SFI_CUSTOMER_PROFILE
ORDER BY attr1, key1;
v_sql_str VARCHAR2(1000);
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text_header SGA_W_LOG.data%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_prev_community SFI_CUSTOMER_PROFILE.attr1%TYPE;
v_is_first_line NUMBER;
v_timestamp VARCHAR2(100);
v_file_handle UTL_FILE.FILE_TYPE;
v_directory_name ALL_DIRECTORIES.directory_name%TYPE;
v_file_name VARCHAR2(300);
v_file_delimiter VARCHAR2(1);
BEGIN
v_module_name := 'CUST_REPORT_COMMUNITIES';
v_msg_text_header := '=================================================================';
v_prev_community := 'CUST_REPORT_COMMUNITIES_DUMMY';
v_directory_name := 'PRT_TMN_COMMUNITIES_DIR';
v_file_delimiter := ';';
v_is_first_line := 0;
v_msg_text := v_msg_text_header;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_msg_text := 'Starting at: '||SYSDATE;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_timestamp := TO_CHAR(SYSDATE,'YYYYMMDD_hh24miss');
-----------------------------------------
-- Populate SFI_CUSTOMER_PROFILE_REPORT Table
-----------------------------------------
v_sql_str := 'TRUNCATE TABLE SFI_CUSTOMER_PROFILE_REPORT';
RUN_SQL (v_sql_str, C_YES);
v_msg_text := 'SFI_CUSTOMER_PROFILE_REPORT Table was Truncated';
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_sql_str := 'INSERT /*+ APPEND */ INTO SFI_CUSTOMER_PROFILE_REPORT SELECT key1, attr1 FROM SFI_CUSTOMER_PROFILE';
RUN_SQL (v_sql_str, C_YES);
COMMIT;
v_msg_text := 'SFI_CUSTOMER_PROFILE_REPORT Table was Populated';
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
FOR get_community_imsi_rec IN get_community_imsi_cur LOOP
--When community changes
IF v_prev_community <> get_community_imsi_rec.community_name THEN
v_msg_text := 'Starting to work on Community '||get_community_imsi_rec.community_name;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
--Close Previos File and open a new one
IF v_is_first_line > 0 THEN
v_msg_text := 'Finished to work on File '||v_file_name||' at: '||SYSDATE;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
UTL_FILE.FCLOSE(v_file_handle);
END IF;
v_prev_community := get_community_imsi_rec.community_name;
v_file_name := 'PRT_TMN_COMMUNITY_'||v_prev_community||'_'||v_timestamp||'.csv';
v_msg_text := 'Starting to work on File '||v_file_name||' at: '||SYSDATE;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_file_handle := UTL_FILE.FOPEN(v_directory_name, v_file_name, 'W');
v_is_first_line := 1;
END IF;
UTL_FILE.PUT_LINE(v_file_handle, get_community_imsi_rec.community_name ||v_file_delimiter|| get_community_imsi_rec.imsi);
END LOOP;
--Close the last file open
v_msg_text := 'Finished to work on File '||v_file_name||' at: '||SYSDATE;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
UTL_FILE.FCLOSE(v_file_handle);
v_msg_text := 'Finished at: '||SYSDATE;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
v_msg_text := v_msg_text_header;
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error. Error Details: '||SUBSTR(SQLERRM, 1, 900);
WRITE_SGA_W_LOG(v_module_name,v_msg_text);
RAISE;
END CUST_REPORT_COMMUNITIES;
=============================
Create directory for expdp
=============================
#!/bin/bash
#must run as root
#setup exp dp directory and permissions to used by my_user to perfrom export/import using data pump
ig_exp_dir=/software_root/my_user/workarea/ora_exp
mkdir -p $ig_exp_dir
chown -R my_user:my_group /software_root/my_user/workarea
setfacl -m u:oracle:rx /software_root/
setfacl -m u:oracle:rx /software_root/my_user
setfacl -m u:oracle:rx /software_root/my_user/workarea
setfacl -m u:oracle:rwx $ig_exp_dir
setfacl -m d:u:oracle:rwx /software_root/my_user/workarea/ora_exp
setfacl -m d:u:iu:rwx /software_root/my_user/workarea/ora_exp
No comments:
Post a Comment