Category Archives: script

12c, Administration, Oracle, Performance, script, SQL

Finding SCN for Oracle Database by Date and time

June 9, 2017 Larry Catt Leave a comment

Oracle provides the function TIMESTAMP_TO_SCN to determine the database SCN number for a given time. This is very useful in performing multiple Oracle Administrative tasks. This procedure explains the use of this function to extract SCN.

1. Logon to Oracle Database server as the Oracle software owner and start SQLPLUS.

[root@linux2 etc]# su – oracle
Last login: Thu Jun 1 09:25:23 EST 2017 on pts/1
[oracle@linux2 ~]$
[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 1 13:47:01 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

2. Determine Oracle SCN one hour ago by specifying sysdate-1/24

SQL> select timestamp_to_scn(sysdate-1/24) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/24)
——————————
5062307

SQL>

3. Determine Oracle SCN 1 day ago by specifying sysdate-1

SQL> select timestamp_to_scn(sysdate-1) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1)
—————————
4975332

SQL>

4. Determine Oracle SCN 1 week ago by specifying sysdate-7

SQL> select timestamp_to_scn(sysdate-7) from dual;

TIMESTAMP_TO_SCN(SYSDATE-7)
—————————
4696226

SQL>

5. Determine Oracle SCN by specific date time by using the to_date() function with appropriate date mask.

SQL> select timestamp_to_scn(to_date(’25-MAY-2017 06:30:00′,’DD-MON-YYYY HH24:MI_SS’)) from dual;

TIMESTAMP_TO_SCN(TO_DATE(’25-MAY-201706:30:00′,’DD-MON-YYYYHH24:MI_SS’))
————————————————————————
4696226

SQL>

6. This completes the use of the function TIMESTAMP_TO_SCN.

Larry Catt
OCP

11g, 12c, Administration, linux, Oracle, Oracle 10g, oracle 9i, script, unix

Executing SQL script from command line or executable shell script

June 4, 2017 Larry Catt Leave a comment

You can call into SQLPLUS to execute SQL script from any shell script. This procedure shows how to structure your statements to LINUX environment for call into SQLPLUS from shell script.

1. Logon to your LINUX server as a user with access to SQLPLUS.
2. Use VI to create a executable file. Replace the connection string with appropriate username/password@SID values. Additionally, change the script you which to execute in this example we are executing “utlrp.sql”.

sqlplus sys/password@orcl as sysdba < < EOF @?/rdbms/admin/utlrp.sql exit; EOF

Larry Catt
OCP

12c, CDB, dbca, script

Create CDB in silent mode

October 10, 2016 Larry Catt Leave a comment

In Oracle 12c RDBMS you can use the Database Configuration Assistant to create a CDB from shell scripts or command line. You can provide all of the necessary parameters for the CDB creation through a response file or at the command prompt. In this example we will provide all required command line option to create new CDB called cdb1.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Wed Oct 5 15:10:16 EDT 2016 on pts/2

[oracle@linux2 ~]$

Define the following parameter with values:
1. -gdbName = the name of the database you wish to create.
2. -sid = the SID you wish to use for the database.
3. -SysPassword = password for the SYS user.
4. -SystemPassword = password for the SYSTEM user.
5. -datafileDestination = location of the data file storage.
6. -storageType = type of storage used for files.
7. -characterSet = character set of database.

You will have to also supply the tags for –silent option, -createDatabase, -templateName, and –createAsContainerDatabase.

Update the below DBCA command with the values defined in step 2.

dbca -silent \

-createDatabase -templateName General_Purpose.dbc \

-gdbName <db name> -sid <SID name> \

-createAsContainerDatabase true \

-SysPassword <SYS Password> -SystemPassword <SYSTEM Password> \

-datafileDestination <Directory Name> \

-storageType <Type of storage> -characterSet <Character Set>

Example execution.

[oracle@linux2 database]$ dbca -silent \

> -createDatabase -templateName General_Purpose.dbc \

> -gdbName cdb1 -sid cdb1 \

> -createAsContainerDatabase true \

> -SysPassword password12 -SystemPassword password12 \

> -datafileDestination /u01/oradata \

> -storageType FS -characterSet AL32UTF8

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

33% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

46% complete

47% complete

52% complete

57% complete

58% complete

59% complete

62% complete

Completing Database Creation

66% complete

70% complete

74% complete

85% complete

96% complete

100% complete

Look at the log file “/opt/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log” for further details.

[oracle@linux2 database]$

NOTE: The following step should only be used if your system only has one SID and ORACLE_HOME. Update profile with the ORACLE_HOME and ORACLE_SID environmental variables. Add the ORACLE_HOME/bin path to your PATH variable. You can use the following two statements update the .bash_profile file located in your oracle software owner’s home file.

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_HOME=”$2″”}’ >> ~/.bash_profile

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/db_1

export ORACLE_SID=cdb1

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Now re-execute the .bash_profile file to update environmental variables with the command: . ~/.bash_profile

[oracle@linux2 app]$ . ~/.bash_profile

[oracle@linux2 app]$

Verify that the variables have been updating by echo the environmental variables of $ORACLE_HOME, $ORACLE_SID, $PATH.

[oracle@linux2 app]$ echo $ORACLE_HOME

/opt/app/oracle/product/12.1.0.2/db_1

[oracle@linux2 app]$ echo $ORACLE_SID

cdb1

[oracle@linux2 app]$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Create the file tnsnames.ora with the following statement:

echo -e “CDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (SID = “$ORACLE_SID”)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Execution Example:

[oracle@linux2 admin]$ echo -e “CDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (SID = “$ORACLE_SID”)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

(SID = cdb1)

)

[oracle@linux2 admin]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

” (SID_NAME = “$ORACLE_SID”)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

> ” (SID_NAME = “$ORACLE_SID”)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

[oracle@linux2 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = cdb1)

(SERVICE_NAME = cdb1)

(GLOBAL_DBNAME = cdb1)

(ORACLE_HOME = /opt/app/oracle/product/12.1.0.2/db_1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

))

ADR_BASE_LISTENER = /opt/app/oracle

LOGGING_LISTENER=ON

TRACE_LEVEL_SERVER=SUPPORT

SQLNET.INBOUND_CONNECT_TIMEOUT=240

[oracle@linux2 admin]$

Stat the listener process with the command: lsnrctl start

[oracle@linux2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:06:54

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 19-OCT-2016 14:06:54

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 admin]$

Test connect to the new database with command: tnsping cdb1

[oracle@linux2 admin]$ tnsping cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:15:55

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes the creation of CDB in silent mode.

Larry Catt

OCP

Oracle, Oracle 10g, oracle 9i, script

Determining the record insert rate for a table

October 1, 2013 Larry Catt Leave a comment

Some system require the insertion of large amounts of data in as short a period of time as possible. In this cases, you must be able to measure actual physical time of insertion per give number of records or number of records inserted per second. The following block allows you to perform this action on a give oracle operation.

We will use the following anonymous block to track our processing time:

SET SERVEROUTPUT ON DECLARE v_loops NUMBER := 1000000; v_start_time NUMBER; v_total_time NUMBER; v_ct NUMBER :=1; BEGIN

— Time we started our process. v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP < Oracle Action: Insert, function call, procedure call, etc > v_ct:=v_ct+1; commit; END LOOP; –Calculate the time in seconds v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100; DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’); END; / Example Execution:

1. Connect as the user scott and create a table test_data

SQL> connect scott/tiger@orcl Connected. SQL> create table test_data(user_no number, fname varchar2(30), lname varchar2(30));

Table created.

SQL>

2. Execute your block with < Oracle Action …> replaced with an insert statement for test data.

SET SERVEROUTPUT ON DECLARE v_loops NUMBER := 1000000; v_start_time NUMBER; v_total_time NUMBER; v_ct NUMBER :=1234; BEGIN

— Time we started our process. v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’); v_ct:=v_ct+1; commit; END LOOP; –Calculate the time in seconds v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100; DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’); END; / Results: SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 v_loops NUMBER := 1000000; 4 v_start_time NUMBER; 5 v_total_time NUMBER; 6 v_ct NUMBER :=1234; 7 8 BEGIN 9 10 — Time we started our process. 11 v_start_time := DBMS_UTILITY.get_time; 12 13 FOR i IN 1 .. v_loops LOOP 14 15 insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’); 16 v_ct:=v_ct+1; 17 commit; 18 END LOOP; 19 20 –Calculate the time in seconds 21 v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100; 22 23 DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’); 24 END; 25 / This process took a total of : 86.45 seconds to complete

PL/SQL procedure successfully completed.

SQL> SQL>

3. The results show a insert rate of 86.45 seconds for 1 million records or 11,567.38 records per second.

Larry Catt

Oracle, script, SQL, sqlplus

Currently executing SQL in Oracle database

August 25, 2013 Larry Catt Leave a comment

I was just asked by a junior administrator, how to determine what SQL is currently executing against her database. While this may seem a trivial task for those in the industry for some time, I thought it a very good question. The below SQL will display currently executing SQL within an Oracle RDBMS.

1. Logon to SQL*PLUS with dba privileges.

2. Execute the following SQL block against the v$sql and v$session tables. NOTE: in a grid environment, use the gv$ views.

select sql_text from v$sql where sql_id in
(select sql_id from v$session where status=’ACTIVE’);

3. Displayed results show currently executing SQL blocks.

SQL> select sql_text from v$sql where sql_id in
2 (select sql_id from v$session where status=’ACTIVE’);

SQL_TEXT
——————————————————————————–
select sql_text from v$sql where sql_id in (select sql_id from v$session where s
tatus=’ACTIVE’)

Larry Catt, OCP

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

Oracle Installation â€“ Add swap space in UNIX or Linux environment

December 5, 2010 Larry Catt Leave a comment

The typical Oracle installation will require more swap space on a system, due to prerequisite checks performed by the installer, than is typical available. The following procedure shows how to create additional swap in Unix or Linux.

1. Create a file on /u01 of 12gb with the dd command. NOTE: Repeat on each RAC node.

dd if=/dev/zero of=/u01/tempswap bs=1k count=12000000

Example:

[root@mylinux1 ~]# dd if=/dev/zero of=/u01/tempswap bs=1k count=12000000
12000000+0 records in
12000000+0 records out
12384000000 bytes (12 GB) copied, 64.2111 seconds, 255 MB/s
[root@mylinux1 ~]#

2. Then format your new file with the mke2fs command. NOTE: Repeat on each RAC node.

/sbin/mke2fs /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/mke2fs /u01/tempswap
mke2fs 1.39 (29-May-2006)
/u01/tempswap is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2003424 inodes, 4000000 blocks
200000 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4097835008
123 block groups
32768 blocks per group, 32768 fragments per group
12288 inodes per group
Superblock backups stored on blocks:
32768, 98304, 123840, 229376, 294912, 819200, 884736, 1205632, 2654208

Writing inode tables: done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@mylinux1 ~]#

3. Make the additional swap space with the mkswap command. NOTE: Repeat on each RAC node.

/sbin/mkswap /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/mkswap /u01/tempswap
Setting up swapspace version 1, size = 12383995 kB
[root@mylinux1 ~]#

4. Make the swap space available to the OS with the command swapon. NOTE: This will have to be executed after every reboot. Repeat on each RAC node.

/sbin/swapon /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/swapon /u01/tempswap
[root@mylinux1 ~]#

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

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

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

April 23, 2010 Larry Catt Leave a comment

Oracle SQL*PLUS utility provides several options to customize the display of information from your database session. In this article we will review how to display the DML and DDL statements which are stored in an OS layer file upon execution. By default SQL*PLUS will not display the DML or DDL statement from a script file.

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

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

select * from v$instance;

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Apr 23 22:00:09 2010

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>

4. Execute the SQL file test.sql with the SQL*PLUS command: @./test.sql

SQL> @./test.sql

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

NOTE: The SQL contained within the file test.sql is not displayed, thus the end user executing such a file may not be aware of the output they are looking at.

5. To remedy this situation we will have to setup our SQL*PLUS environment to display the commands within our file. To do this we will have to turn two options on: ECHO and FEEDBACK.

SQL> set echo on
SQL> set feedback on
SQL>

6. Now re-execute the file test.sql with the SQL*PLUS command: @./test.sql.

SQL> @./test.sql
SQL> select * from v$instance;

SQL>
SQL>

As show above, SQL*PLUS is now displaying the SQL contained within our file, before the output. This will allow the end user to know exactly how the output of our script file was produced, thus turning our script into more usefully information.

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

11g, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus

Oracle – Viewing errors generated when writing a PL/SQL program unit.

April 22, 2010 Larry Catt Leave a comment

When you create a PL/SQL block of code within Oracle in the form of a procedure, function, or package and a compilation error occurs; Oracle will not immediately give you the error details. This article details the procedure to view errors generated by a PL/SQL block within SQL*PLUS.

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. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:00:09 2010

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>

4. Set FEEDBACK and ECHO to on and execute the file create_file.sql.

SQL> set echo on
SQL> set feedback 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
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 /

Warning: Procedure created with compilation errors.

SQL>

5. The procedure was created with compilation errors, however it does not show you the errors. To see the errors generated in your procedure creation statement, use the SQL*PLUS command: show errors

SQL> show errors
Errors for PROCEDURE TEST_FILE:

LINE/COL ERROR
——– —————————————————————–
10/1 PL/SQL: Statement ignored
10/1 PLS-00201: identifier ‘V_WRITE’ must be declared
12/1 PL/SQL: Statement ignored
12/19 PLS-00201: identifier ‘V_WRITE’ must be declared
14/1 PL/SQL: Statement ignored
14/17 PLS-00201: identifier ‘V_WRITE’ must be declared
SQL>

6. Now you can see that the procedure creation statement is missing the definition for the variable V_WRITE. To correct this error replace the contents of your create_file.sql file with the following text, where the variable v_write is defined as utl_file.file_type.

create or replace procedure test_file
is

end test_file;
/

7. Re-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 Thu Apr 22 19:43:17 2010

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>

The procedure now compiles without error, through the use of the SQL*PLUS command â€˜show errorsâ€™ which allowed us to see the detailed error message provided by 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, 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, Oracle, Oracle 10g, oracle 9i, privileges, script, SQL, sqlplus

Oracle – ORA-00604: error occurred at recursive SQL level 1 and revoking a user privileges.

January 17, 2009 Larry Catt Leave a comment

A schema is considered all of the objects owned by a single user within the Oracle database. When a user has a privilege revoked in the database, but owns a particular object which requires that privilege, errors will result. This article will recover an error which will result from a user having a privilege revoked which is require by one of the objects he owns.

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

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 17 11:00:09 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>

3. Create the user LJCATT and grant him the DBA role and create procedure privileges.

SQL> create user ljcatt identified by password10;

User created.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> grant execute on utl_file to ljcatt;

Grant succeeded.

4. Connect as the user ljcatt and create a procedure named test_file.

SQL> connect ljcatt
Enter password:
Connected.

SQL> create or replace procedure test_file
is
2 3 v_record varchar2(50) := ‘Testing file creation’;
4 v_file varchar2(30) := ‘testfile’;
5 v_dir varchar2(100) := ‘/home/lcatt’;
6 v_write utl_file.file_type;
7 begin
8
9 v_write := utl_file.fopen(v_dir, v_file, ‘w’, 4000);

10 11 –utl_file.fopen(v_dir, v_file, ‘w’, 100);
12
13 utl_file.put_line(v_write, v_record);

14 15 utl_file.fclose(v_write);
16
17 end test_file;
/ 18

Procedure created.

5. Reconnect to the database with SYSDBA privileges and revoke the DBA from ljcatt and grant the create session privilege. Then attempt to reconnect as the user LJCATT.

SQL> connect / as sysdba
Connected.
SQL> revoke dba from ljcatt;

Revoke succeeded.

SQL> grant create session to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Warning: You are no longer connected to ORACLE.

6. NOTE: The error is received due to this user owning objects which he does not have privileges to create or access. This is shown below by reconnecting as SYSDBA and displaying objects owned by the user LJCATT.

SQL> connect / as sysdba
Connected.

SQL> select owner, object_name from dba_objects where object_name like ‘TEST_F%’
and owner=’LJCATT’

OWNER OBJECT_NAME
————————- ————————–
LJCATT TEST_FILE

7. As SYSDBA, re-grant the DBA role to LJCATT and attempt to reconnect.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

Now that the user LJCATT has the specific privileges required by the objects he owns, he is able to connect without error.

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

Catt Solutions

Category Archives: script

Finding SCN for Oracle Database by Date and time

Executing SQL script from command line or executable shell script

Create CDB in silent mode

Determining the record insert rate for a table

Currently executing SQL in Oracle database

Oracle Installation â€“ Add swap space in UNIX or Linux environment

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

Oracle – Viewing errors generated when writing a PL/SQL program unit.

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

Oracle – ORA-00604: error occurred at recursive SQL level 1 and revoking a user privileges.

Oracle tips and tricks.