Category Archives: Oracle

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

Oracle

Recreating OEM in 11g on MS Windows

Leave a comment

There are multiple reasons to recreate your Enterprise Manager installation on a Oracle RDBMS system. The following procedures provide the steps to accomplish this task on a MS Windows OS.

1. Remove SYSMAN from database if he exists by using emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -deconfig dbcontrol db -repos drop

EXAMPLE:
—————————————
C:\Windows\system32>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 20, 2013 10:38:10 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:38:43 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_38_10.log.
Apr 20, 2013 10:38:45 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 20, 2013 10:39:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Apr 20, 2013 10:40:29 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:40:30 AM

C:\Windows\system32>
——————————————-

2. Create OEM repository by using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -repos create

EXAMPLE:
——————————————-
C:\Windows\system32>emca -repos create

STARTED EMCA at Apr 20, 2013 10:42:48 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYS user: password

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:43:12 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_42_48.log.
Apr 20, 2013 10:43:14 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 20, 2013 10:45:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:45:30 AM

C:\Windows\system32>
——————————————-

3. Finally, configure EM Grid Control using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -config dbcontrol db

EXAMPLE:
——————————————-
C:\Windows\system32>emca -config dbcontrol db

STARTED EMCA at Apr 20, 2013 10:32:32 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_orcl ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl

Local hostname ……………. orcl_SERVER-PC.CATT.Net
Listener ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl
Listener port number ……………. 1521
Database SID ……………. orcl1
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:33:00 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_32_31.log.
Apr 20, 2013 10:33:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepo
sitory
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 20, 2013 10:33:41 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 20, 2013 10:33:43 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://orcl_SERVER-PC.CATT.Net:5501/em < <<<<<<<<<< Apr 20, 2013 10:34:38 AM oracle.sysman.emcp.EMDBPostConfig invoke WARNING: ************************ WARNING ************************ Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: C:/orac le/product/11.2.0/dbhome_orcl/orcl_SERVER-PC.CATT.Net_orcl/sysman/config/emkey. ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. *********************************************************** Enterprise Manager configuration completed successfully FINISHED EMCA at Apr 20, 2013 10:34:38 AM C:\Windows\system32>
——————————————-

4. Now attempt to access the EM console through the URL given in the INFO section of the last command. In this example, we access the EM console at URL:
https://orcl_SERVER-PC.CATT.Net:5501/em

Larry Catt, OCP

Oracle

ORA-27038

Leave a comment

This error is resulting from the recreation of a database in its original location without removal of all components.

CREATE DATABASE ORCL * ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00200: control file could not be created

ORA-00202: control file: ‘C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL’

ORA-27038: created file already exists

OSD-04010: <create> option specified, file already exists

 

Resolution:

Remove old control file from the original location and the flashback location.

error, Oracle

ORA-00382

Leave a comment

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

This error refers to the OS having limitation on block size selection:   General quide for block sizes per OS

AIX-Based Systems,Compaq Tru64 UNIX,HP 9000 Series HP-UX,Linux Intel,Sun SPARC Solaris, (Windows NT, 95, 98, 2000): Have a DB_BLOCK_SIZE capability of 2048 to 16384

Linux, Solaris,AIX, HP, Tru64: Have a DB_BLOCK_SIZE capability of 2048 to 32768