Monthly Archives: June 2010

11g, Oracle, Oracle 10g, oracle 9i

Oracle UTL_RECOMP package and the RECOMP_PARALLEL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_PARALLEL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

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

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 19:58:23 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>

3. The RECOMP_PARALLEL subprogram unit recompiles all objects or objects within a schema using a degree of parallelism specified in the command or by the init parameter JOB_QUEUE_PROCESSES. This means that if you specified a degree of parallelism of 4, than Oracle would attempt to recompile 4 objects at a single time.

4. To compile all objects in the database with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2);

SQL> execute utl_recomp.recomp_parallel(2);

PL/SQL procedure successfully completed.

SQL>

5. To compile all object in a particular schema with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2, ‘SCOTT’);

SQL> execute utl_recomp.recomp_parallel(2, 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_PARALLEL.

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

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

Oracle – Encoding and Decoding Oracle data with UTL_ENCODE Package

Leave a comment

Oracle RDBMS provides the ability to encode and decode data through the package UTL_ENCODE. This functionality can be extremely helpful in the transmission of data over open networks where plan text transmission would not be wanted. In this article we will review the use of encoding plain text and decoding the string produced.

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 Wed Jun 30 20: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. Execute the following PL/SQL block to produce the encoded text string for: Test encode procedure.

set serveroutput on

declare
v_text varchar2(100):='Test encode procedure';
v_encoded_text varchar2(200);
v_ct number;
begin

v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS

SQL> declare
2
3 v_text varchar2(100):='Test encode procedure';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
VGVzdCBlbmNvZGUgcHJvY2VkdXJl

PL/SQL procedure successfully completed.

SQL>

4. The PL/SQL block executed generated the encoded version of ‘Test encode procedure’ as VGVzdCBlbmNvZGUgcHJvY2VkdXJl. Now we will decode the string with the text decode function.

set serveroutput on

declare

v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
v_encoded_text varchar2(200);
v_ct number;

begin

v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);

dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS:

SQL>
SQL> declare
2
3 v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
Test encode procedure

PL/SQL procedure successfully completed.

SQL>

The decode function displays the original text presented to the encode function.

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

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

Oracle NETCA – Network Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s NETCA (Network Configuration Assistant) is the GUI tool used to configure the network components of your Oracle RDBMS. In this article we will review the startup of the Network Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.


mylinux:> ./netca

6. The Network Configuration Assistant is self explanatory from here on. It allows the ability to configure, add, delete and modify you various Oracle networking components.

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

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

Oracle DBCA – Database Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s DBCA (Database Configuration Assistant) is the GUI tool used to create starter databases within the Oracle RDBMS. The DBCA utility was introduced in Oracle 8i and provides the user a GUI to eliminate the need for manual configuration of database creation scripts. In this article we will review the startup of the Database Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.

mylinux:> ./dbca

6. The Welcome screen will display and the wizard from here on is pretty self explanatory. The DBCA utility provides you the ability to Create Databases, Configure Database Options, Delete Databases, Manage Database Templates, and Configure ASM instances for storage.

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