Category Archives: linux

Oracle DBA role

May 4, 2009 Larry Catt Leave a comment

Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role DBA.

The DBA role is used to give an individual user the right to administrator an oracle database. This role is normally only granted to users who have a need to view dictionary level views and administrate other user accounts. NOTE: Great care has to be used when assigning the DBA role, because users who have it can perform almost every action in the database short of actually shutting down the system. The following instructions define how to create a user account, assign the DBA role to that user and lists the privileges granted by DBA role.

1. Logon the you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 4 21:56:18 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace admin datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/admin01.dbf’ size 5m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace admin_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/O
RADATA/ORCL10G/admin_temp01.dbf’ size 5m;

Tablespace created.

SQL>

4. Create you administrative user.

SQL> Create user admin01 identified by admin
2 Default tablespace admin
3 Quota unlimited on admin
4 Temporary tablespace admin_temp;

User created.

SQL>

5. Grant the role DBA to the user developer01.

SQL> grant dba to admin01;

Grant succeeded.

SQL>

6. Connect to the system as the admin01 user and perform a select from a system view.

SQL> connect admin01/admin
Connected.
SQL> select count(*) from v$session;

COUNT(*)
———-
18

SQL>

7. The PL/SQL block below will list all privileges contained in the role DBA, where the variable V_USER defines the roleâ€™s privileges to be displayed.
set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’DBA’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

34
35 36 37 38 39 40 41 42
grant XDBADMIN to DBA;
grant JAVA_ADMIN to DBA;
grant JAVA_DEPLOY to DBA;
grant WM_ADMIN_ROLE to DBA;
grant SCHEDULER_ADMIN to DBA with admin option;
grant EXP_FULL_DATABASE to DBA;
grant IMP_FULL_DATABASE to DBA;
grant DELETE_CATALOG_ROLE to DBA with admin option;
grant SELECT_CATALOG_ROLE to DBA with admin option;
grant EXECUTE_CATALOG_ROLE to DBA with admin option;
grant GATHER_SYSTEM_STATISTICS to DBA;
grant ADVISOR to DBA with admin option;
grant AUDIT ANY to DBA with admin option;
grant DROP USER to DBA with admin option;
grant RESUMABLE to DBA with admin option;
grant ALTER USER to DBA with admin option;
grant CREATE JOB to DBA with admin option;
grant ANALYZE ANY to DBA with admin option;
grant BECOME USER to DBA with admin option;
grant CREATE ROLE to DBA with admin option;
grant CREATE RULE to DBA with admin option;
grant CREATE TYPE to DBA with admin option;
grant CREATE USER to DBA with admin option;
grant CREATE VIEW to DBA with admin option;
grant ALTER SYSTEM to DBA with admin option;
grant AUDIT SYSTEM to DBA with admin option;
grant CREATE TABLE to DBA with admin option;
grant DROP PROFILE to DBA with admin option;
grant ALTER PROFILE to DBA with admin option;
grant ALTER SESSION to DBA with admin option;
grant DROP ANY ROLE to DBA with admin option;
grant DROP ANY RULE to DBA with admin option;
grant DROP ANY TYPE to DBA with admin option;
grant DROP ANY VIEW to DBA with admin option;
grant QUERY REWRITE to DBA with admin option;
grant ALTER ANY ROLE to DBA with admin option;
grant ALTER ANY RULE to DBA with admin option;
grant ALTER ANY TYPE to DBA with admin option;
grant ALTER DATABASE to DBA with admin option;
grant CREATE ANY JOB to DBA with admin option;
grant CREATE CLUSTER to DBA with admin option;
grant CREATE LIBRARY to DBA with admin option;
grant CREATE PROFILE to DBA with admin option;
grant CREATE SESSION to DBA with admin option;
grant CREATE SYNONYM to DBA with admin option;
grant CREATE TRIGGER to DBA with admin option;
grant DROP ANY INDEX to DBA with admin option;
grant DROP ANY TABLE to DBA with admin option;
grant GRANT ANY ROLE to DBA with admin option;
grant LOCK ANY TABLE to DBA with admin option;
grant MERGE ANY VIEW to DBA with admin option;
grant UNDER ANY TYPE to DBA with admin option;
grant UNDER ANY VIEW to DBA with admin option;
grant ALTER ANY INDEX to DBA with admin option;
grant ALTER ANY TABLE to DBA with admin option;
grant CREATE ANY RULE to DBA with admin option;
grant CREATE ANY TYPE to DBA with admin option;
grant CREATE ANY VIEW to DBA with admin option;
grant CREATE OPERATOR to DBA with admin option;
grant CREATE RULE SET to DBA with admin option;
grant CREATE SEQUENCE to DBA with admin option;
grant DROP TABLESPACE to DBA with admin option;
grant UNDER ANY TABLE to DBA with admin option;
grant ALTER TABLESPACE to DBA with admin option;
grant BACKUP ANY TABLE to DBA with admin option;
grant CREATE ANY INDEX to DBA with admin option;
grant CREATE ANY TABLE to DBA with admin option;
grant CREATE DIMENSION to DBA with admin option;
grant CREATE INDEXTYPE to DBA with admin option;
grant CREATE PROCEDURE to DBA with admin option;
grant DELETE ANY TABLE to DBA with admin option;
grant DROP ANY CLUSTER to DBA with admin option;
grant DROP ANY CONTEXT to DBA with admin option;
grant DROP ANY LIBRARY to DBA with admin option;
grant DROP ANY OUTLINE to DBA with admin option;
grant DROP ANY SYNONYM to DBA with admin option;
grant DROP ANY TRIGGER to DBA with admin option;
grant EXECUTE ANY RULE to DBA with admin option;
grant EXECUTE ANY TYPE to DBA with admin option;
grant INSERT ANY TABLE to DBA with admin option;
grant MANAGE ANY QUEUE to DBA with admin option;
grant MANAGE SCHEDULER to DBA with admin option;
grant SELECT ANY TABLE to DBA with admin option;
grant UPDATE ANY TABLE to DBA with admin option;
grant ALTER ANY CLUSTER to DBA with admin option;
grant ALTER ANY LIBRARY to DBA with admin option;
grant ALTER ANY OUTLINE to DBA with admin option;
grant ALTER ANY TRIGGER to DBA with admin option;
grant COMMENT ANY TABLE to DBA with admin option;
grant CREATE TABLESPACE to DBA with admin option;
grant DEQUEUE ANY QUEUE to DBA with admin option;
grant DROP ANY OPERATOR to DBA with admin option;
grant DROP ANY RULE SET to DBA with admin option;
grant DROP ANY SEQUENCE to DBA with admin option;
grant ENQUEUE ANY QUEUE to DBA with admin option;
grant EXECUTE ANY CLASS to DBA with admin option;
grant FORCE TRANSACTION to DBA with admin option;
grant MANAGE FILE GROUP to DBA with admin option;
grant MANAGE TABLESPACE to DBA with admin option;
grant ON COMMIT REFRESH to DBA with admin option;
grant ALTER ANY RULE SET to DBA with admin option;
grant ALTER ANY SEQUENCE to DBA with admin option;
grant CREATE ANY CLUSTER to DBA with admin option;
grant CREATE ANY CONTEXT to DBA with admin option;
grant CREATE ANY LIBRARY to DBA with admin option;
grant CREATE ANY OUTLINE to DBA with admin option;
grant CREATE ANY SYNONYM to DBA with admin option;
grant CREATE ANY TRIGGER to DBA with admin option;
grant DROP ANY DIMENSION to DBA with admin option;
grant DROP ANY DIRECTORY to DBA with admin option;
grant DROP ANY INDEXTYPE to DBA with admin option;
grant DROP ANY PROCEDURE to DBA with admin option;
grant RESTRICTED SESSION to DBA with admin option;
grant ALTER ANY DIMENSION to DBA with admin option;
grant ALTER ANY INDEXTYPE to DBA with admin option;
grant ALTER ANY PROCEDURE to DBA with admin option;
grant ALTER RESOURCE COST to DBA with admin option;
grant CHANGE NOTIFICATION to DBA with admin option;
grant CREATE ANY OPERATOR to DBA with admin option;
grant CREATE ANY RULE SET to DBA with admin option;
grant CREATE ANY SEQUENCE to DBA with admin option;
grant CREATE EXTERNAL JOB to DBA with admin option;
grant DEBUG ANY PROCEDURE to DBA with admin option;
grant DROP PUBLIC SYNONYM to DBA with admin option;
grant EXECUTE ANY LIBRARY to DBA with admin option;
grant EXECUTE ANY PROGRAM to DBA with admin option;
grant FLASHBACK ANY TABLE to DBA with admin option;
grant GRANT ANY PRIVILEGE to DBA with admin option;
grant READ ANY FILE GROUP to DBA with admin option;
grant SELECT ANY SEQUENCE to DBA with admin option;
grant CREATE ANY DIMENSION to DBA with admin option;
grant CREATE ANY DIRECTORY to DBA with admin option;
grant CREATE ANY INDEXTYPE to DBA with admin option;
grant CREATE ANY PROCEDURE to DBA with admin option;
grant CREATE DATABASE LINK to DBA with admin option;
grant DROP ANY SQL PROFILE to DBA with admin option;
grant EXECUTE ANY OPERATOR to DBA with admin option;
grant EXECUTE ANY RULE SET to DBA with admin option;
grant EXPORT FULL DATABASE to DBA with admin option;
grant GLOBAL QUERY REWRITE to DBA with admin option;
grant IMPORT FULL DATABASE to DBA with admin option;
grant ALTER ANY SQL PROFILE to DBA with admin option;
grant CREATE PUBLIC SYNONYM to DBA with admin option;
grant DEBUG CONNECT SESSION to DBA with admin option;
grant DROP ROLLBACK SEGMENT to DBA with admin option;
grant EXECUTE ANY INDEXTYPE to DBA with admin option;
grant EXECUTE ANY PROCEDURE to DBA with admin option;
grant FORCE ANY TRANSACTION to DBA with admin option;
grant MANAGE ANY FILE GROUP to DBA with admin option;
grant SELECT ANY DICTIONARY to DBA with admin option;
grant ALTER ROLLBACK SEGMENT to DBA with admin option;
grant ANALYZE ANY DICTIONARY to DBA with admin option;
grant CREATE ANY SQL PROFILE to DBA with admin option;
grant SELECT ANY TRANSACTION to DBA with admin option;
grant CREATE ROLLBACK SEGMENT to DBA with admin option;
grant CREATE MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER SQL TUNING SET to DBA with admin option;
grant CREATE EVALUATION CONTEXT to DBA with admin option;
grant DROP PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY MATERIALIZED VIEW to DBA with admin option;
grant GRANT ANY OBJECT PRIVILEGE to DBA with admin option;
grant ADMINISTER DATABASE TRIGGER to DBA with admin option;
grant ADMINISTER RESOURCE MANAGER to DBA with admin option;
grant ALTER ANY MATERIALIZED VIEW to DBA with admin option;
grant CREATE PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER ANY EVALUATION CONTEXT to DBA with admin option;
grant CREATE ANY MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER ANY SQL TUNING SET to DBA with admin option;
grant CREATE ANY EVALUATION CONTEXT to DBA with admin option;
grant EXECUTE ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER on SYS.MAP_OBJECT to DBA;
grant DELETE on SYS.MAP_OBJECT to DBA;
grant INSERT on SYS.MAP_OBJECT to DBA;
grant SELECT on SYS.MAP_OBJECT to DBA;
grant UPDATE on SYS.MAP_OBJECT to DBA;
grant ON COMMIT REFRESH on SYS.MAP_OBJECT to DBA;
grant QUERY REWRITE on SYS.MAP_OBJECT to DBA;
grant DEBUG on SYS.MAP_OBJECT to DBA;
grant FLASHBACK on SYS.MAP_OBJECT to DBA;
grant EXECUTE on SYS.DBMS_FLASHBACK to DBA;
grant EXECUTE on SYS.OUTLN_PKG to DBA;
grant EXECUTE on SYS.OUTLN_EDIT_PKG to DBA;
grant EXECUTE on SYS.DBMS_RESUMABLE to DBA;
grant EXECUTE on SYS.DBMS_DEFER_QUERY to DBA;
grant EXECUTE on SYS.DBMS_DEFER_SYS to DBA;
grant EXECUTE on SYS.DBMS_STORAGE_MAP to DBA;
grant UPDATE on WKSYS.WK$SYS_CONFIG to DBA;
grant EXECUTE on PORTAL.WWV_EXPORT to DBA;
grant EXECUTE on SYS.DBMS_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_INTERNAL_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_SERVER_ALERT to DBA;
grant EXECUTE on SYS.DBMS_TDB to DBA;
grant ALTER on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AW$ to DBA;
grant DEBUG on SYS.AW$ to DBA;
grant SELECT on SYS.PS$ to DBA;
grant DEBUG on SYS.PS$ to DBA;
grant SELECT on SYS.AW_PROP$ to DBA;
grant DEBUG on SYS.AW_PROP$ to DBA;
grant SELECT on SYS.AW_OBJ$ to DBA;
grant DEBUG on SYS.AW_OBJ$ to DBA;
grant EXECUTE on SYS.DBMS_SERVER_TRACE to DBA;
grant EXECUTE on SYS.DBMS_SERVICE to DBA;
grant EXECUTE on SYS.DBMS_MONITOR to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant EXECUTE on SYS.DBMS_UADV_ARR to DBA;
grant EXECUTE on SYS.DBMS_UNDO_ADV to DBA;
grant EXECUTE on SYS.LOAD_UNDO_STAT to DBA;
grant EXECUTE on SYS.RESET_UNDO_STAT to DBA;
grant EXECUTE on SYS.DBMS_FEATURE_USAGE_REPORT to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_CAPTURE to DBA;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, security, SQL, sqlplus, unix

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

January 29, 2009 Larry Catt Leave a comment

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle provides the procedure UTL_FILE to allow for the reading of external OS layer files from within the Oracle RDBMS. This article will give an example of how to read the contents of an external OS file and display it in the Oracle database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called read.txt with the following text.

This is line one.

3. Logon to SQL*PLUS with an account which has DBA privileges. In this example we are using the account LJCATT.

mylinux:> sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 29 23:28:23 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Create the procedure read_file as detailed below.

create or replace procedure read_file
is

v_record varchar2(50);
v_file varchar2(30) := ‘read.txt’;
v_dir varchar2(512) := ‘/home/lufsora’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘r’, 2000);

utl_file.get_line(v_write,v_record, v_record);

dbms_output.put_line(v_record);

utl_file.fclose(v_write);

end read_file;
/

5. Execute the procedure read_file with the command execute and the test within the OS file will be display.

SQL> execute read_file;
This is line one.

PL/SQL procedure successfully completed.

SQL>

This completes the use of UTL_FILE to read OS level files into your Oracle RDBMS.

Larry J. Catt, OCP9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus, unix

Oracle – ORA-29280: invalid directory path when using UTL_FILE

January 10, 2009 Larry Catt Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will describe the common error of ORA-29280 which maybe received when using the UTL_FILE procedure and how to resolve it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql as a user with DBA privileges. In this example we are using the account LJCATT.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 10 10:43:17 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Use the SQL*PLUS command show to display the initialization parameter utl_file_dir and ensure that the directory /home/lcatt is not present.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /home/lcatt/output
SQL>

5. If the directory is present, remove it with the following command:

SQL> alter system set utl_file_dir=” scope=spfile;

System altered.

SQL>

6. Logon to the database as sysdba and restart your oracle instance.

SQL> connect / as sysdba
Connected.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>

7. Now logon as the user LJCATT and execute the package test_file with the command execute.

SQL> execute test_file
BEGIN test_file; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at “LJCATT.TEST_FILE”, line 10
ORA-06512: at line 1

SQL>

8. NOTE: The error produced is a result of no reference to the defined directory structure in the initialization parameter UTL_FILE_DIR. This parameter is used to allow the database access to OS layer directories. To resolve this error, connect to the database as sysdba and place the directory path in the initialization parameter UTL_FILE_DIR with the command alter system and restart the database.

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————

utl_file_dir string /home/lcatt

9. Connect back to the database as the user LJCATT and re-execute the procedure test_file.

SQL> connect ljcatt
Enter password:
Connected.
SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

10. Exit out of SQL*PLUS, change directories to the location of your output file, and perform an ls command to display the file generated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Mylinux:/home/lcatt:>cd /home/lcatt
Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>

The file was produced under the directory defined by the initialization parameter utl_file_dir and the error ORA-29280 has been resolved.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, script, SQL, sqlplus, unix

Creating an external file from within the Oracle RDBMS.

January 3, 2009 Larry Catt Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will show a basic procedure for the producing an OS layer file from within the Oracle RDBMS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 03 09:43:17 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Ensure that the system initialization parameter for utl_file_dir contains the directory to which utl_file is going to write with the command show utl_file_dir.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string

SQL>

5. If it does not contain the directory, as is the case in step 4, logon to your Oracle database as sysdba and add the directory structure and bounce the database, as seen below:

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

7. Exit out of SQL*PLUS and change directory to where you wrote your file.

8. Execute the ls and more command on the file testfile.

Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>more testfile
Testing file creation
Mylinux:/home/lcatt:>

This completes the creation of external OS layer file from within the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, unix

UNIX CRON â€“ Shell script errors with [10]: sqlplus: not found

July 20, 2008 Larry Catt Leave a comment

In normal administration of an Oracle RDBMS on UNIX or LINUX operating systems, the DBA will be required to develop shell and SQL scripts to maintain his/her RDBMS. In fact, to be considered a mid to senior DBA you will need to be well versed in scripting at the OS layer. In this article we will review a common error which occurs in a shell script called by CRON due to a lack of properly set variables.

1. In a normal scenario, the DBA has developed a shell script which calls and executes a SQL script to perform a certain maintenance task. In testing, the DBA executes the script as the oracle software owner and the script performs perfectly. However, when placed in the OS CRON for execution, the following error occurs.

[10]: sqlplus: not found

2. The error occurs because the environmental variables are not being read. Thus, the OS does not know where to find the executable: sqlplus.

3. The solution is to update your shell script and hard code the directory structure for the command sqlplus. An example would be if sqlplus is located in the following path /u01/opt/app/oracle/bin then perform the following:

Replace:

sqlplus With:

/u01/opt/app/oracle/bin/sqlplus

This completes resolving the error message [10]: sqlplus: not found in a shell script called through CRON.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Catt Solutions

Category Archives: linux

Oracle DBA role

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle – ORA-29280: invalid directory path when using UTL_FILE

Creating an external file from within the Oracle RDBMS.

UNIX CRON â€“ Shell script errors with [10]: sqlplus: not found

Oracle tips and tricks.