READ access to everyone on Trace files

By default, non-dba users will not have access to the tracefiles generated under user_dump_dest directory. It may not be compliant with audit procedures, if we give access to these files in production. But, atleast in development, it will be very helpful for the developers, if they can see their tracefiles generated.

DBA will have to modify permissions on these tracefiles, inorder to give the developers access to read. Or, the better approach may be by setting the following undocumented parameter in the init.ora file and restarting the database instance.

_trace_files_public = true

Reading/Writing files using PL/SQL

Prior to Oracle 9i R2, each directory to be accessed by utl_file must be specified in the init.ora parameter and the database needs to be restarted inorder to make the directory effective.
From 9i R2, instead of specifying directory in the in the init.ora file, a directory object can be used.
Directory object can be created using the create directory command. You need to check with your admin to make sure the directory exists and has correct permissions at the os level. Also, DBA user can control the permissions on the directory object by granting read and write privileges only to the required users.

Examples:

1) Using directory specified in the utl_file_dir parameter in init.ora file
Add the directory e.g. /u01/utilout to the utl_file_dir parameter in the init.ora file and bounce the database.
Then use the directory to read and write the files using UTL_FILE package.

DECLARE
v_out_file UTIL_FILE.FILE_TYPE;
BEGIN
v_out_file := UTL_FILE.FOPEN(‘/u01/utilout’,'test.txt’, ‘W’);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message1′);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message2′);
UTL_FILE.FCLOSE(v_out_file);
END;
/

2) Using directory object
create directory object using create directory command and grant read, write to the user as shown below :
create directory UTIL_OUT as ‘/u01/utilout’;
grant read, write on directory UTIL_OUT to testuser;
Then use the directory object name to read and write the files using UTL_FILE package.

DECLARE
v_out_file UTIL_FILE.FILE_TYPE;
BEGIN
v_out_file := UTL_FILE.FOPEN(‘UTIL_OUT’,'test.txt’, ‘W’);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message1′);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message2′);
UTL_FILE.FCLOSE(v_out_file);
END;
/

The files created by UTIL_FILE package will have the permissions as per the umask settings of the oracle user.These file permissions can be changed manually after the files are created using chown and chgrp commands.
Incase you need to have the files created automatically with different permissions, you can try changing the umask permissions of the oracle user.

Exporting/Importing Large Data

When exporting Large data, you can specify multiple files in the export command. This can be used from Oracle 8i version.

$> exp testuser1/testuser1 files=/oraexport/data/tab1.dmp, /oraexport/data/tab2.dmp filesize=1024m log=large_exp.log

Please note that the export files need to be specified in the same order, when you import the data.

Or else, you can write the export data to a pipe and compress the data before writing to a file.
for export :
$PIPE=/tmp/exp_ora9i.dmp
$MAXSIZE=1024m
$ExportFile=employees.dmp
$ExportLog=exp_employees.log
$mknod $PIPE p
$ORACLE_USER=testuser1/testuser1
$ParamString=”compress=n feedback=10000 tables=employees log=$ExportLog statistics=none”
$( gzip < $PIPE ) split -b $MAXSIZE – $ExportFile. & $exp $ORACLE_USER buffer=2000000 constraints=n triggers=n grants=n indexes=n file=$PIPE $ParamString

To import the data exported as above, you can uncompress the data to a pipe and use the same pipe to import.

$PIPE=/tmp/imp_ora9i.dmp
$mknod $PIPE p
$ORACLE_USER=testuser2/testuser2
$cat `echo employees.* sort` gunzip > $PIPE &
$imp $ORACLE_USER file=$PIPE ignore=y