Category Archives: Oracle

12c, linux, Oracle, VM

Part 2 – Building Oracle 12c development suite – Installation of Oracle VirtualBox

Leave a comment

Installation of Oracle VirtualBox
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process of installing Oracle VirtualBox for installation of guest Operating Systems on your windows desktop or laptop.

NOTE: This suite can be installed on any type of OS, just download the appropriate VirtualBox software package for your base system.
Required files:
VirtualBox-4.3.20-96997-Win.exe
or
The latest version available at www.oracle.com
1. Logon to your windows desktop or laptop as an administrator.
2. Double click on the VirtualBox executable.
3. Click on Next button at the Welcome page.

4. Click on the Next button at the Custom Setup page.

5. Click on the Next button at the Custom Setup Options page.

6. Click on the Next button at the Warning Network Interfaces page.

7. Click on the Install button at the Ready to Install page.

8. After Installation completes, click the Finish button.

9. The administration console will open after installation is completed.

Larry Catt

Oracle

Part 1 – Building Oracle 12c development suite – Media packages required for installation of Oracle Enterprise Linux 6.5

Leave a comment

Media packages required for installation of Oracle Enterprise Linux 6.5
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the required packages to download from Oracle Corporation website to deploy a development site on your personal windows based desktop or laptop.

NOTE: This suite can be installed on any type of OS, just download the appropriate VirtualBox software package for your base system.
Required downloads:
VirtualBox-4.3.20-96997-Win.exe
V41362-01 – Oracle Linux Release 6 Update 5 for x86_64 (64 Bit)
1. If you do not already have an Oracle Corporation Developers logon, go to www.oracle.com and create an account.
2. Logon to the oracle website with your developers account and navigate the website to the latest Virtual Box download. As of this writing, the URL was: http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html?ssSourceSiteId=ocomen#vbox
3. Logon to the oracle website with your developers account and navigate the website to the latest Oracle Enterprise Linux download. As of this writing, the URL was: https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=17052719 Select the media package V41362-01 for Oracle Linux 6.5 Release Date: Dec – 10- 2013.
4. This are the two required packages to build an Oracle Linux system on your Windows based desktop or laptop.

Larry Catt

Oracle

Continue Oracle loop past exception

Leave a comment

Oracle provides for handling of error messages in programming blocks through the use of exceptions.    During the execution of a loop, the general exception clause at the end of a procedure, function or pl/sql block will exit the loop for handling of error, thus stopping the processing.    However, you can change this behavior by placing an exception clause within the loop block itself.    This procedure shows how to setup a loop with an exception clause, so the loop is not exited on error.  NOTE:  To just see the loop exception go to step 5.

  1. Create the following test table:

create table test_a(a number);

 

declare

n number:=1;

begin

while n<4

loop

insert into test_a(a) values(n);

n:=n+1;

end loop;

end;

/

 

 

  1. Now execute the following block to extract the data from table test_a

set serveroutput on

declare

n number;

begin

for rec in(select * from test_a)

loop

dbms_output.put_line(rec.a);

end loop;

end;

/

1

2

3

PL/SQL procedure successfully completed.

 

  1. Create an error by Insert the line “if mod(rec.a,2)=0 then n:=rec.a/0 else dbms_output.put_line(rec.a);” after the

 

set serveroutput on

declare

n number;

begin

for rec in(select * from test_a)

loop

if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;

end loop;

end;

/

1

declare

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

ORA-06512: at line 6

 

 

SQL>

 

  1. Now insert the exception “exception when others then null;” at the end of the block and we see that the exception is handled by the loop exited before giving us the value of 3.

SQL> set serveroutput on

declare

n number;

begin

for rec in(select * from test_a)

loop

if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;

end loop;

exception when others then null;

end;

/

1

 

PL/SQL procedure successfully completed.

 

SQL>

  1. Wrap the internals of the for loop in its own block and move the exception clause inside the block, now you see the exception is handled and the loop continues, giving us the the value of 3.

 

SQL> set serveroutput on

declare

n number;

begin

for rec in(select * from test_a)

loop

begin

if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;

exception when others then null;

end;

end loop;

end;

/

1

3

 

PL/SQL procedure successfully completed.

 

SQL>

 

  1. This completes handling of exceptions in a oracle loop which continues.

 

Larry Catt, OCP

Oracle

Determining the errors of a package body during recompile

Leave a comment

Oracle provides several views to find the exact errors created during recompile of a package or any oracle programming unit.  This procedure show one method of seeing the errors during recompilation of a package.

 

 

1.  Logon to your oracle server as the oracle software owner.

 

 

[root@mylinux ~]$ su – oracle

Password:

[oracle@mylinux ~]$

 

 

 

2.  Logon to Oracle SQL*PLUS as sysdba.

 

 

[oracle@mylinux ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 21 14:12:47 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

 

 

3.  Execute recompile of your package.  NOTE the error generated.

 

SQL> alter package larry.test_proc compile;

 

Warning: Package altered with compilation errors.

 

SQL>

 

 

 

4.  Execute a select against the table DBA_ERRORS referencing the name of your package with the following statement:

 

SQL> set linesize 120

SQL> column text format a50

SQL> select line, position, text from dba_errors where name=’test_proc’;

SQL> SQL>

LINE   POSITION TEXT

———- ———- ————————————————–

9         14 PL/SQL: ORA-00942: table or view does not exist

8          5 PL/SQL: SQL Statement ignored

23         18 PL/SQL: ORA-00942: table or view does not exist

22          9 PL/SQL: SQL Statement ignored

40         62 PL/SQL: ORA-00942: table or view does not exist

37          9 PL/SQL: SQL Statement ignored

51         14 PL/SQL: ORA-00942: table or view does not exist

50          5 PL/SQL: SQL Statement ignored

 

 

5.  In this example the error is that object being referenced does not exist.

The two main reasons for this error are:

–  The object really does not exist.

–  The user does not have access/permission to object.

 

6.  Find the line being referenced through access to table DBA_SOURCE with the following statement:

 

select text from dba_source where name=’test_proc’ and line in(9,23,40,51);SQL>

 

TEXT

————————————————–

 

 

FUNCTION get_results(v_ct1 NUMBER, v_ct2 NUMBER ) RETURN NUMBER;

 

FROM larry.test_tab

FROM larry.mv_test_tab

 

FROM larry.mv_test_tab

 

SQL>

 

7.  From this results we can see that the user account ‘test’ does not have access to the table larry.test_tab

and materialized view larry.mv_test_tab.  Grant permission for these objects and re-execute the compile command.

 

SQL> grant select on larry.test_tab to test;

 

Grant succeeded.

 

SQL> grant select on larry.mv_test_tab to test;

 

Grant succeeded.

 

SQL>

 

 

NOTE:  Repeat the process if more errors or generated or the original error is not fix.

 

8.  Re-execute compile command.

 

 

SQL> alter package larry.test_proc compile;

 

Package altered.

 

SQL>

 

 

9.  This completes user of Oracle tables to diagnosis problem with recompile of package or program unit.

 

 

 

Larry Catt

 

 

 

 

 

 

 

 

 

 

 

 

Oracle

Removal of Oracle Software and Database from Linux

Leave a comment

Remove an Oracle RDBMS from a configured system can be performed in three steps: Removal of existing RDBMS, removal of Oracle binaries, and finally removal of Oracle system references.  This procedure demonstrates the execution of this process.

 

  1. Logon to Linux as oracle software owner:

 

 [root@mylinux ~]# su – oracle

[oracle@mylinux ~]$

 

  1. Execute the following DBCA command to remove the existing database, you will need to know the information:  ORACLE_SID, User with SYSDBA privileges, and SYSDBA password.

 

 [oracle@mylinux bin]$ dbca -silent  -deleteDatabase -sourceDB orcl -sysDBAUserName sys -sysDBAPassword xxxxxx

 

Connecting to database

4% complete

9% complete

14% complete

19% complete

23% complete

28% complete

47% complete

Updating network configuration files

52% complete

Deleting instance and datafiles

76% complete

100% complete

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

[oracle@mylinux bin]$

 

  1. Remove Oracle Home directory as the root user.

 

[root@mylinux oracle]# rm -rf /opt/app/oracle/orcl_db

[root@mylinux oracle]#

 

  1. Remove Oracle storage directories if they exist.

 

[root@mylinux u01]# cd /u01/oradata

[root@mylinux oradata]#

 

  1. Remove Oracle system references in the /etc directory

 

[oracle@mylinux ~]$ rm -rf /etc/*ora*

[oracle@mylinux ~]$

 

 

  1. Remove Oracle Inventory.

 

 

[root@mylinux app]# rm -rf /opt/app/inventory_location/

[root@mylinux app]#

 

  1. This completes removing oracle software from a Linux environment.

 

 

 

 

Larry Catt, OCP

listener, network, Oracle

Oracle defining different name for SID and Database

Leave a comment

There are a ton of reasons to define a different name for Oracle SID and database, however you must ensure that your network configuration is properly setup to support this function. The key is defining the variable GLOBAL_DBNAME in your tnsnames.ora file to match your init parameter db_name, which allows various applications to include EM to resolve the database regardless of names defined.

TNSNAMES.ora for orcl on both SID and DB_NAME.


(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

TNSNAMES.ora for orcl on SID and ORACLE on DB_NAME.

(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(GLOBAL_DBNAME=ORACLE)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

Larry Catt OCP

Oracle, Oracle 10g

Testing Rate of Oracle inserts into a table

Leave a comment

You can use any type of table or any data to see the rate of insert you are getting out of your Oracle RDBMS.   For this demonstration, we used a test table named INSERT_TEST and repeating insert of ever changing record sets.    You can change the scripts attached to satisfy your particular needs.

 

1.  Create table to hold insert records.

create table insert_test( var_a varchar2(100), var_b number(10,1));

2.  Insert a single record into your test table.

insert into insert_test VALUES(‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’,999999999.9);

3.  Determine the size of row inserted.

SQL> select avg_row_len from dba_tab_statistics where table_name=’INSERT_TEST’; AVG_ROW_LEN

———–        

112

SQL>

4.  Edit the following script, replacing the record size with the actual size you received in step 3 above.

SET SERVEROUTPUT ON

DECLARE  

l_loops           NUMBER := 100000;

 l_start           NUMBER;

 total_time number;

v_ct  number:=0;

v_insert_ct number:=1.1;

v_var_a varchar2(100);

v_mb number;

v_rec_ct number;

record_size number:=112;  

BEGIN

   l_start := DBMS_UTILITY.get_time; 

  FOR i IN 1 .. l_loops LOOP

  v_insert_ct:=v_insert_ct+1; v_var_a:=’AAAAAAAAAAAAAAA’||v_insert_ct; insert into insert_test VALUES(v_var_a,v_insert_ct);

v_ct:=v_ct+1;

if v_ct>99

then

commit;

v_ct:=0;

 end if;

   END LOOP;   

 DBMS_OUTPUT.put_line(‘Execution Time   : ‘ ||(DBMS_UTILITY.get_time – l_start)||’  ms’); DBMS_OUTPUT.put_line(chr(10));

select round(100000/((DBMS_UTILITY.get_time – l_start)/100),2) into v_rec_ct from dual;

DBMS_OUTPUT.put_line(‘Number of Inserts per second: ‘||v_rec_ct||’ records’);

DBMS_OUTPUT.put_line(chr(10));

select round((100000/((DBMS_UTILITY.get_time – l_start)/100)*record_size)/1024/1024,2) into v_mb from dual;

DBMS_OUTPUT.put_line(‘MB of Inserts per second: ‘||v_mb|| ‘ MB’);

END; 

 /

5.  The result set below is returned, showing the insert rate achieved by your Oracle installation.   NOTE:  Multiple things will impact your insert rate, so for additional question leave a comment.

Execution Time   : 513  ms

Number of Inserts per second: 19493.18 records

MB of Inserts per second: 2.08 MB

PL/SQL procedure successfully completed.

SQL>

Larry Catt

Oracle, Oracle 10g, oracle 9i, script

Determining the record insert rate for a table

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

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

hp-ux, linux, Oracle

Translate UNIX / Linux EPOCH time to Oracle date

Leave a comment

Translate UNIX / Linux EPOCH time to Oracle date

Epoch time in computers is general defined as the number of milliseconds since 1 January 1970 and provides for a very useful way of determining times between system and other time specific functions.   However, it is very difficult for humans to read.   The following code will convert an Epoch time to Oracle date time format.

Given epoch time is 579052800000. we can retrieve oracle time as below.

SQL> define test_time=579052800000

SQL> select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual;

old   1: select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual

new   1: select  (TO_DATE’01-01-1970′,’MM-DD-YYYY’) + (579052800000 / 86400000)-1) from dual

(TO_DATE(

———

07-MAY-88

SQL>

and convert standard oracle date to epoch

 

select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

 

SQL> select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

TO_CHAR(SYSDATE-TO_DATE(’01-01-1970′,’MM-DD-YYYY’))*86400000 ————————————————————

1.3807E+12

SQL>

Larry Catt